MySQL 8 First Adaptive parameter turned out

MySQL 8 First Adaptive parameter turned out
MySQL 8.0 introduces a self-adaptive server-capable parameter that ensures that server resources are automatically provisioned under a variety of servers, virtual machines, and containers. This article will give you a detailed description of this parameter.

Author Li Chun Vaux Technology co-founder & advanced MySQL database expert
Produce WO Fun Technology

| What are adaptive parameters
MySQL8.0 introduces a self-adaptive server parameters to ensure that server resources are automatically adapted under a variety of servers, VMS, and containers, so let's take a look at exactly where it can be.

| Adaptive parameters are how to set and adapt to changing
Parameter Innodb_dedicated_server=on can be set to allow MySQL to automatically probe the server's memory resources, determine innodb_buffer_pool_size, innodb_log_file_size, and Innodb_ Flush_method a value of three parameters. The specific value policy is as follows.

Innodb_buffer_pool_size:

· <1g:128m (default value when innodb_dedicated_server= is off)

· <=4G: Detected physical memory * 0.5

· >4G: Detected physical memory * 0.75

Innodb_log_file_size:

· <1g:48m (default value when innodb_dedicated_server= is off)

· <=4g:128m

· <=8g:512m

· <=16g:1024m

· >16g:2g

Innodb_flush_method:

If the system is allowed to be set to O_direct_no_fsync. Set to InnoDB default flush method if not allowed by the system.

These parameters automatically detect the configuration of the server (including the memory of the virtual machine and the container) and automatically take effect each time MySQL starts.

Adaptive parameter Use note
· Innodb_dedicated_server is set to off by default and does not adaptively adjust 3 parameter values. This parameter is not a dynamic parameter and cannot be dynamically adjusted, that is, the parameter cannot be modified after MySQL is started.

· Innodb_dedicated_server=on after setting it only detects the server memory, so currently can only adaptively adjust memory-related three parameters

· In case of Innodb_dedicated_server=on, if the Innodb_buffer_pool_size/innodb_log_file_size/innodb_flush_method parameter is also explicitly set, These parameters of the display settings take precedence, and the following is printed in the MySQL error log:

"[Warning] innodb:option Innodb_dedicated_server is ignored for because ' variable name ' =? is specified explicitly. "
' Variable name ' refers to the Innodb_buffer_pool_size/innodb_log_file_size/inndob_flush_method parameter.

Note: Whether you set the above three parameters in the configuration file, command line, or MySQL newly introduced curing configuration is considered to be an explicit parameter value and will take precedence.

· display specifies a value that does not affect the adaptive parameter value setting for other variables. For example, if Innodb_buffer_pool_size is explicitly set, the buffer pool will be initialized with the value of your display setting instead of the value corresponding to the Innodb_dedicated_server parameter. However, Innodb_log_file_size and Innodb_flush_method are not affected, and they are set according to the server's memory size in accordance with the Innodb_dedicated_server adaptive values.

· Innodb_dedicated_server=on, the above three parameter values are automatically adjusted after each restart of the MYSQLD service process. At any time, MySQL does not store the adaptive values in a persistent configuration.

· If the system does not support O_direct_no_fsync,mysql, it will inherit the previous default values. MySQL must still be able to start normally on all platforms without any other changes.

· If adaptive results in innodb_log_file_size corresponding redo log file exceeds the disk space limit (how small this space is!). ), the following measures will be taken:

·· The newly generated log file redo log will be deleted

·· The error log appears as follows

"[ERROR] innodb:error number means ' No space left on device '
[ERROR] Innodb:cannot set log file to size MB "

"' sql* mysqld service refused to start. Innodb_dedicated_server=on is not necessarily the optimal configuration. For example, when you use other storage engines such as Myisam,myrocks, it is recommended to manually adjust instead of setting Innodb_dedicated_server=on XFS System please set inndob_flush_method=o_direct manually. Under Inndob_flush_method=o_direct_no_fsync, InnoDB uses O_direct to refresh Io, but skips the Fsync () step. Valid for some file systems, but not for XFS file systems. To ensure that the file's metadata is flushed to disk, XFS must use O_direct. | What happens before you adapt? On 5.7, innodb_buffer_pool_size defaults to 134217728, or 128MB, and if you use the default settings, MySQL 5.7 can only consume the system's 512M memory roughly. innodb_log_file_size=50331648, or 48M, does not apply to requests under large concurrency. This also leads to a number of articles suggesting the appropriate method to optimize settings for these parameters, for example: is 80% of RAM How do you should tune your innodb_buffer_pool_size? How to calculate a good InnoDB log file sizehow to Choose the MySQL Innodb_log_file_sizemysql 8.0 provides innodb_dedicated_serve R=on This parameter can solve this problem to a great extent. | Why adjust these parameters instead of the other parameters this parameter has a relatively large effect on the performance of the InnoDB, and it is also the most urgent need for adaptive adjustment of several parameters. (The individual feels that innodb_buffer_pool_instances should also be within the scope of adaptive adjustment) currently it only detects the system memory, it is relatively simple to implement, and is very effective for performance improvement, which basically solves the performance problems of most of the starter DBA installations. Like a class in the ranking of the penultimate students, first to help him solve the problem of 60-point pass and then consider raising to the top 10 class. To solve other problems, such as sort_buffer_size,read_rnd_buffer_size, such as the self-adaptive adjustment of memory, requires fine control of the RAM, and the various application access methods are not the same, not so easy to adapt, and innodb_rEad_io_threads,innodb_write_io_threads needs to be adjusted according to the CPU core number, also has a certain relationship with the application access mode, for innodb_io_capacity, to detect the underlying storage device specific IO capability, and set the corresponding , nor is it a simple job. In the end, the other effects of the performance of the adaptive parameters when the tune, can only be expected. | The server that adapts to the scenario running MySQL is dedicated to MySQL service. The default setting for Innodb_dedicated_server is to assume that the server's resources, MySQL can be used up. | Not adapted to the scene · Single-machine multi-instance situations do not fit. · Other special scenario requirements are not applicable. For example: not primarily InnoDB as the storage engine, there are other applications on the server, and so on. | The great significance of the members of the cloud vendors are blessed to use this parameter can be used to ensure that the server (virtual machine or container) extension, MySQL can "adapt" to try to consume more server resources, rather than design an automatic extension of the MySQL server resource configuration script. Not only to avoid the server expansion after the Mysqlbuffer pool and so on, not to use so many resources, but also to avoid the server reduced after the Mysqlbuffer pool too large, resulting in the MySQL service process can not start up. This change in parameters also means that subsequent MySQL-like parameters will become more and more optimized, and the DBA will be less likely to consider MySQL parameters when troubleshooting the problem. MySQL's operations DBAs are getting easier and more intelligent, and MySQL will become more and more smart reference documents: [MySQL 8.0 The first adaptive parameter was born] (Http://blog.woqutech.com/2018/07/10/mysql-8-0-%E9%A6%96%E4%B8%AA%E8%87%AA%E9%80%82%E5%BA%94%E5%8F %82%e6%95%b0%e6%a8%aa%e7%a9%ba%e5%87%ba%e4%b8%96/)

MySQL 8 First adaptive parameter was born