Hello, So I have this somewhat busy mysql box (300qps - 1100 qps) holding a bunch (20) of myISAM tables. The Box is Core2Duo 2.4 / 4GB RAM / Gentoo64 / MySQL 5.0.38 There are a number of scripts accessing those tables, both located on the same machine and on others.
Every day, 1 to 3 tables get corrupted. Some of these tables are accessed from the other servers, but some tables get corrupted which are only accessed locally too. Most of the time I do a REPAIR, I lose some rows in the battle. Imagine what the result is when some of those tables are like "customers" or contain other vital information. Sometimes, REPAIR returns that the table status is OK, when CHECK returns that the table needs to be repaired. In such cases, i need to shutdown the server, myisamchk -r the table, and restart mysql. Sometimes even that doesn't change the CHECK output, and i need somehow to re-run a REPAIR to see the table finally fixed. MYSQL docs state for possible reasons for corruption: [quote] 1- The mysqld process being killed in the middle of a write. 2- Unexpected shutdown of the computer (for example, if the computer is turned off). 3- A hardware error such as faulty memory/cpu/motherboard and or hard drive. 4- You are using an external program (like myisamchk) on a live table. 5- A software bug in the MySQL or MyISAM code[/quote] 1 -> tables get corrupted without killed processes 2 -> server never gets unexpectedly shutdown 3 -> my hosts' monitoring system says ahrdware is fine (although i don't know what that means). 4 -> never do that without shutting mysql down first 5 -> I'm using a somewhat recent version so if it was this it'd be really curious. The software I use is a popular one for which no such problems have been reported before, so I doubt it's query/PHP related. The fact it occurs specifically on one out of 7 machines I have running "similar" configurations makes me think about an environmental problem rather than an application-layer one. Some more info if that may help : Typical errors showing up (error codes vary as you can see): 070502 3:27:45 [ERROR] /usr/sbin/mysqld: Table 'my_table' is marked as crashed and should be repaired 070502 4:23:30 [ERROR] Got error 127 when reading table './mysite/my_table' 070502 17:55:58 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './mysite/my_table2.MYI'; try to repair it 070521 11:48:48 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './mysite/my_table2.MYI'; try to repair it 070521 11:48:53 [ERROR] Got error 134 when reading table './mysite/my_table2' 070521 22:54:41 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './mysite/my_table2.MYI'; try to repair it 070521 22:54:41 [ERROR] Got error 126 when reading table './mysite/my_table2' Another weird thing is that the errors do not always show up in the mysqld.err log file. Sometimes I get on-screen errors mentionning crashed tables, which are not reported in the log file, making me wonder how much more often it occurs. Would mysqladmin variables help helping me ? And now on to the questions : 1) Are there any known problems to running mysql 5 on gentoo 64 bits ? 2) Could delay_key_write ON be the reason for this corruption, or setting it off minimise the chances of it happenning ? 3) How can I debug / add logs to give me a clue about the corruption origin ? What would you do to find out the problem / solve it ? 4) Are there settings that minimize / maximize table corruption risks ? 5) I realized today that the "march" option in my /etc/make.conf file is set to "k8" (AMD64 processors), when I use Core2Duo (for which recommended "march" is "nocona"). Could having compiled mysql with this parameter be the reason for the table corruptions ? Thanks !
