概述

[[272150]]

今天主要分享下mysql數(shù)據(jù)庫寫壓力的性能監(jiān)控與調(diào)優(yōu)，分成性能監(jiān)控、相關(guān)調(diào)優(yōu)參數(shù)兩方面來做介紹。

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

一、寫壓力性能監(jiān)控

1、OS層面的監(jiān)控：iostat -x

# iostat -x 

說明：

• rrqm/s：每秒進行merge的讀操作數(shù)目。
• wrqm/s：每秒進行merge的寫操作數(shù)目。
• r/s：每秒完成的讀I/O設(shè)備次數(shù)。
• w/s：每秒完成的寫I/O設(shè)備次數(shù)。
• rsec/s：每秒讀扇區(qū)數(shù)。
• wsec/s：每秒寫扇區(qū)數(shù)。
• rkB/s：每秒讀K字節(jié)數(shù)。
• wkB/s：每秒寫K字節(jié)數(shù)。
• avgrq-sz：平均每次設(shè)備I/O操作的數(shù)據(jù)大小(扇區(qū))。
• avgqu-sz：平均I/O隊列長度。
• await：平均每次設(shè)備I/O操作的等待時間(毫秒)。
• svctm：平均每次設(shè)備I/O操作的服務(wù)時間(毫秒)。
• %util：一秒中有百分之多少的時間用于I/O操作，或者說一秒中有多少時間I/O隊列是非空的。

其實監(jiān)控系統(tǒng)的io狀況，主要查看%util、r/s、w/s，一般繁忙度在70%，每秒寫也在理想值了;但如果系統(tǒng)目前繁忙度低，每秒寫很低，可以增加寫入。

2、DB層面監(jiān)控：監(jiān)控各種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) 

寫掛起次數(shù)值大于0，甭管是什么寫掛起，出現(xiàn)掛起的話就說明出現(xiàn)寫壓力，所以值最好的是保持為0。監(jiān)控“掛起”狀態(tài)值，出現(xiàn)大于0且持續(xù)增加，報警處理。

3、寫入速度監(jiān)控：日志寫、臟頁寫

3.1、日志寫入速度監(jiān)控

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

從 Innodb_os_log_written 可以知道 innodb 日志的寫入量，從而判斷 innodb_log_buffer_size 的大小是否合適，同時也可以估計出一個 log_file 大概能支持多長時間。innodb 當buffer滿了，事務(wù)提交或每秒1次刷新，看哪個更早發(fā)生。

3.2、臟頁寫入速度監(jiān)控

mysql> show global status like '%a%written'; 
+----------------------------+---------+ 
| Variable_name | Value | 
+----------------------------+---------+ 
| Innodb_data_written | 1073152 | #目前為止寫的總的數(shù)據(jù)量，單位字節(jié) 
| Innodb_dblwr_pages_written | 7 | 
| Innodb_pages_written | 58 | #寫數(shù)據(jù)頁的數(shù)量 
+----------------------------+---------+ 
3 rows in set (0.01 sec) 

3.3、關(guān)注比值：Innodb_dblwr_pages_written / Innodb_dblwr_writes，表示一次寫了多少頁

mysql> show global status like '%dblwr%'; 
+----------------------------+-------+ 
| Variable_name | Value | 
+----------------------------+-------+ 
| Innodb_dblwr_pages_written | 7 | #已經(jīng)寫入到doublewrite buffer的頁的數(shù)量 
| Innodb_dblwr_writes | 3 | #doublewrite寫的次數(shù) 
+----------------------------+-------+ 
2 rows in set (0.00 sec) 

1、如果該比值是64：1，說明doublewrite每次都是滿寫，寫的壓力很大。

2、如果系統(tǒng)的double_write比較高的話，iostat看到的wrqm/s(每秒合并寫的值)就高，因為double_write高意味著每次寫基本上都是寫2M，這時候就發(fā)生更多的合并，但wrqm/s高并不害怕，因為發(fā)生合并是好事，看wrqm/s和繁忙度能不能接受。

4、臟頁的量監(jiān)控

mysql> show global status like '%dirty%'; 
+--------------------------------+-------+ 
| Variable_name | Value | 
+--------------------------------+-------+ 
| Innodb_buffer_pool_pages_dirty | 0 | #當前buffer pool中臟頁的數(shù)量 
| Innodb_buffer_pool_bytes_dirty | 0 | #當前buffer pool中臟頁的總字節(jié)數(shù) 
+--------------------------------+-------+ 
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中數(shù)據(jù)頁總量 
+--------------------------------+-------+ 
1 row in set (0.01 sec) 

關(guān)注比值：Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total，臟頁占比

通過比值看臟頁是否多，比如臟頁10%的話，可以判斷系統(tǒng)可能不是寫為主的系統(tǒng)。

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中已經(jīng)沒有可用頁，等待后臺往回刷臟頁，騰出可用數(shù)據(jù)頁，這樣就很影響業(yè)務(wù)了，hang住。

Innodb_log_waits，如果該值大于0，說明寫壓力很大，出現(xiàn)了日志等待。如果Innodb_log_waits狀態(tài)變量(等待日志緩沖刷出的次數(shù))的值比較高，而且繼續(xù)增長，可以增大log buffer或者降低事務(wù)大小。

6、系統(tǒng)真實負載：rows增刪改查 、事務(wù)提交、事務(wù)回滾

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) 

通過監(jiān)控系統(tǒng)真實負載，如果業(yè)務(wù)正常，負載上升，此時，就要根據(jù)業(yè)務(wù)具體情況，進行相應(yīng)的調(diào)優(yōu)。

二、寫壓力調(diào)優(yōu)參數(shù)

通過調(diào)整參數(shù)降低寫壓力時，一定要實時關(guān)注iostat系統(tǒng)的各項指標。

1、臟頁刷新的頻率

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

默認1024，遍歷lru list刷新臟頁，值越大，說明刷臟頁頻率越高。

2、磁盤刷新臟頁的量：磁盤io能力

mysql> show variables like '%io_c%'; 

根據(jù)磁盤io能力進行調(diào)整，值越大，每次刷臟頁的量越大。

3、redolog調(diào)優(yōu)

mysql> show variables like 'innodb_log%'; 

logfile大小和組數(shù)可能會導致寫抖動：日志切換頻率需要監(jiān)控(文件系統(tǒng)層面技巧)。

4、redolog的刷新機制

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

這里我優(yōu)化了一下

默認MySQL的刷盤策略是1，最安全的，但是安全的同時，自然也就會帶來一定的性能壓力。在寫壓力巨大的情況下，根據(jù)具體的業(yè)務(wù)場景，犧牲安全性的將其調(diào)為0或2。

5、定義每次日志刷新的時間

mysql> show variables like 'innodb_flush_log_at_timeout'; 

默認是1，也就是每秒log刷盤，配合innodb_flush_log_at_trx_commit來設(shè)置，為了充分保證數(shù)據(jù)的一致性，一般innodb_flush_log_at_trx_commit=1，這樣的話，innodb_flush_log_at_timeout的設(shè)置也就沒有意義了。因此，該參數(shù)的設(shè)置只針對innodb_flush_log_at_trx_commit為0/2起作用。

6、內(nèi)存臟頁占比控制

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

在內(nèi)存buffer pool空間允許的范圍下，可以調(diào)大臟頁允許在內(nèi)存空間的占比，可解燃眉之急，降低寫壓力。

7、關(guān)閉doublewrite降低寫壓力

mysql> show variables like '%doub%'; 

兩次寫特性，默認開啟，靜態(tài)參數(shù)。

以上就是針對mysql在以寫為主的情況下的相關(guān)參數(shù)監(jiān)控及調(diào)優(yōu)，如何調(diào)整(靜態(tài)參數(shù)、動態(tài)參數(shù))最好都要根據(jù)官方文檔來調(diào)整。后面會分享更多devops和DBA方面的內(nèi)容，感興趣的朋友可以關(guān)注一下~