Project:MySQL: Difference between revisions

Latest revision as of 16:34, 28 October 2024

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 |

@@ Line 1: / Line 1: @@
-== Heading text ==
+== How to use the slow query log ==
-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
+* For a permanent solution, edit ''my.cnf'' and set:
-slow_query_log_file = /var/lib/mysql/slow.log
+** slow_query_log = 1
-long_query_time = 1 #your time in seconds, the lower it is, the more will be logged
+** slow_query_log_file = /var/lib/mysql/slow.log
-For a temporary solution, log in to mysql as root and execute:
+** long_query_time = 1 ''#your time in seconds, the lower it is, the more will be logged''
-SET GLOBAL slow_query_log = 'ON';
+* For a temporary solution, log in to mysql as root and execute:
-SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';
+** SET GLOBAL slow_query_log = 'ON';
-SET GLOBAL long_query_time = 1;  #your time in seconds, the lower it is, the more will be logged
+** 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
+Then, go to location of slow log file and run ''mysqldumpslow ./slow.log'' to get a summary of the queries.
-to get a summary of the queries.
+Initial analysis:
+<syntaxhighlight lang=sql>
+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
+</syntaxhighlight>
+<syntaxhighlight lang=markdown>
+| 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 |
+</syntaxhighlight>