Converting Large MyISAM table to InnoDB

Question

I have a MyISAM table (10M rows, 3.5G, planning to reach ~80M) and I always fail converting it to InnoDB.

I tried :

• ALTER TABLE - It loses connection after 2 minutes. Maybe I'm doing it wrong.

• mysqldump - Tried creating a dump and then change the ENGINE=MyISAM to ENGINE=InnoDB.

It starts well but as the number of rows in the new table grows (~3M), It becomes slower and slower and finally after some hours it times out (--reconnect is ON).

If I increase the buffer pool size to 2G it slows after more rows (~6M) but the machine runs out of RAM.

In SHOW PROCESSLIST during the dump restore I see that many queries stuck for 2-3 minutes on "query end" state. Can't understand from google-ing what does it mean.

• INSERT INTO ... SELECT * FROM - Created the same-structure table and tried this. Also slows down after some millions of rows and then times out. (Thanks @Ernestas Stankevičius for reminding me this.)

The server:

Aws EC2 4GB Ubuntu14.04

my.cnf:

wait_timeout=28800
connect_timeout=28800
innodb_lock_wait_timeout=28800
net_read_timeout=7200
net_write_timeout=7200
innodb_buffer_pool_size=1G
innodb_io_capacity=100 /*200 is heavy on the machine*/
innodb_flush_log_at_trx_commit=0
reconnect=1

Answer 1

innodb_buffer_pool_size=2G is probably dangerously high for a 4GB machine. Try 1500M. Swapping or running out of memory is worse than having a small cache.

Run the ALTER from the mysql commandline tool, not some UI. (The UI probably has the time limit you are hitting.

What version of MySQL are you running? How many indexes do you have? Please show us SHOW CREATE TABLE. Drop all secondary keys, keep only the PRIMARY KEY. Add the other indexes after the conversion; 5.6 or latter can do that "inplace".

If you have no PRIMARY KEY, create one; InnoDB really needs one.

This probably covers most cases:

CREATE TABLE new LIKE real;
ALTER TABLE new ENGINE=InnoDB,
    DROP ..., -- all the secondary keys
    ADD PRIMARY (...), -- if needed
    ENGINE=InnoDB;
INSERT INTO new (...)
    SELECT ... FROM real ORDER BY ... -- insert in PK order
ALTER TABLE new
    ADD index ...;  -- rebuild secondary key(s) (see note)
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

Note: If you are running 5.5 or older, add all the secondary keys in a single ALTER. If you are running 5.6 or newer, add them one at a time.

Answer 2

My solution was to remove some indexes from the new (InnoDB) table structure and then add the data.
I used INSERT new_table SELECT * FROM old_table to copy the data

The more indexes you remove - the faster the data gets in.

After that, I re-created the indexes.
Thanks to @i486.