MySQL写性能监控及调优
MySQL写性能监控及调优
磁盘性能对数据库的读写能力影响很大，如何从多个角度监控数据库的写性能就变得至关重要，当写性能成为瓶颈时我们又该如何调优呢？
一、写压力性能监控
1、OS层监控：
[root@localhost ~]# iostat -x
Linux 4.18.0-193.el8.x86_64 (localhost.localdomain) 05/13/22 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
30.91 0.00 19.45 0.78 0.00 48.85
Device r/s w/s rkB/s wkB/s rrqm/s wrqm/s %rrqm %wrqm r_await w_await aqu-sz rareq-sz wareq-sz svctm %util
nvme0n1 173.29 42.89 5431.13 677.45 0.03 3.81 0.02 8.15 0.46 0.55 0.03 31.34 15.79 0.48 10.30
scd0 0.44 0.00 16.78 0.00 0.00 0.00 0.00 0.00 0.44 0.00 0.00 38.52 0.00 0.48 0.02主要关注以下几个指标：
1.iowait：io等待情况，iowait很高的话说明cpu花太多的时间在io等待上，说明系统的IO成瓶颈了，iowait一般希望小于5，大于25一般就需要进行分析查看了。
2.r/s和w/s：每秒的读和写，即IOPS。
3.%util：繁忙度，周期内用于IO操作的时间比例，如果IO不高，但是繁忙度高，就说明磁盘有问题了。
2、DB层面监控
1.数据库挂起监控 建议采集成趋势，当挂起状态值大于0且持续增加时做告警处理。
mysql> show global status like '%pend%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| Innodb_data_pending_fsyncs | 0 | #被挂起的fsync
| Innodb_data_pending_reads | 0 | #被挂起的物理读
| Innodb_data_pending_writes | 0 | #被挂起的写
| Innodb_os_log_pending_fsyncs | 0 | #被挂起的日志fsync
| Innodb_os_log_pending_writes | 0 | #被挂起的日志写
+------------------------------+-------+
5 rows in set (0.01 sec)2.写性能监控
mysql> show global status like '%Innodb_buffer_pool_wait_free%';
mysql> show global status like '%Innodb_log_waits%';重点指标关注：
1、Innodb_buffer_pool_wait_free，如果该值大于0，说明buffer pool中已经没有可用页，等待后台往回刷脏页，腾出可用数据页，这样就很影响业务了，hang住。
2、Innodb_log_waits，如果该值大于0，说明写压力很大，出现了日志等待。
3.脏页占比监控
Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total,通过该比值可以看出脏页的占比情况，如果该系统脏页占比持续增高，说明该系统是以写为主，根据情况进行调优。
二、写压力调优参数
1、刷新脏页的频率
mysql> show variables like '%innodb_lru_scan_depth%';
#默认1024，遍历lru list刷新脏页，值越大，说明刷脏页频率越高。2、磁盘io能力
mysql> show variables like '%io_c%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
+------------------------+-------+
2 rows in set (0.00 sec)
#根据磁盘io能力进行调整，值越大，每次刷脏页的量越大。3、redolog的刷新机制
mysql> show variables like '%innodb_flush_log_at_trx_commit%';默认MySQL的刷盘策略是1，最安全的，但是安全的同时，自然也就会带来一定的性能压力。
关于redolog的刷盘策略如下：
0：当提交事务时，并不将事务的redo log写入logfile中，而是等待master thread每秒的刷新redo log，数据库崩溃丢失数据，丢一秒钟的事务。
1：执行commit时将redo log同步写到磁盘logfile中，同时做刷盘动作，保证不丢失事务。
2：在每个提交，日志缓冲被写到文件系统缓存，但不做磁盘的刷新，数据库宕机而操作系统及服务器并没有宕机时，可以保证数据不丢失，但是文件系统崩溃会丢失数据。
4、内存脏页占比控制
mysql> show variables like '%innodb_max_dirty_pages_pct%';
#在内存buffer pool空间允许的范围下，可以调大脏页允许在内存空间的占比，降低写压力。