In this post, we’ll look at the MySQL 8.0 innodb_dedicated_server variable.
MySQL 8.0 introduces a new variable called innodb_dedicated_server. When enabled, it auto tunes innodb_buffer_pool_size, innodb_log_file_size and innodb_flush_method at startup (if these variables are not explicitly defined in my.cnf).
The new MySQL 8.0 variable automatically sizes the following variables based on the RAM size of the system:
innodb_buffer_pool_size:
- <1G: 128M(default value if innodb_dedicated_server is OFF)
- <=4G: Detected Physical RAM * 0.5
- >4G: Detected Physical RAM * 0.75
innodb_log_file_size:
- <1G: 48M(default value if innodb_dedicated_server is OFF)
- <=4G: 128M
- <=8G: 512M
- <=16G: 1024M
- >16G: 2G
The variable also sets the following:
innodb_flush_method:
- Set to O_DIRECT_NO_FSYNC if the setting is available on the system. If not, set it to the default InnoDB flush method
These new default values are very reasonable, and the changes to these three variables show considerable performance improvements from the get-go than using the old default values. As stated in the worklog of this feature, the current MySQL version (5.7) only uses around 512M RAM with the default settings. With the new feature, these variables can easily adapt to the amount of RAM allocated to the server for the convenience of the system/database administrator.
With that said, you can achieve the best setting for these three variables by tuning it to your workload and hardware.
For InnoDB buffer pool size (based on this article), consider allocating 80% of physical RAM for starters. You can increase it to as large as needed and possible, as long as the system doesn’t swap on the production workload.
For InnoDB log file size, it should be able to handle one hour of writes to allow InnoDB to optimize writing the redo log to disk. You can calculate an estimate by following the steps here, which samples one minute worth of writes to the redo log. You could also get a better estimate from hourly log file usage with Percona Monitoring and Management (PMM) graphs.
Finally, for innodb_flush_method, O_DIRECT_NO_FSYNC prevents double buffering between the OS cache and disk, and works well with low-latency IO devices such as RAID subsystem with write cache. On the other hand, in high-latency IO devices, commonly found on deployments where MySQL is stored in SAN drives, having an OS cache with the default flush method fsync is more beneficial.
All in all, the MySQL 8.0 innodb_dedicated_server variable provides a fairly well-tuned InnoDB configuration at startup. But if it’s not enough, you can still tune these variables based on your workload and hardware. While MySQL 8.0 isn’t released yet, you can take a look at this article that helps you tune the current version (MySQL 5.7) right after installation.
Does this new auto-tuning understand inspecting cgroup limits?
I wonder what the author actually means by “the RAM size of the system”?
Perhaps, “system” means the host that runs MySQL server.
However, what if the host runs multiple MySQL server instances?
Are those variables aware about other servers that have demand for the RAM as well?
The name of the varibale is innodb_dedicated_server
As dedicated server per MySQL instance.
So it’s safe to assume that the MySQL will not be tuned for multiple MySQL instances on one server if you use this vaiable .
I predict this option will become the new “magic_quotes”.
Serious question: how on earth is O_DIRECT_NO_FSYNC being done safely?
O_DIRECT does not and has never implied that the writes will been made durable by the time the write call returns. It bypasses host buffer cache, but does not flush the caches on the storage device. It’s possible for writes done using O_DIRECT to hang out there indefinitely until something issues a cache flush.
So, is there something done at the end of the writes that forces everything to the platters? If not, this sounds like a bug. O_DIRECT gives no durability guarantee whatsoever by itself.