mysql参数调优

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

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

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

服务器上需要关注那些

• 硬件情况
  
  • cpu（几核、超线程）
  • 内存
  • 磁盘（容量、性能）
• 操作系统版本（是否为稳定版）
• CPU、网卡节电模式（建议数据库应用的服务器，关闭节电模式）
• 服务器numa设置
• RAID卡缓存

• 数据写入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卡缓存）
• 完善的监控系统，提前发现问题
• 数据库版本要跟上，不要太新，也不要太老：
• 数据库优化
  - 查询优化：索引优化为主，参数优化为辅
  - 写入优化：业务优化为主，参数优化为辅