What's faster than REPAIR?

[Sarah Sproehnle]

After Chris' great post, What's faster than ALTER, I got thinking about REPAIR. REPAIR TABLE can be slow, but may seem necessary if your table is corrupt.

A little background:
MyISAM tables are prone to corruption if they are receiving updates/inserts/deletes/etc and your server crashes. Doing a REPAIR TABLE can generally "fix" them, but it can take time. The larger the table, the longer it takes. How long? Well, a rough approximation that I use is:

Size of table	Approximate time
KBs	N seconds
MBs	N minutes
GBs	N hours

So a several-hundred GB table can take a long time (several days) to repair.

What's the alternative? If you have a backup of the table, you can restore it. That means copy the backup of the .frm, .MYD and .MYI files into the database directory. Then you'll want to restore the activity since your backup. You may be able do this with the binary logs, however, there is no option for restoring just a single table from the binary log. There's a good reason why MySQL didn't include this option. Tables are not always updated in seclusion from one another. You might have multi-table updates or queries such as INSERT..SELECT. But, if you are confident that it would be safe to extract just the DML for this table and re-execute it, then you could use grep. See Beat's blog post on this.

The restore is likely to be much faster than a REPAIR table.

[Chris]

Hey Mark, Great comments here and this is getting interesting... So I got to thinking last night what was the largest MyISAM installation I have run. Here are some specs, 100GB - 350GB data set ALL myisam. 1 database per three node cluster times 10 - 15 clusters, all local storage RAID 5 with 16GB RAM. When working in this environment I dreaded knowing that one day something would break or get corrupted.

Sure enough, it happens, corrupted myisam table, thank god it was a small one at 73GB. Given that I had the third node (glad I put those in!) in the cluster there I felt that this would be a good opportunity to try different techniques.

Step 1 for the experiment:
Take a snapshot of the system so I can at least roll back to this point and test again.
Test 1:
I started with repair table. Two weeks later it had not finished so I killed the process and rolled back with the snapshot. This also almost doubled the table size on disk.
Test 2:
I tried to restore the table from backups and binlogs. Fortunately, this table was almost fully ramped up so there was not much data in the binlogs. This process worked in about 18 hours worth of manual loading, grepping, awking and waiting.
Test 3:
Rebuilt just that one table with a very similar process to, "What's faster than ALTER." That worked great because any holes in the table were gone and I ended up reclaiming about 8GB of space on disk. Also only had to scp 61GB or so of a CSV file dump instead of the whole data set.
Test 4:
My favorite, the easy way, Just shutdown the other slave and run a binary copy to the broken server. So 4 hours later on a Gig pipe the corrupted slave was on its way back up and replicating

In the end I would NEVER use repair in this situation! I am still of the opinion that there are just better ways to do rather than repair! That said, REPAIR still has its place in "my" world of MySQL for small myisam tables (MBs || < ).

With all said and done all that matters in the end is:
1. Is the data correct? Cause you can still lose rows with repair.
2. Did the customers experience more than your SLA stated downtime?
3. How long did it take you (operations) to fix the issue? If it's more on the two week side there are other problems to deal with.

One thing to note, REPAIR is not used for INNODB tables at all! Woohoo for ACID!

[Mark R]

If your table fits in ram, or rather, the maximum number of tables you're repairing in parallel fit in ram, REPAIRs are fairly fast.

If it doesn't, they aren't. This is because to rebuild the secondary indexes it needs to do multiple table scans of the (newly rebuilt) data file. If the table fits in ram, the 2nd and subsequent scans are quick, if it doesn't, they involve heaps of bulk IO and are very slow.

However, either of them is going to be faster than a "Repair with keycache", and also a binlog replay (unless your table hardly changed single the backup).

The trick to using MyISAM is therefore to partition your data into partitions which are sufficiently small that their data files individually fit in ram (ideally with at least 1 index as well).

I don' t have enough experience with InnoDB with large tables to know how it compares (other than knowing that the repair is necessary far less often)

[Sarah Sproehnle]

Good comments Chris. InnoDB certainly can give you less (or different) headaches! I've seen some perfect scenarios for MyISAM, but it's not the regular case. For example, smallish "data warehouses" are well-suited to MyISAM. Often they do no updates, only bulk loads, and they like the small footprint that MyISAM offers. Plus MyISAM is good at full table scans if you need them.

Another note about REPAIR. As Mark R alluded to, fast disk and lots of RAM helps, but REPAIR can still take a long time and uses twice the space while it repairs. You can help make it faster by increasing myisam_sort_buffer_size.

[Chris]

This is a very interesting problem and I have always avoided this situation by using INNODB. MyISAM, as far as I'm concerned, is only good for a few things. 1. KB or low MB in size tables with 95% or more reads but a boat load of them, and 2. the mysql database in every installation.

Some would disagree and say, "what about full text searching?" I would answer, use sphinx! There are some other reasons why not to use MyISAM, but if you do, make sure you have the best point-in-time recovery! You should should also consider having more than one replica so you don't have to rebuild from backups and binary logs.

Although I believe that Sarah's process is sound and can be done with a little automation, personally I would rather just use INNODB or, if I had MyISAM, have at least two replicated servers.

Cheers

[Sarah Sproehnle]

Mark R, You bring up a good point. This is not going to be faster in all cases! If you have a lot of inserts or updates to that table since your last backup, replaying the bin logs could take awhile. But, if this table is updated infrequently as many MyISAM tables are (excluding logging apps), than this could be significantly faster. You mentioned that you "might not get the same data". True, but same for REPAIR ;)

[Mark R]

I am not convinced that replaying the binary logs would be faster than a repair.

You might not get the same data, for a start. Secondly, you'll end up doing normal inserts rather than a "repair by sorting" index rebuild, normal inserts are generally slower than "repair by sorting"

Do you have any evidence that this is actually faster in real cases? I'm talking about machines with loads of ram and fast discs here.