Adjust MariaDB settings to accelerate database processing
Let's continue tuning.
The MySQL database system that comes with the standard CentOS 7 environment is MariaDB 5.5, and it uses InnoDB for its default storage engine. We will configure two parameters that have an effect on how WordPress utilizes InnoDB.
In "/etc/my.cnf.d/server.cnf" edit the following code:
[mysqld] innodb_buffer_pool_size = 512M query_cache_size = 64M
Restart MariaDB to save the changes.
[root@ip ~]# systemctl restart mariadb
Reload the front page in your browser and refresh a few times to see the changes. In my environment the page load time was 64ms, and requests per second was 31.82. This is only a 5% difference, but the query cache is responsible for it.
The query cache takes SELECT queries and stores them for later use when the same query is sent, to speed up the database execution time. Like the PHP accelerators, if the database is updated the cache gets deleted. Also, there is no difference between enabling or disabling the query cache.
Query cache is disabled by default. We have enabled it by entering what we did above in the "server.cnf" file.
The other parameter, innodb_buffer_pool_size, is a memory cache size for InnoDB data and indexes. The default is 128MB but we have changed it to 512MB. InnoDB differs from MyIsam in that it doesn't use the OS disc cache, so the bigger this number is the more queries you can allow.
If we consider the database size used by WordPress in one year to be x, then query_cache_size should be more than 10% of x, and innodb_buffer_pool_size should be 120% of x. In my example I supposed the database size to be 400MB, which gave me 64MB and 512MB respectively.
Our results this time are due to the fact that the WordPress database size is only 160KB. This will obviously increase in a real site, and so will queries from plugins that you may add. In that case, performance can increase 1000% depending on the case. The more your site grows, the more important these two parameters are.
By the way, if the storage engine is MyIsam, query cache still has a large effect. However, for the other parameters you need a much different configuration than InnoDB. If you install WordPress fresh on an InnoDB default environment, all of your tables will be made for InnoDB, so you can continue to follow this guide with no issues. On the other hand, if you were to move your WordPress data to another environment, you would have to make sure what storage engine is being used.
| Tuning contents | Page load time | Requests per second |
| Default environment | 176ms | 11.24 |
| APC | 70ms (251%) | 29.20 |
| OPcache+APCu | 66ms (266%) | 30.51 |
| MariaDB settings |
64ms (275%) | 31.82 |
