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

Posted on

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 :

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 :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *