Should I stop MySQL repair?

Question

MySQL database (20 Gigabytes of tables and index) went corrupted. I launched the repair option using MySQL-admin graphic interface. Now it has been running for 24 hours. When I have checked the MyISAM options (buffers, threads, etc.) I have 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?

Thanks

Answer 1

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.

Answer 2

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?

Answer 3

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.]

Answer 4

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 :-)

Answer 5

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.

Answer 6

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