Buffer Pools in WordPress Databases Are Often Too Small

I publish the WordPress plugin Index WP MySQL For Speed. Fairly often, when supporting that plugin’s users, I find a misconfiguration in the database software that seriously reduces performance. Virtually every WordPress installation uses a MariaDB or MySQL database. That database software has a feature called a *buffer pool*. It is an area of random-access memory used to hold recently-accessed table data. And, the default configuration of the database software can leave that buffer pool too small.

When the buffer pool is too small, the database server has to continually reread data from its drives (solid-state drives or old-school hard disk drives). That slows it down. So the WordPress software has to wait for each request (for an option or a post or whatever) to complete. That in turn slows user response. This article includes my observations about that.

The plugin includes support features that allow users to upload their database metadata (anonymously) to a server of mine. Users can also monitor database operation for a short period of time and upload the monitor data. These features help with user support; they remove the need for a series of questions and answers about the database configuration.

Configuration information for a large server

Some of the uploaded metadata includes configuration information. That same information is summarized on the plugin’s About tab (accessed via Tools -> Index MySQL -> About from the WordPress dashboard). The summary looks like this.

This particular WordPress site runs on a well-provisioned shared hosting service. Notice the line about database buffer pool size: Database buffer pool size: 250GiB. 64.8% used, 1.8% dirty. 250 gigabytes of RAM for buffer pooling is generous. There’s plenty of headroom: only about 65% of the pool is in use. And the notation 1.8% dirty means the server is keeping up with changes to data, writing them to the drives promptly. Plus, the server hardware itself has 18 cores and 0.7 terabytes of physical RAM with half of it available. All in all, this hosting company is nowhere near to overselling this server. (They are GreenGeeks, and you are reading this article on their server.)

Misconfiguration of a small server

Here’s the problem, though. Not all servers are so generously provisioned. Many servers, including budget virtual private servers, have only a gigabyte or two of RAM. And they have small default database buffer pool sizes. Here is an example of such a small server.

Notice the 128 megabyte buffer pool: Database buffer pool size: 128MiB. 86.1% used, 9.7% dirty. It’s almost saturated, and almost 10% of it is dirty, waiting to be written to a drive. And, notice that the current database’s size is 143 megabytes, larger than the buffer pool. And, the databases for other sites on the shared server total 0.8 gigabytes. That’s much larger than the buffer pool.

This small buffer pool is the misconfiguration. For efficient operation, the buffer pool should be larger than this. When a server machine (virtual or physical) is dedicated to running the database software, the buffer pool should be 70-80% the size of the physical RAM in the machine. When (as in this case) the virtual machine is used both for the web server software and the database software, the buffer pool should use something like 40%-50% of the physical RAM.

Configuration data from multiple servers

Users of my Index WP MySQL For Speed plugin have uploaded their server metadata several thousand times, which gives me access to this configuration information for lots of database servers in the wild. These servers are not any kind of statistically significant random sample, of course. They simply represent users of one plugin that have chosen to upload their metadata. Still, analyzing all these uploads shows a significant opportunity for improving site performance.

I have information from 1943 distinct WordPress instances.

Of those, 650 (about a third) have a buffer pool size of 256 megabytes or smaller. Operators of such sites should try to increase their buffer pool sizes. Of those 650, 35 sites have a total database size of ten times or more the pool size. Those sites will almost certainly get big performance gains from increasing the pool size.

There are a small handful of configurations in my sample where the buffer pool is larger than physical RAM. That is a serious misconfiguration that causes the server to thrash or crash.

How to increase the buffer pool size

The buffer pool size is is a configuration parameter called innodb_buffer_pool_size for your MariaDB or MySQL database software. The configuration line looks something like this:

innodb_buffer_pool_size = 1024M

In a default configuration the line may be absent and you may need to add it. It is in a configuration file on your server called mysql.cnf or something similar.

If you use a shared hosting service, you need to ask your hosting company’s tech support person to increase it. After you change the configuration restart the database server to make it take effect.

You can retrieve the current buffer pool size in megabytes with this SQL command.

SELECT @@innodb_buffer_pool_size/1024/1024;

MariaDB

MariaDB’s documentation for the buffer pool is here.

On an Ubuntu (Debian) Linux system with a standard MariaDB installation, the configuration parameter is found in the file /etc/mysql/mariadb.conf.d/50-server.cnf.

MySQL

MySQL’s documentation for the buffer pool is here.

On an Ubuntu (Debian) Linux system with a standard MySQL installation, the configuration parameter is found in the file /etc/mysql/mysql.conf.d/mysqld.cnf.