Analyzing WordPress database operations

People are using the Index WP MySQL For Speed WordPress plugin! That’s great. But it shoots into the dark: nobody can tell how much improvement they’ve seen except subjectively. That’s not good. It’s hard to improve something unless we can actually measure it.

Two things can make a troublesome query. If it runs just once but very slowly, it’s troublesome. And if it runs a lot and the aggregated time it takes is large, it’s troublesome.

So, let’s capture and measure troublesome queries. This is an idea for plugin code.

We’ll capture the queries used in each WordPress page load and their elapsed times. WordPress does this for us. Then we’ll log them, keeping track of how many times each query is used and how long it takes in aggregate.

Then, because we have access to the MySQL instance we can run EXPLAIN or even ANALYZE on the most troublesome queries and present the output to the site owner. Of course, most site owners will not know or care about database arcana, so we also need a simple way to ship the data to somebody who does understand it.

Here’s the proposed details of operation.

  1. Site owner installs and activates the plugin
  2. Site owner visits the plugin’s admin page and activates a monitor.
  3. The monitor runs and captures troublesome query information
  4. The site owner shows the results of the monitor.
  5. The site owner sends the monitor to someone who understands it (us).
  6. We use the information to refine Index WP MySQL For Speed.

A monitor has these characteristics.

  • A name
  • A description
  • A start time: we might want to run a monitor for a few minutes during a busy time on the site.
  • A duration
  • A random sampling factor, for use on very busy sites. For example we might grab queries from only 10% of the page loads.
  • Accumulated queries and their performance statistics

From the accumulated data in a monitor we can compute the overall weight of various queries and present the slowest of them. We could show the dirty dozen, or we could show all queries above the 95th percentile.

Dealing with repeated queries that are exactly the same is straightforward. For example, this one happens a lot in exactly this form. We can match each occurrence to the others.

SELECT option_name, option_value
FROM wp_options
WHERE autoload = 'yes'

But this one varies from page-view to page-view. So we’ll need some sort of injective hash function that can make variants of it map to the same statistics bucket.

SELECT post_id, meta_key, meta_value
FROM wp_postmeta
WHERE post_id IN (15866,15868,15877,15879,16355,16451,21285,22502,23204,23223)
ORDER BY meta_id ASC

Leave a Comment