Mysql – How to speed up MySQL table repairing process

MySQL

guys. I'd really appreciate to have your support on this one, as it's driving me crazy…

I have a corrupt table on a MySQL database and I've been trying, unsuccessfully, to repair it on the past few days. The table has 1,2 Gb of information.

I've detected the corruption of the table using "phpMyAdmin" and so I asked it to REPAIR TABLE.
This was on July 20th, 14h47m (GMT). It is now July 22nd, 23h28m (GMT) and the process isn't finished, although my WHM "MySQL Process List" shows:


Time – State – Info

203558 – Repair by sorting – REPAIR
TABLE xyz


(yes, it has been running for more than 200.000 seconds by now…)

The query I executed, that led to this state, was:


SET @@session.myisam_sort_buffer_size := 67108864;

SET @@session.read_buffer_size := 524288;

SET @@session.read_rnd_buffer_size := 524288;

SET @@session.sort_buffer_size := 8388608;

SET @@session.key_buffer_size := 8388608;

SET @@session.tmp_table_size := 67108864;

— Execute query

REPAIR TABLE xyz;

SET @@session.myisam_sort_buffer_size := DEFAULT;

SET @@session.read_buffer_size := DEFAULT;

SET @@session.read_rnd_buffer_size := DEFAULT;

SET @@session.key_buffer_size := DEFAULT;

SET @@session.sort_buffer_size := DEFAULT;

SET @@session.tmp_table_size := DEFAULT;


I've read on some other sites that I should use larger buffer size to speed up the repair process, so I opted to use larger values than my MySQL default values, that I list below:

DEFAULT VALUES


key buffer size: 8,388,600

myisam sort buffer size: 8,388,608

read buffer size: 131,072

read rnd buffer size: 262,144

sort buffer size: 2,097,144

tmp table size: 33,554,432


I'm not familiarized with shell access to the server, so that's not a real option to start with.

What do you suggest to help me speed up the repairing process? Should I terminate the current REPAIR TABLE process? What should I do?

Thanks in advance for your support.

Best Answer

Is your harddrive full ?

The repair procedure will need at least twice as much space as the original table, if there's not enough room it'll just sit waiting for enough space. Repairing a 1.2Gb table should be very fast.