upgrading from 4.1 to 5.0 "trick"

--001485f78c426447e204720a5ac0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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

--001485f78c426447e204720a5ac0--

Re: upgrading from 4.1 to 5.0 "trick"

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgrading from 4.1 to 5.0 "trick"

--001485f6d9d07b4c790472a05148
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Aug 28, 2009 at 9:18 AM, Shawn Green 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.

--001485f6d9d07b4c790472a05148--