I'm trying to convert some MyISAM tables in my database to InnoDB (MySQL 5.1). The server has quite a bit of memory (96G), and the tables that are giving me problems can be on the order of 50 millions rows - 160 million rows (anywhere from 10G to 25G), but the same thing is happening regardless of table size.

During the "INSERT INTO ... SELECT * FROM..." MySQL starts using swap memory and I have to kill the conversion because it will take over 10 hours or so. While researching the conversion process, I found multiple sources that say that innodb_buffer_pool_size should be about 70% of your server RAM, so I made it about that, but I think that may be what's causing the issue.

Here are some important my.cnf settings:

innodb_buffer_pool_size=64G innodb_flush_method=O_DIRECT innodb_log_file_size=224K innodb_log_buffer_size=24M innodb_flush_log_at_trx_commit=0 innodb_additiona_mem_pool_size=24M

key_buffer_size=8G sort_buffer_size=4M read_buffer_size=128K

Any ideas what I'm doing wrong/why it's going to swap? Any ideas would be helpful.

2 Answers 2


To switch of MySQL engine you could :

1 Make a ALTER TABLE myTable ENGINE=InnoDB

2 Make a mysqldump of your table, then edit the CREATE TABLE statement to replace MyISAM by InnoDB and restore the dump in the new table (i called it myTable_InnoDB):

CREATE TABLE `mytable` (
  `id` int(11) DEFAULT NULL


CREATE TABLE `mytable_InnoDB` (
  `id` int(11) DEFAULT NULL

Best regards


Here is the reason why you are hitting swap: You are using MySQL 5.1.

The InnoDB Storage Engine in MySQL 5.1 isn't as aware of memory as MySQL 5.5+.

What I have seen over the years is this: Whenever the InnoDB Buffer Pool is bigger than 50% of installed RAM, you get lots of swap happening. I wrote about this with regard to MySQL 5.5 (See my post How do you tune MySQL for a heavy InnoDB workload?)

I would further suggest you read

Please implement 1 or more of the following suggestons:


Upgrade to MySQL 5.6 and set innodb_buffer_pool_instances to 2 or more.


Reduce innodb_buffer_pool_size to 48G (I would use 44G just to be well under the 50% RAM mark)


You could mysqldump the data, but I would reduce the chunks of data to 1 row at a time as follows:

STEP 1 : Make the InnoDB table

CREATE TABLE mytable_innodb LIKE mytable_myisam;
ALTER TABLE mytable_innodb ENGINE=InnoDB;

STEP 2 : mysqldump the data

MYSQLDUMP_OPTIONS="--no-create-info --skip-extended-insert"
mysqldump ${MYSQLDUMP_OPTIONS} mydb mytable_myisam | sed 's/mytable_myisam/mytable_innodb/g' > mydata.sql

Note: The mysqldump will insert only one row at a time (--skip-extended-insert). If you think mysql and InnoDB can handle it with a smaller Buffer Pool, remove the --skip-extended-insert.


Restart mysql with the InnoDB Double Write Buffer Off. This will make for fast writes to InnoDB tables.

service mysql restart --innodb-doublewrite=0

STEP 3 : import the data

mysql -Dmydb < mydata.sql

STEP 3a (if you ran STEP 2a)

service mysql restart

Give it a Try !!!

  • Upgrading to 5.6 (or even 5.5) isn't really an option right now, so I will try reducing the buffer pool and report back. Thanks!
    – stix122
    Dec 16, 2013 at 17:56
  • So I reduced the buffer pool and it is not using swap any longer, but the performance is still not great. Mysqld is using approximately 85% of the CPU and 50% of the memory, which is fine, but it's still taking over 4 hours to convert a ~90 million row table (it's been 4 hours, it's not done yet). During testing, I was able to convert this size table in about 2 hours, so I'm not sure why it's taking so much longer now. Ideas?
    – stix122
    Dec 17, 2013 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.