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 & 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
Best Answer
You should get away from MyISAM as soon as possible. Why ???
I have mentioned this many times before
May 06, 2013
: MySQL high CPU usage (MyISAM table indexes)Apr 08, 2013
: If MySQL isn't caching, then what is?Sep 26, 2012
: Choosing MyISAM over InnoDB for these project requirements; and long term optionsOct 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:
Give it a Try !!!