User Tools

Site Tools


mysql_slow_query

Slow Query Logs

Checking the slow query logs via mysql prompt.
\\Note: You CAN enable the slow query logging on runtime. A mysql restart will flush the changes made via runtime. If you wish for persistence then add it to the /etc/my.cnf file:

mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /path/to/slow_query.log       |
+---------------------+-------------------------------+
Set the logs to another path:
mysql> SET GLOBAL slow_query_log_file = '/path/to/slow_query.log'; 
mysql> SET GLOBAL slow_query_log = 'ON';

Via the command line:

You can check to see if slow query logging is currently enabled. The following command assumes you have configured .my.cnf file:
show variables like "%slow%";
Or you can run the following from the command line:
mysql -e 'show variables;' | grep slow_query_log
Output should show something similar to:
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+---------------------+---------------------------------+
Or:
slow_query_log  OFF
slow_query_log_file     /var/run/mysqld/mysqld-slow.log

How many slow queries?

You can also use the following command to view if you currently have any slow queries (if logging has been enabled):

show status like '%slow%';
Or from the command line:
mysql -e 'show status;' | grep -i slow
This should produce a similar output:
Slow_launch_threads     0
Slow_queries    0


Configuring Slow Query logs


There are 2 options for configuring slow query logs:

- Run time - this will configure the slow query logs in the current runtime environment, when mysql is restarted the value will be lost. You will need to configure the value in the /etc/my.cnf for variables to remain

- Permanent - editing the /etc/my.cnf will allow mysql to retain the values once the service has been restarted. Changing this file will NOT change the current runtime environment.

NOTE: To enable to variables without restarting the service, use a combination of both techniques above.

Run-time Configuration


If run the following command it will set slow query logging without needing to restart mysql

mysql -e 'set global log_slow_queries = 1;'

You can now run the following command and it should produce an output saying that slow_query logging is enabled:
mysql -e 'show variables;' | grep slow

/etc/my.cnf - Permanent Configuration

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 10
log_queries_not_using_indexes = 1
Note: Change permissions so that mysqld can write to the specified log file. Giving write permissions to the 'other' group should suffice.

mysql_slow_query.txt · Last modified: 2024/05/23 07:26 by 127.0.0.1

Except where otherwise noted, content on this wiki is licensed under the following license: Public Domain
Public Domain Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki