21 December 2010

This is a blog item that needs to be converted to the new Bliki format

Killer query problem

We kept getting MySQL running at 100% CPU, making the site inaccessible for many minutes at a time! Whenever this occurred, logging in to MySQL and doing a show processlist command would reveal that a heavy DPL query was running. We had found previously (on 4 January 2008) that some kinds of DPL queries were extremely CPU intensive, but had removed them all from the site.

By checking the activity log for requests made by the same IP as that relating to the heavy query in the MySQL process list, I discovered that the problem was always being caused by link-crawler robots scanning through historical articles, so as a temporary solution I've disabled history for anonymous users.