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

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

 概述

[[272150]]

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

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

一、写压力性能监控

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

  1. # iostat -x 
详解MySQL以写为主的系统如何进行性能监控与调优,值得收藏

说明:

  • 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(挂起)

  1. mysql> show global status like '%pend%'
  2. +------------------------------+-------+ 
  3. | Variable_name | Value | 
  4. +------------------------------+-------+ 
  5. | Innodb_data_pending_fsyncs | 0 | #被挂起的fsync 
  6. | Innodb_data_pending_reads | 0 | #被挂起的物理读 
  7. | Innodb_data_pending_writes | 0 | #被挂起的写 
  8. | Innodb_os_log_pending_fsyncs | 0 | #被挂起的日志fsync 
  9. | Innodb_os_log_pending_writes | 0 | #被挂起的日志写 
  10. +------------------------------+-------+ 
  11. rows in set (0.01 sec) 

 

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

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

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

3.1、日志写入速度监控

  1. mysql> show global status like '%log%written'

 

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

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

3.2、脏页写入速度监控

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

 

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

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

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

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

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

4、脏页的量监控

  1. mysql> show global status like '%dirty%'
  2. +--------------------------------+-------+ 
  3. | Variable_name | Value | 
  4. +--------------------------------+-------+ 
  5. | Innodb_buffer_pool_pages_dirty | 0 | #当前buffer pool中脏页的数量 
  6. | Innodb_buffer_pool_bytes_dirty | 0 | #当前buffer pool中脏页的总字节数 
  7. +--------------------------------+-------+ 
  8. rows in set (0.01 sec) 
  9.   
  10. mysql> show global status like 'i%total%'
  11. +--------------------------------+-------+ 
  12. | Variable_name | Value | 
  13. +--------------------------------+-------+ 
  14. | Innodb_buffer_pool_pages_total | 8192 | #buffer pool中数据页总量 
  15. +--------------------------------+-------+ 
  16. 1 row in set (0.01 sec) 

关注比值:Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total,脏页占比

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

5、写性能瓶颈

  1. mysql> show global status like '%t_free'
  2. +------------------------------+-------+ 
  3. | Variable_name | Value | 
  4. +------------------------------+-------+ 
  5. | Innodb_buffer_pool_wait_free | 0 | 
  6. +------------------------------+-------+ 
  7. 1 row in set (0.01 sec) 
  8.   
  9. mysql> show global status like '%g_waits'
  10. +------------------+-------+ 
  11. | Variable_name | Value | 
  12. +------------------+-------+ 
  13. | Innodb_log_waits | 0 | 
  14. +------------------+-------+ 
  15. 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增删改查 、事务提交、事务回滚

  1. mysql> show global status like 'i%rows%'
  2. +----------------------+-------+ 
  3. | Variable_name | Value | 
  4. +----------------------+-------+ 
  5. | Innodb_rows_deleted | 0 | 
  6. | Innodb_rows_inserted | 145 | 
  7. | Innodb_rows_read | 233 | 
  8. | Innodb_rows_updated | 5 | 
  9. +----------------------+-------+ 
  10. rows in set (0.01 sec) 
  11.   
  12. mysql> show global status like '%commit%'
  13. +----------------+-------+ 
  14. | Variable_name | Value | 
  15. +----------------+-------+ 
  16. | Com_commit | 0 | 
  17. | Com_xa_commit | 0 | 
  18. | Handler_commit | 16 | 
  19. +----------------+-------+ 
  20. rows in set (0.01 sec) 
  21.   
  22. mysql> show global status like '%rollback%'
  23. +----------------------------+-------+ 
  24. | Variable_name | Value | 
  25. +----------------------------+-------+ 
  26. | Com_rollback | 0 | 
  27. | Com_rollback_to_savepoint | 0 | 
  28. | Com_xa_rollback | 0 | 
  29. | Handler_rollback | 0 | 
  30. | Handler_savepoint_rollback | 0 | 
  31. +----------------------------+-------+ 
  32. rows in set (0.01 sec) 

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

二、写压力调优参数

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

1、脏页刷新的频率

  1. mysql> show variables like 'i%depth%'

 

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

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

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

  1. mysql> show variables like '%io_c%'

 

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

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

3、redolog调优

  1. mysql> show variables like 'innodb_log%'

 

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

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

4、redolog的刷新机

  1. mysql> show variables like '%flush%commit'

 

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

这里我优化了一下

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

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

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

  1. mysql> show variables like 'innodb_flush_log_at_timeout'

 

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

默认是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、内存脏页占比控制

  1. mysql> show variables like '%dirty%pct%'

 

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

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

7、关闭doublewrite降低写压力

  1. mysql> show variables like '%doub%'
详解MySQL以写为主的系统如何进行性能监控与调优,值得收藏

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

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

责任编辑:华轩 来源: 今日头条
相关推荐

2012-03-26 10:55:03

JavaJava EE

2011-03-10 14:40:54

LAMPMysql

2019-11-01 08:49:07

JVM监控性能

2022-11-30 08:17:41

JVM调优技巧

2023-11-15 16:35:31

SQL数据库

2018-11-12 14:53:09

Redis性能调优数据库

2019-09-12 08:03:56

MySQL索引数据库

2019-09-04 08:13:53

MySQLInnodb事务系统

2011-04-07 16:15:31

MySQL服务器调优

2019-09-26 08:59:39

DockerGoogle软件

2020-11-09 07:34:49

JVM性能监控

2023-11-28 08:43:48

2011-03-18 11:21:48

2014-09-22 13:31:46

Linux

2011-03-21 09:35:38

LAMP调优网络文件

2013-09-24 13:06:56

AngularJS性能优化

2009-04-01 11:17:52

内存分配调优Oracle

2019-02-19 10:25:28

JVM性能工具

2011-03-10 14:40:52

2022-03-14 18:29:34

云合规监控云平台
点赞
收藏

51CTO技术栈公众号