MySQL Slow Query Log

Check

SHOW VARIABLES LIKE '%long_query_time%';

SHOW VARIABLES LIKE '%slow_query_log%';

SHOW VARIABLES LIKE '%log_slow%';

SHOW VARIABLES LIKE '%log_queries%';

SHOW VARIABLES LIKE '%log_throttle%';

The variables returned are:

long_query_time

slow_query_log

slow_query_log_always_write_time

slow_query_log_file

slow_query_log_use_global_control

log_slow_admin_statements

log_slow_filter

log_slow_rate_limit

log_slow_rate_type

log_slow_slave_statements

log_slow_sp_statements

log_slow_verbosity

log_queries_not_using_indexes

log_throttle_queries_not_using_indexes


Default is 10.000000

Default is OFF


Location of log file


Default is OFF




Allows a Replica to log slow queries



Default is OFF

Queries logged per minute. Default 0

The value of long_query_time is specified in seconds witha resolution of microseconds (i.e. up to 6 places after the decimal point).
If slow_query_log is OFF then long_query_time is ostensibly ignored.


Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE. 



The log_slow_slave_statements variable is only relevent if binlog_format=STATEMENT or binlog_format=MIXED



For log_throttle_queries_not_using_indexes, the default value of 0 indicates no limit.

Enable

You can use something like the following to start logging slow queries immediately without the need to restart the instance. But, you do also need to set the variable in the /etc/my.cnf or equivalent in order for it to survive a restart...

SET GLOBAL slow_query_log=ON;

SET GLOBAL log_queries_not_using_indexes=ON;

/etc/my.cnflog-queries-not-using-indexes = 1slow-query-log = 1slow-query-log-file = /mysql/data/mysql-slow.log

Disable

You can use something like the following to stop logging slow queries immediately without the need to restart the instance. But, you do also need to set the variable in the /etc/my.cnf, or equivalent in order for it to survive a restart...

SET GLOBAL slow_query_log=OFF;

SET GLOBAL log_queries_not_using_indexes=OFF;

/etc/my.cnflog-queries-not-using-indexes = 0slow-query-log = 0

Usage

The slow query log will contain the slow SQL (with any passwords suitably obfuscated) preceded by a line showing the following information...

The statement execution time in seconds.
The time to acquire locks in seconds.
The number of rows sent to the client.
The number of rows examined by the server layer (not counting any processing internal to storage engines).

mysqldumpslow

This summarises the content of the slow query log to make it easier to identify the queries to focus on...

mysqldumpslow mysql-slow.log

pt-query-digest

This command is part of Percona Toolkit

To report the slowest queries from slow.log...

pt-query-digest slow.log

Log File Management

To cycle logs, without logrotate, you will need to do something like this...

mv /mysql/data/mysql-slow.log /mysql/data/mysql-slow.log.old

install -omysql -gmysql -m0644 /dev/null /mysql/data/mysql-slow.log

mysqladmin flush-logs

Flush

FLUSH SLOW LOGS;

Closes and reopens any slow log file to which the server is writing.This operation requires the RELOAD privilege. (1)

Bibliography