积累了长达十五年的数据库相关经验, 期间担任过 DBA, 还曾作为架构师, 也一度成为技术顾问。并非追求那种称为“颠覆”的效果, 仅仅是渴望达成所谓“靠谱”的状态。数据库装完就能跑但默认配置离生产就绪差得远。许多团队在上线之前执行的最后一个行为是, 安装数据库, 导入数据且应用与之相连, 测试使其能够顺利运行方可上线。在全部的这一系列流程当中, 尚无任何人对数据库参数进行过触碰啊。上月承接了一个线上性能方面的问题, 经排查后发觉, 有着8核32G配置的服务器, 其ize竟依旧是默认的128M。数据库能够正常运行, 然而90%的读请求均需返回至磁盘。将pool调整至20G后, 性能提升了3倍。仅仅更改了一个参数, 并未对任何代码作出改动。从事 DBA 工作这么多年, 参数调优这件事, 是最容易被忽视掉的, 同时也是最容易取得成果的。值此今日, 将参数调优的方法论梳理出来, 从内存方面说起, 再到连接层面, 接着是日志领域, 最后到优化器部分, 逐一依次把它们说透彻、讲明白。01 内存参数——最花钱的硬件别让它闲着在数据库里头, 内存属于最为关键重要的资源, 磁盘运行速度慢, 网络运行速度也慢, 然而内存运行速度快得很, 但凡能够将数据放置进内存进行处理的情况, 就千万不要让其前往磁盘去处理。MySQL pool 是头号参数ize对引擎缓存数据的内存大小予以控制, 数据库将读到的数据页放进pool, 下次再度读到同一条数据时, 直接从内存返回, 而非前往磁盘。默认值128M。生产建议内存总量的 60%-70%。以怎样的方式去进行计算呢? 比如说假定服务器具备32G的内存, 操作系统以及数据库进程自身大概会占用2 - 3G, 其他的应用程序占用2 - 3G, 那么剩余约26G能够给予pool。较为保守地设定为20G大概是60%, 更为激进些设定为24G大约是75%。查看eads也就是从磁盘读的请求数, 再查看总读请求数, 这是验证方法。两个数的比值被称作“磁盘读取率”。要是超过了5%, 那这就表明pool不够大, 大量读请求仍然是在通过磁盘进行。 和这种情况是共享缓冲区, 它和类似 MySQL 的 pool 相类似, 其默认值为 128MB, 对于生产提出的建议是设置为内存的 25%至 40%, 不过在这里是不建议设置得太大的, 一旦超过 40%就会出现收益递减的状况, 原因在于它是还依赖操作系统的 page cache 的。对于单个查询排序, 或者哈希操作而言存在着可用的内存上限, 其默认值是4MB, 然而这一数值实在是太小了。当要排序具有大规模数据的表, 或者进行哈希连接操作时, 4MB的内存量完全无法满足需求, 此时数据库会将中间结果写入设置在磁盘上创建出的临时文件中用于持久保存该中间数据。留意: 并非全局限定, 而是每一个查询每一回排序或者散列操作用到的内存。要是一个查询采用了3个排序操作, 它有可能耗费3倍。要是同时存在100个连接在运行, 总的内存耗费有可能远远达到100乘以3倍。因而这个数值不能够设定得太大, 通常64兆字节到256兆字节会更安全。踩坑提醒曾经, 我见到过这样个团队, 他们把ize设置到了30G, 然而服务器总共才32G。随后, 操作系统内存不够了, 于是开始swap也就是用磁盘当作内存, 可整个系统反倒比设置为20G的时候慢了好多倍。所以说, 内存参数不能贪心, 得留有余量。02 连接参数——不是越多越好很多人以为 设得越大越好觉得设大了总不会出问题。实际情况与之相反, 每一个数据库连接均会占用内存, 其中MySQL的每个连接大约占用2至5MB, 而另外的则每个连接约占用10MB, 倘若连接数设置得过大, 当连接全部处于满负荷状态时, 内存便会出现爆掉的情况。MySQL 和默认的值是: 150。关于生产给出的建议是: 要依据实际并发来进行调整, 一般范围是 500 - 2000。在进行设置之前自己要问一下: 应用层实际所拥有的活跃连接数量是多少呢? 要是连接池配置的是 50 个连接, 设置为 5000 是没有意义的。和: 连接时, 空闲状态下, 自动断开的时长是多久。其默认的值是 28800 秒 , 也就是 8 小时。这个时长太长。每当有连接处于空闲状态 , 要经过 8 小时才会断开 , 这就表明原本已经泄漏的连接 , 需要等待 8 小时那般长的时间 , 才能被清理完成。生产方面给出的建议是, 设置时长为六百秒到一千八百秒, 也就是十分钟到三十分钟, 并且要配合连接池的健康检查, 如此才能够及时回收出现泄漏的连接。 和默认值是 100。就生产环境而言该数值偏低, 一般给出的建议是 200 到 500。其连接属于进程模型, 也就是每个连接对应一个操作系统进程, 内存开销相较于 MySQL 会更大, 因而连接数不适宜设定得过高。要是存在大量连接所需场景, 建议借助连接池, 在中间层进行复用。数量是留给超级用户的连接数, 目的在阻止掉普通连接打满之后, DBA 就登不进去的情况出现。其默认的值是 3。给出的建议就是予保留, 不要将其改成 0。踩坑提醒并非是“数据库能承受的数量”, 而是“应用层所需求的数量”才是连接数在最佳实践方面的体现。首先要去查询应用层连接池的配置, 其中包括某种特定的、Druid的, 以此来确认出实际的并发连接数 , 进而再依据此倒推出数据库的情况。数据库端所设定的值只要比连接池总和大20% - 50%作为余量便足够了。03 WAL/日志参数——数据安全的关键WAL亦称作预写式日志, 它属于数据库确保数据无丢失的一种机制, 在进行数据写入操作之前, 会先执行日志记录过程, 哪怕数据库出现了诸如突然崩溃之类的异常状况, 在重启之后, 其照样能够借助日志记录实现完全恢复。MySQLredo log 和重新执行日志文件大小, 默认数值为48M。该数值过小。其是环形的, 一旦写满便要去做将脏数据刷写到磁盘。重新执行日志越小, 操作越频繁, 刷写到磁盘的开销也就越大。生产方面给出的建议是, 范围为1G到4G。要是写入量庞大, 也就是每秒存在几百个事务这种情况 , 那么能够设置到4G。此两者参数, 用于控制数据刷盘频率, 其决定了崩溃之后会丢失多少数据, 可以丢多少数据。存在这样一条生产建议, 核心业务, 也就是金融以及支付方面, 要采用最为安全的组合, 对于可以接受的场景, 则使用折中方案, 不要采用最快的组合, 因为所面临的丢失数据的风险, 要远远大于性能提升带来的好处。WAL 和WAL日志所记录的详细程度, 其默认的值是, 要是有逻辑复制的需求, 那么就得改成。表示 WAL 文件的最大累积起来的大小, 其默认的值是 1GB, 当进行写入的量比较大的时候, 建议将其调整到 4 至 8GB, 以此来降低频率。时间占比被完成的情况。默认数值是: 0.9。这所意味的是, 进行刷盘时, 要运用90%的间隔去完成, 为的是防止将磁盘一次性填满。通常, 这个数值是不需要进行更改的。踩坑提醒在一个项目里, 线上忽然慢了好几分钟, 经过排查后发现, 原来是触发了大量的刷盘情况, 致使磁盘I/O被完全占满了。造成这种状况的原因是, 设置得十分小仅仅1GB, 当写入量比较大的时候, 就会频繁地触发此项。随后将其调大到了4GB, 并且同时把维持在0.9, 如此一来, 问题便消失了。04 查询优化器参数——让数据库更聪明数据库优化器所要做的决策, 是依据统计信息来进行的。统计信息一旦不够准确, 或者优化器所设置的 “胆量” 存在不正确的情况那么所选的执行计划就会出现错误。MySQL 和 limit限定: 处于IN列表之中的索引选择策略的阈值。当IN列表较短之时小于这个数值, 优化器会实际前往索引里进行潜水操作也就是index dive来估算行数, 其结果准确但速度缓慢。当IN列表较长之时大于或者等于这个数值, 优化器运用统计信息来估算, 速度快但不准确。通常所设定的默认值是: 200。一般情形下并不需要去进行调整与更改。但是倘若业务存在超长的 IN 列表, 举例来说, 就是 IN 后面跟随几千个具体价值, 那么这种时候或许就需要将这个值予以调大, 以此让优化器能够更为精准精确地做出估算。arget 和arget: 用于规定在下达收集统计信息指令之际的采样精准程度。其默认数值为: 100。要是面对数据分布并非均匀的大型表格, 可将其调整至500 - 1000, 如此便能够明显增进统计信息的精确程度。数据库中, 优化器会对随机I/O的代价进行估算, 其默认值为4.0, 该值是基于机械硬盘的假设而设定, 如果数据库使用的是SSD, 那么随机I/O与顺序I/O的代价差异不大, 这种情况下建议将该值调整到1.0 - 1.5。关键之处何在: 要是 SSD 服务器之上, 依旧处于默认的 4.0 状态, 那么优化器就会对随机 I/O 的代价作出过高估计, 从而趋向于采用 Seq Scan 而非 Index Scan。实际呈现的情况便是, 明明存在索引, 然而优化器却并不加以运用。踩坑提醒一个 SSD 服务器之上, SQL 查询出现了全表扫描的情况, 人们都觉得是索引构建有误。经过长时间查找之后, 发现它依旧是默认的 4.0, 优化器认定走索引的随机 I/O 代价过高, 宁愿进行全表扫描。在改成 1.1 之后, 优化器马上采用了索引扫描, 查询时间从 5 秒变为 50ms。对比开发/测试/生产环境参数差异核心认知是, 当可以将测试环境安排得越跟生产环境接近时, 测试结果所具备的参考价值那就会越大 ,有好多性能方面的问题在测试环境中没有能够被发现, 仅只是由于测试环境动用的是默认参数罢了, 跟生产环境之间的差距实在是太远了。决策框架按 类型给参数基线OLTP 场景在线交易短查询、高并发关键想法如下, 一是内存优先供应给pool , 二要降低磁盘读取, 三连接数量够用即可, 不可过分贪多, 四刷盘策略选最具安全性的, 五确保交易数据不会丢失。OLAP 场景分析查询大扫描、复杂聚合关键想法是, 进行大型查询的时候, 会需要更多的操作来开展排序以及哈希处理, 对于pool可以少分配一些这是由于在对整个表进行扫描, 缓存的命中率并不高, 把统计的信息精度进行提高, 如此优化器才能够挑选出针对大表的正确执行计划。混合负载OLTP OLAP 共存核心思路是, OLTP与OLAP会争抢资源, pool采取折中方式, 不能太大因为要防止OLAP的大查询将所有内存吃光, 如果OLAP查询负担过重, 就得考虑将分析查询路由至只读副本, 避免和生产争夺资源。从参数就是填数字到参数是系统的一部分实事求是来讲, 在从事DBA工作的前三年时间中, 我对于参数进行调优时所秉持的态度是遵循”参照着网络上现成的模板去填写数据”这个方式, 当其他人声称pool设置为内存的70%时, 我便会按照此建议将之设定为70%, 当有些人表示应该设置为64MB的时候, 那我也就会依言设定为64MB。往后承接了好些性能优化的项目, 这才弄明白: 参数不存在标准的答案。相同的参数, 于SSD服务器上是良好的, 于机械盘上或许是灾祸。同样地, 在OLTP场景里是合乎情理的, 在OLAP场景下可能过小。这个认知上的转变, 使得我对参数调优的本质有了全新的理解, 参数并非简单的“填数字”行为, 而是向数据库传达“你的运行环境是怎样一种状况”的意味。并且, 你得告知它所使用的是 SSD 或者机械盘, 是 OLTP 类型还是 OLAP 类型, 数据量究竟有多大, 并发数量又有多少, 唯有如此, 它才能够据此做出最为优化的决策。参数调优不是玄学是把系统特征翻译成数据库能理解的参数。深度分析为什么照着模板调参数行不通在网上, 存在着数目众多的“生产环境参数模板”, 这些模板是拿来即可投入使用的。然而, 当真正将其运用起来的时候, 所呈现出的效果并非必然良好。根因所在之处为: 参数模板预设了一个称作“标准生产环境”的情况, 具体是16核64G、SSD、OLTP负载以及MySQL 8.0。然而情况或许大不相同于这般的存在: 就如8核32G、机械盘、混合负载, 还有15。更深层次而言, 参数彼此之间存有联动关联。举例来说, 当把它设置得较大时, 单个的查询过程会变得更快, 然而要是并发查询的数量较多, 那么总的内存消耗就有可能出现爆掉的情况。当将某一参数调大之后, 其频率会有所降低, 不过崩溃恢复所需的时间则会变长。恰当的举措是: 首先弄明白自身系统的特性, 诸如硬件、负载类别、数据分布、并发模式等方面的情况, 接着依据这些特性来做出参数的调节, 至于每一次调节一个参数之后, 那就去验证一下所呈现的成效, 此处并非凭借数据库能否正常运行来评判, 而是要去查看关键指标有没有获得改善, 例如考察 pool 的命中率、慢查询的数量、磁盘 I/O 的利用率等这些关键指标有没有提升。这一思路, 跟 DBA 的日常所给出的建议是相一致的, 不存在银弹参数, 有的只是在对你的系统予以理解之后所做出的合理调整。参数调优验证清单调完参数后按这个清单逐项验证1. 内存验证2. 连接验证3. 日志/WAL 验证4. 优化器验证5. 压力验证总结数据库参数调优存在着这样一套流程, 那便是先去了解系统的特征, 接着按照类型设定基线, 然后逐项进行验证, 最后通过压测予以确认。核心层面的认知是, 并非像那种简单的“填数字”般是对参数的理解, 参数是向数据库传达你所处运行环境究竟是何种状况的标记东西。不存在能适应于所有情况的万能参数, 有的只是与你所拥有的系统匹配得上的具备合理性的这种参数。防止参数出现问题的较为重要的点在于, 内存之中要留存下来一定的余量, 连接之时不能过多贪图, 刷盘要挑选出安全的方式, 对SSD进行调整时要关注cost, 统计应当是最新的。调整过数量达到上百个的数据库的参数, 每一次返回至这套流程, 效果便能呈现出来。并不需要何种高级工具, 单单理解其原理以及具备耐心进行验证就足够了。接下来, 我会持续分享有关数据库容量规划, 以及迁移前 SQL 审计的这些相关话题, 按照我的分享一篇一篇地学习, 那么在数据库这个领域就不会出现问题了。