Embedded Files
Check
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.
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
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
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
Usage
The slow query log will contain the slow SQL (with any passwords suitably obfuscated) preceded by a line showing the following information...
Query_time: duration
Lock_time: duration
Rows_sent: N
Rows_examined: N
mysqldumpslow
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 Toolkitpt-query-digest
To report the slowest queries from slow.log...
pt-query-digest slow.log
Log File Management
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
FLUSH SLOW LOGS;
Closes and reopens any slow log file to which the server is writing.This operation requires the RELOAD privilege. (1)
Bibliography
Bibliography
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_long_query_timehttps://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_log_throttle_queries_not_using_indexeshttps://dev.mysql.com/doc/refman/5.7/en/slow-query-log.htmlhttps://stackoverflow.com/questions/2403793/how-can-i-enable-mysqls-slow-query-log-without-restarting-mysqlhttps://stackoverflow.com/questions/577339/truncate-slow-query-log-in-mysqlhttps://stackoverflow.com/questions/12737885/mysql-slow-query-log-is-logging-faster-querieshttps://www.thegeekdiary.com/mysql-what-is-the-slow-query-log-and-how-to-control-where-the-log-is-stored-and-what-is-logged/(1) https://dev.mysql.com/doc/refman/8.0/en/flush.html#flush-logs
mysqldumplsowhttps://dev.mysql.com/doc/refman/8.0/en/mysqldumpslow.htmlhttps://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
mysqldumplsowhttps://dev.mysql.com/doc/refman/8.0/en/mysqldumpslow.htmlhttps://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
Page updated
Google Sites
Report abuse