The Most Important MySQL Setting

If we were to select the most important MySQL setting, if we were given a freshly installed MySQL or Percona Server for MySQL and could only tune a single MySQL variable, which one would it be?

---

It has always bothered me that “out-of-the-box” MySQL performance is subpar: if you install MySQL or Percona Server for MySQL in a new server and do not “tune it” (as in change default values for configuration settings), it just won’t be able to make the best use of the server’s available resources – particularly memory.

To illustrate this, I ran the Sysbench-TPCC synthetic benchmark against two different GCP instances running a freshly installed Percona Server for MySQL version 8.0.31 on CentOS 7, both of them spec’d with four vCPUs but with the second one (server B) having a tad over twice as much memory than the reference one (server A).

Sysbench ran on a third server, which I’ll refer to as the application server (APP). I’ve used a fourth instance to host a PMM server to monitor servers A and B and used the data collected by the PMM agents installed on the database servers to compare performance. The table below summarizes the GCP instances used for these tests:

Server identifier	Machine type	vCPU	Memory (GB)
A	n1-standard-4	4	15
B	n2-highmem-4	4	32
APP	n1-standard-8	8	64
PMM	e2-medium	2	4

 

Sysbench-TPCC has been executed with the following main options:

• ‐‐threads=256
• ‐‐tables=10
• ‐‐scale=100
• ‐‐time=3600

It generated a dataset with the following characteristics:

mysql> SELECT
-> ROUND(SUM(data_length+index_length)/1024/1024/1024, 2) as Total_Size_GB,
-> ROUND(SUM(data_length)/1024/1024/1024,2) as Data_Size_GB,
-> ROUND(SUM(index_length)/1024/1024/1024,2) as Index_Size_GB
-> FROM information_schema.tables
-> WHERE table_schema='sbtest';
+---------------+--------------+---------------+
| Total_Size_GB | Data_Size_GB | Index_Size_GB |
+---------------+--------------+---------------+
|     92.83     |     77.56    |     15.26     |
+---------------+--------------+---------------+

 

One of the metrics measured by Sysbench is the number of queries per second (QPS), which is nicely represented by the MySQL Questions (roughly, “the number of statements executed by the server”) graph in PMM (given these servers are not processing anything other than the Sysbench benchmark and PMM monitoring queries):

Server A (4 vCPU, 15G RAM)	Server B (4 vCPU, 32G RAM)

Server A produced an average of 964 QPS for the one-hour period the test was run, while Server B produced an average of 1520 QPS. The throughput didn’t double but increased by 57%. Are these results good enough?

I’ll risk “adding insult to injury” and do the unthinkable of comparing apples to oranges. Here’s how the same test performed when running Percona Distribution for PostgreSQL 14 on these same servers:

	Queries: reads	Queries: writes	Queries: other	Queries: total	Transactions	Latency (95th)
MySQL (A)	1584986	1645000	245322	3475308	122277	20137.61
MySQL (B)	2517529	2610323	389048	5516900	194140	11523.48
PostgreSQL (A)	2194763	2275999	344528	4815290	169235	14302.94
PostgreSQL (B)	2826024	2929591	442158	6197773	216966	9799.46

 

	QPS (avg)
MySQL (A)	965	100%
MySQL (B)	1532	159%
PostgreSQL (A)	1338	139%
PostgreSQL (B)	1722	178%

For a user that does not understand how important it is to tune a database server or doesn’t know how to do it and just experiments with these two RDMS offerings, PostgreSQL seems to have the edge when it comes to out-of-the-box performance. Why is that?

MySQL comes pre-configured to be conservative instead of making the most of the resources available in the server. That’s a heritage of the LAMP model when the same server would host both the database and the web server.

To be fair, that is also true with PostgreSQL; it hasn’t been tuned either, and it, too, can also perform much better. But, by default, PostgreSQL “squeezes” the juice out of the server harder than MySQL does, as the following table with server resource usage indicates:

	CPU	Memory	IO
M(A)
M(B)
P(A)
P(B)

Data caching

To ensure durability, the fourth and last property of ACID-compliant databases such as MySQL and PostgreSQL, data must be persisted to “disk” so it remains available once the server is restarted. But since retrieving data from disk is slow, databases tend to work with a caching mechanism to keep as much hot data, the bits and pieces that are most often accessed, in memory.

In MySQL, considering the standard storage engine, InnoDB, the data cache is called Buffer Pool. In PostgreSQL, it is called shared buffers. A curious similarity is that both the Buffer Pool and the shared buffers are configured with 128M by default.

On the other hand, one of the big differences in their implementation stands from the fact MySQL (InnoDB) can load data (pages) from disk straight into the Buffer Pool’s memory area. PostgreSQL’s architecture uses a different approach: as is the case for the majority of the applications, it relies on the file system (FS) cache to load a page from disk to memory and then makes a copy of that page in the shared buffer’s memory area.

I have no intention of discussing the pros and cons of each of these RDBMS’ caching implementations; the only reason I’m explaining this is to highlight how, in practice, they are configured in opposite ways: when we tune MySQL, we tend to allocate most of the memory to the Buffer Pool (let’s simplify and say 80% of it), whereas, on PostgreSQL, we tend to do the inverse and allocate just a small portion of it (say, 20%). The reasoning here is that since PostgreSQL relies on the FS cache, it pays off to allow free memory to be naturally used for FS cache as it ends up working as a sort of 2nd-level caching for PostgreSQL: there’s a good chance that a page that has been evicted from the shared buffers can still be found in the FS cache – and copying a page from one memory area to another is super fast. This explains, in part, how PostgreSQL performed better out of the box for this test workload.

Now that I got your attention, I’ll return the focus to the main subject of this post. I’ll make sure to do a follow-up one for PostgreSQL.

Just increase the Buffer Pool size

I wrote above that “we tend to allocate most of the memory to the Buffer Pool (let’s simplify and say 80% of it)”. I didn’t make up that number; it’s in the MySQL manual. It’s also probably the most well-known MySQL rule-of-thumb. If you want to learn more about it, Jay Janssen wrote a nice blog post (innodb_buffer_pool_size – Is 80% of RAM the right amount?) dissecting it a few years ago. He started that post with the following sentence:

It seems these days if anyone knows anything about tuning InnoDB, it’s that you MUST tune your innodb_buffer_pool_size to 80% of your physical memory.

There you have it: if one could only tune a single MySQL variable, that must be innodb_buffer_pool_size. In fact, I once worked with a customer that had added a slider button to their product’s GUI to set the size of the Buffer Pool on the adjacent MySQL server and nothing else.

Realistically, this has been the number one parameter to tune on MySQL because increasing the data cache size makes a big difference for most workloads, including the one I’ve used for my tests here.

But the 80% rule just doesn’t fit all cases. On Server A, 80% of 14.52G was roughly 12G, and allocating that much memory to the Buffer Pool proved to be too much, with Linux’s Out-Of-Memory (OOM) monitor killing the mysqld process:

[Fri Mar 10 16:24:49 2023] Killed process 950 (mysqld), UID 27, total-vm:16970700kB, anon-rss:14226528kB, file-rss:0kB, shmem-rss:0kB

That’s the blacked-out mark in the graph of the table below. I had to settle for a Buffer Pool size of 10G (69% of memory), which left about 4.5G for the OS as well as other memory-consuming parts of MySQL (such as connections and temporary tables). That’s a good reminder that we don’t simply tune MySQL for the server it is running on; we need to take the workload being executed into (high) consideration too.

For Server B, I’ve tried to go with a Buffer Pool size of 27G (84% of memory), but that also proved too much. I settled with 81%, which was good enough for the task at hand. The results are summarized in the table below.

Buffer Pool size	Default                                                  |                                     Tuned
MySQL (A)
MySQL (B)

As we can see above, throwing more memory (as in increasing the data cache size) just does not cut it beyond a certain point. For example, if the hot data can fit in 12G, then increasing the Buffer Pool to 26G won’t make much of a difference. Or, if we are hitting a limit in writes, we need to look at other areas of MySQL to tune.

Dedicated server

MySQL finally realized almost no one else keeps a LAMP stack running in a single server. We have long been surfing the virtualization wave (to keep it broad). Most production environments have MySQL running on their own dedicated server/VM/container, so it makes no sense to limit the Buffer Pool to only 128M by default anymore.

MySQL 8.0 introduced the variable innodb_dedicated_server, which configures not only the Buffer Pool size (innodb_buffer_pool_size) according to the server’s available memory but also the redo log space (now configured through innodb_redo_log_capacity), which is InnoDB’s transaction log and plays an important role in data durability and in the checkpointing process, which in turn influences… write throughput. Oh, and the InnoDB flush method (innodb_flush_method) as well.

This option for Enabling Automatic Configuration for a Dedicated MySQL Server is a bit more sophisticated than a rule of thumb and employs a simple algorithm to define the value for the Buffer Pool size and the redo log space, and configured my test servers as follows:

	Server A	Server B
innodb_buffer_pool_size	11G	24G
innodb_redo_log_capacity	9G	18G
innodb_flush_method	O_DIRECT_NO_FSYNC	O_DIRECT_NO_FSYNC

 

The default values for innodb_redo_log_capacity and innodb_flush_method being used so far were, respectively, 100M and FSYNC. Without further ado, here are the results for the three test rounds for each server side-by-side for easier comparison:

Server A:

 

Server B:

Note how CPU usage is now close to maximum usage (despite a lot of the time being spent in iowait due to the slow disk, particularly for the smaller server).

With the dedicated server (third “peak” in the graphs below) using very similar Buffer Pool values to my Buffer Pool-tuned test (second “peak”), the much larger redo log space coupled with the O_DIRECT flush method (with “no fsync”) allowed for much-improved write performance:

Server A
Server B

It’s probably time to change the default configuration and consider every new MySQL server a dedicated one.

---

NOTE – I hit a “limitation” from my very first Sysbench run:

Running the test with following options:
Number of threads: 256
(...)
FATAL: error 1040: Too many connections

Due to MySQL’s cap on the number of concurrent connections, it allows, by default, 150. I could have run my tests with 128 Sysbench threads, but that would not have driven as much load into the database as I wanted, so I raised max_connections to 300.

Technically, this means I cheated since I have modified two MySQL settings instead of one. In my defense, max_connections doesn’t influence the performance of MySQL; it just controls how many clients can connect at the same time, with the intent of limiting database activity somewhat. And if your application attempts to surpass that limit, you get a blatant error message like the one above.

BTW, I also had to increase the exact same setting (max_connections) on PostgreSQL to run my initial provocative test.

---

The goal of this post was to encourage you to tune MySQL, even if just one setting. But you shouldn’t stop there. If you need to get the most out of your database server, consider using Percona Monitoring and Management (PMM) to observe its performance and find ways to improve it.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

 

Download Percona Monitoring and Management Today