Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
    
Which MySQL version? –  Kev Aug 6 '12 at 12:23
    
@Kev Hi, it's 5.0.95. Cheers –  Larry B Aug 6 '12 at 12:36
add comment

2 Answers

up vote 2 down vote accepted

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:

share|improve this answer
    
Cheers, this looks really helpful. –  Larry B Aug 20 '12 at 15:55
add comment

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.

share|improve this answer
    
Thanks. There are no fixed plans as yet, however an upgrade to 5.5 then conversion to InnoDB certainly seems like the right move. –  Larry B Aug 6 '12 at 15:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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