概述

[[272150]]

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

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

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

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

# iostat -x 

說(shuō)明：

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

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

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 | #被掛起的寫(xiě) 
| Innodb_os_log_pending_fsyncs | 0 | #被掛起的日志fsync 
| Innodb_os_log_pending_writes | 0 | #被掛起的日志寫(xiě) 
+------------------------------+-------+ 
5 rows in set (0.01 sec) 

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

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

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

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

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

3.2、臟頁(yè)寫(xiě)入速度監(jiān)控

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

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

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

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

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

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

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

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

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

5、寫(xiě)性能瓶頸

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) 

說(shuō)明：

Innodb_buffer_pool_wait_free，如果該值大于0，說(shuō)明buffer pool中已經(jīng)沒(méi)有可用頁(yè)，等待后臺(tái)往回刷臟頁(yè)，騰出可用數(shù)據(jù)頁(yè)，這樣就很影響業(yè)務(wù)了，hang住。

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

6、系統(tǒng)真實(shí)負(fù)載：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) 

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

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

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

1、臟頁(yè)刷新的頻率

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

默認(rèn)1024，遍歷lru list刷新臟頁(yè)，值越大，說(shuō)明刷臟頁(yè)頻率越高。

2、磁盤(pán)刷新臟頁(yè)的量：磁盤(pán)io能力

mysql> show variables like '%io_c%'; 

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

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

mysql> show variables like 'innodb_log%'; 

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

4、redolog的刷新機(jī)制

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

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

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

5、定義每次日志刷新的時(shí)間

mysql> show variables like 'innodb_flush_log_at_timeout'; 

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

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

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

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

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

mysql> show variables like '%doub%'; 

兩次寫(xiě)特性，默認(rèn)開(kāi)啟，靜態(tài)參數(shù)。

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