Differences

This shows you the differences between two versions of the page.

--- mysql_slow_query [2015/04/05 12:05] – created luke7858
+++ mysql_slow_query [2024/05/23 07:26] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+=== 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:
+<sxh bash>
+mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%';
++---------------------+-------------------------------+
+| Variable_name       | Value                         |
++---------------------+-------------------------------+
+| slow_query_log      | ON                            |
+| slow_query_log_file | /path/to/slow_query.log       |
++---------------------+-------------------------------+
+</sxh>
+Set the logs to another path:
+<sxh bash>
+mysql> SET GLOBAL slow_query_log_file = '/path/to/slow_query.log';
+</sxh>
+<sxh bash>
+mysql> SET GLOBAL slow_query_log = 'ON';
+</sxh>
+\\
+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:
+<sxh bash>
+show variables like "%slow%";
+</sxh>
+Or you can run the following from the command line:
 <sxh bash>
 mysql -e 'show variables;' | grep slow_query_log
 </sxh>
 Output should show something similar to:
+<sxh bash>
++---------------------+---------------------------------+
+| 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 |
++---------------------+---------------------------------+
+</sxh>
+Or:
 <sxh bash>
 slow_query_log  OFF
 slow_query_log_file     /var/run/mysqld/mysqld-slow.log
 </sxh>
+\\
+=== 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):
+<sxh bash>
+show status like '%slow%';
+</sxh>
+Or from the command line:
+<sxh bash>
+mysql -e 'show status;' | grep -i slow
+</sxh>
+This should produce a similar output:
+<sxh bash>
+Slow_launch_threads     0
+Slow_queries    0
+</sxh>
+\\
+----------------------------------------------------------------
+=== 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
+<sxh bash>
+mysql -e 'set global log_slow_queries = 1;'
+</sxh>
+\\ You can now run the following command and it should produce an output saying that slow_query logging is enabled:
+<sxh bash>
+mysql -e 'show variables;' | grep slow
+</sxh>
+\\
+=== /etc/my.cnf - Permanent Configuration ===
+<sxh bash>
+slow_query_log = 1
+slow_query_log_file = /var/log/mysql/slow.log
+long_query_time = 10
+log_queries_not_using_indexes = 1
+</sxh>
+**Note**: Change permissions so that mysqld can write to the specified log file. Giving write permissions to the 'other' group should suffice.