ACZoom  ·  Archive-2016

A very small site started running into performance problems - some pages taking too long to load, and certain MySQL queries taking over 5000 to 6000 milliseconds, and being killed because of resource limits set on the hosting computer. The pages affected were the watchdog log display pages - one of which is the Menu -> administer page when logged in as the adminstrator, and it displays data from the watchdog table.

This seemed odd - for a site with less than 200 nodes, and very low traffic, there should be no performance issue, and no single database query should be taking as long as 6000 milliseconds.

So the options were to increase the time limit for queries, or to spend the time debugging the problem.

Drupal is very feature rich, and this may have negative impact on performance, but in this case, it turned out to be a database issue. The Drupal site has many performance related tips, including a subsection on Tuning MySQL for Drupal.

After looking around in the database for the site, it was discovered that the overhead for the watchdog table was over 40 times its actual size! So, the size was 176MiB, and the overhead was 172MiB. Running optimize on this table got the size down to under 4MiB, overhead to 0, and got the queries to be much faster - way below the 6000 millisecond time limit, and the administer and log display pages now rendered much faster, way below the old times.

One question remains - why did removing overhead fix the query times?

In terms of disk space or memory, 176MiB is not a large number. Secondly, as the OPTIMIZE TABLE Syntax page mentions, "for MyISAM tables, OPTIMIZE TABLE works as follows: If the table has deleted or split rows, repair the table", so this recovered all the overhead space. But it also mentions that deleted rows are kept in a separate list, and presumably this means that there is no way the number of deleted rows will impact running a SELECT command which only has to look at active rows.

So, cannot yet see why removing overhead leads to improved query performance times, especially for such a small table, with only 4MiB of active data. Maybe the indexing done by Optimize fixed the problem? But that would be Drupal issue, not a MySQL issue, since Drupal should keep the database indexes up to date.

In any case - as attested by the so many pages on the web that describe in detail how to optimize, it may be worth running optimize once a month or so even at small sites on the tables mentioned at the Tuning MySQL for Drupal page. For manual operation, phpMyAdmin can be used to run optimize - select all the tables that have overhead, and with selected, run the Optimize command.