[EMAIL PROTECTED] (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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
