4

MySQL database (20 Gigabytes of tables and index) went corrupt. I launched the repair option using MySQL-admin graphic interface, now it has been running for 24 hours. When I have check the MyISAM options (buffers, threads, etc.) I realized that they are extremely low performance: 1 repair thread, 8 Mb of sort buffer...

My question is what is better: to stop the repair process and launch again with a better configuration, or keep it on running and wait. Is there any way to know what is doing the repair process or how much will it take?

1
  • Probably doesn't need to be CW.
    – Dan Carley
    Jul 1, 2009 at 11:42

6 Answers 6

6

You can keep track of the repair progress by watching the contents of your data directory; you'll see tables with names starting with # which are used during repair.

You can also check SHOW PROCESSLIST to make sure it's not doing a "Repair with keycache" because that is much worse than "Repair by sorting"

Repair is very slow with large tables; you should probably avoid very large tables, especially with lots of indexes, with MyISAM. Prefer to partition them instead to reduce the repair times. This may require a major change to your application code.

5

Basically there was a blackout and the database went corrupted.

I do not stop the repair process, but it seems to take a long. I hope it to be correctly restored. Anyway I have followed some indications for the future:

1.- Add to the my.conf file the following

[mysqld]
myisam-recover=backup,force 

With this the mysql will force a recover every time the myisam engine goes corrupt.

2.- Increase the number of threads for repair, and the size of the sort buffer.

3.- Do not use the graphical mysql-admin interface, use the command line. Basically because the command line mysqlcheck have a verbose option.

And of course do backup :-)

3

I would strongly recommend to NOT stop the process.

The 1 thread you see in the processlist is the way MySQL works and you can do damage to the existing database if you stop it in the middle.

Maybe you can give more information as to what happened to your database to get corrupted?

1

Hmm... as a starter for ten, you could confirm that the repair is actively running by executing "show processlist;" from a MySQL prompt.

(Probably not the answer you were looking for, but it's hopefully a step along the path.)

[Ah - beaten to it by MarkR.]

1

My repair was taking forever because the disk filled with the temp files. I didn't notice. My servername.err file in the mysql directory contained lines like this....

101104 5:43:24 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STiktcbP' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs

-1

check your php.ini time limit, it is very likely your script had stopped running after 300 seconds anyway! (you can also do phpinfo() and check the output)

you should do repairs on such a big DB only via command line mysql interface.

1
  • 1
    Thanks but I'm not using PHP, it is just the ubuntu mysql-admin graphic interface.
    – unknown (google)
    Jul 1, 2009 at 8:34

You must log in to answer this question.