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

This article introduces the relevant knowledge of "the relevant parameters of the slow query log in mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Relevant parameters:

Slow_query_log

Whether to enable slow query log in ◦

Slow_query_log_file

◦ slow log file name. In my.cnf, we have defined it as slow.log. The default is machine name-slow.log.

Long_query_time

◦ sets a slow query threshold in seconds, and when the version > = 5.5.X, milliseconds are supported. For example, 0.5 is 500ms.

The ◦ is greater than the value, excluding the value itself. For example, if the value is 2, the SQL statement whose execution time is exactly equal to 2 will not be recorded.

Log_queries_not_using_indexes

◦ will not log the SQL of the index to the slow query log

◾ if the time-consuming SQL statements are not recorded at first because of the lack of data, the table lookup is fast, and the SQL may be executed for a long time when the amount of data is large

◾ needs to find problems in the testing phase to reduce the probability of problems after launch.

Log_throttle_queries_not_using_indexes

◦ limits the number of SQL statements that do not use indexes in the slow query log per minute; version > = 5.6.X

◾ may be executed repeatedly for a short time because the SQL that does not use the index. In order to avoid the rapid growth of logs, limit the number of records per minute.

Min_examined_row_limit

SQL whose ◦ scan records less than the modified value will not be recorded in the slow query log.

When ◾ is combined to record examples of SQL statements that do not use an index, it is possible that there is a table with a data volume of about a few rows and no index. Even if this kind of table is not indexed, the query is very fast, and the scan record is very small. If you are sure that there is such a table, you can set this parameter to not log the SQL to the slow query log.

Log_slow_admin_statements

◦ records timeout administrative operations SQL to slow query logs, such as ALTER/ANALYZE TABLE

Log_output

◦ slow log format: [FILE | TABLE | NONE]. Default is FILE; version > = 5.5

If ◦ is set to TABLE, it will be recorded to mysql.slow_log

Log_slow_slave_statements

◦ opens the slow query log from the server

Log_timestamps 5.7

◦ writes time zone information. UTC time or server local system time can be recorded according to requirements

Second, the use of mysqldumpslow tools

If you operate online, you don't need mysqldumpslow to scan the entire slow.log, you can go to tail-n 10000 slow.log > last_10000_slow.log (the number of 10000 is adjusted according to the actual situation), and then mysqldumpslow last_10000_slow.log

Third, the slow query log storage table

-- add log_output = TABLE to my.cnf, open the slow_query_log option, and then restart the database instance

--

Mysql > show variables like "log_output%"

+-+ +

| | Variable_name | Value |

+-+ +

| | log_output | TABLE |

+-+ +

1 row in set (0.00 sec)

Mysql > show variables like "slow_query_log"

+-+ +

| | Variable_name | Value |

+-+ +

| | slow_query_log | ON |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from mysql.slow_log

+- -+

| | start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |

+- -+

| | 2015-11-20 19 localhost 50 select sleep 28.574677 | root [root] @ localhost [] | 000.000306 | 00.000000 | 1 | 0 | 0 | 0 | 11 | select sleep (4) | 3 |

+- -+

1 row in set (0.00 sec)

Mysql > show create table mysql.slow_log

--

Table structure output omitted

The key sentence is as follows:

--

ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'-ENGINE=CSV uses the CSV engine here, and its performance is poor.

-- it is recommended to change the storage engine of slow_ log table to MyISAM

Mysql > alter table mysql.slow_log engine = myisam

ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled'-prompts me that I am logging and cannot be converted

Mysql > set global slow_query_log = 0;-- stop logging first

Query OK, 0 rows affected (0.01 sec)

Mysql > alter table mysql.slow_log engine = myisam;-- then convert the engine of the table

Query OK, 2 rows affected (5.05 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > set global slow_query_log = 1;-- enable logging again

Query OK, 0 rows affected (0.00 sec)

Mysql > show create table mysql.slow_log

--

Table structure output omitted

The key sentence is as follows:

--

ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'-- ENGINE becomes MyISAM

The advantage of using TABLE is that it is easy to query, but remember that when backing up, do not back up the tables of the slow log to avoid backing up too large.

It is also possible to use FILE, which needs to be cleared regularly to avoid a single file being too large.

This is the end of the content of "parameters related to the slow query log in mysql". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.