- 为何要调整参数
- 不同服务器之间的配置、性能不一样
- 不同业务场景对数据的需求不一样
- Mysql的默认参数只是个参考值，并不适合所有的应用场景
- 优化之前我们需要知道什么
- 服务器相关的配置
- 服务器型号
- 操作系统版本
- 内核版本
- 磁盘存储介质（sas sata ssd)
- 业务相关的情况
- 读多写少，读少写多
- 业务数据增长量
- mysql相关的配置
- 服务器相关的配置
- 服务器上需要关注那些
- 硬件情况
- cpu（几核、超线程）
- 内存
- 磁盘（容量、性能）
- 操作系统版本（是否为稳定版）
- CPU、网卡节电模式（建议数据库应用的服务器，关闭节电模式）
- 服务器numa设置
- RAID卡缓存
- 硬件情况
- 磁盘调度策略-write back（回写）（宕机的话cache中数据，如果没有刷入磁盘，可能丢失）
- 数据写入cache既返回，数据异步的从cache刷入存储介质
- 数据写入cache既返回，数据异步的从cache刷入存储介质
- 磁盘调度策略-write through（安全但性能比write back低）
- 数据同时写入cache和存储介质才返回写入成功
- 数据同时写入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版本>5.5
- 系统调优的依据：监控
- 实时监控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代表不做控制
- 如果并发请求较多，参数设置较小，后进来的请求将会排队
- innodb_buffer_pool_size
- 写优化
- 表结果设计上使用自增字段作为表的主键
- 只对合适的字段加索引，索引太多影响写入性能
- 监控服务器磁盘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事务的刷新方式，一共有三个值：0，1，2
- 保障事务的安全
- 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
- 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卡缓存）
- 完善的监控系统，提前发现问题
- 数据库版本要跟上，不要太新，也不要太老：
- 数据库优化
- 查询优化：索引优化为主，参数优化为辅
- 写入优化：业务优化为主，参数优化为辅
时间： 2024-10-30 03:31:53