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 :
# 96 /100
# CDN is a biased result
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
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
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
innodb_log_buffer_size=XYM A value something like 16M-32M is good for most situations. Larger is for lesser disk IO usage. Replace
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
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
innodb_write_io_threads=XYZ 4 * the number of cores / virtual cores / real multiple (physical processor * number of cores per processor). Replace
innodb_io_capacity=XYZ Safely you can set to 500 for 2 GB+ PVHVM instance or ask Rackspace support. Replace
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.