MySQL using swap during conversion to InnoDB

Question

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.

Answer 1

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):

DROP TABLE IF EXISTS `mytable`;
CREATE TABLE `mytable` (
 `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

.

DROP TABLE IF EXISTS `mytable_InnoDB`;
CREATE TABLE `mytable_InnoDB` (
 `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Best regards

Answer 2

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

• Jeremy Cole's blog on Swap Insanity
• Brief Update on that Blog

Please implement 1 or more of the following suggestons:

SUGGESTION #1

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

SUGGESTION #2

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

SUGGESTION #3

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.

STEP 2a : OPTIONAL

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

• stix122: 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: 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?