Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Probably doesn't need to be CW. –  Dan Carley Jul 1 '09 at 11:42

6 Answers 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.

share|improve this answer

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

share|improve this answer

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?

share|improve this answer

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
1  
Thanks but I'm not using PHP, it is just the ubuntu mysql-admin graphic interface. –  unknown (google) Jul 1 '09 at 8:34

protected by Michael Hampton Mar 1 at 23:48

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?