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

|什么是自适应参数

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的错误日志中会打印如下内容:
"[Warning] InnoDB: Option innodb_dedicated_server is ignored for because 'variable name'=? is specified explicitly."

'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服务进程启动不起来。

这个参数的改变,也意味着:

  • 后续MySQL的类似参数会越来越优化,DBA排查问题时对MySQL参数的考虑会越来越少
  • MySQL的运维DBA的工作越来越简单了,MySQL也会越来越智能

|作者简介

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

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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏快乐八哥

PHP+MySQL代码部署在Linux(Ubuntu)上注意事项

最近帮同学做一个网站,同学买的是阿里云服务器,Linux发行版是Ubuntu12.04。我在本地把程序写好,都调试好了。然后他让我自己发布和部署。之前在大学里上...

472100
来自专栏杨建荣的学习笔记

使用pt工具检测MySQL主从延迟(r12笔记第7天)

今天翻看了下《高性能MySQL》,真是让人拍手称绝,里面的很多实战思路非常不错,各种问题分析如数家珍,如果是有一定基础的同学,看起来会非常不错。 当然里...

49060
来自专栏程序员宝库

手把手教会你小程序登录鉴权

为了方便小程序应用使用微信登录态进行授权登录,微信小程序提供了登录授权的开放接口。乍一看文档,感觉文档上讲的非常有道理,但是实现起来又真的是摸不着头脑,不知道如...

32450
来自专栏咸鱼不闲

jsp与数据库的面对面交流

前言:要完成数据的交互的一种方式就是jsp+jsp+数据库。下面就来演示一个用jsp展示数据库内的图片的小例子。

12330
来自专栏沃趣科技

ASM 翻译系列第三十弹:高级知识 Physical metadata replication

原作者:Bane Radulovic 译者: 邱大龙 审核: 魏兴华 DBGeeK社群联合出品 Physical metadata replicat...

36640
来自专栏PHP在线

手把手教会你小程序登录鉴权

57040
来自专栏云计算教程系列

如何在MySQL Ubuntu 16.04上将MySQL数据目录移动到新位置

数据库随着时间的推移而增长,有时会超出文件系统的空间。当它们与操作系统的其余部分位于同一分区时,您也可能遇到I / O争用。RAID,网络块存储和其他设备可以提...

19000
来自专栏数据和云

【MySQL】磁盘写满之后,数据库show status受到阻塞的原因

编辑手记:前两天同事讨论到一个问题,当mysql从库磁盘满之后,show status及show slave status会被卡住,但其他select操作不受影...

40960
来自专栏从流域到海域

《笨办法学Python》 第46课手记

《笨办法学Python》 第46课手记 这节课制作了一个Python的项目骨架,花了我一个晚上和一个早上的时间,原因是我下载的pdf里面只有OX S的命令行,而...

25760
来自专栏java一日一条

数据库的读写分离

读写分离,基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE),而从数据库处理SELECT查询操作。数据库复制被用来把事务...

18730

扫码关注云+社区

领取腾讯云代金券