How to Optimise MySQL 8.4 on Windows

5 months since my last post! That’s what having a full-time job and a third daughter will do to you! Let’s get distracted from the real world for a bit and look at how to optimise MySQL 8.4 on Windows.

Why do we need to look at the settings? Which settings need to be optimised? Will I ever find true love? Listen, these are all great questions, but I can only answer two of them.

MySQL 8.4 is typically installed onto Windows with some very conservative default config. Usually just 2GB of InnoDB pool size.

I wanted to show you the settings that I change to make MySQL more performant, and I’ll explain how you can find the best settings for your system too.

Think of the default settings like driving a Ferrari but only be first gear.

You can also read this on Medium!

MySQL stores most of it’s configuration settings in the my.ini file. And this is also conveniently located in a folder that is usually hidden in Windows:

C:\ProgramData\MySQL\MySQL Server 8.4\my.ini

I would ALWAYS recommend that you take a backup of this file so that you can rollback any changes should you break the config.

The simplest way to do this is to create a copy, and then name it my.ini.bak.

Let’s jump in!

You know I like to jump straight into the most important bit, so we’re starting off with the InnoDB buffer settings. Specifically, innodb_buffer_pool_size and innodb_buffer_pool_instances.

These have the largest effect when optimising MySQL 8.4 on Windows.

If you have these set wrong, your database could operate entirely on disk or entirely in volatile RAM.

This setting is the amount of RAM MySQL will use to cache the database. Caching makes the database overall more performant since memory is much faster than disk or even SSD storage.

Whilst each system will have a “sweet spot” for this value, we can use some common sense to provide you a guideline for setting your own pool size. Hint: it highly depends on how much system memory you have!

If this is a server dedicated to MySQL, then you can probably afford to provide a larger buffer pool. If you have other applications or services running, then you’ll need to be more conservative.

My typical rule of thumb is 70-80% of memory on a dedicated server, and 20-50% on a shared server or workstation.

So, for example, if you’re on a shared system that has 32GB of memory, I’d recommend assigning 6GB to 8GB to the buffer pool size

Another example, if you’re on a dedicated MySQL system that has 32GB of memory, I’d recommend assigning 20GB to 24GB to the buffer pool size

You can of course play around with this value, but the main driving parameters are:

• Is this a shared system or dedicated for MySQL?
• How much RAM does the system have?
• What is your workload?

Now that you’ve set an appropriate buffer pool size, you don’t really want that to operate in one large chunk.

You want to split that buffer pool across a number of instances as this will help with concurrency. It also reduces contention between threads. Essentially acting like a raid array where reads and writes can be split across multiple physical disks to increase the theoretical data speeds.

Splitting your buffer pool across multiple instances can allow for better performance as different threads on your machine will handle different queries rather than everything being queued for one big pool of memory.

This is often set so that each buffer pool has at least 1GB of memory, I typically set this to half the innodb pool size.

So, continuing with our example, a system with an 8GB buffer pool would likely benefit the most from having 4 instances. 2GB per instance

And on a system with a 24GB buffer pool, I would recommend 10-12 instances

Here is where you can set your tolerance for risk, and this depends on your use case for MySQL/

Innodb has a setting for flush log at trx commit and this has 3 options.

• 1, which is the default, will follow full ACID compliancy by flushing to disk for every single commit. This is the slowest but safest option
• You also have 0 and 2. 0 which flushes once per second. With 0, you’d lose 1 second of data if the server crashes
• With 2, you will only lose data if Windows crashes or your system loses power.

In short, you can likely leave this at the default value of 1 unless you want to go blazing fast. Although, you’ll likely not notice a difference in speed unless you’re really hammering MySQL.

For max_connections, I would highly recommend looking at your MySQL logs and checking if you currently receive any of these warnings:

Too many connections

If you see this, then you’ll need to increase max_connections to an appropriate value. Typically, I set and forget this to 1024

max_connections: 1024

max_allowed_packet is not often encountered and can silently slow down MySQL in the logs. Look for entries that look like this:

Packet too large

This typically affects large blobs or strings of data when they are being uploaded. Similar to max connections, I set and forget this to 1024M (1G). But this highly dependent on the type of data you’re passing to and from MySQL.

Each of these settings could net you a positive performance improvement from MySQL. Play around with them and find what values work best for you.

Oh, and please remember to restart the MySQL service after making configuration changes in my.ini.

Changes to the my.ini file only take effect after restarting MySQL!

Thanks for reading! 🎉