MySQL Tables Require Daily Repairs - Server, Table or HD? I've been experiencing a weird issue with one of MySQL DBs. Every day, sometimes 2-3 times per day, I'll need to repair the tables. The MySQL DB has 25 tables with 5.6m rows in total.

The bigger ones are:

Table A - 599k rows / 867MB
Table B - 2.1m rows / 146MB
Table C - 2.2m rows / 520MB


It seems table C needs to be repaired pretty frequently, Tables A &amp; B not as much.

When the table needs to be repaired, I'm not seeing it being marked as crashed or in use. But through other tools, I can see the data is not what it should be.

When I do repair the table, I'll see a message similar to:

[table c] repair info Wrong bytesec:  54-55-102 at 368251940; Skipped
[table c] repair warning Number of rows changed from 2127934 to 2127931


or

[table c] repair info Wrong bytesec:  171-30-101 at 341237312; Skipped
[table c] repair warning Number of rows changed from 1984585 to 1984582


I've tried making adjustments in my.cnf but no difference.

The server is a cloud server running both MySQL and Apache. Plenty of space available on all HDs:

Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda2             99G   14G   80G  15% /
tmpfs                 1.9G     0  1.9G   0% /dev/shm
/dev/xvda1             97M   49M   44M  53% /boot
/dev/xvdc1            296G   25G  257G   9% /data


I'm not sure if this is a problem with the cloud HD, the server or the tables themselves. The problem didn't start happening until about 2 months ago and the size of the DB has only changed by 300-400MB until now.

Any idea what I should be looking at to verify where the problem might be?

Using MySQL v5.1.66 and MyISAM

Thanks in advance.

Best, Cent
You should get away from MyISAM as soon as possible. Why ???


MyISAM caches only index pages in the Key Buffer
InnoDB caches Data Pages and Index Pages in the InnoDB Buffer Pool


I have mentioned this many times before


May 06, 2013 : MySQL high CPU usage (MyISAM table indexes)
Apr 08, 2013 : If MySQL isn&#39;t caching, then what is?
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Apr 04, 2011 : What are the main differences between InnoDB and MyISAM?


This is very critical because data pages must rely solely on the OS for cached writes to the .MYD of a MyISAM table. In a highly trafficked website, the OS will experience a glut in memory with swapping as a consequence and foregone conclusion. An OS crash or a mysqld crash  will yield corrupt MyISAM tables for every MyISAM table that was open and not fully flushed.   A crashed MyISAM table could simply be just a bad open file count in it header. In other case, it could be incomplete writes to the .MYD file. In the two repair messages in the question, you lost three row both times.

IMHO this problem is very apparent when running MySQL in Windows (Imagine : Windows handling disk writes ???) and no less different for Linux. MyISAM will suffer just the same.

I highly recommend three(3) things:


Switch to InnoDB
Increase installed RAM
Configure as much buffer pool as possible


Give it a Try !!!