mysql undo管理

宣告：本文內容是筆者閱讀《MySQL運維內參》後整理的筆記

innodb支援的回滾段總共有128*1024=131072個。

在事務的執行過程中，會產生兩種回滾日誌，一種是insert的undo日誌，一種是update的undo日誌。可能有人會問，delete哪裡去了。其實是包含在update回滾日誌中的。

因為innodb把undo分為兩類：一類是新增，也就是insert，一類是修改，也就是update。分類的依據就是commit後要不要做purge操作，因為insert是不需要purge的，只要事務提交了，那個回滾日誌就可以丟掉了；而對於更新和刪除操作，如果事務提交了，還需要為MVCC服務，那就需要將這些日誌放到history list中去，等待去做purge或MVCC的多版本查詢等。

通常情況下，如果一個事務既有插入，又有更新（或刪除），那麼這個事務對應兩個UNDO段，即在一個rseg的1024個槽中，要使用兩個槽來儲存事務的回滾段，一個是插入段，一個是更新段。

在事務要儲存回滾記錄的時候，事務就要從1024個槽中，根據相應的更新型別（插入或者更新）找到空閒的槽做為自己的UNDO段。如果已經申請過相同型別的undo段，就直接使用，否則就需要建立一個段，並將段首頁號寫入這個rseg長度為1024的陣列多贏的空閒位置中去。

如果在1024個槽中，找不到空間的位置，那麼這個事務就被回滾掉，報出錯誤“too many active concurrent transactions”，錯誤號為1637的異常。當然，這種情況一般不會見到，如果能把這個用完，估計資料庫已經根本動不了了。

回滾時刻：在資料庫的啟動過程中，先進行redo日誌恢復，然後才進行undo的操作。

mysql是根據innodb_force_recovery來決定要不要做回滾操作，如果設定為3或3以上，就不需要回滾了，這可能會導致資料庫邏輯上的不一致。

因為undo是反向操作，所以應該是先處理新產生的事務，後處理老的事務，透過事務號區分新老關係。也就是說，innodb是透過從大到小的順序遍歷。

針對每一個undo段，innodb會將所有的狀態為ACTIVE的事務的undo日誌掃描出來，然後一條一條的做回滾操作。

InnoDB支援128個undo logs，這裡特別說明下，從5.7開始，innodb_rollback_segments的名字改成了innodb_undo_logs，但表示的都是回滾段的個數。

我們先大致看下InnoDB的undo在不同的版本上的一些演進:

MySQL 5.5的版本上

InnoDB undo是放在系統表空間即ibdata file檔案中，這樣如果有比較大的事務(即需要生成大量undo的)，會撐大ibdata資料檔案，

雖然空間可以重用，但檔案大小不能更改。

關於回滾段的，只有這個主要的引數，用來設定多少個rollback segment。

[html] view plain copy

mysql> show global variables like '%rollback_segment%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| innodb_rollback_segments | 128 |

+----------------------------+-------+

MySQL 5.6的版本上

InnoDB undo支援獨立表空間，增加如下引數：

[html] view plain copy

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| innodb_undo_directory | . |

| innodb_undo_logs | 128 |

| innodb_undo_tablespaces | 1 |

+-------------------------+-------+

這樣，在install的時候，就會在data目錄下增加undo資料檔案，來組成undo獨立表空間，但檔案變大之後的空間回收還是成為問題。

MySQL 5.7的版本上

InnoDB undo在支援獨立表空間的基礎上，支援表空間的truncate功能，增加了如下引數：

[html] view plain copy

mysql> show global variables like '%undo%'; +--------------------------+------------+

| Variable_name | Value |

+--------------------------+------------+

| innodb_max_undo_log_size | 1073741824 |

| innodb_undo_directory | ./ |

| innodb_undo_log_truncate | OFF |

| innodb_undo_logs | 128 |

| innodb_undo_tablespaces | 3 |

+--------------------------+------------+

mysql> show global variables like '%truncate%';

+--------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------+-------+

| innodb_purge_rseg_truncate_frequency | 128 |

| innodb_undo_log_truncate | OFF |

+--------------------------------------+-------+

InnoDB的purge執行緒，會根據innodb_undo_log_truncate開關的設定，和innodb_max_undo_log_size設定的檔案大小閾值，以及truncate的頻率來進行空間回收和rollback segment的重新初始化。

註釋：

innodb_undo_log_truncate引數設定為1，即開啟線上回收（收縮）undo log日誌檔案，支援動態設定。

innodb_undo_tablespaces引數必須大於或等於2，即回收（收縮）一個undo log日誌檔案時，要保證另一個undo log是可用的。

innodb_undo_logs: undo回滾段的數量，至少大於等於35，預設128。

innodb_max_undo_log_size：當超過這個閥值（預設是1G），會觸發truncate回收（收縮）動作，truncate後空間縮小到10M。

innodb_purge_rseg_truncate_frequency：控制回收（收縮）undo log的頻率，表示purge undo輪詢128次後，進行一次undo的truncate。undo log空間在它的回滾段沒有得到釋放之前不會收縮，想要增加釋放回滾區間的頻率，就得降低innodb_purge_rseg_truncate_frequency設定值。

innodb_undo_directory:undo檔案存放的位置；

預設情況下，是purge觸發128次之後，進行一次rollback segment的free操作，然後如果全部free就進行一個truncate。

當設定innodb_undo_log_truncate=ON的時候， undo表空間的檔案大小，如果超過了innodb_max_undo_log_size，就會被truncate到初始大小，但有一個前提，就是表空間中的undo不再被使用。

注意：

如果是線上庫，要注意影響，因為當一個undo tablespace在進行truncate的時候，不再承擔undo的分配。只能由剩下的undo 表空間的rollback segment接受事務undo空間請求。

MySQL 5.7 設定undo為獨立表空間

MySQL5.7中開始支援把undo log分離到獨立的表空間，並放到單獨的檔案目錄下；採用獨立undo表空間，再也不用擔心undo會把 ibdata1 檔案搞大，還可以自動收縮空間；

也給我們部署不同IO型別的檔案位置帶來便利，對於併發寫入型負載，我們可以把undo檔案部署到單獨的高速儲存裝置上.

在資料庫初始化的時候就使用如下幾個引數，就可以分離出單獨的undo表空間

(root@localhost)[(none)]> show variables like '%undo%';

+--------------------------+--------------+

| Variable_name | Value |

+--------------------------+--------------+

| innodb_max_undo_log_size | 104857600 |

| innodb_undo_directory | /log/undolog |

| innodb_undo_log_truncate | ON |

| innodb_undo_logs | 128 |

| innodb_undo_tablespaces | 3 |

+--------------------------+--------------+

5 rows in set (0.01 sec)

(root@localhost)[(none)]> show variables like '%truncate%';

+--------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------+-------+

| innodb_purge_rseg_truncate_frequency | 128 |

| innodb_undo_log_truncate | ON |

+--------------------------------------+-------+

show status like 'Innodb_available_undo_logs';

無法動態修改：

root@localhost [(none)]> set global innodb_undo_tablespaces=3;

ERROR 1238 (HY000): Variable 'innodb_undo_tablespaces' is a read only variable

已安裝資料庫不能修改 innodb_undo_tablespaces

2016-03-24 16:27:18 7164 [Note] InnoDB: Using Linux native AIO

2016-03-24 16:27:18 7164 [Note] InnoDB: Using CPU crc32 instructions

2016-03-24 16:27:18 7164 [Note] InnoDB: Initializing buffer pool, size = 100.0M

2016-03-24 16:27:18 7164 [Note] InnoDB: Completed initialization of buffer pool

2016-03-24 16:27:18 7fb591068720 InnoDB: Expected to open 3 undo tablespaces but was able

2016-03-24 16:27:18 7fb591068720 InnoDB: to find only 0 undo tablespaces.

2016-03-24 16:27:18 7fb591068720 InnoDB: Set the innodb_undo_tablespaces parameter to the

2016-03-24 16:27:18 7fb591068720 InnoDB: correct value and retry. Suggested value is 0

2016-03-24 16:27:18 7164 [ERROR] Plugin 'InnoDB' init function returned error.

2016-03-24 16:27:18 7164 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2016-03-24 16:27:18 7164 [ERROR] Unknown/unsupported storage engine: InnoDB

2016-03-24 16:27:18 7164 [ERROR] Aborting

---the end

參考文件：

《MySQL運維內參》一書

http://blog.csdn.net/gua___gua/article/details/52678102

http://blog.chinaunix.net/uid-31396856-id-5753413.html

mysql undo管理

相關文章