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

This article is to share with you what the MySQL slow log option parameters are. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

0 log_output

Enumerated type, dynamic parameters.

Used to set the output object for slow log and general log.

Can be set to none,table,file, which represents: do not output, save in table, save in file.

You can also combine settings:

Like SET GLOBAL log_output='table,file'.

Represents output to both the table and the file.

If you set SET GLOBAL log_output='none,file' or 'none,table' or' table,file,none' represents' none'

0 slow_query_log and slow_query_log_file

Slow_query_log Boolean, dynamic parameter, default is OFF.

Used to control whether slow log is enabled.

The slow_query_log_file dynamic parameter that specifies the name and path of the slow log file.

If not, the file name of slow log takes the default value of $host_name-slow.log and is stored under $datadir.

0 long_query_time

Dynamic parameter, the default value is 10.

Record the SQL for which the execution time (real time) exceeds this value.

0 log_queries_not_using_indexes

Boolean, dynamic parameter, default is OFF.

If on, it records all SQL of unused indexes, whether or not they exceed the value set by long_query_time.

Do not follow long_query_time.

Mysql > SET SESSION long_query_time=10000

Query OK, 0 rows affected (0.00 sec)

Mysql > CREATE TABLE test.slow (id int)

Query OK, 0 rows affected (0.03 sec)

Mysql > INSERT INTO test.slow SELECT 1

Query OK, 1 row affected (0.01sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql > SELECT * FROM test.slow WHERE id=1

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT start_time, query_time,lock_time,sql_text FROM mysql.slow_log

+-+

| | start_time | query_time | lock_time | sql_text | |

+-+

| | 2017-11-16 15 09JV 32.114488 | 00Rom 00VOR 00.000346 | 00VOR 00RO 00.000166 | SELECT * FROM test.slow WHERE id=1 |

+-+

1 row in set (0.00 sec)

At this point, the above SQL is recorded to slow log, and the setting of long_query_time is ignored.

Add an index and query again:

Mysql > ALTER TABLE test.slow ADD PRIMARY KEY Competition (id)

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > SELECT * FROM test.slow WHERE id=1

+-- +

| | id |

+-- +

| | 1 |

+-- +

1 row in set (0.00 sec)

Mysql > SELECT start_time, query_time,lock_time,sql_text FROM mysql.slow_log

+ -+

| | start_time | query_time | lock_time | sql_text | |

+ -+

| | 2017-11-16 15 09JV 32.114488 | 00Rom 00VOR 00.000346 | 00VOR 00RO 00.000166 | SELECT * FROM test.slow WHERE id=1 |

| | 2017-11-16 15-10 50. 196590 | 00-00 00.000437 | 0000-00 00.000148 | SELECT start_time, query_time,lock_time,sql_text FROM mysql.slow_log |

+ -+

2 rows in set (0.00 sec)

It can be found that the SQL of the second SELECT is not recorded to slow log.

As for the statement that queries slow log itself is logged to slow log, it is also because the SQL that looks up mysql.slow_log does not use the index.

Cases where slow log skyrocketed due to enabling this parameter:

Http://blog.itpub.net/29773961/viewspace-1811829/

0 log_throttle_queries_not_using_indexes

Integer, dynamic parameter, default is 0.

If log_queries_not_using_indexes is turned on

Then log_throttle_queries_not_using_indexes is used to limit the number of slow log recorded per minute.

A setting of 0 means "no limit".

0 log_slow_admin_statements

Boolean, dynamic parameter, default is OFF. Parameters added after 5.7.

Can be used to control whether slow log records the SQL of database management.

If enabled, these SQL are recorded.

The SQL for database management includes:

ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE .

Follow long_query_time.

The first time check table was executed, it took more than 2 seconds, but it was not recorded.

The second time to execute check table, open log_queries_not_using_indexes, more than 2 seconds, is recorded.

Mysql > SELECT @ @ long_query_time, @ @ log_queries_not_using_indexes

+-- +

| | @ @ long_query_time | @ @ log_queries_not_using_indexes |

+-- +

| | 2.000000 | 0 | |

+-- +

1 row in set (0.00 sec)

Mysql > CHECK TABLE test.t0

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | test.t0 | check | status | OK | |

+-+

1 row in set (4.28 sec)

Mysql > SET GLOBAL log_slow_admin_statements = 1

Query OK, 0 rows affected (0.00 sec)

Mysql > CHECK TABLE test.t0

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | test.t0 | check | status | OK | |

+-+

1 row in set (4.27 sec)

Mysql > SELECT start_time, query_time,lock_time,sql_text FROM mysql.slow_log

+-+

| | start_time | query_time | lock_time | sql_text | |

+-+

| | 2017-11-16 15 31VR 24.378343 | 00VOV 04.271940 | 00VOUR 00.000134 | CHECK TABLE test.t0 |

+-+

1 row in set (0.00 sec)

0 log_slow_slave_statements

Boolean, dynamic parameter, default is OFF. Parameters added after 5.7.

When enabled, log records exceeding long_query_time will be recorded on slave.

Even if this option is turned on, it will not take effect immediately, and the new changes need to take effect again after START SLAVE.

0 min_examined_row_limit

Integer, dynamic parameter, default is 0.

Setting this value means that sql with more than or equal to the number of rows returned will be recorded in slow log.

Mysql > SET SESSION long_query_time=0, SESSION min_examined_row_limit=5

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT * FROM test.t0 LIMIT 4

.

4 rows in set (0.00 sec)

Mysql > SELECT * FROM test.t0 LIMIT 5

.

5 rows in set (0.00 sec)

Mysql > SELECT * FROM test.t0 LIMIT 10

.

10 rows in set (0.00 sec)

Mysql > SELECT start_time, query_time,lock_time,sql_text FROM mysql.slow_log

+-+

| | start_time | query_time | lock_time | sql_text | |

+-+

| | 2017-11-17 16 088 14.851394 | 0000 FROM test.t0 LIMIT 00.000286 | 0012 0000 134 | 00 * 0000 5 |

| | 2017-11-17 16 SELECT 088 FROM test.t0 LIMIT 16.744389 | 0000 FROM test.t0 LIMIT 00.000284 | 000.000284 | 00 * 0000 135 | 00 * 0000 |

+-+

2 rows in set (0.00 sec)

Personally, I think it is not necessary to set this value deliberately in most scenarios, so it is fine to set it to 0 by default.

0 log-short-format

The default is FLASE, which is only a startup option and does not support the system variable.

If this option is activated, less information is recorded in the slow log.

0 log_timestamps

Enumerated, dynamic, appears after UTC,5.7.2 by default.

Thank you for reading! This is the end of this article on "what are the parameters of MySQL slow log options". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.