We run a large forum with lots of reads and writes, particularly to the posts
and topics
tables which are both innodb.
Last week I started doing 12 hourly backups with innobackupex because mysqldump just takes forever (7+ million rows in posts
table.) It seems that something doesn't like these backups because I have a recurring problem every other day.
The symptoms;
The front page of the site starts throwing errors
The logs start showing errors like Error: 126 - Incorrect key file for table '/tmp/mysql/#sql_4e87_14.MYI'; try to repair it
The /tmp/ dir fills up and we start getting Error: 1030 - Got error 28 from storage engine
in the logs.
The only way to fix is to optimize table
on each of the posts and topics tables.
I'm trying all I can to stop MySQL using disks for temp tables, but I'd have more problems than this if it used all my memory also.
My my.cnf is here; https://gist.github.com/cbiggins/0aa26f6defb7a14541d7
The box has 32GB memory and I don't come near that usually. Currently at 15GB use.
Thanks in advance.
Update 1: Despite the conf looking like there is replication, there isn't. This is a stand alone instance.
Update 2: Having now not done backups for more than 24hrs, the problem has just occurred again. So this is not the result of the backups.
Update 3: I have given MySQL 20gb of temp space now using tmpfs. Instructions here. Going to watch for the next little while and see how it goes.
Update 4: I found a killer query! 13 seconds and 2.3 million rows examined. Do this 20 times concurrently and I was filling up my new 20GB temp dir pretty quickly. I've disabled the block that was using this query and provided some feedback to the maintainer.
I've decided to get a super cheap dedicated server to replicate to to run backups from. Hopefully we can see my uptime climb again. :)