MySQL InnoDB Optimization From my.cnf

On Rackspace Cloud Server PVHVM, We Can Further Optimize MySQL. Here are some performance tweaks for MySQL InnoDB optimization from my.cnf. WordPress or other PHP-MySQL based softwares usually have no default set choice for InnoDB or MyISAM as driving Engine. We previously discussed the topic – InnoDB actually performs better than MyISAM For WordPress, specially on multi-server configuration. You can apply this method for PaaS as well.

 

MySQL InnoDB Optimization From my.cnf : Assumptions

 

If you are on Rackspace, we assume that, you are not using fully managed / intensely managed plan. This guide is only for developmental purpose or for hardly helping the Rackspace team. You must not interfere with the instances which they are optimizing.

We previously discussed about optimizing MySQL Database performance by editing my.cnf. As we have discussed before – InnoDB Table Optimization per se does exist, there is no such optimization trick unlike MyISAM. So, no WordPress plugin can ever optimize the tables – because the function does not exist. However, the WP Optimize WordPress plugin can do some other works apart from optimizing the tables.

And, we are taking that, your Application Server is at least an 01 GB PVHVM instance. This website is practically a showcase of how fast WordPress can be tweaked with very good score on webpagetest and Google Pagespeed Insights :

We are yet not happy, because there are points which we need to work.

 

MySQL InnoDB Optimization From my.cnf : Guide

 

We are not Database Experts. You possibly should consult a database administrator for any performance lag. From our knowledge, these tweaks will not harm :

We are describing the functions of the parameters in my.cnf file, by default it is minimally touched :

innodb_buffer_pool_size=XYG We tested that allocating ~75% of the memory for a 64bit MySQL server works fine. To make value (digit) higher, you’ll require more RAM. Replace XY.

innodb_change_buffering=all Directive to update and delete operations which are buffered.
innodb_additional_mem_pool_size=XYM The extra memory InnoDB needs to carry out other works. A safe value is 20M for server more than 2GB. Replace XY.

innodb_file_per_table Related to backup, restore and compress function of a table.
innodb_log_file_size= ABCDM Log file size. Should not cross 20-25% of the buffer pool size. Replace ABCD.
innodb_log_buffer_size=XYM A value something like 16M-32M is good for most situations. Larger is for lesser disk IO usage. Replace XY
innodb_flush_log_at_trx_commit=1 1 means database transaction is more important than performance, 2 is set if performance is more important than transaction. You can test with both value with a considerable time gap.
innodb_thread_concurrency=N Default is 0. Usually it is double of the value of the number of cores / virtual cores / real multiple (physical processor * number of cores per processor). Replace N.
innodb_flush_method=O_DIRECT We can avoid double buffering by flushing.

innodb_read_io_threads=XY 4 * the number of cores / virtual cores / real multiple (physical processor * number of cores per processor). Replace XY.
innodb_write_io_threads=XYZ 4 * the number of cores / virtual cores / real multiple (physical processor * number of cores per processor). Replace XYZ.
innodb_io_capacity=XYZ Safely you can set to 500 for 2 GB+ PVHVM instance or ask Rackspace support. Replace XYZ.
innodb_lock_wait_timeout=ABC Determines. It should be tuned by manual checking.
innodb_data_file_path= Set the path somewhere you can remember.
transaction-isolation=READ-COMMITTED This should be tested carefully, it can increase performance, but test it.
ibdata1:50M;ibdata2:50M:autoextend Complex function, tl;dr

Test with Major’s script.