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! Comment * Name * Email * Website
Δ
