MySQL · 社區動態 · MariaDB InnoDB表空間碎片整理 · 數據庫內核月報

## 介紹當你對InnoDB進行修改操作時，例如刪除一些行，這些行只是被標記為“已刪除”，而不是真的從索引中物理刪除了，因而空間也沒有真的被釋放回收。InnoDB的Purge線程會異步的來清理這些沒用的索引鍵和行，但是依然沒有把這些釋放出來的空間還給操作系統重新使用，因而會導致頁面中存在很多空洞。如果表結構中包含動態長度字段，那么這些空洞甚至可能不能被InnoDB重新用來存新的行，因為空間空間長度不足。有些用戶可能會使用 OPTIMIZE TABLE 或者 ALTER TABLE ENGINE=InnoDB 來重建這些表，但是這樣會導致表的拷貝，如果臨時空間不足甚至不足以進行一次 OPTIMIZE TABLE 操作。并且如果你用的是共享表空間方式，OPTIMIZE TABLE 會導致你的共享表空間文件持續增大，因為整理的索引和數據都追加在數據文件的末尾。 ## 新的碎片整理算法從MariaDB 10.1開始，MariaDB把Facebook的碎片整理代碼合并進來了，并且把所有代碼都調整到InnoDB/XtraDB層去實現，因而只需要使用現成的 OPTIMIZE TABLE 命令就行。開啟新的整理算法需要把下面的配置加到 my.cnf 配置文件中： ~~~ [mysqld] innodb-defragment=1 ~~~ 這樣配置以后新的碎片整理功能就會替代原有的 OPTIMIZE TABLE 算法，不會有新的表生成，也不需要把舊表的數據拷貝到新表。新的算法會載入 n 個頁面嘗試把上面的記錄緊湊的合并到一起，從而讓頁面存滿記錄，然后釋放掉完全空了的頁面。 ## 新的配置項 * innodb_defragment: 打開或關閉InnoDB碎片整理算法。當設置為FALSE時，所有正在做的整理操作會暫停。暫停的整理操作會在變量設置為TRUE時重新開始。默認值是FALSE。 * innodb_defragment_n_pages: 一次性讀取多少個頁面進行合并整理操作。取值范圍是2~32，默認值是7。 * innodb_defragment_stats_accuracy: 統計信息寫到持久化存儲前可以有多少個整理統計信息的變更。設置為0表示不禁用整理統計追蹤，默認值是0. * innodb_defragment_fill_factor_n_recs: 在進行空間整理時在一個頁面上留多少條記錄的空間。這個變量和 innodb_defragment_fill_factor 一起可以讓頁面不至于被塞得太滿，從而減少后面插入記錄導致的頁面分裂。取值范圍是 1~100，默認值是20。 * innodb_defragment_fill_factor: 可以設置為[0.7~1]之間的值，讓整理算法知道應該把一個頁面填的多滿。默認值是0.9\. 設置為0.7以下沒有什么意義。這個變量和 innodb_defragment_fill_factor_n_recs 一起可以讓頁面不至于被塞得太滿，從而減少后面插入記錄導致的頁面分裂。上面這量個變量也是告訴我們，持續的整理操作可以讓InnoDB更高效。 * innodb_defragment_frequency: 對單個索引每秒的整理操作不要超過這個數量。這個變量控制了整理線程在一段時間內請求索引的 X_LOCK 的次數。整理線程會檢查是當前操作的索引離上次操作的時間否超過了1/defragment_frequency (s) ，如果還沒過足夠長的時間，則把索引重新放回隊列中。實際的操作頻率只會比這個值低。 ## 新的狀態變量 * Innodb_defragment_compression_failures: 整理碎片時重新壓縮頁面失敗的次數。 * Innodb_defragment_failures: 整理操作失敗的次數（例如沒有可壓縮的頁面）。 * Innodb_defragment_count: 整理操作的次數。 ## 舉例 ~~~ set @@global.innodb_file_per_table = 1; set @@global.innodb_defragment_n_pages = 32; set @@global.innodb_defragment_fill_factor = 0.95; CREATE TABLE tb_defragment ( pk1 bigint(20) NOT NULL, pk2 bigint(20) NOT NULL, fd4 text, fd5 varchar(50) DEFAULT NULL, PRIMARY KEY (pk1), KEY ix1 (pk2) ) ENGINE=InnoDB; delimiter //; create procedure innodb_insert_proc (repeat_count int) begin declare current_num int; set current_num = 0; while current_num < repeat_count do INSERT INTO tb_defragment VALUES (current_num, 1, REPEAT('Abcdefg', 20), REPEAT('12345',5)); INSERT INTO tb_defragment VALUES (current_num+1, 2, REPEAT('HIJKLM', 20), REPEAT('67890',5)); INSERT INTO tb_defragment VALUES (current_num+2, 3, REPEAT('HIJKLM', 20), REPEAT('67890',5)); INSERT INTO tb_defragment VALUES (current_num+3, 4, REPEAT('HIJKLM', 20), REPEAT('67890',5)); set current_num = current_num + 4; end while; end// delimiter ;// commit; set autocommit=0; call innodb_insert_proc(50000); commit; set autocommit=1; ~~~ 在建表和插入操作之后，我們可以在 INFORMATION_SCHEMA 中看到如下信息： ~~~ select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'PRIMARY'; Value 313 select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'ix1'; Value 72 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_pages_freed'); count(stat_value) 0 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_page_split'); count(stat_value) 0 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) 0 SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables WHERE engine LIKE 'InnoDB' and table_name like '%tb_defragment%'; table_name data_free_MB table_rows tb_defragment 4.00000000 50051 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'PRIMARY'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` PRIMARY 25873 4739939 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'ix1'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` ix1 50071 1051775 ~~~ 現在如果我們刪掉 3/4 的記錄就會在頁面中留下很多空洞，然后我們通過 OTPTIMIZE TABLE 執行整理操作： ~~~ delete from tb_defragment where pk2 between 2 and 4; optimize table tb_defragment; Table Op Msg_type Msg_text test.tb_defragment optimize status OK show status like '%innodb_def%'; Variable_name Value Innodb_defragment_compression_failures 0 Innodb_defragment_failures 1 Innodb_defragment_count 4 ~~~ 執行完之后我們可以看到有些頁面被釋放掉了，有些頁面被合并了： ~~~ select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'PRIMARY'; Value 0 select count(*) as Value from information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name = 'ix1'; Value 0 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_pages_freed'); count(stat_value) 2 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_page_split'); count(stat_value) 2 select count(stat_value) from mysql.innodb_index_stats where table_name like '%tb_defragment%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) 2 SELECT table_name, data_free/1024/1024 AS data_free_MB, table_rows FROM information_schema.tables WHERE engine LIKE 'InnoDB'; table_name data_free_MB table_rows innodb_index_stats 0.00000000 8 innodb_table_stats 0.00000000 0 tb_defragment 4.00000000 12431 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'PRIMARY'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` PRIMARY 690 102145 SELECT table_name, index_name, sum(number_records), sum(data_size) FROM information_schema.innodb_buffer_page where table_name like '%tb_defragment%' and index_name like 'ix1'; table_name index_name sum(number_records) sum(data_size) `test`.`tb_defragment` ix1 5295 111263 ~~~