Enable mysql slow_query_log on the fly, no reboot required

I always forget how to do this, so finally decided to write it down (I’m talking about mysql > 5.1):

  1. Login to mysql with administrator rights
  2. Inspect current variables before changing those (optional):

     mysql> show variables like 'long_query_time' \G;
     *************************** 1. row ***************************
     Variable_name: long_query_time
             Value: 10.000000
     1 row in set (0.00 sec)
    
     mysql> show variables like 'slow_query_log' \G;
     *************************** 1. row ***************************
     Variable_name: slow_query_log
             Value: OFF
     1 row in set (0.00 sec)
    
     mysql> show variables like 'log_output' \G;
     *************************** 1. row ***************************
     Variable_name: log_output
             Value: FILE
     1 row in set (0.00 sec)
    
     mysql> show variables like 'slow_query_log_file' \G;
     *************************** 1. row ***************************
     Variable_name: slow_query_log_file
             Value: /var/lib/mysql/web01-slow.log
     1 row in set (0.00 sec)
    

  3. Adjust values to your needs, in my example I will be logging queries which took longer than 50ms

     mysql> set global long_query_time = 0.05;
     Query OK, 0 rows affected (0.01 sec)
    
     mysql> set global slow_query_log = 1;
     Query OK, 0 rows affected (0.04 sec)
    
     mysql> flush logs;
     Query OK, 0 rows affected (0.01 sec)
    

    Update: If you are runing Perocona server, you may want to do set global log_slow_verbosity = full; to get additional info for query analysis. Here is more detailed post about it.

    At this point your log file should start growing and getting data. Make sure you disable it after a while (depends on the workload), so you don’t run out of space if there are too many queries that fit into you time long_query_time span. Plus it will put more load on the server.

    To disable on the fly just run:

     mysql> set global slow_query_log = 0;
     Query OK, 0 rows affected (0.01 sec)
    

  4. Analyze resulted log file with mk-query-digest to find the most offensive queries and fix those

     mk-query-digest /path/to/slow.log
    

Wake up, Neo... The matrix has you...

I'm Sergey Khaladzinski (@gansbrest). Not so long ago, I decided to take my life to another level. If you'd like to follow my journey, feel free subscribe below to get periodic updates about my life and the new stuff I explore.

Don't be mad if you don't receive anything from me for a period of time. I'm still learning the process.. Powered by ConvertKit

Leave a Reply

Your email address will not be published. Required fields are marked *