3 Minuten Lesezeit (533 Worte)

Just take it easy. InnoDB automatic configuration

Data Management MySQL (PDO) MySQL - English Articles Englisch

Mittwoch, 06. April 2022

Repeatedly customers ask us how to set the most important parameters of the InnoDB storage engine. But the answer can be amazingly simple for MySQL Version 8.0: innodb_dedicated_server = ON

Don't think twice it is alright?

With MySQL 8.0 the InnoDB engine is the de facto standard for operating MySQL databases. A wise configuration of the most important parameters is indispensable to fundamentally ensure a good performance of the server. These parameters include in any case:

innodb_buffer_pool_size (size of the InnoDB buffer)
innodb_log_buffer_size (size of the transaction log files)
innodb_log_files_in_group (number of transactions logs)
innodb_flush_method (method which writes data to the filesystem)

Explicitly dedicated!

As the parameter "innodb_dedicated_server" already suggests, this setting is only recommended for systems on which only one MySQL database is in operation, which mainly uses InnoDB as an engine. Additional MySQL instances or other applications should not be used here.

The individual parameters mentioned above are then calculated using specified formulas but can be manually adjusted at any time using a "dedicated" configuration.

The formula(s) of success

The size of the buffer pool is calculated directly by using the server's memory. The following method is used:

Server has less than 1 GB RAM --> 128 MB
Server has between 1 and 4 GB RAM --> RAM * 0.5
Server has more than 4 GB RAM --> RAM * 0.75

In our example, our small server (Linux VM) has only 2 GB of RAM. So, the MySQL instance automatically allocates "2 GB * 0.5 = 1 GB" InnoDB buffer pool.

[root@localhost ~]# mysql -uroot --execute="select variable_value/1024/1024 'Buffer Pool GB' from performance_schema.global_variables where variable_name = 'InnoDB_Buffer_Pool_Size'"; 

+----------------+ 
| Buffer Pool GB | 
+----------------+ 
|           1024 | 
+----------------+

It is similar for the other parameters. The "formula" for the "innodb_log_file_size" is as follows (from version 8.0.14):

If less than 8 GB Buffer Pool Size --> 512 MB
Between 8 and 128 GB buffer pool size --> 1024 MB
Above 128 GB buffer pool size (yes, such systems do exist 😉) --> 2048 MB

The number of log files ("innodb_log_files_in_group") is explained like this:

buffer pool size is smaller than 8 GB --> round(buffer pool size)
buffer pool size between 8 and 128 GB --> round(buffer pool size + 0,75)
More than 128 GB buffer pool size --> 64

Finally, the parameter "innodb_flush_method" is usually set to "O_DIRECT_NO_FSYNC". This prevents the buffer from being flushed twice between the OS cache and the hard disk and works very well for low latency I/O systems.

Conclusion: Simple and good?

If the general conditions (dedicated MySQL InnoDB database servers) are right, the selected parameters are quite useful for a first setup. Of course, in the subsequent operation (if problems occur) it should be checked repeatedly whether a "manual" intervention could be necessary. As with any "rule of thumb", the settings that make sense here do not claim to be correct in every (!) case.

Do you have questions about the operation of MySQL? Then please contact us.