MySQL using swap during conversion to InnoDB

innodbmemorymyisamMySQLmysql-5.1

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.

Best Answer

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

Related Question