mysql参数调优

  • 为何要调整参数
    • 不同服务器之间的配置、性能不一样
    • 不同业务场景对数据的需求不一样
    • Mysql的默认参数只是个参考值,并不适合所有的应用场景
  • 优化之前我们需要知道什么

    • 服务器相关的配置
      • 服务器型号
      • 操作系统版本
      • 内核版本
      • 磁盘存储介质(sas sata ssd)
    • 业务相关的情况
      • 读多写少,读少写多
      • 业务数据增长量
    • mysql相关的配置
  • 服务器上需要关注那些

    • 硬件情况
      • cpu(几核、超线程)
      • 内存
      • 磁盘(容量、性能)
    • 操作系统版本(是否为稳定版)
    • CPU、网卡节电模式(建议数据库应用的服务器,关闭节电模式)
    • 服务器numa设置
    • RAID卡缓存
  • 磁盘调度策略-write back(回写)(宕机的话cache中数据,如果没有刷入磁盘,可能丢失)
    • 数据写入cache既返回,数据异步的从cache刷入存储介质
      mysql参数调优
  • 磁盘调度策略-write through(安全但性能比write back低)

    • 数据同时写入cache和存储介质才返回写入成功
      mysql参数调优
  • RAID

    • 生产环境里一般不太会用裸设备,通常会使用RAID卡对一个盘或多块盘做RAID
    • RAID卡会预留一块内存,来保证数据高效的存储与读取
    • 常见的RAID类型:RAID1、RAID0,RAID10、RAID5
      mysql参数调优
      mysql参数调优
    • RAID如何保证数据安全
    • BBU(backup battery unit)
      • BBU保证在WB策略下,即使服务器发生掉电或宕机,也能够将缓存中的数据写到磁盘,从而保证数据的安全
    • BBU损坏或没电了,这时如果宕机,cache中数据可能丢失。并且,调度策略会从WB->WT,这时数据库性能会瞬间下降。
  • Mysql注意事项

    • Mysql的部署安装
    • Mysql的监控(监控程序,及时报警保存错误现场)
    • Mysql参数调优
  • 部署Mysql的要求

    • 推荐Mysql版本>5.5
      • 推荐的Mysql存储引擎:innoDB(支持事务,支持宕机故障恢复等)
  • 系统调优的依据:监控

    • 实时监控Mysql的slow log
    • 实时监控数据库服务器的负载情况(IO、load、cpu利用率、网卡流量)
    • 实时监控Mysql内部状态值
      • Com_Select/Update/Delete/Insert(以判断数据库的请求是否变多)
      • Bytes_received/Bytes_sent(接收发送字节数,反映mysql总的吞吐量)
      • Buffer Pool Hit Rate(innoDB buffer区的命中率,直接反映性能)
      • Threads_connected/Treads_created/Threads_running(连接的状态,如果前两项特别多,可以看看应用是否使用连接池或者设置是否合理;)
        mysql参数调优
  • Mysql参数调优

    • 读优化

      • 合理的利用索引对Mysql查询性能至关重要
      • 适当的调整Mysql参数也能提高查询性能
        • innodb_buffer_pool_size
          • innoDB存储引擎自己维护一块内存区域完成新老数据的替换
          • 内存越大越能缓存更多的数据
        • innodb_thread_concurrency(在5.5以后的版本,建议关闭)
          • innoDB内部并发控制参数,设置为0代表不做控制
          • 如果并发请求较多,参数设置较小,后进来的请求将会排队
    • 写优化

      • 表结果设计上使用自增字段作为表的主键
      • 只对合适的字段加索引,索引太多影响写入性能
      • 监控服务器磁盘IO情况,如果写延迟较大则需要扩容
      • 选择正确的mysql版本,合理的设置参数
        • innodb_flush_log_at_trx_commit && sync_binlog(写性能主要参数)
        • innodb log file size
        • innodb_io_capacity
        • innodb insert buffer
    • innodb_flush_log_at_trx_commit 控制redo日志的刷新
      • 控制innoDB事务的刷新方式,一共有三个值:0,1,2
        • N=0 每隔一秒,把事务日志缓冲区的数据写到日志文件中,以及把日志文件的数据刷新到磁盘上(高效,但不安全)
        • N=1 每个事务提交时候,把事务日志从缓冲区写到日志文件中,并且刷新日志文件的数据到磁盘上,优先使用此模式保障数据安全性(低效,非常安全)
        • N=2 每个事务提交的时候,把事务日志数据从缓存区写到日志文件中;每隔一秒,刷新一次日志文件,但不一定刷新到磁盘上,而是取决于操作系统的调度(高效,但不安全)
      • sync_binlog 控制innoDBbinlog日志的刷新
        • 控制每次写入binlog,是否都需要进行一次持久化
    • 保障事务的安全
      • innodb_flush_log_at_trx_commit 和sync_binlog都设为1
      • 事务要和binlog保证一致性
        innoDB中事务提交的过程
        mysql参数调优
    • 串行的问题
      • SAS盘一般每秒只能有150-200个Fsync
      • 换算到数据库每秒只能执行50-60个事务
    • 社区和官方的改进

      • MariaDB提出改进,即使两个参数都是1也能做到合并效果,性能得到了大幅提升。
      • 官方吸收了MariaDB的思想,并在此基础上进行了改进,性能再次得到了提升
      • Tips:官方在5.6以后才有这个优化;Percona和MariaDB版本的mysql5.5已经包含了这个优化
        mysql参数调优
    • InnoDB Redo Log

      • write ahead log
        mysql参数调优
    • redo log的作用
      • 用于数据库崩溃后的故障恢复
    • redo log的问题

      • 如果写入频繁导致redo log里对应的最老的数据脏页还没有刷新到磁盘,此时数据库将卡住,强制刷新脏页到磁盘
      • Mysql默认配置两个文件才10M,非常容易写满,生产环境中应适当调整大小
    • innodb_io_capacity

      • innoDB每次刷多少个脏页,决定InnoDB存储引擎的吞吐能力。
      • 在SSD等高性能存储介质下,应该提高该参数以提高数据库的性能。
    • Insert Buffer(本质是把随机请求合并为顺序请求)

      • 5.1开始支持insert buffer
      • 5.5同时支持update和delete的merge
      • insert buffer只对二级索引且非唯一索引有效
        mysql参数调优
  • 总结

    • 服务器配置要合理(内核版本、磁盘调度策略、RAID卡缓存)
    • 完善的监控系统,提前发现问题
    • 数据库版本要跟上,不要太新,也不要太老:
    • 数据库优化
      - 查询优化:索引优化为主,参数优化为辅
      - 写入优化:业务优化为主,参数优化为辅
上一篇:mysql主从复制原理及存在的问题


下一篇:mysql semi-sync(半同步复制)