Differences

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

--- mysql_slow_query [2016/02/01 16:42] – 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 full variables like "%slow%";
+show variables like "%slow%";
 </sxh>
 Or you can run the following from the command line:
@@ Line 24: / Line 48: @@
 </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
@@ Line 35: / Line 64: @@
 \\
 ----------------------------------------------------------------
+=== Configuring Slow Query logs ===
 \\
-There are now 2 options for configuring slow query logging. You can add the settings in the .my.cnf file or you can set the variables through the mysql command line.
+There are 2 options for configuring slow query logs:
-===Option 1===
+\\
+\\
+-** 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>
@@ Line 46: / Line 87: @@
 mysql -e 'show variables;' | grep slow
 </sxh>
+\\
-===Option 2 - .my.cnf ===
+=== /etc/my.cnf - Permanent Configuration ===
 <sxh bash>
 slow_query_log = 1
@@ Line 53: / Line 95: @@
 long_query_time = 10
 log_queries_not_using_indexes = 1
-Change permissions so that mysqld can write to the specified log file. Giving write permissions to the 'other' group should suffice.
 </sxh>
+**Note**: Change permissions so that mysqld can write to the specified log file. Giving write permissions to the 'other' group should suffice.