mysql-Slow Query log

mysql-Slow Query log

tags (space delimited): MySQL

Turn on slow query log

慢查询是一种mysql提供的日志，记录所有执行时间超过某个时间界限的sql的语句。这个时间界限我们可以指定。在mysql中默认没有开启慢查询，即使开启了，只会记录执行的sql语句超过10秒的语句

[Way One]

Enter mysql:mysql-uroot-h127.0.0.1-proot# to view the parameters related to slow query show variables like ' slow_query% '; [Slow query is open and log location]show variables like ' long_query_time '; [Slow query time]mysql> show variables like ' slow_query% '; +---------------------+--------------------------------------+| variable_name | Value |+---------------------+--------------------------------------+| Slow_query_log | OFF | | Slow_query_log_file | /usr/local/mysql/var/cherry-slow.log |+---------------------+--------------------------------------+2 rows in Set ( 0.02 sec) mysql> Show variables like ' long_query_time '; +-----------------+-----------+| variable_name | Value |+-----------------+-----------+| Long_query_time | 10.000000 |+-----------------+-----------+1 row in Set (0.00 sec) # Open set global slow_query_log= ' on '; # Log storage location set global SLO w_query_log_file= '/usr/local/mysql/data/slow.log '; # Setting query time set global long_query_time=1;mysql> set global Slow_ Query_log = ' on ';Query OK, 0 rows affected (0.37 sec) mysql> set global slow_query_log_file = '/usr/local/mysql/var/cherry-slow.log '; Query OK, 0 rows affected (0.01 sec) mysql> Set global long_query_time = 1; Query OK, 0 rows Affected (0.00 sec)

[Way II]

# 修改配置文件vi /etc/my.cnf[mysqld]配置下    slow_query_log=ON    slow_query_log_file=‘/usr/local/mysql/data/slow.log‘;    long_query_time=1# 重启mysql服务service mysql restart

Test Slow Query

Select Sleep (2) [Execute a slow query SQL]

mysql> select sleep(2);+----------+| sleep(2) |+----------+|        0 |+----------+1 row in set (2.00 sec)

Benchmark (count,expr) [function to test the time required to perform the count expr operation]

mysql> select benchmark(900000000, 90000000*4);+----------------------------------+| benchmark(900000000, 90000000*4) |+----------------------------------++----------------------------------+1 row in set (10.80 sec)

Vi/usr/local/mysql/var/cherry-slow.log [view slow query log]

# Time: 180811 11:24:01# [email protected]: root[root] @ localhost []# Query_time: 5.000886  Lock_time: 0.000000 Rows_sent: 1  Rows_examined    : 0SET timestamp=1533957841;select sleep(5);# Time: 180811 11:34:16# [email protected]: root[root] @ localhost []# Query_time: 10.798234  Lock_time: 0.000000 Rows_sent: 1  Rows_examine    d: 0SET timestamp=1533958456;select benchmark(900000000, 90000000*4);

Slow query analysis

Show variables like '%general% '; [Information about the current database and version number]

Mysql> Show variables like '%quer% '; +-------------------------------+--------------------------------------+| variable_name | Value |+-------------------------------+--------------------------------------+| Ft_query_expansion_limit | 20 | | Have_query_cache | YES | | log_queries_not_using_indexes | OFF | | log_slow_queries | On | | Long_query_time | 10.000000 | | Query_alloc_block_size | 8192 | | Query_cache_limit | 1048576 | | Query_cache_min_res_unit | 4096 | | Query_cache_size | 8388608 | | Query_cache_type | On | | Query_cache_wlock_invalIdate | OFF | | Query_prealloc_size | 8192 | | Slow_query_log | On | | Slow_query_log_file | /usr/local/mysql/var/cherry-slow.log |+-------------------------------+--------------------------------------+ Rows in Set (0.00 sec) 1) The value of Slow_query_log is on to turn on the slow query log, and off is to turn off the slow query log. 2) The value of Slow_query_log_file is a slow query log to the file (note: The default name is host name. Log, the slow query log is written to the specified file, you need to specify the output log format for the slow query as a file, the related command is: Show variables like '% Log_output% '; To see the format of the output). 3) long_query_time Specifies the threshold for a slow query, which is a slow query statement if the time to execute the statement exceeds the threshold, and the default value is 10. 4) Log_queries_not_using_indexes If the value is set to ON, all queries that do not utilize the index are logged (note: If you just set log_queries_not_using_indexes to ON, Slow_ Query_log is set to OFF, this setting also does not take effect, that is, the setting takes effect if the value of Slow_query_log is set to ON), which is normally turned on temporarily when performance is tuned.

Show variables like '%log_output% '; [How to log Records]

mysql> show variables like ‘%log_output%‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    | FILE  |+---------------+-------+1 row in set (0.00 sec)默认记录方式是文本 可以修改成记录到数据表方式

Set global log_output= ' file,table '; [How to set up logging]

mysql> show variables like ‘%log_output%‘;+---------------+------------+| Variable_name | Value      |+---------------+------------+| log_output    | FILE,TABLE |+---------------+------------+1 row in set (0.00 sec)

Show global status like '%slow% '; [Number of statements that query the current slow query]

mysql> show global status like ‘%slow%‘;+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| Slow_launch_threads | 0     || Slow_queries        | 4     |+---------------------+-------+2 rows in set (0.00 sec)

mysql-Slow Query log