Basic InnoDB Tuning
MySQL 8.0 innodb_dedicated_server
MySQL offers a lot of knobs and levers to tune its behavior, and InnoDB offers a few more.
In general, I believe you shouldn’t tune the majority of MySQL settings until you are in a position to need to.
So today let us learn how to simplify tuning InnoDB.
Basic InnoDB tuning in MySQL 8.0
Although there are many settings in MySQL and InnoDB to adjust, there are not many settings that need to be changed on a new MySQL database before you know the workload.
Adjusting too much in advance can easily lead to headache in the future.
Starting in MySQL 8.0, there is an option innodb_dedicated_server which attempts to autotune the main InnoDB settings that will get you the most bang for your buck if MySQL runs on a dedicated server.
If for some reason MySQL isn’t on a dedicated server, you can tune these settings yourself once you know what they do.
Let’s go over them.
InnoDB tuning of memory
The first variable is innodb_buffer_pool_size.
In MySQL, the simple math is the more memory the better.
InnoDB has to pull all the data needed to respond to queries into the buffer pool for processing.
The bigger the buffer pool, the less reading from disk InnoDB needs to do.
Reducing reads from disk speeds up query processing and can decrease the CPU load on the server.
Keep in mind that connections and other caches take memory, so you can’t just give 100% memory to InnoDB.
With that in mind, give as much memory as you can to InnoDB if it is the only storage engine you run.
However, if your working data set doesn’t fill the buffer pool, you won’t get as much benefit from the memory.
If you run into that situation and you don’t expect data growth, you may have over-provisioned the server.
InnoDB redo logging
Next, we have the settings that affect InnoDB redo logging:
Redo logging is InnoDB’s method to guarantee transactions are persisted even through crashes of MySQL.
The auto-tune settings affect the size of the redo log. Your redo log capacity is innodb_log_file_size*innodb_log_files_in_group.
If the amount of data changes is too much for the redo log to handle, InnoDB will throttle write performance.
To avoid that potential performance throttling, give the redo log a lot of space.
Keep in mind that the more space provided, the longer crash recovery can take as those changes are processed.
I’d still recommend giving more space to redo log and taking the hit on crash recovery time if that becomes necessary.
InnoDB Flush Method
The innodb_flush_method setting affects how InnoDB writes to redo log and data files.
The auto setting attempts to set this to O_DIRECT_NO_FSYNC to avoid expensive fsync operations.
This is an optimization for write performance, but it could have significant data loss consequences depending on the setup of your system.
This option requires a restart, so getting it right from the beginning is important. Pay attention to the warnings in the documentation and make sure your system is configured to safely support this option.
Conclusion
There are only a few settings in InnoDB that need to be tuned from the start, either because of the expected gain in performance, or because of the impact to adjusting them later in production requiring a MySQL restart.
In fact, MySQL 8.0 provides an to auto-size these settings for a dedicated server.
You’ll do well to focus on those settings initially, and then monitor the behavior of the database over time to only make needed adjustments based on your workload.
