MySQL backups causing problems

Posted on

Lately I’ve been getting the occasional error from my WebFaction instance — alerting me that a running MySQL query had been shut down because it had been running for too long and was slowing the system. Here’s the offending SQL query:

SELECT /*!40001 SQL_NO_CACHE */ * FROM `phpbb_search_wordmatch`

Very mysterious. The offending table is one relating to my phpBB instance. I did not have any further information at hand to work out what was going on, and so my first port of call in attempting to resolve the issue was to adjust my phpBB search settings. However, a few days later, the error occurred again — back to the drawing board.

My next step was to inspect the source code of phpBB. However, I could not find anything that would produce SQL like the above statement.

And then it occurred to me. I started receiving the error messages about the same time as I set up a Cron database backup job. Sure enough, all the errors were coming through at around the same time of day, coinciding with when the backups were scheduled to run. A quick web search confirmed that running mysqldump against very large tables was a typical culprit for long-running SQL statements.

Once I knew the source of the error, the fix was very simple: Because the table in question is just an index table for the purposes of searching posts (i.e. not essential data to backup), it was just a case of adding --ignore-table=db.phpbb\_search\_wordmatch to the mysqldump command line in my cron job. Now, the search index table is skipped in my backups, resulting in smaller, faster backups, and no more error messages!

I couldn’t find much information about this problem specific to phpBB or Webfaction, so hopefully this helps someone else out!