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