I have recently become responsible for a LAMP site which has a decent
MySQL install (v5.0.24a). The database is around 40GB with a single
master to single slave replication scheme, although all activity goes to
the master at this time, with the exception of backups which are taken
from the slave.

I have several tables which are fairly large, one has 120 million
records, and I need to migrate these tables to InnoDB from MyISAM to
reduce the number of table locks that occur on a daily basis which bring
down the site's performance.

What is the best way to perform this migration? Should I simply take an
outage and alter table to set the engine type to InnoDB, or should I
rename the table, and select into a new table?

What are the upper limits of MySQL performance in terms of data set size
using MyISAM vs InnoDB?

TIA,
--
Josh Miller, RHCE

joshua@itsecureadmin.com (Josh Miller) writes:

> I have recently become responsible for a LAMP site which has a decent
> MySQL install (v5.0.24a). The database is around 40GB with a single
> master to single slave replication scheme, although all activity goes
> to the master at this time, with the exception of backups which are
> taken from the slave.
>
> I have several tables which are fairly large, one has 120 million
> records, and I need to migrate these tables to InnoDB from MyISAM to
> reduce the number of table locks that occur on a daily basis which
> bring down the site's performance.
>
> What is the best way to perform this migration? Should I simply take
> an outage and alter table to set the engine type to InnoDB, or should
> I rename the table, and select into a new table?

It depends on this table usage. If you can, then the ideal situation
might be to create the new table with a temporary name and fill it in
the background, and finally update for any changed values during the
process. This process might be time-consuming for 120,000,000 rows but
may work. If you use replication beware of the delays that may arise
from doing this in anything but small enough chunks.

> What are the upper limits of MySQL performance in terms of data set
> size using MyISAM vs InnoDB?

Be careful: the InnoDB footprint of this table may be much larger than
your existing MyISAM footprint. I've seen issues with this especially
as you'll be needing to adjust the the innodb_buffer_pool_size and
key_buffer values during this process. Thus you _may_ suffer a
performance problem, not because of the engine change but because of
the increased memory requirements. Consider also the use of
innodb_file_per_table which makes the resulting files easier to
manage.

A different solution might be to make a new slave, convert the
table(s) on the slave to InnoDB, finally promoting it to be the new
master. You'd also need to rebuild your existing slave. This avoids
downtime to the site except for the master switchover period. It also
gives you time to tweak all values while doing the conversion from
MyISAM to InnoDB.

Hope this helps.

Simon