Assistance Needed with Table Corruption

[shawnpaolo]

Hello Gentlemen,

I’ve got several tables getting corrupted at a regular basis.
These tables range from 1000+ rows to 5M rows. All the errors are corrupted indexes. I am not sure if it is hardware or mysql.
Can you give me any suggestions on what might be causing this?

Here are the specs:

Software

OS Fedora Core 5 64-bit
DB MySQL 5.0.27 x64
25 databases
latin_swedish_ci
37M total rows
4GB total data size
2.5GB total index size

NOTE: table corruption happens only on several tables which are very frequently accessed/updated. All are MyISAM. I converted one large table to innodb and it has stopped from being corrupted regularly. I am not sure with this solution as InnoDB wont be as fast as MyISAM, and I need the tables to be updated and viewed very quickly.

Hardware

Athlon 64 X2 dual core processor 4200+
2 200GB SATA on RAID1 as main partitions
1 250GB IDE as backup partition
4GB RAM

I do a daily check on the tables but sometimes they crash during operating hours and have to repair them.
These crashes never happened with mysql4.1. The hardware was recently upgraded.

Any suggestions would be appreciated.

Just tell me if anyone needs any additional info.

Thanks in advance.

[inner]

Hello, shawnpaolo

I’ve been using the same software configuration on the other hardware and I found Fedora Core 5 to be the one to blame. Since the server was very critical (and a dedicated MySQL server), I did not have a chance to make further investigations so I simply downgraded the system to the old good Fedora Core 2 and haven’t seen a crashed MyISAM table since (for about half a year now). As for MySQL, I used the same rpms from dev.mysql.com for both fc5 and fc2.

If you wonder why I moved right to the bottom line (that is fc2, but not fc3 or fc4), the answer would be short - it was the only fully supported (FS) x86_64 Linux distribution out of those we could’ve used (a list goes here: http://www.mysql.com/support/supportedplatforms/oem.html) However, for more widely used server, I would really recommend trying fc3 or even fc4, as these seem to be pretty stable as well.

[B]shawnpaolo wrote on Fri, 15 December 2006 04:52[/B]

Hello Gentlemen, I’ve got several tables getting corrupted at a regular basis. These tables range from 1000+ rows to 5M rows. All the errors are corrupted indexes. I am not sure if it is hardware or mysql. Can you give me any suggestions on what might be causing this? Here are the specs: Software OS Fedora Core 5 64-bit DB MySQL 5.0.27 x64 25 databases latin_swedish_ci 37M total rows 4GB total data size 2.5GB total index size NOTE: table corruption happens only on several tables which are very frequently accessed/updated. All are MyISAM. I converted one large table to innodb and it has stopped from being corrupted regularly. I am not sure with this solution as InnoDB wont be as fast as MyISAM, and I need the tables to be updated and viewed very quickly. Hardware Athlon 64 X2 dual core processor 4200+ 2 200GB SATA on RAID1 as main partitions 1 250GB IDE as backup partition 4GB RAM I do a daily check on the tables but sometimes they crash during operating hours and have to repair them. These crashes never happened with mysql4.1. The hardware was recently upgraded. Any suggestions would be appreciated. Just tell me if anyone needs any additional info. Thanks in advance.

[shawnpaolo]

inner,

Sorry for the long reply. I was busy with the servers this week.

Thanks for the tip.

I forgot to tell you that I have slave servers on FC5 and mysql5.0.27 x64 and they have never had any table corruptions.
The only difference was that the slaves where using single cores and had IDEs instead of SATAII raids.

The DB is stable for now. All I did was to change the concurrency of the main DB from 50 to 5… and viola! no more crashes up to this day for three days. I’m not sure yet if this is the real solution.
Hopefully the DB will stabilize up to the weekend, otherwise, FC4 to the rescue.

I have a question though, would raising the concurrency introduce race conditions or the possibility of records overwriting each other?

Thanks again and a Merry Christmas to everyone!