InnoDB configuration options

In this lesson you will explore some of the most important InnoDB configuration settings that affect WordPress performance.

You'll learn how memory is allocated through the InnoDB buffer pool, how redo logs improve performance and ensure durability, how flush behavior and I/O capacity influence write speed, and why the old query cache is best left disabled.

Not a silver bullet

If you're looking for a make_things_go_fast configuration variable that will solve all your WordPress database performance problems, I have to admit there isn't one. Some of the things in this lesson can certainly help with resource utilization and overall performance, but there are no silver bullets here.

Remember, the fastest database query is the one you don't have to run. Optimizing your application, data structure and queries will have a far bigger impact on performance than anything you can fine-tune in InnoDB.

Having said that, it is also important that your MariaDB database service is healthy, especially when run in a non-exclusive environment, right next to Nginx, PHP and WordPress, and various other services.

InnoDB

InnoDB is the default storage engine for MySQL and MariaDB. It's quick and reliable, supports row-level locking, crash recovery and plenty of other features, including transactions of course, which you'll very unlikely encounter in the WordPress world.

InnoDB is also the default storage engine used in the WordPress schema declaration, but this hasn't always been the case. You may come across MyISAM tables when migrating older installs, or when working with some old plugins that rely on MyISAM features.

My general approach is to convert everything to InnoDB where possible.

There's over 230 InnoDB configuration variables and we are certainly not going to cover all of them. We will, however, touch on some of the most important ones. All these settings go into the [mysqld] context of our /config/mysql/global.cnf file.

The buffer pool

The InnoDB buffer pool is an area in memory, which acts as a cache for table and index data. Having this buffer pool significantly reduces the need for disk IO, which greatly improves database operations.

The buffer pool alone has a dozen configuration options, but the one you should be concerned about is the innodb_buffer_pool_size. This defines the size of this buffer pool in bytes.

The default value for MariaDB and MySQL is usually 128M, which seems to be a safe low value for small VMs. If you're running over 4G of RAM, my recommendation is to aim for about 50% of your total physical memory. This will leave enough room for Nginx, PHP, Redis and other services on our host.

I have 96 GB of total physical memory on my server, so I'm going to set the buffer pool size to 48 GB in /config/mysql/global.cnf:

[mysqld]
innodb_buffer_pool_size = 48G

You may come across innodb_buffer_pool_instances too, however note that this configuration option has been removed in MariaDB 10.6 as it is no longer necessary to split the buffer pool.

Redo logs

InnoDB uses a redo log to make sure that data is written to disk reliably. It acts as a transaction log for your database, and is used during crash recovery.

Changes to your data in InnoDB are first written to a sequential redo log, rather than the InnoDB tablespace directly. These changes are then periodically flushed to the actual tablespace in a background process.

These logs provide better performance, as it is faster to write to a sequential write-ahead log, rather than random writes in the tablespace. They also provide durability and allow for crash recovery, when some data may have been committed, but hasn't yet made it to the tablespace during a crash.