How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces?

Question

I have a database that, because of some issues, I needed to convert some tables from MyISAM to InnoDB.

I basically did this:

set sql_log_bin = 0; 

set sql_mode = 'STRICT_ALL_TABLES';

ALTER TABLE  `table1` ENGINE = INNODB;

ALTER TABLE  `table2` ENGINE = INNODB;

ALTER TABLE  `table3` ENGINE = INNODB;

ALTER TABLE  `table4` ENGINE = INNODB;

ALTER TABLE  `table5` ENGINE = INNODB;

ALTER TABLE  `table6` ENGINE = INNODB;

It worked right, but as far as I know now I should reconfigure the my.cnf and then restart the mysql server, correct?

How should I tune the my.cnf file if the server contains both MyISAM and InnoDB tables?

Answer 1

In order to tune RAM for InnoDB and MyISAM, you have to know what is cached for each storage engine.

• MyISAM caches indexes only
• InnoDB caches data and indexes

There are two queries that will help you generate the right size for their caches. @DTest named those caches in his answer (BTW @DTest +1).

For sizing the MyISAM Key Cache, set key_buffer_size based on this:

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; 

For sizing the InnoDB Buffer Pool, set innodb_buffer_pool_size based on this:

SELECT CONCAT(ROUND(KBS/POWER(1024, 
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999), 
SUBSTR(' KMG',IF(PowerOf1024<0,0, 
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size 
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables 
WHERE engine='InnoDB') A, 
(SELECT 2 PowerOf1024) B; 

@DTest mentioned another important: the version of MySQL. This is important for InnoDB because MySQL 5.5 now has options to force InnoDB to engage more CPUs. MySQL 5.1.38 introduced these options but are usable if and only if you have the InnoDB Plugin patched in. Rather than attempt to salvage MySQL 5.1 by incorporating the Plugin, just upgrade to MySQL 5.5.

Here is some light reading you can do :

• What are the main differences between InnoDB and MyISAM?
• Any gotchas at all with converting from MyISAM to InnoDB?
• From where does the MySQL Query Optimizer read index statistics?
• Is it common practice to mix InnoDB and MyISAM tables on same server?
• Any problem will all InnoDB and one MyISAM table?
• Optimizing InnoDB default settings
• How do you tune MySQL for a heavy InnoDB workload?
• Best of MyISAM and InnoDB
• Multi cores and MySQL Performance

Answer 2

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.