Regular issue with keys on temp tables
- by Christian
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.
