概述

[[272150]]

今天主要分享下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方面的內容，感興趣的朋友可以關注一下~