How to analyse slow MySQL 8 performance

• Author
• Recent Posts

Talk to me

Dave Hilditch

Founder at WP Intense

Author of the WPI performance guides and creator and coder of the plugins sold by WP Intense.

Talk to me

Latest posts by Dave Hilditch (see all)

• Running huge imports with WP All Import reliably (yes, even if you use Cloudflare!) - September 19, 2023
• New SQL enhancement for Scalability Pro to fix WooCommerce long-running query in the product-hero block - August 31, 2023
• Create a static favicon.ico to avoid surplus PHP requests for rush traffic - July 25, 2023

You can chat to all our authors on our Discord

I’ve written in the past about building incredibly fast WordPress stacks, and in our stack maintenance guide I covered a great script to help you optimise your MySQL 8 configuration.

In this guide, I’ll focus on how to record slow SQL queries with MySQL 8 and then how to install and use tools to tell you the source of these slow queries. I’ll also cover various optimisation techniques you can use to conquer the most common MySQL performance issues.

Enable your slow query log

In order to analyse slow queries, you first need to enable your slow query log. Presuming you have a standard MySQL 8 setup, here’s how you would do that. First, you need to create a log folder that MySQL has permissions to write to.

mkdir /var/log/mysql
touch /var/log/mysql/slow.log
chown mysql:mysql -R /var/log/mysql

Next, you need to edit your mysqld.cnf file. Based on our stack guide, this will be located in the following folder:

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Once you have that file open, add the following lines to the end:

slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow.log
long_query_time = 1
log-queries-not-using-indexes

To deconstruct that, the first line above enables the slow log, the 2nd line tells MySQL where to write the slow log, the 3rd line is how long (in seconds) is considered slow and the final line will make MySQL record queries not using indexes.

Once you’ve edited your configuration, you’ll need to restart MySQL.

service mysql restart

You should leave your site running for a day or two to gather normal website traffic so you have something useful to analyse.

Installing the Percona Toolkit to analyse your MySQL 8 logs

The Percona Toolkit includes a tool called pt-query-digest which can analyse the log files generated by MySQL 8.

apt install percona-toolkit

To run the pt-query-digest, run the following command once your slow.log file has enough entries to be useful (a day or two of data should be enough).

pt-query-digest /var/log/mysql/slow.log

Analysing the pt-query-digest output

Once the pt-query-digest is complete, it will provide two sections in its analysis. The first is a ranking league table of your slowest queries affecting your server the most, and the second section goes into more detail for each of these query patterns. Here’s an example of the output:

In the example above, you can see 76% of the slow queries are against the wp_wsal_metadata table. These clues are incredibly useful for optimising your server.

Optimising your database after analysing pt-query-digest

The easiest approach is to eliminate plugins that are using up your server resource. In this example, to discover the plugin which is referencing the wp_wsal_metadata table, you can either google wp_wsal_metadata or run a grep to search for this code in your plugin files.

cd /home/826397.cloudwaysapps.com/vaxvhpbyme/public_html/wp-content/plugins
grep wsal_metadata . -R

In the above, I change directory to the plugins folder and then grep for wsal_metadata – note: it’s important to drop wp_ from your search since this prefix is appended by WPDB.

In this case, the WP Security Audit plugin is at fault so I simply removed it.

If you discover slow queries inside plugins that you need to keep, you should try and figure out indexes that can be created to optimise these queries.

Covering query optimisation using indexes is too big an area for this article, but generally these rules help:

1. If you have a highly selective WHERE clause, there should be an index on that column
2. If you have a JOIN between two tables, there should be indexes on those columns on both tables
3. Where possible, you should use covering indexes

Searching for slow queries inside slow.log

Once you have your league table, you’ll have a search pattern to search for. In addition to performing a grep on your wp-content/plugins folder, you can download your slow.log file and search inside that to find specific queries that were actually slow against your database.

Here’s a screenshot of what I found before I disabled the WP Security Audit plugin:

Rewriting Queries

For backwards compatibility, WordPress WP_Query uses LEFT JOINS a lot rather than INNER JOINS. It also uses GROUP BY statements to ensure you don’t get repeating posts. This is a lot slower than using WHERE EXISTS.

If it’s possibly for you, you can rewrite the queries to use custom SQL queries rather than using WPDB. Again, this is too big an area to go into in this article, but our Scalability Pro plugin covers adding indexes and rewriting the most common slow queries to make them fast, including optimising WooCommerce and your wp-admin area.

Provide your slow queries here and I’ll show you how to optimise them

Once you’ve run your pt-query-digest and discovered slow queries, if you’re struggling to optimise the SQL queries, provide the query here in the comments and I’ll give you directions to optimise those queries using indexes and by rewriting the SQL code.