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

innodbmy.cnfmyisamMySQL

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?

Best Answer

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.

Related Question