Optimizing MySQL for WordPress running on Virtual Server is not exactly easy. There is no way to predict how the hardware will behave. Most of us deliver optimization tips for the dedicated servers. I saw that cloud server from Rackspace, HP Cloud and IBM Cloud are like dedicated servers. Often for one server setup, we end up with some compromise. If you are using cloud server, then after trial and error of optimization, switch to master-slave replication and use HyperDB, use various methods of caching at application level. We are assuming that you are using InnoDB.
MySQL tuner is the best tool to fix up your database. There are other helpful scripts such as MySQL Tuning primer.
MySQL qery_cache_size was the most misunderstood configuration. The wordings sound like more is better. Funnily, it does instantly increase performance. Setting it to zero :
---
1 2 | query_cache_type = 0 query_cache_size = 0 |
does the best job at long-term. The query cache is deprecated as of MySQL 5.7.20 and is removed in MySQL 8.0. The headache is now gone!
As for Percona MySQL, run /usr/sbin/mysqld --verbose --help command and copy-paste the output on a file to quickly find the default values. Usually, you’ll find one default my.cnf at /etc/mysql/my.cnf and nothing at /etc/my.cnf. Copy it and open in any text-editor to edit :
1 2 3 4 5 | locate my.cnf cat /etc/mysql/my.cnf cat /etc/my.cnf cp /etc/mysql/my.cnf /etc/my.cnf nano /etc/my.cnf |
Change one variable each time and reload the settings by a complete restart :
1 | service mysql restart |
Check the front-end for the actual situation. When you’ll reach stable settings, then back up the /etc/mysql/my.cnf file somewhere and use the same my.cnf in both locations.
innodb_buffer_pool_size is the main settings to look at for any installation using InnoDB. The is where data and indexes are cached. You can allocate up to 70% RAM for these settings. 50% is a suggested stable value.
innodb-buffer-pool-instances is the settings to divide the above as separate threads. The number should be 1 for 1.2GB innodb_buffer_pool_size. For 4GB innodb_buffer_pool_size, it should be around 3.
innodb_log_file_size is the size of the redo logs. 1G is a good value for WordPress with one user, not many comment forms.
max_connections does the job as the name suggests. It is practical to have a value between 300 to 800. Too high max_connections like 1000 or more will make the server unresponsive leading to failure. A too low number will force to throw an error.
Newer MySQL are already far better tuned, below is enough good to test (when the server is minimum of 6GB RAM) :
1 2 3 4 5 6 7 8 9 10 11 12 | [mysqld] # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 1G innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 4G innodb-buffer-pool-instances = 3 innodb-large-prefix = true innodb-file-per-table = true |
We have kept the example my.cnf on our GitHub repo as well as the default my.cnf. The default io-threads value is 4 for both read and write :
1 2 | innodb-write-io-threads = 4 innodb-read-io-threads = 4 |
You can increase both to 8. When a lot of innodb_read_io_threads are seen then these settings improve the situation. Do not over-optimize. Do not change all the unknown settings (to you) in a day.
