You will need to check out the sum total of your indexes for MyISAM. Please run this query:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;
Whatever number comes out, use either that number or 8G, whichever is smaller.
MyISAM only caches index pages.
If you plan to migrate your data to InnoDB, use these settings:
[mysqld]
innodb_file_per_table
innodb_log_file_size=2047M
innodb_log_buffer_size=64M
innodb_buffer_pool_size=18G
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=10000
You wiil need to optimize the InnoDB infrastructure
One of the most important variables for InnoDB is innodb_buffer_pool_size
. This is the amount of memory allocated to load tablespace information for InnoDB only.
Since you are mixing MyISAM and InnoDB, you will need to find a good balance between key_buffer_size
(for MyISAM indexes) and innodb_buffer_pool_size
(for InnoDB reads).
In general, you want to fit your entire data into memory if possible, as disk reads are (obviously) very expensive.
You didn't mention which MySQL version you are running, but I would recommend reading through the documentation of the appropriate version for tuning InnoDB for more detailed information.
Best Answer
You mentioned before you are running
Here are some good settings to start with
Don't do the conversion yet !!!
Put there settings in my.cnf, then do the following
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 performanceSep 12, 2011
: Possible to make MySQL use more than one core?Sep 20, 2011
: Multi cores and MySQL PerformanceHere 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?