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
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.