MySQL – How to Fix Terribly Slow Disk Read When Repairing MyISAM Table

myisamMySQL

So I've launched the myisamchk utility with repair option on all tables within the database.

Smaller tables (1GB max) got fixed in no time, virtually seconds. However, my largest table (9GB) takes forever (20mb/min) to fix. My server is down because of that.

I've checked 'iotop -ao' and this is what i got:

IOTOP dump

Whats going on here? The read of 300 KB/S is VERY HIGH in this screenshot, i was aiming at capturing the super-fast write. Sometimes the read spikes to 5-10 MB/s, but for the rest of the time its within 0-50KB/s range.

The table has only several columns, 2 indexes (1) Long, (2) Varchar(72). Mysql 5.5.56, CentOS 6. Its doing repair by sort.

Best Answer

To anyone whom it may concern;

The problem of such slow disk read lies in the fragmentation of the data. A subsequent repair of the same table gives very high disk read i/o, indicating sequential access as opposed to random access.

The table has plenty of deleted rows, and the newly inserted records must then be read in a random fashion, resulting in slow read speeds. Again, after the table was repaired, the records where stored sequentially, and the same repair operation gave very fast results. I guess this is another reason to use InnoDB over MyISAM.

Thanks, Michael.

Related Solutions

Mysql – Repairing Myisam Table when there was no additional Disk space & Table corrupted

If you cannot free up or add any disk space, you could copy the table_data.* files (MYD,MYI,frm,etc) to another machine with plenty of free disk, run the repair there with myisamchk, then copy the files back to the original machine. If the server needs to stay running, do a FLUSH TABLES WITH READ LOCK table_data before copying the data to the other server and UNLOCK TABLES after you copy the data back. You will need to keep the FLUSH TABLES ... session running for this duration.

So, it would look like this

server1:

# keep this session running for the duration of the repair!
mysql> FLUSH TABLES WITH READ LOCK table_data;
/var/lib/mysql# rsync -aP table_date.* server2:/somedir/

server2:

# make a backup
/somedir# tar -czvf table_data_backup.tgz table_date.*
# run the repair
/somedir# myisamchk -r table_data
# copy the files back
/somedir# rsync -aP table_date.* server1:/var/lib/mysql/

server1:

# make sure the permissions are correct on /var/lib/mysql/table_date.*
mysql> UNLOCK TABLES;

Remember, do not close the FLUSH TABLES WITH READ LOCK session.

You may want to give myisamchk additional memory for the following parameters to speed it up: --key_buffer_size --sort_buffer_size --read_buffer_size --write_buffer_size

Next, convert your table to InnoDB. There's almost no good reason to use MyISAM these days.

Mysql – Can MySQL 5.1 read a MySQL 5.5 MyISAM file

What could go wrong? If the way MyISAM changed the format of how something is stored in their files, it will break on 5.1. This will render your stats database on the master corrupt and unusable.

Even if the initial load went fine, it doesn't mean something won't come along later with the result listed above...a corrupt database. It's a risk, and if it can be avoided, I personally would do so.

And in your case, it can be avoided by dumping the data using mysqldump quickly and then load into the 5.1 instance.

Best Answer

Related Solutions

Mysql – Repairing Myisam Table when there was no additional Disk space & Table corrupted

Mysql – Can MySQL 5.1 read a MySQL 5.5 MyISAM file

Related Question