mysql my.cnf shows InnoDB as disabled "skip-innodb". Should I enable it for large Magento DB?

Question

The MySQL my.cnf file on our centOS server shows skip-innodb as present.

We are looking to optimize my.cnf for a large Magento database. The server has 12GB of memory and 5 Xeon Processors.

Would it be best to comment/remove the skip-innodb line? If would there be anything else that would need amending in the file?

Current my.cnf file:

[mysqld]
skip-innodb
ft_min_word_len=3    
query_cache_limit = 4M    
query_cache_size = 16M ## 32MB for every 1GB of RAM    
query_cache_type = 1    
max_user_connections = 50    
max_connections = 50    
interactive_timeout = 300    
wait_timeout = 200    
connect_timeout = 200    
thread_cache_size = 32    
key_buffer_size = 64M ## 128MB for every 1GB of RAM    
join_buffer_size = 1M    
max_connect_errors = 20    
max_allowed_packet = 12M    
table_cache = 1024    
record_buffer = 1M    
sort_buffer_size = 1M ## 1MB for every 1GB of RAM    
read_buffer_size = 1M ## 1MB for every 1GB of RAM    
read_rnd_buffer_size = 1M ## 1MB for every 1GB of RAM    
thread_concurrency = 4 ## Number of CPUs x 2    
myisam_sort_buffer_size = 32M    
tmp_table_size = 16M    
max_heap_table_size = 12M

[safe_mysqld]    
open_files_limit = 2048

[mysqldump]    
quick    
max_allowed_packet = 12M

Answer 1

Since you have MySQL 5.0.95, I strongly recommend that you not only remove skip-innodb but that you also upgrade to MySQL 5.5. Why ???

InnoDB has been enhanced to take advantage of multiple CPUs and hyperthreading. There is one catch: You have to configure my.cnf properly in order to do so. I say this because, in some cases, MySQL 5.0 OUT-OF-THE-BOX runs faster than MySQL 5.5 OUT-OF-THE-BOX.

I have written earlier posts about this in the DBA StackExchange:

• Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
• Oct 05, 2011 : Query runs a long time in some newer MySQL versions
• Sep 20, 2011 : Multi cores and MySQL Performance
• Jun 19, 2011 : How do I properly perform a MySQL bake-off?
• May 26, 2011 : About single threaded versus multithreaded databases performance

Answer 2

The fact that MySQL works with that flag set suggests that your tables are all MyISAM. Therefore, i don't think removing the flag will make any difference on its own. What would make a difference would be removing it, then converting your tables to InnoDB. Is that what you're considering doing?

If you were using MySQL 5.5, i would confidently advise you to do that, and to use InnoDB exclusively. A benchmark done by a guy on the MySQL team shows that InnoDB is substantially faster and more scalable across multiple processors than MyISAM. There is a popular perception that MyISAM is faster than InnoDB, but it seems this perception is wrong.

However, i have no idea whether this is true on 5.0. I imagine there was a lot of tuning between 5.0 and 5.5. Nonetheless, i would suggest switching to InnoDB if you can, just because it has better data integrity guarantees. Indeed, i'm a bit surprised - and somewhat suspicious - that your installation appears not to be using it already.