MySQL 8.0 首个自适应参数横空出世

｜作者简介

李春，沃趣科技联合创始人，高级MySQL数据库专家，浙江大学数据库硕士

从事MySQL相关工作10年+。曾作为阿里巴巴MySQL数据库leader，负责产品，应用架构的优化和部署；实现了阿里巴巴3亿产品从Oracle小型机到MySQL SSD的平滑迁移。技术上专注于MySQL复制、数据一致性、数据安全性、扩展性和运维自动化的领域。

｜什么是自适应参数

MySQL8.0推出一个号称可以自适应服务器的参数，保证在各种不同的服务器、虚拟机、容器下自动适配服务器资源，让我们一起来看看到底它能做到什么地步。

｜自适应参数是如何设置和适应变化的

可以设置参数 innodb_dedicated_server=ON来让MySQL自动探测服务器的内存资源，确定innodb_buffer_pool_size, innodb_log_file_size 和 innodb_flush_method 三个参数的取值。具体取值策略如下。

innodb_buffer_pool_size:

<1G: 128M(innodb_dedicated_server=为OFF时的默认取值)
<=4G: 探测到的物理内存 * 0.5
>4G: 探测到的物理内存 * 0.75

innodb_log_file_size:

<1G: 48M(innodb_dedicated_server=为OFF时的默认取值)
<=4G: 128M
<=8G: 512M
<=16G: 1024M
>16G: 2G

innodb_flush_method:

如果系统允许设置为O_DIRECT_NO_FSYNC。如果系统不允许，则设置为InnoDB默认的Flush method。

上述这些参数在MySQL每次启动时自动探测服务器（包括虚拟机和容器的内存）配置并自动生效。

｜自适应参数使用注意

innodb_dedicated_server默认设置为OFF，不会自适应调整3个参数值。该参数也不是动态参数，无法动态调整，也就是说MySQL启动后无法修改这个参数
innodb_dedicated_server=ON 设置以后它其实只探测了服务器内存，所以目前只能自适应调整内存相关的三个参数
innodb_dedicated_server=ON的情况下，如果还显式设置了 innodb_buffer_pool_size / innodb_log_file_size / innodb_flush_method 参数，显示设置的这些参数会优先生效，并且在MySQL的错误日志中会打印如下内容：

MySQL 8.0 首个自适应参数横空出世

'variable name' 指的就是 innodb_buffer_pool_size/innodb_log_file_size/inndob_flush_method参数。

注意：你不管是在配置文件、命令行、还是MySQL新引入的固化配置中设定上述三个参数都被认为是显式指定了参数值，都会优先生效。

显示指定某一个值，并不会影响其他变量的自适应参数值设置。例如显式设置了innodb_buffer_pool_size，那么buffer pool会按照你显示设置的值初始化，而不是 innodb_dedicated_server参数对应的值。但是innodb_log_file_size 和 innodb_flush_method 并不会受影响，它们还是会按照innodb_dedicated_server的自适应值按照服务器的内存大小来设置。
innodb_dedicated_server=ON的情况下，mysqld服务进程每次重启后都会自动调整上述三个参数值。在任何时候MySQL都不会将自适应值保存在持久配置中。
如果系统不支持O_DIRECT_NO_FSYNC，MySQL会沿用之前的默认值。MySQL仍然必须保证在所有平台上能正常启动，不需要任何其他更改。
如果自适应导致innodb_log_file_size对应的redo log file超过了磁盘空间限制（这个空间得有多小！），将会采取以下措施：
新生成的日志文件redo log将被删除
错误日志显示如下

"[ERROR] InnoDB: Error number 28 means 'No space left on device'
[ERROR] InnoDB: Cannot set log file to size MB"

* mysqld服务拒绝启动。

innodb_dedicated_server=ON并不见得是最优的配置。例如，你用了MyISAM，MyRocks等其他存储引擎时，建议手工调整，而不是设置innodb_dedicated_server=ON
XFS系统请手工设置inndob_flush_method=O_DIRECT。在inndob_flush_method=O_DIRECT_NO_FSYNC下，InnoDB使用O_DIRECT来刷新IO，但是跳过fsync()步骤。对某些文件系统有效，但是对XFS文件系统并不适用。为了保证文件的metadata刷新到磁盘中，XFS必须使用O_DIRECT。

｜自适应之前是怎么样的

在5.7上，innodb_buffer_pool_size默认为134217728即128MB，如果采用默认设置，MySQL 5.7大致只能消耗系统的512M内存。

而innodb_log_file_size=50331648 即48M，对于大并发下的请求并不适用。

这也导致大量文章建议采用相应的方法优化设置这些参数，例如：

https://www.percona.com/blog/2015/06/02/80-ram-tune-innodb\_buffer\_pool\_size/

https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

https://www.percona.com/blog/2017/10/18/chose-mysql-innodb\_log\_file\_size/

MySQL 8.0提供了innodb_dedicated_server=ON这个参数可以很大程度解决这方面的问题。

｜为什么调整这几个参数而不是其他参数

这个参数在InnoDB上对性能的影响相对较大，并且也是最急迫需要自适应调整的几个参数。(个人觉得innodb_buffer_pool_instances也应该在自适应调整的范围内)

目前它也只是探测了系统内存，实现起来比较简单，并且对性能改进非常有效，基本能解决绝大部分入门DBA安装的性能问题。就像一个在班级成绩排名倒数的同学，先帮他解决了60分及格的问题再考虑提高到班级前10名。

要解决其他问题，例如sort_buffer_size，read_rnd_buffer_size等连接内存自适应调整，需要对内存的精细控制，并且各种应用访问方式并不一样，并不是那么容易自适应；而innodb_read_io_threads，innodb_write_io_threads等需要根据CPU核数调整，也跟应用访问模式有一定关系；对于innodb_io_capacity而言，要探测底层存储设备具体的IO能力，并相应设置，也不是一个简单的工作。

到底其他影响性能的自适应参数什么时候调，只能敬请期待了。

｜适应场景

运行MySQL的服务器上是专门给MySQL提供服务的。innodb_dedicated_server的默认设置都是假设这个服务器的资源，MySQL都能用起来。

｜不适应场景

单机多实例情况下不适应。
其他有特殊场景要求的不适用。比如：不是主要以InnoDB为存储引擎的；服务器上还有其他应用程序的等等。

｜重大意义

各位云厂商的同志们有福了，利用这个参数就可以保证服务器（虚拟机或者容器）扩展以后，MySQL能“自适应”以尽量消耗更多的服务器资源，而不用自己设计一个自动扩展MySQL服务器资源配置的脚本。既避免了服务器扩展以后MySQLbuffer pool不变等，使用不了那么多资源；也避免了服务器缩减了以后MySQLbuffer pool过大等，导致MySQL服务进程启动不起来。

这个参数的改变，也意味着：