08 November 2013

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
    

Contact me on Codementor


Some popular ones

My books recommendations

Great book for operations people. Helped me to design and build solid deployment pipelines. Awesome advices on automated testing as well. The author advocates against feature branches, every commit goes to master! Scary? I know, but it actually makes sense once you get the idea. Read the book to find out more.

One of those rare books where every word counts!

Classics from John Allspaw who is SVP of Infrastructure and Operations at Etsy (and used to work for Flickr). The book covers very important topics like metrics collection, continuous deployment, monitoring, dealing with unexpected traffic spikes, dev and ops collaboration and much more. Def recommend if you are starting out in the operations field or been doing it for a while ( in latter case you probably read this book already :).

This book is must read for every software engineer, no matter which language you use! It will change your perspective on writing code. I was amazed by the quality of material - very detailed and up to the point.

"The only way to make the deadline -- the only way to go fast -- is to keep the code as clean as possible at all times."


blog comments powered by Disqus