Sizing the database server buffer pool for MySQL

Are you viewing this page because a notice in the Index WP MySQL For Speed plugin for WordPress sent you here? If so, the plugin detected that your MySQL database server might benefit from a larger page buffer pool.

Many WordPress installations use a MySQL database. (Others use a MariaDB database.) It’s vital to the operation of WordPress, as it’s used for storing posts, pages, products, orders, and basically all the content in the site. That database software has a feature called a *page buffer pool*. It is an area of physical random-access memory hardware used to hold recently-accessed table data. This is important to good performance. For example, lots of people view a site’s front door page, so WordPress gets that information from its database frequently. If the database software has it in RAM already, WordPress gets its data back more quickly and users wait less time.

And, the default configuration of the database software often leaves that buffer pool too small.

Notice that the page buffer pool in a MySQL server is not a cache of recent query results. Instead, it’s inherent to the way MySQL efficiently handles the data it stores.

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

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.

On a micro virtual machine with 2GiB or less, make sure to reserve 1Gib – 1.5 GiB for the operation of the server software and keep the buffer pool smaller. So the example 2GiB server probably needs 384MiB or 512MiB for its buffer pool.

How to increase the buffer pool size

MySQL’s documentation for the buffer pool is here.

Version 8.0 of MySQL has a default buffer pool size of 1GiB. Other versions, including the newer 8.4 and 9.1 have a default of 128MiB. So does the older version 5.7.

The buffer pool size is a configuration parameter called innodb_buffer_pool_size for your MySQL database software. The configuration line, if it is present, 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.

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.

Figure out how much RAM to give to the buffer pool. Again, roughly 75% of the RAM on a dedicated server, 40-50% on a shared server, and a small amount on a micro virtual machine.

For example, if you’re running on a shared server, where the database software runs on the same machine as your WordPress instance, and your server has 16384MiB (16GiB) of memory. 40% of that is 6553.6MiB. So round that off and use this configuration line.

innodb_buffer_pool_size = 6500M

If that same 16GiB virtual machine is dedicated to running MySQL, 75% of that is 12GiB. So use this configuration line.

innodb_buffer_pool_size = 12G

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.

The following shell commands, or similar ones, will help you to change the parameter. (Please, if you’re not familiar with using shell commands for system administration, please get help.)

cd /etc/mysql/mysql.conf.d
sudo vi mysqld.cnf
  (Edit the file to add the configuration line)
  (Save the file and exit the editor)
sudo service mysql restart

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

SELECT @@innodb_buffer_pool_size/1024/1024;