How To Increase The InnoDB Buffer Pool Size

One of the ways you can speed up your dynamic page generation is by increasing the amount of RAM your database uses for storing its queries and temporary objects. For WordPress sites, this is called the InnoDB buffer pool size. A lot of people on shared hosting don’t have the permissions to access the interface necessary to change this pool size. But if you’re on a VPS, then you can go directly into the MySQL configuration file, make the change, restart the MySQL server, and you’re done!

What Should be the Size of the Buffer Pool?

By default, the InnoDB buffer pool size is 128 MB. This might or might not be enough for you. However, the MySQL documentation states that you can safely increase the size of the buffer to up to 80% of your available RAM on a VPS or a dedicated server! Clearly this is too much if you’re hosting in a shared environment, but it recommends that such users start at 10% just to see what works and what doesn’t.

So if your VPS server has 2 GB of free RAM, and if you want to follow the recommendations of MySQL, you could increase the innodb buffer size from 128 MB to around 1600 MB! For sites with large databases, and a huge number of visitors, that can really speed things up!

Step 1: Locate the my.cnf File for MySQL

The configuration file holding the global options for MySQL is called my.cnf. Its location varies depending on your OS. For example, I’m running CentOS 7 on my VPS, and so I can find it at /etc/my.cnf. Your location might vary. You can check the MySQL documentation to see where to find my.cnf for your operating system.

I should also mention that you need root privileges to modify this file. Otherwise, it’s saved in a read-only format. So either you sign in as root (not recommended), or you give your user sudo privileges to minimize the risk of damage. Now when you need to edit the my.cnf file, simply type “sudo” before the command, enter your account password, and you can modify the file.

Step 2: Take Backups of my.cnf

It should be easy to reverse the changes you make here, but take a backup just in case. You can accomplish this using the following steps.

1. Navigate to the folder containing my.cnf
2. Type cp my.cnf my.cnf.bk

This will copy the my.cnf file to another file called my.cnf.bk. Now if something goes wrong, you can delete my.cnf and rename the copy to the original.

You can verify the creation of your backup using the following command:

ls -l my*

Your output should look like this:

Once you’ve confirmed the backup, we move to the next step.

Step 3: Change the Value of the InnoDB Buffer

As mentioned, you need to use sudo to modify the my.cnf file. So something like:

sudo vi my.cnf

Is needed. Once you’ve opened it up, remove the hash (#) symbol next to the comment holding the following line:

innodb_buffer_pool_size=128M

This will “uncomment” the line. Now change it to whatever value you feel is necessary. In the above example, I changed it to 400 MB.

Save your changes, and you’re done! Now we just need one final step.

Step 4: Restart MySQL

Depending on your OS, the command to restart the MySQL server will vary. For me, it’s:

sudo systemctl restart mysqld

This restarts MySQL, and applies the changes in my.conf. Now your new InnoDB buffer pool size is active!

This is an easy tweak for those who have access to their VPS servers. It can help speed up your database, and reduce important metrics like TTFB times!