mysql调优_详解MySQL以写为主的系统如何进行性能监控与调优，值得收藏

概述

今天主要分享下mysql数据库写压力的性能监控与调优，分成性能监控、相关调优参数两方面来做介绍。

参考：http://www.cnblogs.com/geaozhang/

---

一、写压力性能监控

1、OS层面的监控：iostat -x

# iostat -x

说明：

• rrqm/s：每秒进行merge的读操作数目。
• wrqm/s：每秒进行merge的写操作数目。
• r/s：每秒完成的读I/O设备次数。
• w/s：每秒完成的写I/O设备次数。
• rsec/s：每秒读扇区数。
• wsec/s：每秒写扇区数。
• rkB/s：每秒读K字节数。
• wkB/s：每秒写K字节数。
• avgrq-sz：平均每次设备I/O操作的数据大小(扇区)。
• avgqu-sz：平均I/O队列长度。
• await：平均每次设备I/O操作的等待时间(毫秒)。
• svctm：平均每次设备I/O操作的服务时间(毫秒)。
• %util：一秒中有百分之多少的时间用于I/O操作，或者说一秒中有多少时间I/O队列是非空的。

其实监控系统的io状况，主要查看%util、r/s、w/s，一般繁忙度在70%，每秒写也在理想值了；但如果系统目前繁忙度低，每秒写很低，可以增加写入。

2、DB层面监控：监控各种pending(挂起)

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)

写挂起次数值大于0，甭管是什么写挂起，出现挂起的话就说明出现写压力，所以值最好的是保持为0。监控“挂起”状态值，出现大于0且持续增加，报警处理。

3、写入速度监控：日志写、脏页写

3.1、日志写入速度监控

mysql> show global status like '%log%written';

从 Innodb_os_log_written 可以知道 innodb 日志的写入量，从而判断 innodb_log_buffer_size 的大小是否合适，同时也可以估计出一个 log_file 大概能支持多长时间。innodb 当buffer满了，事务提交或每秒1次刷新，看哪个更早发生。

3.2、脏页写入速度监控

mysql> show global status like '%a%written';+----------------------------+---------+| Variable_name | Value |+----------------------------+---------+| Innodb_data_written | 1073152 | #目前为止写的总的数据量，单位字节| Innodb_dblwr_pages_written | 7 || Innodb_pages_written | 58 | #写数据页的数量+----------------------------+---------+3 rows in set (0.01 sec)

3.3、关注比值：Innodb_dblwr_pages_written / Innodb_dblwr_writes，表示一次写了多少页

mysql> show global status like '%dblwr%';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| Innodb_dblwr_pages_written | 7 | #已经写入到doublewrite buffer的页的数量| Innodb_dblwr_writes | 3 | #doublewrite写的次数+----------------------------+-------+2 rows in set (0.00 sec)

1、如果该比值是64：1，说明doublewrite每次都是满写，写的压力很大。

2、如果系统的double_write比较高的话，iostat看到的wrqm/s(每秒合并写的值)就高，因为double_write高意味着每次写基本上都是写2M，这时候就发生更多的合并，但wrqm/s高并不害怕，因为发生合并是好事，看wrqm/s和繁忙度能不能接受。

4、脏页的量监控

mysql> show global status like '%dirty%';+--------------------------------+-------+| Variable_name | Value |+--------------------------------+-------+| Innodb_buffer_pool_pages_dirty | 0 | #当前buffer pool中脏页的数量| Innodb_buffer_pool_bytes_dirty | 0 | #当前buffer pool中脏页的总字节数+--------------------------------+-------+2 rows in set (0.01 sec) mysql> show global status like 'i%total%';+--------------------------------+-------+| Variable_name | Value |+--------------------------------+-------+| Innodb_buffer_pool_pages_total | 8192 | #buffer pool中数据页总量+--------------------------------+-------+1 row in set (0.01 sec)

关注比值：Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total，脏页占比

通过比值看脏页是否多，比如脏页10%的话，可以判断系统可能不是写为主的系统。

5、写性能瓶颈

mysql> show global status like '%t_free';+------------------------------+-------+| Variable_name | Value |+------------------------------+-------+| Innodb_buffer_pool_wait_free | 0 |+------------------------------+-------+1 row in set (0.01 sec) mysql> show global status like '%g_waits';+------------------+-------+| Variable_name | Value |+------------------+-------+| Innodb_log_waits | 0 |+------------------+-------+1 row in set (0.00 sec)

说明：

Innodb_buffer_pool_wait_free，如果该值大于0，说明buffer pool中已经没有可用页，等待后台往回刷脏页，腾出可用数据页，这样就很影响业务了，hang住。

Innodb_log_waits，如果该值大于0，说明写压力很大，出现了日志等待。如果Innodb_log_waits状态变量(等待日志缓冲刷出的次数)的值比较高，而且继续增长，可以增大log buffer或者降低事务大小。

6、系统真实负载：rows增删改查 、事务提交、事务回滚

mysql> show global status like 'i%rows%';+----------------------+-------+| Variable_name | Value |+----------------------+-------+| Innodb_rows_deleted | 0 || Innodb_rows_inserted | 145 || Innodb_rows_read | 233 || Innodb_rows_updated | 5 |+----------------------+-------+4 rows in set (0.01 sec) mysql> show global status like '%commit%';+----------------+-------+| Variable_name | Value |+----------------+-------+| Com_commit | 0 || Com_xa_commit | 0 || Handler_commit | 16 |+----------------+-------+3 rows in set (0.01 sec) mysql> show global status like '%rollback%';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| Com_rollback | 0 || Com_rollback_to_savepoint | 0 || Com_xa_rollback | 0 || Handler_rollback | 0 || Handler_savepoint_rollback | 0 |+----------------------------+-------+5 rows in set (0.01 sec)

通过监控系统真实负载，如果业务正常，负载上升，此时，就要根据业务具体情况，进行相应的调优。

---

二、写压力调优参数

通过调整参数降低写压力时，一定要实时关注iostat系统的各项指标。

1、脏页刷新的频率

mysql> show variables like 'i%depth%';

默认1024，遍历lru list刷新脏页，值越大，说明刷脏页频率越高。

2、磁盘刷新脏页的量：磁盘io能力

mysql> show variables like '%io_c%';

根据磁盘io能力进行调整，值越大，每次刷脏页的量越大。

3、redolog调优

mysql> show variables like 'innodb_log%';

logfile大小和组数可能会导致写抖动：日志切换频率需要监控(文件系统层面技巧)。

4、redolog的刷新机制

mysql> show variables like '%flush%commit';

这里我优化了一下

默认MySQL的刷盘策略是1，最安全的，但是安全的同时，自然也就会带来一定的性能压力。在写压力巨大的情况下，根据具体的业务场景，牺牲安全性的将其调为0或2。

5、定义每次日志刷新的时间

mysql> show variables like 'innodb_flush_log_at_timeout';

默认是1，也就是每秒log刷盘，配合innodb_flush_log_at_trx_commit来设置，为了充分保证数据的一致性，一般innodb_flush_log_at_trx_commit=1，这样的话，innodb_flush_log_at_timeout的设置也就没有意义了。因此，该参数的设置只针对innodb_flush_log_at_trx_commit为0/2起作用。

6、内存脏页占比控制

mysql> show variables like '%dirty%pct%';

在内存buffer pool空间允许的范围下，可以调大脏页允许在内存空间的占比，可解燃眉之急，降低写压力。

7、关闭doublewrite降低写压力

mysql> show variables like '%doub%';

两次写特性，默认开启，静态参数。

---

以上就是针对mysql在以写为主的情况下的相关参数监控及调优，如何调整(静态参数、动态参数)最好都要根据官方文档来调整。后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~