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
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
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:
my.cnf values:
Global values of the variables without innodb_dedicated_server variable.
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:
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.
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:
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}}