MariaDB 10.1.1：整理InnoDB表空间中的未用空间

作者 作者 Jan Lindstrom 5 评论 关于 MariaDB 10.1.1：整理InnoDB表空间中的未用空间

引言

当你例如删除行时，这些行只是被标记为已删除，而不是真正从索引中物理删除，并且产生的空闲空间也不会返回给操作系统以便后续重用。清理线程（Purge thread）会物理删除索引键和行，但产生的空闲空间仍然不会返回给操作系统，并且此操作可能在页面中留下空洞。如果你的行是变长的，这可能会导致无法将此空闲空间用于新行（如果这些新行比旧行更大）。用户可以使用 OPTIMIZE TABLE 或 ALTER TABLE <table> ENGINE=InnoDB 来重建表。

不幸的是，对存储在共享表空间文件 ibdata1 中的 InnoDB 表运行 OPTIMIZE TABLE 会做两件事

  • 使表数据和索引在ibdata1
    内部连续
  • 使 ibdata1 增长，因为连续的数据和索引页面被附加到 ibdata1 末尾

新的碎片整理功能

在 MariaDB 10.1 中，我们合并了 Facebook 的碎片整理代码，这些代码由 Matt 和 Kakao 的 Seong Uck Lee 为 MariaDB 准备。与 Facebook 的代码和 Matt 的补丁相比，主要的区别在于 MariaDB 决定不引入新的 SQL 关键字，也不改变服务器代码。相反，我们使用现有的 OPTIMIZE TABLE，所有代码更改都在 InnoDB/XtraDB 存储引擎内部。要启用此新功能，你需要向 my.cnf 添加以下内容（此要求是为了保留 OPTIMIZE TABLE 对需要它的用户原有的行为）。

[mysqld]
innodb-defragment=1

这个新的碎片整理功能是就地执行的（inplace），因此不会创建新表，也无需将数据从旧表复制到新表。相反，此功能会加载 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_defragment_frequency: 每个索引每秒碎片整理的次数不超过此数。这控制了碎片整理线程请求索引 X_LOCK 的次数。碎片整理线程将检查自上次处理此索引以来是否已过去 1/defragment_frequency (秒)，如果时间不足，则将索引放回队列。实际频率只能低于给定值。

新的状态变量

  • 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;

在 CREATE TABLE 和 INSERT 操作后，我们可以从 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 的记录，这将在页面中留下空洞，然后我们优化表以执行碎片整理

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

链接

WebScaleSQL Git 仓库 https://github.com/webscalesql/webscalesql-5.6

Facebook Percona Live 演示文稿：https://www.google.fi/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CCQQFjAB&url=https%3A%2F%2Fwww.percona.com%2Flive%2Fmysql-conference-2014%2Fsites%2Fdefault%2Ffiles%2Fslides%2Fdefragmentation.pdf&ei=UgNKVNnZMcHhywP7qwI&usg=AFQjCNGREUpen21jCcy0bchUa6Ro83ol_A&sig2=MDZU2Ue9sX1kB9OusvdiFA