converting to innodb with minimal downtime?

[Tim Smith]

Given a database with MyISAM tables, that one does not want to take offline
for more than is absolutely necessary, what's the quickest (in terms of
offline time) way to convert those tables to InnoDB?

The database I'm contemplating doing this on takes about 6 hours to load
into InnoDB tables from a mysqldump dump.

Is there anything faster/better than this method?

1. Get a snapshot of the database with mysqldump. Note the position in the
binary logs.

2. For each table foo, create an InnoDB table, new_foo, and restore new_foo
from the snapshot of foo.

3. Stop clients from accessing the database (either by unplugging the
network connection, or by restarting mysql on a port other then 3306).

4. For each table foo, rename foo to old_foo, and new_foo to foo.

5. Use mysqlbinlog to play back the binary logs from the time of step #1.

6. Plug the network cable back in, or restart mysql on the normal port
(depending on what was done in #3).

7. Drop old_foo, for each table foo.

Total unavailable time this way is about 10 minutes to get the snapshot, and
another few minutes to swap the tables and play back the logs, which beats
the heck out of 6 hours. Is there something even faster, or easier without
being much longer?

[Gordon Burditt]

Post by Tim Smith
Given a database with MyISAM tables, that one does not want to take offline
for more than is absolutely necessary, what's the quickest (in terms of
offline time) way to convert those tables to InnoDB?

Is the existing server capable of handling InnoDB tables?

If not, I suggest:
- set up for use with InnoDB in my.cnf files.
- If needed, replace mysqld with one compiled to use InnoDB,
preferably of the SAME version of MySQL.
- Shut down and restart the server.

Then, whether you had to upgrade the server or not, convert the tables.

Post by Tim Smith
The database I'm contemplating doing this on takes about 6 hours to load
into InnoDB tables from a mysqldump dump.

I suggest

ALTER TABLE foo ENGINE=InnoDB;

for each of the relevant tables. If your web site does not use
transactions (considering the existing tables are MyISAM, I hope it
doesn't), there's no need to lock out access at this point, but access
might be stalled while the ALTER TABLE is running.

Gordon L. Burditt

[Axel Schwenke]

Post by Tim Smith
Given a database with MyISAM tables, that one does not want to take offline
for more than is absolutely necessary, what's the quickest (in terms of
offline time) way to convert those tables to InnoDB?

If you're looking for zero-downtime, you're probably best off with a
replicate-to-an-InnoDB-incarnation-then-switch-over solution.

Post by Tim Smith
The database I'm contemplating doing this on takes about 6 hours to load
into InnoDB tables from a mysqldump dump.

Loading from dump is not particularly fast, because it is single-
threaded. Also some people have AUTOCOMMIT on - this dramatically
slows down the load. Another recommendation would be to use the --opt
switch for mysqldump.

Post by Tim Smith
Is there anything faster/better than this method?
1. Get a snapshot of the database with mysqldump. Note the position in the
binary logs.
2. For each table foo, create an InnoDB table, new_foo, and restore new_foo
from the snapshot of foo.
3. Stop clients from accessing the database (either by unplugging the
network connection, or by restarting mysql on a port other then 3306).
4. For each table foo, rename foo to old_foo, and new_foo to foo.
5. Use mysqlbinlog to play back the binary logs from the time of step #1.
6. Plug the network cable back in, or restart mysql on the normal port
(depending on what was done in #3).
7. Drop old_foo, for each table foo.

Alternative:

1. setup another MySQL instance on another port
2. get a clean dump from your database, record the binlog position
at dump time (you can have mysqldump flush the binlog for you)
3. load the dump into the second MySQL instance
4. convert the tables to InnoDB (ALTER TABLE ... ENGINE=InnoDB)
5. check your InnoDB tables for desirable schema changes
- add foreign key constraints
- avoid non-numeric primary keys
6. turn on replication to get all updates to the InnoDB tables
7. wait until the replica is up-to-date
8. stop all writes to the master database (READ LOCK all tables), wait
until the slave database is up-to-date
9. shutdown both MySQL instances
10. start the InnoDB instance on the default port

If properly done, steps 8-10 should take only a few seconds. You may
want to try that a few times.

Post by Tim Smith
Total unavailable time this way is about 10 minutes to get the snapshot, and
another few minutes to swap the tables and play back the logs, which beats
the heck out of 6 hours. Is there something even faster, or easier without
being much longer?

Some more ideas

- if you backup your database regularly, you can get away with
restoring a backup to the InnoDB instance, then apply the binlog
(point in time recovery)

- you can use mysqlhotcopy to clone your MyISAM tables. This should be
much faster then dump/reload. You don't even need to copy the index
files (they can be regenerated with REPAIR TABLE ... USE_FRM)

- you may want to have different settings in my.cnf for the InnoDB
instance. You may even want to have different settings during the
clone phase and then later for production.


HTH, XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

[Tim Smith]

Post by Axel Schwenke

Post by Tim Smith
The database I'm contemplating doing this on takes about 6 hours to load
into InnoDB tables from a mysqldump dump.

Loading from dump is not particularly fast, because it is single-
threaded. Also some people have AUTOCOMMIT on - this dramatically
slows down the load. Another recommendation would be to use the --opt
switch for mysqldump.

I do use --opt with mysqldump.

When I tried a load with AUTOCOMMIT off, a couple hours in it was killed. I
didn't save the logs from this, unfortunately, but will be trying again.

Post by Axel Schwenke
1. setup another MySQL instance on another port
2. get a clean dump from your database, record the binlog position
at dump time (you can have mysqldump flush the binlog for you)
3. load the dump into the second MySQL instance
4. convert the tables to InnoDB (ALTER TABLE ... ENGINE=InnoDB)

I've tried ALTER TABLE, and the result is a few hours in, it gets killed,
with a very large amount of stuff in the logs.

Since I'm just starting to investigate switching these tables to InnoDB, I
haven't spent any time tuning my InnoDB setup, so it is whatever it is
out-of-the-box on SuSE 9.2, so it's quite possible that some tunable
parameter is set waaaaaaay too low for my needs, so I'm not too concerned
about this yet. (And this is Mysql 4.0.21, because that's what comes with
this version of SuSE, so I could be hitting bugs that have since been
fixed...).

Post by Axel Schwenke
5. check your InnoDB tables for desirable schema changes
- add foreign key constraints
- avoid non-numeric primary keys
6. turn on replication to get all updates to the InnoDB tables
7. wait until the replica is up-to-date
8. stop all writes to the master database (READ LOCK all tables), wait
until the slave database is up-to-date
9. shutdown both MySQL instances
10. start the InnoDB instance on the default port
If properly done, steps 8-10 should take only a few seconds. You may
want to try that a few times.

In broad outline, that's essentially the same as my scheme, right? The
basic idea is get a second copy of the tables somewhere (in the same
database my way, in another database under another server your way), and use
the binary logs to catch up (using mysqlbinlog my way, replication your
way), and then swap the tables (rename my way, switch servers you way).

What I like about your way is that the conversion work is done on a second
server, so if there are any problems, they don't affect the first server.


...

Post by Axel Schwenke
Some more ideas
- if you backup your database regularly, you can get away with
restoring a backup to the InnoDB instance, then apply the binlog
(point in time recovery)
- you can use mysqlhotcopy to clone your MyISAM tables. This should be
much faster then dump/reload. You don't even need to copy the index
files (they can be regenerated with REPAIR TABLE ... USE_FRM)

That's an interesting idea I hadn't thought of. For many tables, the index
files are almost as large, or even larger than the data files, so not
copying indexes could reduce downtime quite a bit.

[Axel Schwenke]

Post by Tim Smith

Post by Axel Schwenke
Loading from dump is not particularly fast, because it is single-
threaded. Also some people have AUTOCOMMIT on - this dramatically
slows down the load.

When I tried a load with AUTOCOMMIT off, a couple hours in it was killed.

Do you set innodb_buffer_pool_size? The default of 8MB is *very*
small and will cause failure of bigger transactions. There should
be a clear message in the error log if this happens.

Post by Tim Smith

Post by Axel Schwenke
4. convert the tables to InnoDB (ALTER TABLE ... ENGINE=InnoDB)

I've tried ALTER TABLE, and the result is a few hours in, it gets killed,
with a very large amount of stuff in the logs.

Same thing.

Post by Tim Smith
In broad outline, that's essentially the same as my scheme, right? The
basic idea is get a second copy of the tables somewhere (in the same
database my way, in another database under another server your way), and use
the binary logs to catch up (using mysqlbinlog my way, replication your
way), and then swap the tables (rename my way, switch servers you way).

The difference is, that replication works online. Under normal
conditions a slave instance is less than a second behind the master.
So you don't have to wait for the InnoDB instance to catchup with
updates collected during table conversion.

Post by Tim Smith
What I like about your way is that the conversion work is done on a second
server, so if there are any problems, they don't affect the first server.

It's still running on the same hardware. You may wish to have more
memory. Bulk loading as well as table conversion profits much from
a lot of memory. In your case you should increase the InnoDB buffer
pool size as much as possible. InnoDB has to (re)create all indexes,
so big buffers will save a lot of I/O.

If you have a spare machine, you can install MySQL (but *exactly* the
same version) and do the conversion there. Then copy the InnoDB table-
space (ibdata + iblogs) back.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/