MySQL-性能调优思路
2024-05-24 10:52:11 4 举报AI智能生成
基于实际测试数据梳理
作者其他创作
大纲/内容
架构图
MySQL 引擎架构
innodb 架构图
基准测试
工具一：sysbench
参考1
参考2
调优方向
服务器
阿里云ecs实例规格说明
磁盘
阿里云云盘性能指标
文件系统
内存
启用大的内存页面
适合内存密集型应用、高性能需求场景；可以减少内存管理开销，减少TLB(页表)未命中和缺页异常
cpu
MySQL 架构层面调优
版本升级
bin log
记录数据库的更改操作，用于数据恢复、主从同步等；记录的是逻辑日志（SQL级别的操作），而非物理存储级别的更改
binlog_format
statement
row
mixed
max_binlog_size
单个日志文件大小
bin log cache优化
binlog_cache_size
基于会话设置可用缓存的大小
关注缓存使用率，减少磁盘IO次数
binlog_cache_use/binlog_cache_disk_use
基于disk_use的占比来识别cache大小是否足够
日志提交优化
组提交（慎用）
刷盘策略(sync_binlog)
0
mysql不控制刷新，有操作系统对缓存进行控制
1
每个事务都进行刷新（写盘）；可靠性最好，但资源开销最大
N
N个事务后进行刷新，可靠性和性能之间做权衡
binlog_row_image
row的格式
默认为full，记录行的完整前镜像和后镜像
full和minimal的差别
minimal，记录必要的更改内容
partial，在full和minimal之间折中；
对于被更改的每一行，binlog 包含该行所有列的 after image，
但 before image 仅包含主键列和已更改的非主键列。
如果表中没有主键，则包含所有列
对于被更改的每一行，binlog 包含该行所有列的 after image，
但 before image 仅包含主键列和已更改的非主键列。
如果表中没有主键，则包含所有列
连接池
thread_cache_size
关注缓存命中率
thread_stack
max_connections
cache / buffer
table_open_cache
关注表缓存命中率
join buffer
sort buffer
read buffer / read rnd buffer
tmp table size / max heap table size
tmp_table_size: 内存临时表的最大大小，超过该大小时在磁盘上创建
关注临时表的创建次数(Created_tmp_tables / Created_tmp_disk_tables)
Innodb 存储引擎调优
buffer pool
主要缓存数据(页)和索引(页)信息，用于减少磁盘IO
缓存大小(innodb_buffer_pool_size)
关注总页数、空闲页数、脏页数、刷盘速度
页-空闲率 = （Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) * 100%
脏页比 = (Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total ) * 100%
刷盘速度 = 两次Innodb_pages_written采样计算写入速度
Innodb_pages_written：启动以来，Innodb写入磁盘的总页面数量
关注读缓存命中率
读命中率 = (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100%
实例数(innodb_buffer_pool_instances)
innodb_fsync_threshold
innodb_old_blocks_pct / innodb_old_blocks_time
控制old blocks区域的占比 / 被访问后在old区停留时长
刷新策略
innodb_page_cleaners
脏页写入磁盘的线程数量
innodb_max_dirty_pages_pct_lwm
脏页百分比低水位线(安全线)；脏页比低于该值时，innodb会减少脏页刷盘操作
innodb_max_dirty_pages_pct
脏页百分比高水位线(报警线)；脏页比高于该值时，innodb会增加脏页刷盘操作
innodb_idle_flush_pct
定义空闲时脏页刷新速度，默认为100
空闲时脏页刷新速度计算：innodb_io_capacity * innodb_idle_flush_pct%，单位：页/s
innodb_flush_neighbors
8.0之后默认关闭
刷盘时是否刷新临近页
redo log
用于确保事务的持久性和数据的一致性，在数据库发生异常宕机后，用来进行数据恢复；记录的是物理日志（数据页的物理更改）
日志文件配置
innodb_log_file_size：文件大小
innodb_log_files_in_group：文件个数
日志提交策略（innodb_flush_log_at_trx_commit）
0
每秒将日志写入并刷新到磁盘；性能最好，但也最不安全；如果mysql宕机，可能会丢失1秒的数据
1
每次事务提交时，日志都会被写入并刷新到磁盘。这是最安全的设置，但也是最慢的；
2
每次事务提交时，写入操作系统的文件缓存
由操作系统来调度控制刷盘时机，当操作系统宕机时，可能丢失最后一次未刷盘的数据，一般可认为1s
性能和安全性介于0和1之间
缓存大小（innodb_log_buffer_size）
undo log
回滚日志，当事务执行异常时对执行的操作进行回滚
(其它) io相关
刷盘策略
刷新方式(innodb_flush_method): O_DIRECT, fsync等
fsync：默认，安全性+兼容性好，但性能相对较低
O_DSYNC: 安全性和性能均适中
O_DIRECT：性能较高，安全性较低；绕过了操作系统缓存，对硬件和系统配置要求高
innodb_use_fdatasync
on
性能更好，适用于对数据一致性要求不是特别严格的场景
off
牺牲部分性能，适用于对数据一致性要求高的场景
io吞吐量
innodb_io_capacity
innodb_io_capacity_max
io并发数
innodb_thread_concurrency
innodb_read_io_threads
innodb_write_io_threads
innodb_doublewrite
关闭可以提升性能，但降低了可靠性；不建议关闭
部署层面
多块磁盘，分担IO压力
业务驱动
事务隔离级别（按需）
sql
慢sql
slow log
未走索引sql
全部sql
general log
sql效率分析
explain
查看执行计划
profile
分析观察耗时、cpu、context切换、block信息
optimizer_trace
查看查询优化器在解析和执行SQL查询时的内部决策过程
案例
隐式转换
数据类型
字符集
delete limit
表结构
索引
死锁/事务回滚
事务回滚次数：Handler_rollback
长事务
表优化
alter or optimize table
其他
error log
工具
MySQL Workbench
percona_toolkit
sysbench
自带的命令
...
测试过程数据
主要的调优方向
比较激进的调优方向
收藏
0 条评论
下一页
