關於MySQL的commit非規律性失敗案例的深入分析

案例描述：

一個普通的事務提交，在應用裡面會提示commit超時，失敗。

一、理論知識

1、關於commit原理，事務提交過程

1、尋找修改的數據頁：

1、如果該數據頁在內存中，則直接是內存讀；

2、如果該數據頁內存中沒有，物理讀，就從磁碟調入內存；

2、磁碟中的undo頁調入內存；

3、先將原來的數據存入undo，然後修改數據(數據頁成臟頁)；

4、修改數據的信息生成redo數據存入log_buffer(內存buffer_pool的一個空間，默認16M)中；

mysql> show variables like "%log_buffer%";
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.01 sec)

5、log_buffer通過log線程(後台線程，非常勤快)，持續不斷的將redo信息寫入disk的innodb_log_file中；

mysql> show variables like "innodb_log_file%";
+---------------------------+----------+
| Variable_name | Value |
+---------------------------+----------+
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
+---------------------------+----------+
2 rows in set (0.01 sec)

6、事務提交，刻意觸發log線程，將剩餘的log_buffer中的redo數據信息寫入磁碟中，數據量已剩不多，寫完提交成功。

注意：

1、修改記錄前，一定要先寫日誌；

「日誌先行」，這是資料庫最基本的原則。

2、事務提交過程中，一定要保證日誌先落盤，才能算事務提交完成。

3、意外掉電，內存臟頁丟失，但是磁碟的innodb_log_file中存放了redo日誌信息，待重啟伺服器，MySQL通過讀取磁碟的log_files數據，自動將數據的修改重新跑一邊。

Q：為什麼mysql commit速度總是很快，儘管事務修改的數據量可能很大？

A：

因為事務提交，並不是對磁碟數據進行修改，而是將修改數據的redo信息通過後台log線程寫入磁碟的redo logfile中，完成mysql commit，無論事務修改的數據量有多大，這個過程速度是很快的。

而內存中的臟塊，也就是修改後的數據頁，正常情況下是由後台相關write線程周期性的將臟頁數據刷入磁碟中，保證innodb buffer pool有足夠的乾淨塊、可用塊。

2、關於rollback原理，回滾過程

1、MySQL讀取內存中undo頁信息

2、通過undo信息找到臟頁，反著對數據進行修改

3、do、undo的時間相同，且都會產成redo信息

4、事務提交

MySQL回滾處理機制：

如果線程中斷，事務沒有提交，undo會將記錄此信息，待另一會話進程連上，查看該塊數據信息，MySQL自動回滾進行數據頁修改，然後被讀取。也就是說為了避免系統因為rollback被hang住，通過直接殺死進程的方式，中斷事務，等待後來者要讀取該數據信息時進行回滾，再返回結果。

Q：rollback為什麼有時候很慢，rollback的風險和風險避免方式？

A：

rollback的時間取決於回滾前事務修改數據的時間，處理量大回滾時間長，處理量小回滾時間短。

1、rollback風險：容易導致系統被hang住；

2、風險避免方式：直接殺死會話進程或是mysql進程。

3、存儲寫入性能分析

Q：mysql commit，存儲為什麼寫速度能夠保持在0ms，極少出現1ms情況？

A：

對於存儲來說，寫性能相當高：假設存儲cache總有空閑空間的情況下，事務提交，將log buffer中剩餘的很少的redo數據寫入存儲cache，即為完成mysql commit，這個過程是相當快的(能夠保持在0ms，極少出現1ms情況)，後續redo數據由cache寫入磁碟的過程是後台進行。

4、存儲級別的災備(同城災備)

1、災備同步過程：commit

1、redo、binlog寫入本地存儲cache；

2、通過網路同步binlog寫入遠端同步的伺服器的存儲cache中；

3、響應本地資料庫；

4、事務提交成功；

2、風險：

網路出現問題(信號斷續，纜線斷了)，導致寫hang住，commit超時失敗。

3、解決：

通過超時設置，網路中斷超過限制，自動將同步改為災備非同步，儘可能少的影響業務commit超時失敗。

二、分析與處理

存儲寫性能比較差，很多時段會達到5ms，甚至於10ms以上

備註：災備同步已經停止的情況下。

1、存儲中BBU問題，出現監控BBU的bug；

解決：重啟BBU，不行就更新BBU。

2、cache被佔滿

1、海量數據寫入，commit數據佔滿cache；

2、硬碟I/O異常，異常SQL導致的海量物理讀；

解決：索引優化。

3、存儲性能差

解決：找老闆掏錢，更換優質設備。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！