Slow query log of MySQL keeps track of queries that are taking too much time to execute. Value of a MySQL variable called long_query_time decides as to which queries are to be considered slow. You can change/set the value of this variable, but the default values are 1 second (minimum) and 10 seconds (maximum). That is to say that if any query will take more than 10 seconds –the SQL statement will be recorded in the slow query log.
You can enable logging of slow queries by using the following statement in MySQL:
SET GLOBAL slow_query_log = ‘ON’;
If you’re using MySQL version 5.1 or later –you would not need to restart the server. But if you’re using an earlier version, you would need to do a restart for the above statement to take effect.
NOTE 1: By default, slow query log does not record slow administrative SQL statements (e.g. ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX) –but you can change this by using the following option while switching on the slow query logging:
–log-slow-admin-statements
NOTE 2: Also, queries that don’t use indexes are not recorded but, again, you can change this by using –log-queries-not-using-indexes
NOTE 3: Queries in slow query log may contain passwords and therefore this log should be protected.
Slow queries should be analyzed in order to know why they are so slow. These queries are candidates for your attention when you are trying to enhance performance of MySQL. However, it might turn out to be a difficult task to analyze a huge log. You can use mysqldumpslow to produce a gist of your slow query log. You can use the following command on shell prompt:
shell> mysqldumpslow [options] [location of the log file]
Also there are some other tools that can analyze and sum-up the slow query log. Here is a list:
I hope it was useful. If you’re struggling to enhance performance of a MediaWiki installation, I also have a guide on how to increase performance of slow MediaWiki.
Thank you for using TechWelkin!
Leave a Reply