How to enable the slow query log and identify slow queries with PerconaDB
The slow query log is the single most useful tool for finding which SQL statements are hurting your database. Edit your /etc/mysql/my.cnf file – the default file comes with a bug for setting up this slow query log, but find the line which starts log_slow_queries (it will be commented out probably). Do not uncomment it, instead use something like these values:
slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow.log
long_query_time = 1
log-queries-not-using-indexeslong_query_time can be a decimal if you wish – it is in seconds, but you can enter 0.1 or even 0 to just log everything.
Set long_query_time to 0 to capture every query for a short profiling window, then put it back to 1 second once you have a representative sample. Logging every query on a busy live server fills the disk quickly and adds its own overhead.
On current Ubuntu (24.04 LTS) with modern Percona Server or MySQL 8, my.cnf pulls in extra files from /etc/mysql/mysql.conf.d/. You can drop these settings into a file there instead of editing my.cnf directly, which survives package upgrades more cleanly.
You also need to create that log file, so use these commands to do that:
mkdir /var/log/mysql
touch /var/log/mysql/slow.log
chown mysql:mysql -R /var/log/mysqlNow restart MySQL/Percona using:
service mysql restartRun the pages on your site you wish to analyse – you can use a webcrawler if you like. Screaming Frog is great for this if you do not have a specific set of pages you are looking to test and just wish to test everything – download the free version, point it at your website’s home page and it will spider your site and hit a lot of pages. Or just visit whichever pages you know to be slow, or even just leave it running to capture your real users’ usage pattern and discover which queries are slow for them. Run this command to check your log file is filling up:
tail /var/log/mysql/slow.logYou will see some text that Percona is saving to the file. To analyse this file and get a nice hierarchy of results showing you which queries are having the most impact on your server, run this command:
pt-query-digest /var/log/mysql/slow.log(if you do not have it installed already, you will be told the command to install it – it ships as part of Percona Toolkit)
This will show you your league table of SQL statements with the one having the most negative impact on your server at the top. You can copy one of the examples of this query to your favourite MySQL client (MySQL Workbench, DBeaver, or just directly through SSH) and run the EXPLAIN command against it to identify which tables are being hit the most and where an index or query modification will help. For the full workflow of reading an EXPLAIN plan and choosing the right index, see how to analyse slow MySQL 8 performance.
Comments