mysql参数调优

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

• 优化之前我们需要知道什么

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

• 服务器上需要关注那些

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

• 磁盘调度策略-write through（安全但性能比write back低）

  • 数据同时写入cache和存储介质才返回写入成功
    

• RAID

  • 生产环境里一般不太会用裸设备，通常会使用RAID卡对一个盘或多块盘做RAID
  • RAID卡会预留一块内存，来保证数据高效的存储与读取
  • 常见的RAID类型：RAID1、RAID0，RAID10、RAID5
    
    
  • 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参数也能提高查询性能
      
      • 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中事务提交的过程
      
  • 串行的问题
    
    • SAS盘一般每秒只能有150-200个Fsync
    • 换算到数据库每秒只能执行50-60个事务

  • 社区和官方的改进

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

  • InnoDB Redo Log

    • write ahead log
      
  • 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只对二级索引且非唯一索引有效
      

• 总结

  • 服务器配置要合理（内核版本、磁盘调度策略、RAID卡缓存）
  • 完善的监控系统，提前发现问题
  • 数据库版本要跟上，不要太新，也不要太老：
  • 数据库优化
    - 查询优化：索引优化为主，参数优化为辅
    - 写入优化：业务优化为主，参数优化为辅