On Fri, Aug 28, 2009 at 9:18 AM, Shawn Green <shawn.gr...@sun.com> wrote:
> Hank wrote: > >> Hello All, >> I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS. >> I've been testing the "mysqlcheck --check-upgrade --auto-repair" >> command, >> and on one of my MYISAM tables, it's taking forever to upgrade the table. >> It has about 114 million rows, and I'm guessing it needs to be upgraded >> due >> to the VARCHAR columns. Anyway, it's been running for a day and a half, >> and >> I finally had to kill it. >> >> So will this old "trick" still work? I've done this many times on 4.1 >> with >> great success: >> >> In mysql 5.0 - I create two new empty tables, one identical to the >> original >> and one identical but with no indexes. I name these tables with "_ion" >> and >> "_ioff" suffixes. >> >> I then do a "insert into table_ioff select * from source" which inserts >> just >> the original data into the new table, but doesn't have to rebuild any >> indexes. I then flush the tables. >> >> Then in the file system, I swap the "table_ion.frm" and "table_ion.MYI" >> files with the table_ioff ones. Flush tables again. >> >> I then just use myisamchk -r to repair the index file. It runs in about >> an >> hour. >> >> Can I do this same thing to "upgrade" the tables, instead of using >> mysqlcheck, which seems to be rebuilding the table row-by-row, instead of >> sorting (which myisamchk does). >> >> thanks. >> >> -Hank >> >> > Hello Hank, > > Your technique will work within the following narrow limits of operation: > > * This will only work for MyISAM tables. > > * myisamchk is dangerous to run against any table that is in active use as > it operates at the file level and has caused corruptions with live tables. > Whenever possible either stop the server or prevent access from MySQL to > that table with a FLUSH TABLES WITH READ LOCK before using myisamchk. > > http://dev.mysql.com/doc/refman/5.0/en/flush.html > > Alternatively, you should be able to match or improve this "import then > index" process if you use an "ALTER TABLE ... DISABLE KEYS" command before > the import followed by an "ALTER TABLE ... ENABLE KEYS" command after the > import or if you use LOAD DATA INFILE ... . Also if you can import all of > the data to an empty table in a single batch (statement), the indexes will > be computed only once using the batch-index algorithm (it's a sort, not a > merge) and that will also save processing time. > > http://dev.mysql.com/doc/refman/5.0/en/alter-table.html > http://dev.mysql.com/doc/refman/5.0/en/load-data.html > > http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html > > The overall problem is still that the on-disk structure of the 5.0 tables > has changed and that you still need to perform some kind of dump-restore or > rebuild of the data as part of the conversion. > Warmest regards, > Shawn Green, MySQL Senior Support Engineer > Sun Microsystems, Inc. > Office: Blountville, TN > > > Hello Shawn, Thanks for your reply. Yes, I have all of your conditions covered. 1. They are myisam tables 2. This is not a production system, so other people aren't accessing the tables. 3. And your last comment about dump/restore is taken care of (in my original note) since I am creating a new table (without indexes) in mysql 5.0, and then inserting all the data from the old table into the new one. Then I'm swapping the MYI/frm files, and then rebuilding the new table. I've tested this several times now, and it works like a charm. Finally, I don't like to use the "ALTER TABLE DISABLE/ENABLE" statements, since they operate in silent mode -- I have no idea what it's doing, or how long to expect the process to take. It would be very nice of those commands had some built-in progress meter or feedback/callback method.
