Project:MySQL

How to use the slow query log

Set the slow query log to "on", by doing one of these things:

• For a permanent solution, edit my.cnf and set:
  • slow_query_log = 1
  • slow_query_log_file = /var/lib/mysql/slow.log
  • long_query_time = 1 #your time in seconds, the lower it is, the more will be logged
• For a temporary solution, log in to mysql as root and execute:
  • SET GLOBAL slow_query_log = 'ON';
  • SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';
  • SET GLOBAL long_query_time = 1; #your time in seconds, the lower it is, the more will be logged

Leave it running as long as desired.

Then, go to location of slow log file and run mysqldumpslow ./slow.log to get a summary of the queries.

Initial analysis:

SELECT /* MediaWiki\Deferred\LinksUpdate\GenericPageLinksTable::fetchExistingRows  */
    lt_namespace AS `ns`,
    lt_title     AS `title`
FROM `pagelinks`
         JOIN `linktarget` ON ((pl_target_id = lt_id))
WHERE pl_from = 12562861

| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |
| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
| 1 | SIMPLE | linktarget | index | PRIMARY | lt\_namespace\_title | 261 | null | 3733736 | Using index |
| 1 | SIMPLE | pagelinks | ref | pl\_target\_id | pl\_target\_id | 12 | my\_wiki.linktarget.lt\_id,const | 1 | Using index |