Configuring MySQL (my.cnf) for MyISAM and InnoDB

Question

I want to convert some (not all) the MyISAM tables to InnoDB.

• Current my.cnf
• Show engines output
• Server has two 6-core Xeon processors (2 threads per core = 24 threads)

What would be the best configuration? (need to add InnoDB settings, because there are no InnoDB settings in my my.cnf).

Answer 1

You mentioned before you are running

• MySQL 5.5.29
• 16 GB RAM

Here are some good settings to start with

innodb_buffer_pool_size=4G
innodb_buffer_pool_instance=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M

Don't do the conversion yet !!!

Put there settings in my.cnf, then do the following

service mysql stop
cd /var/lib/mysql
rm -f ibdata1 ib_logfile*
service mysql start

Now, you can do the conversion.

Give it a Try !!!

UPDATE 2013-05-10 10:44 EDT

Here are my past posts on tuning InnoDB for better CPU usage

• May 26, 2011 : About single threaded versus multithreaded databases performance
• Sep 12, 2011 : Possible to make MySQL use more than one core?
• Sep 20, 2011 : Multi cores and MySQL Performance

Here are my past posts on tuning InnoDB

• Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?
• Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
• Mar 25, 2012 : Why does InnoDB store all databases in one file?
• Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?