4

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

Improve this question
5
  • are you seeing anything in the error log?
    – Derek Downey
    Apr 19, 2013 at 1:59
  • From the mysql.log, I see these: 130418 8:44:41 [ERROR] Got error 134 when reading table './[table c]' 130418 9:20:37 [ERROR] /usr/libexec/mysqld: Table '[table c]' is marked as crashed and should be repaired 130418 9:20:37 [ERROR] /usr/libexec/mysqld: Sort aborted 130418 10:04:38 [ERROR] Got error 134 when reading table './[table c]' 130418 11:08:40 [ERROR] Got error 127 when reading table './[table c]' 130418 12:30:05 [Note] Found 2127931 of 2127934 rows when repairing './[table c]'
    – CentDEV
    Apr 19, 2013 at 3:01
  • I know the 134 error is a corrupt table. 127 seems like an error reading the table. The problem happens when there's a lot of web traffic or very little. Sometimes a table will go corrupt, i'll fix it and it will get corrupt 20 mins later. Sometimes after repairing a table, it will be fine for 10-20 hrs. No specific reason that I can see. I'm wondering if it might be bad sectors on the cloud server HD but hoping these issues ring a bell with someone that may have had a similar problem.
    – CentDEV
    Apr 19, 2013 at 3:03
  • Have you any other kind of software running on this server that read the mysql files... like an antivirus or similar?
    – Cristian Porta
    Apr 19, 2013 at 19:21
  • 3
    Why use MyISAM - it is known to corrupt your data. Using InnoDB is a much better choice.
    – user1822
    Jul 28, 2013 at 8:29

3 Answers 3

Reset to default
3

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

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 !!!

Improve this answer
0

Antivirus or "Non-Conventional" Backup software has also often caused corruption.

Your first step is to try to find out what is changing the MySQL files.

Countinuous table repairing can only be a temporary workaround.

Improve this answer
0

what is frequently crushed in myisam table is it's index which you can see under database path as table_name.MYI , mysql use key_buffer_size to cache the myisam index . sometime when key_buffer_size is not sufficient, mysql can not load the whole needed part of index ,so table will be crushed , i think as first step you need to increase this buffer ,and be sure that you have sufficient RAM before change it find more details about key_buffuer_size in this link http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.