Drupal MySQL Performance Problem

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.


Thank You!

I actually ran into your old post in a hosting zoom forum and followed it here. I still consider myself a newbie and I'm definitely still learning, so a lot of what I read on SQL databases still goes a bit over my head. But I wanted to say THANK YOU! I was looking everywhere for why my pages were loading so slowly (or not at all) and when I optimized the watchdog table everything just went so smoothly. It definitely set me off on the right track!


same here

I was having problems with storage space actually, and since I'm no mysql expert, it took a while for me to realize that all my databases were 500MB in size, and that the overhead on drupal's watchdog was 1GB! Optimize did the trick, thank you very much.

And sorry for my broken English. ;)

Use the cron

You should have a cron job calling [base_url]/cron.php on a regular basis. This will clean up your watchdog and sessions.
As well as other maintenance tasks