主頁(yè) > 知識(shí)庫(kù) > Mysql在線回收undo表空間實(shí)戰(zhàn)記錄

Mysql在線回收undo表空間實(shí)戰(zhàn)記錄

熱門標(biāo)簽:福建外呼系統(tǒng)定制化 銅陵防封電銷卡 電銷卡外呼系統(tǒng)供應(yīng)商 騰訊地圖標(biāo)注提升 怎么在地圖標(biāo)注位置生成圖片 美國(guó)反騷擾電話機(jī)器人 真人語(yǔ)音電話機(jī)器人 悟空科技電話機(jī)器人 400電話可以免費(fèi)申請(qǐng)嗎

1 Mysql5.6

1.1 相關(guān)參數(shù)

MySQL 5.6增加了參數(shù)innodb_undo_directory、innodb_undo_logs和innodb_undo_tablespaces這3個(gè)參數(shù),可以把undo log從ibdata1移出來單獨(dú)存放。

  • innodb_undo_directory:指定單獨(dú)存放undo表空間的目錄,默認(rèn)為.(即datadir),可以設(shè)置相對(duì)路徑或者絕對(duì)路徑。該參數(shù)實(shí)例初始化之后雖然不可直接改動(dòng),但是可以通過先停庫(kù),修改配置文件,然后移動(dòng)undo表空間文件的方式去修改該參數(shù)。

默認(rèn)參數(shù):

mysql> show variables like '%undo%';
+-------------------------+-------+
| Variable_name      | Value |
+-------------------------+-------+
| innodb_undo_directory  | .   |
| innodb_undo_logs    | 128  |
| innodb_undo_tablespaces | 0   |
+-------------------------+-------+
  • innodb_undo_tablespaces:指定單獨(dú)存放的undo表空間個(gè)數(shù),例如如果設(shè)置為3,則undo表空間為undo001、undo002、undo003,每個(gè)文件初始大小默認(rèn)為10M。該參數(shù)我們推薦設(shè)置為大于等于3,原因下文將解釋。該參數(shù)實(shí)例初始化之后不可改動(dòng)

實(shí)例初始化是修改innodb_undo_tablespaces:

mysql_install_db ...... --innodb_undo_tablespaces

$ ls
...
undo001 undo002 undo003
  • innodb_rollback_segments:默認(rèn)128個(gè)。每個(gè)回滾段可同時(shí)支持1024個(gè)在線事務(wù)。這些回滾段會(huì)平均分布到各個(gè)undo表空間中。該變量可以動(dòng)態(tài)調(diào)整,但是物理上的回滾段不會(huì)減少,只是會(huì)控制用到的回滾段的個(gè)數(shù)。

1.2 使用

初始化實(shí)例之前,我們只需要設(shè)置innodb_undo_tablespaces參數(shù)(建議大于等于3)即可將undo log設(shè)置到單獨(dú)的undo表空間中。如果需要將undo log放到更快的設(shè)備上時(shí),可以設(shè)置innodb_undo_directory參數(shù),但是一般我們不這么做,因?yàn)楝F(xiàn)在SSD非常普及。innodb_undo_logs可以默認(rèn)為128不變。

undo log可以存儲(chǔ)于ibdata之外。但這個(gè)特性依然雞肋:

  • 首先你必須在install實(shí)例的時(shí)候就指定好獨(dú)立Undo tablespace, 在install完成后不可更改。
  • Undo tablepsace的space id必須從1開始,無法增加或者刪除undo tablespace。

1.3 大事務(wù)測(cè)試

mysql> create table test.tbl( id int primary key auto_increment, name varchar(200));
Query OK, 0 rows affected (0.03 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.tbl(name) values(repeat('1',00));
Query OK, 1 row affected (0.00 sec)

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

...

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 2097152 rows affected (24.84 sec)
Records: 2097152 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (7.90 sec)

觀察undolog已經(jīng)開始膨脹了!事務(wù)commit后空間也沒有回收。

$ du -sh undo*
10M  undo001
69M  undo002
10M  undo003

2 Mysql5.7

5.7引入了在線truncate undo tablespace

2.1 相關(guān)參數(shù)

必要條件:

  • innodb_undo_tablespaces:最少有兩個(gè),這樣一個(gè)在清理的時(shí)候可以使用另一個(gè),該參數(shù)實(shí)例初始化之后不可改動(dòng)
  • innodb_rollback_segments:回滾段的個(gè)數(shù),總會(huì)有一個(gè)回滾段分配給系統(tǒng)表空間,32個(gè)保留給臨時(shí)表空間。所以如果想使用undo表空間的話,這個(gè)值要至少為33。例如使用兩個(gè)undo表空間,這個(gè)值就配35。如果設(shè)置多個(gè)undo表空間,系統(tǒng)表空間中的回滾段會(huì)變成非活躍狀態(tài)。

啟動(dòng)參數(shù):

  • innodb_undo_log_truncate=on
  • innodb_max_undo_log_size:超過這個(gè)值的表空間會(huì)標(biāo)記為truncate,動(dòng)態(tài)參數(shù)默認(rèn)是1G
  • innodb_purge_rseg_truncate_frequency:指定purge操作被喚起多少次之后才釋放rollback segments。當(dāng)undo表空間里面的rollback segments被釋放時(shí),undo表空間才會(huì)被truncate。由此可見,該參數(shù)越小,undo表空間被嘗試truncate的頻率越高。

2.2 清理過程

  1. undo表空間大小超過innodb_max_undo_log_size后,標(biāo)記該表空間需要清理。標(biāo)記會(huì)循環(huán)進(jìn)行,避免一個(gè)表空間被反復(fù)清理。
  2. 標(biāo)記表空間內(nèi)的回滾段變?yōu)榉腔钴S狀態(tài),正在運(yùn)行的事務(wù)等待執(zhí)行完。
  3. 開始purge
  4. 釋放undo表空間中的所有回滾段后,運(yùn)行truncate并將undo表空間截?cái)酁槠涑跏即笮?,初始大小由innodb_page_size決定,默認(rèn)16KB的大小對(duì)應(yīng)表空間為10MB
  5. 重新激活回滾段,以便將它們分配給新事務(wù)

2.3 性能建議

truncate表空間時(shí)避免影響性能的最簡(jiǎn)單方法是增加撤消表空間的數(shù)量

2.4 大事務(wù)測(cè)試

配置8個(gè)undo表空間,innodb_purge_rseg_truncate_frequency=10

mysqld --initialize ... --innodb_undo_tablespaces=8

開始測(cè)試

mysql> show global variables like '%undo%';
+--------------------------+------------+
| Variable_name      | Value   |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory  | ./     |
| innodb_undo_log_truncate | ON     |
| innodb_undo_logs     | 128    |
| innodb_undo_tablespaces | 8     |
+--------------------------+------------+

mysql> select @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                   10 |
+----------------------------------------+

select @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|          10485760 |
+----------------------------+

mysql> create table test.tbl( id int primary key auto_increment, name varchar(200));
Query OK, 0 rows affected (0.03 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.tbl(name) values(repeat('1',00));
Query OK, 1 row affected (0.00 sec)

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

...

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 2097152 rows affected (24.84 sec)
Records: 2097152 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (7.90 sec)

undo表空間情況,膨脹到100MB+后成功回收

$ du -sh undo*
10M    undo001
10M    undo002
10M    undo003
10M    undo004
10M    undo005
10M    undo006
125M   undo007
10M    undo008

$ du -sh undo*
10M    undo001
10M    undo002
10M    undo003
10M    undo004
10M    undo005
10M    undo006
10M    undo007
10M    undo008

3 Reference

https://dev.mysql.com/doc/ref...

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章:
  • MySQL 清除表空間碎片的實(shí)例詳解
  • 解析mysql 表中的碎片產(chǎn)生原因以及清理
  • MySQL的表空間是什么
  • Mysql臟頁(yè)flush及收縮表空間原理解析
  • MySQL InnoDB表空間加密示例詳解
  • 深度解析MySQL 5.7之臨時(shí)表空間
  • mysql Innodb表空間卸載、遷移、裝載的使用方法
  • MySQL中查詢所有數(shù)據(jù)庫(kù)占用磁盤空間大小和單個(gè)庫(kù)中所有表的大小的sql語(yǔ)句
  • MySQL 表空間碎片的概念及相關(guān)問題解決

標(biāo)簽:臨汾 湖北 烏海 湖南 武威 白銀 云浮 聊城

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《Mysql在線回收undo表空間實(shí)戰(zhàn)記錄》,本文關(guān)鍵詞  Mysql,在線,回收,undo,表,空間,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《Mysql在線回收undo表空間實(shí)戰(zhàn)記錄》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于Mysql在線回收undo表空間實(shí)戰(zhàn)記錄的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章