Shulou(Shulou.com)05/31 Report--

How to enable and analyze the slow query log in MySQL, many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

Mysql has a feature that can record slow records of long-time queries, which can be configured by yourself, but slow queries are not necessarily bad SQL, may also be affected by other queries, or caused by system resource constraints, it is also our commonly used performance analysis tool. [related recommendation: mysql video tutorial]

MySQL's slow log is controlled by long_query_time and min_examined_row_limit, which means that if the query takes more than so many seconds, the MySQL server logs it to the slow log file with slow log enabled.

Enable slow query log

To enable slow query logging in MySQL, follow these steps:

Let's first take a look at the status of slow queries in MySQL.

MariaDB [(none)] > show variables like'% slow%' +- -+ | Variable_name | Value | +- -+- -+ | log_slow_admin_statements | ON | | log_slow_disabled_statements | sp | | log_slow_filter | admin | Filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table Tmp_table_on_disk | | log_slow_rate_limit | 1 | | log_slow_slave_statements | ON | | log_slow_verbosity | | | slow_launch_time | 2 | | slow_query_log | OFF | | | slow_query_log_file | hxl-slow.log | +-| -+- -+ 9 rows in set (0.001 sec)

First of all, let's look at the status of slow_query_log, which represents whether the slow query log is enabled. The value can be 0 or OFF to disable, 1 or ON to enable. The destination of the log output is controlled by the log_output system variable. If the value is NONE, it will not be written to the log even if the slow query log is enabled. The value of the log_output variable is as follows:

TABLE: (default) writes a general query to the mysql.general_ log table and a slow query to the table mysql.slow_log.

FILE: writes general and slow query logs to the file system.

NONE-disables logging.

There are also general_log_file and slow_query_log_file variables that represent the names of query logs and slow query log files. You can set these variables when the server is up or running.

Start slow query log

The slow query log feature is turned off by default in MySQL, so to turn on this feature, we need to set slow_query_log to ON, as shown below.

SET GLOBAL slow_query_log = 1

Set recording duration

The long_query_time variable indicates that only the run time exceeds this value will be recorded, as shown below, the change time is 5 seconds, he defaults to 10 seconds, and the minimum value is 0.

SET GLOBAL long_query_time = 5

Modify the output location

By default, the slow log file is located in / var/lib/mysql/hostname-slow.log, and we can also use the slow_query_log_file variable to set another location.

SET GLOBAL slow_query_log_file ='/ var/log/mysql/mysql-slow.log'

Write the slow query log to the table

The slow log can also be written into the slow_ log table by changing the log_output system variable to TABLE as mentioned above, as follows:

SET GLOBAL log_output='TABLE'

Variables related to slow query logs

Slow_query_log-enables / disables slow query logs

Log_output-how to write output

Slow_query_log_file-name of the slow query log file

Long_query_time-defines the time of slow query (in seconds / microseconds)

Log_queries_not_using_indexes-whether to record queries that do not use indexes

Log_slow_admin_statements-whether or not to record certain administrative statements

Log_slow_disabled_statements-types of statements that should not be recorded in the slow query log

Min_examined_row_limit-the minimum number of rows that a query must check to slow down

Log_slow_rate_limit-allows some slow queries to be recorded

Log_slow_verbosity-amount of detailed information in the log

Log_slow_filter-restrict the queries to be recorded

Analyze slow query

After logging, we also need to analyze these logs to find out the queries that affect the system. MySQL provides a tool called mysqldumpslow, which can simply display the results by passing the log path to him, as follows:

# mysqldumpslow-a mysql-slow.logReading mysql slow query log from mysql-slow.logCount: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=21.0 (21), Rows_examined=21.0 (21), Rows_affected=0.0 (0), root [root] @ localhost show databasesCount: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=2.0 (2), Rows_examined=2.0 (2), Rows_affected=0.0 (0) Root @ localhost select * from usersCount: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), Root [localhost select] @ @ version_comment limit 1Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), Root [root] @ localhost

His parameters are as follows

-s, sort it in what way. The default at, which is sorted by average query time. Are all arranged in reverse order.

Al: average lock time of average lock time

Average number of rows returned by ar: average rows sent

At: average query time of average query time

C: total number of count execution

L: total lock time of lock time

R: the total number of rows returned by rows sent

T: total query time query time

-t, show the top n queries, showing the number of top records

-a, this option is not enabled by default. Mysqldumpslow replaces the value of a similar SQL (string or number) with N, and when this option is turned on, the real value is displayed. Do not turn on this option, which is somewhat similar to the recording of Oracle's bound variables. \

-g, similar to the grep command, filters out the information you need. For example, only slow query records of table An are queried. \

-l, including the lock time in the total time

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.