Help more people learn by sharing this post!
MySQL – Slow query analysis
Ever wonder why your application is slow? Then there is a good chance you got slow queries! In this post, you will learn how to enable the slow query log and analyze it to find how what you need to optimize.
The mysql slow log
MySQL (and probably other databases as well) have this feature that allows you to log queries that take too long to execute. It’s disabled by default (in version 5.6) and to enable it we will need to edit the configuration file. Normally this file is located at /etc/mysql/my.cnf
We will add the following lines inside the [mysqld] block:
|
1 2 3 |
slow_query_log = 1 # Enable the slow query log, 0 to disable. long_query_time = 1 # Seconds until a query is considered slow. slow_query_log_file = /var/log/mysql/slow-query.log |
Make sure that /var/log/mysql exist, if not create it and change permissions so mysql can write to it. Next you will need to restart the server for the changes to take effect, if the server doesn’t come up you may have a syntax error in your config file.
If this is a dev environment you will need to stress you app a bit to generate some data and then you can jump to the next section.
Log analysis
You could just open the log directly, but it’s not the most pretty thing to look at, so we are going to use a tool from the Percona toolkit. You can either download the full toolkit or just the specific tool that you need.
|
1 2 |
wget http://percona.com/get/percona-toolkit.tar.gz wget http://percona.com/get/TOOL |
In this case, the tool we need is pt-query-digest which will parse the query log and show us some statistics and detailed info about our queries.
|
1 |
pt-query-digest /var/log/mysql/slow-query.log |
The top queries in the list are probably a good target for optimization.
But what if you can’t change your MySQL server settings? Well this tool has you covered! It has 2 other ways it can read queries from: tcpdump output and the ‘show process’ command.
To use the tcpdump output option you need to do this:
|
1 2 |
tcpdump -s 0 -x -nn -q -tttt -i any -c 1000 > tcpdump.output pt-query-digest --type tcpdump tcpdump.output |
And for the process option:
|
1 |
pt-query-digest --processlist h=host_name |
If you would like to learn more about MySQL administration and performance I would recommend checking out Percona’s site which has plenty of free resources to help you.
I hope you found that useful, please leave a comment if you have any questions or anything interesting to add.