The MySQL slow query log is great for being able to tell you which queries are intensive on the CPU’s, which is most likely impacting your websites performance. When looking at the slow query log, it can appear like its an unorganized mess of random queries. So how do you summarize this list so you can know what queries to start fixing first? Welcome mysqldumpslow!
By default, MySQL comes with a tool called mysqldumpslow. Its primary purpose is to parse the slow query log and group similar queries together. This allows you to quickly see which queries need your attention in the slow query log.
If the slow query log is not already enabled, follow the older article I have here:
https://www.stephenrlang.com/2016/02/mysql-slow-query-log/
Assuming the slow query log is already enabled, locate the slow query log by running:
[root@db01 ~]# grep slow-query-log-file /etc/my.cnf slow-query-log-file = /var/lib/mysql/slow-log
Queries that return a lot of rows and run often are typically the cause of CPU contention within MySQL. These CPU heavy queries may be able benefit from either a LIMIT clause, a table index or just needs to be rewritten so it returns less rows.
To display the top 10 queries that returned the highest amount of rows, run:
[root@db01 ~]# mysqldumpslow -a -s c -t 10 /var/lib/mysql/slow-log
Sometimes you just want to see which queries are constantly in the slow query log. Many times this could be from something unexpected and easily fixable by the developers.
To display the top 10 queries without any other sorting options, run:
[root@db01 ~]# mysqldumpslow -a -s r -t 10 /var/lib/mysql/slow-log
The data returned will show:
Count - How many times the query has been logged Time - Both the average time and the total time in the () Lock - Table lock time Rows - Number of rows returned
Below is one entry returned by the mysqldumpslow command that should give you a rough idea what information will be presented to you:
[root@db01 ~]# mysqldumpslow -a -s c -t 10 /var/lib/mysql/slow-log ... Count: 4201 Time=68.34s (278868s) Lock=0.00s (0s) Rows=998512 (4194748912), root[root]@localhost SELECT PL.pl_title, P.page_title FROM page P INNER JOIN pagelinks PL ON PL.pl_namespace = P.page_namespace WHERE P.page_namespace = N ...