We have seen with most of the consulting projects where the customer might be having a dedicated DB (MySQL) server ,but running with a default configuration, without any optimisation for underlying hardware, “An idle hardware is similar to idle money will give you no returns”.
Well again if you are from a non-DBA background and you have chosen InnoDB as your engine of choice. The next question will be, what are the major variable that needs to be tuned for the available hardware? here is the answer for you
In this post, We are going to detail about the variable innodb_dedicated_server in MySQL 8.0.11. This variable solves our above query and of course MySQL 8.0 comes with best default values for production use cases.
If innodb_dedicated_server is enabled in my.cnf, MySQL will tune the below variables as per the memory in the server.
Overview:
innodb_buffer_pool_size
Server Memory | Buffer Pool Size |
---|---|
< 1 G | 128 MiB (the innodb_buffer_pool_size default) |
<= 4 G | Detected server memory * 0.5 |
> 4 G | Detected server memory * 0.75 |
Innodb_buffer_pool_size can be set up to 80% of physical RAM in offline (at startup). From MySQL 5.7 we can increase the value in the online method without need of restarting the server.
innodb_log_file_size
Server Memory | Log File Size |
---|---|
< 1 GB | 48 MiB (the innodb_log_file_size default) |
<= 4 GB | 128 MiB |
<= 8 GB | 512 MiB |
<= 16 GB | 1024 MiB |
> 16 GB | 2048 MiB |
Innodb_log_file_size is primarily used for recovery of DB server, in case of a crash. The recommended value for redo log sizing is that log should have the ability to hold at least last one-hour of the transaction for better stability and durability. Having small size can slow down the write performance. ie., commit latency, by waiting for transaction logs to get freed up.
innodb_flush_method
The flush method is set to O_DIRECT_NO_FSYNC when innodb_dedicated_server is enabled. If the O_DIRECT_NO_FSYNC setting is not available for the server, the default innodb_flush_method setting is used with respect to the architecture.
Testing innodb_dedicated_server:
Configuration:
- RAM – 4G
- CPU – 2
- DISK – 10G SSD
Variable disabled (default):
Memory:
root@localhost :(none) > \! free -m total used free shared buff/cache available Mem: 3849 630 1706 8 512 1009 Swap: 0 0 0
my.cnf values:
root@localhost :(none) > \! cat /etc/my.cnf | grep -i innodb innodb_buffer_pool_size = 1G innodb_log_file_size = 96M innodb_flush_log_trx_at_commit = 2 innodb_flush_method = O_DIRECT
Global values of the variables without innodb_dedicated_server variable.
root@localhost :(none) > select @@innodb_dedicated_server `Innodb Dedicated Server`, concat(round((@@innodb_buffer_pool_size / 1024 / 1024 / 1024 ),2),' GB') `Innodb Buffer Pool Size`,concat(round((@@innodb_log_file_size / 1024 / 1024),2),' MB') `Innodb Log file size`,@@innodb_flush_method `Innodb Flush Method`\G *************************** 1. row *************************** Innodb Dedicated Server: 0 Innodb Buffer Pool Size: 1.00 GB Innodb Log file size: 96.00 MB Innodb Flush Method: O_DIRECT 1 row in set (0.00 sec)
There is no change in variables because innodb_dedicated_server is disabled and the other values are in place as per the cnf values.
Variable enabled:
my.cnf values:
root@localhost :(none) > \! cat /etc/my.cnf | grep -i innodb innodb_dedicated_server = 1 #innodb_buffer_pool_size = 1G #innodb_log_file_size = 96M innodb_flush_log_trx_at_commit = 2 #innodb_flush_method = O_DIRECT
The above variables will not effect by innodb_dedicated_server if they are explicitly mentioned in my.cnf. So I have commented these variables out and restarted the mysql server.
Global values of the variables with innodb_dedicated_server variable.
root@localhost :(none) > select @@innodb_dedicated_server `Innodb Dedicated Server`, concat(round((@@innodb_buffer_pool_size / 1024 / 1024 / 1024 ),2),' GB') `Innodb Buffer Pool Size`,concat(round((@@innodb_log_file_size / 1024 / 1024),2),' MB') `Innodb Log file size`,@@innodb_flush_method `Innodb Flush Method`\G *************************** 1. row *************************** Innodb Dedicated Server: 1 Innodb Buffer Pool Size: 2.00 GB Innodb Log file size: 128.00 MB Innodb Flush Method: O_DIRECT_NO_FSYNC 1 row in set (0.00 sec)
So if innodb_dedicated_server is enabled those variables will be affected as the above formula.
We will receive a warning message in MySQL error log if we specify the variable in my.cnf along with innodb_dedicated_server.
Error log:
[Warning] [MY-012358] [InnoDB] InnoDB: Option innodb_dedicated_server is ignored for innodb_buffer_pool_size because innodb_buffer_pool_size=419430400 is specified explicitly.
Key Takeaways:
- Do not enable this variable if the server is not dedicated for MySQL.
- Do not enable the dependent variables if innodb_dedicated_server is enabled.
This variable can ensure that you are using your hardware better but there are more that can be tuned based on the db usage patterns.
O_DIRECT_NO_FSYNC requires professional grade hardware, with battery backed write cache, for durability, which documentation fails to mention.
LikeLike
Thanks for sharing informative articles about Ensure better defaults with InnoDB Dedicated server.
LikeLike