Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

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

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

share|improve this answer
add comment

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:

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

share|improve this answer
    
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 '13 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 '13 at 19:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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