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.
The post New MySQL 8.0 innodb_dedicated_server Variable Optimizes InnoDB from the Get-Go appeared first on Percona Database Performance Blog.
via Planet MySQL
New MySQL 8.0 innodb_dedicated_server Variable Optimizes InnoDB from the Get-Go