Mysql – How do convert a 66,862,521 row table from MyISAM to InnoDB without going offline for several hours

innodbMySQL

is it possible (and how) to convert a huge MyISAM table into InnoDB without taking the application offline. It requires to insert a couple of rows into that table every second but it is possible to suspend it for about 2 minutes.

Obviously ALTER TABLE … engine=innodb will not work. Therefor I had the plan to create a new table with the innodb engine and copy the content into it. And in the end, suspend the application log thread and RENAME TABLE.

Unfortunately even doing the copying in small batches of 100 rows generates significant lag after some time.

Edit: Existing rows are never changed, this table is used for logging.

Best Answer

Create a Master-Master setup as follows:

Create second master, MasterB
MasterB acts as slave to logTable
Create logTable_new as innodb
Run INSERT INTO logTable_new SELECT * FROM logTable (psuedocode) on MasterB, which sends the replication over to MasterA
When logTable_new on MasterA finishes syncing, swap out the tables

Related Solutions

Mysql – Select “,* from table

You can do the following:

CREATE TABLE tablename_temp (id int AUTO_INCREMENT PRIMARY KEY)
ENGINE=InnoDB 
SELECT * FROM tablename;

ALTER TABLE b
ADD UNIQUE INDEX some_index_name (your_original_key);

You can find the documentation here: http://dev.mysql.com/doc/refman/5.1/en/create-table-select.html And a small example on SQLFiddle.

Mysql – LOAD DATA INFILE blocks on concurrent use against MyISAM tables

Concurrent LOAD DATA INFILE on MyISAM tables are limited by mutex congestion due a global Key Cache. To alleviate lock congestion, MyISAM tables can be set to have their own Key Cache.

SET GLOBAL isam_cache.key_buffer_size=8*1024;
SET GLOBAL isam_cache_two.key_buffer_size=8*1024;
CACHE INDEX log_isam IN isam_cache;
CACHE INDEX log_isam2 IN isam_cache_two;

Best Answer

Related Solutions

Mysql – Select “,* from table

Mysql – LOAD DATA INFILE blocks on concurrent use against MyISAM tables

Related Question