Ensure better defaults with InnoDB Dedicated server.

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.

• innodb_buffer_pool_size
• innodb_log_file_size
• innodb_flush_method

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.