6

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?

2
  • Try to download SQL Yog its best mysql database UI where you can simply change the properties of any database with clicks and restart your mysql server or restart your system. Make sure if you are creating Innodb for relational database then its ok else you can proceed with My Isam for better indexing when compare to Innodb. Jan 13, 2012 at 13:40
  • 1
    I will say there are many reasons to use InnoDB over MyISAM. In fact, in newer versions of MySQL, there are only a few use-cases to recommend MyISAM (such as Full Text Search...which innodb will support in 5.6) Jan 13, 2012 at 14:52

2 Answers 2

5

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 :

3
  • But how can I specify on the same cnf some tables with myisam and some with innodb?
    – user5793
    Jan 13, 2012 at 17:49
  • You can set the option for both. However, keep in mind that the limiting factor is the actual amount of RAM for your DB Server. As a rule of thumb, make sure the sum of the key_buffer_size and innodb_buffer_pool_size does not exceed 75% of your Installed RAM. If you are running other services on the DB server (such as apache, memcached, monyog, nagios), then you will need to do one of two things: 1) use 25% of the installed RAM for the sum of the key_buffer_size and innodb_buffer_pool_size, or 2) get a separate DB server where you can apply the 75% rule or even upgrade the amount of RAM. Jan 13, 2012 at 17:59
  • Thank you Rolando, but how it would look the my.cnf? could please show me an example, sorry but I'm in a hurry...
    – user5793
    Jan 13, 2012 at 18:00
4

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.

2
  • Sorry, already reached my vote limit. I'll +1 this after 7PM EDT. Jan 13, 2012 at 16:33
  • As promised, +1 !!! Jan 14, 2012 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.