Ensure better defaults with InnoDB Dedicated server.

Mydbops
May 25, 2018
10
Mins to Read
All

Is your MySQL database on a dedicated server but not delivering peak performance? 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”.

Why Most Dedicated MySQL Servers Run Suboptimally

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

innodb_dedicated_server: A Game-Changer for Automated MySQL Configuration

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.

Understanding innodb_dedicated_server:

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.

Optimize performance, enhance reliability, and ensure data security with Mydbops' MySQL InnoDB Cluster Consulting and Support Services. Our team can help you fine-tune temporary tablespace configuration, troubleshoot performance issues, and implement best practices. Contact us today to schedule a free consultation.

{{cta}}

No items found.

About the Author

Mydbops

Subscribe Now!

Subscribe here to get exclusive updates on upcoming webinars, meetups, and to receive instant updates on new database technologies.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.