A better REPAIR TABLE for myisam tables (or for upgrading tables)
am 17.12.2010 17:37:42 von Hank--001636283ad213d2f404979dca4f
Content-Type: text/plain; charset=ISO-8859-1
I've posted a similar post in the past -- but there I was mucking around
with blank index files and frm files to fool myisamchk into repairing a
table.
But now I think I've come across a much better and more efficient way to do
a REPAIR Table in order to upgrade my database tables from Mysql 4.1.x to
5.5.8.
All this comes from the fact that REPAIR TABLE does not rebuild the table
indexes like myisamchk does, which is very unfortunate. Sure, REPAIR TABLE
works great for small tables, but if you have any tables of larger size
(millions of records or more, with multiple indexes), REPAIR TABLE can take
hours or days to do a simple repair/upgrade. And in most cases,
applications just can't be down for that long during an upgrade cycle (not
everyone runs a huge shop with multiple dev/test/upgrade/production
servers).
So here is what I have done, and propose this as a better REPAIR TABLE for
MYISAM tables (in pseudo code):
1. Retrieve the original CREATE TABLE DDL with "show create table SOURCE"
2. Modify DDL to change the table name to a new target table, let's call it
TARGET
3. Execute new DDL to create empty TARGET table
4. Run 'myisamchk -r --keys-used=0 TARGET' (to disable all index keys on
new table)
5. flush tables; lock table SOURCE read, TARGET write;
6. insert into TARGET select * From SOURCE;
7. flush tables; unlock tables;
8. 'myisamchk -prqn TARGET' (repair to re-enable all keys, do not modify
MYD table, use sorting, in parallel)
9. rename tables to replace SOURCE with TARGET
I've written a PHP script to do exactly this, and it works beautifully. My
source tables are mysql 4.1.x tables, and the target tables are now fully
5.5 compliant (verified with mysqlcheck --check-upgrade).
The best part is that for tables with 50 million short rows, it ran in 7
minutes, and a table with 30 million rows, it ran in 4 minutes.
I'm now running it on a table with over 200 million rows, and I expect it to
take an hour or so... but in all cases, doing a REPAIR TABLE on any of these
large tables would take days to complete.
So why can't the REPAIR TABLE command do something like this in the
background for large MYISAM tables?
-Hank
--001636283ad213d2f404979dca4f--