Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use the mysql slow query log, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand how to use the mysql slow query log.

The mysql slow query log is a kind of logging provided by mysql, which is used to record SQL statements executed by sql for more than long_query_time seconds. The minimum and default values for long_query_time are 0 and 10, respectively. It means to run a sql statement for more than 10 seconds. This value can be specified as microseconds. For recording to a file, the write time includes the microsecond part. In order to record to the table, only integer times are written. The microsecond part is ignored. By default, slow query logs are disabled and have to be opened manually.

Related parameters

Slow_query_log [= {0 | 1}]: whether to enable slow log. 0: disable, 1: enable.

Long_query_time: slow query threshold. Log is recorded when the query time exceeds the set threshold.

Slow_query_log_file: set log targets. If you do not set this parameter, the system will default to a default file host_name-slow.log

Log-output [= {FILE | TABLE}]: specify whether the log is saved to a file or a table.

Log_queries_not_using_indexes: queries that do not use indexes are also recorded in the slow query log (optional).

Log-slow-admin-statements: include slow management statements (optional) in the statements written to the slow log.

Min_examined_row_limit: checking that queries with fewer than this number of rows are not logged in the slow query log.

Slow query configuration

Mysql > show variables like'% slow_query_log%' +-+ | Variable_name | Value | +-+- -- + | slow_query_log | ON | | slow_query_log_file | / usr/local/mysql/var/huosuSDK-slow.log | +- -+ 2 rows in set (0.00 sec)

The value of slow_query_log: OFF means slow query is disabled, and ON means slow query is enabled.

Set global slow_query_log=1

Slow query is enabled globally, but if mysql is restarted, it will be invalid. If you want to take effect permanently, you have to configure it under / ect/my.cnf

Log-output=FILE saves files slow_query_log=1 starts slow query long_query_time=2 time 2 seconds log_queries_not_using_indexes=1 does not have an index also records

Then restart mysql.

If you want to query how many slow query records there are, you can use the system variable.

Show global status like'% Slow_queries%'

You can also use tools provided by officials to analyze slow queries.

Mysqldumpslow

Mysqldumpslow parses the MySQL slow query log file and prints a summary of its contents.

In general, mysqldumpslow group queries are similar except for specific values of numeric and string data values. When the summary output is displayed, these values are "extracted" to N and "S". The-an and-n options can be used to modify the value abstraction behavior.

Call mysqldumpslow like this:

Shell > mysqldumpslow [options] [log_file...]

Mysqldumpslow supports the following options.

Format Description

-a Don't abstract all numbers into N and string S

-n at least an abstract number that specifies a number

-- debug writing debugging information

-g only consider statements that match the pattern

-- help displays help and exits

-h hostname of the CVM in the log file name

-I name of the CVM instance

-l do not subtract the lock time from the total time

-r reverses the sort order

-s how to sort the output

-t displays only the first numeric query

-- verbose detailed mode

-- help

Display help information and exit-a

Don't abstract all numbers into N and the string S--debug,-d

Write debugging information-g pattern

Only queries that match (grep-style) patterns are considered. -h host_name

The hostname of the MySQL CVM is-slow.log file name. The value can contain wildcards. The default value is (match all). -I name

The name of the CVM instance (if mysql.server startup script is used). -l

Do not subtract the lock time from the total time. -n N

There are at least N digits in the abstract number. -r

Reverse the sort order. -s sort_type

How to sort the output. The value of sort_type should be selected from the following list: t, at: sort l by query time or average query time, al: sort by lock time or average lock time r, ar: sort by rows sent or average rows sent c: sort by quantity

By default, mysqldumpslow sorts by average query time (equivalent to-s at). -t N

Only the first N queries in the output are displayed. -verbose,-v

After reading the above about how to use mysql slow query log, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.