Дефрагментация табличных пространств InnoDB

Обзор

При удалении строк из таблицы InnoDB, строки просто помечаются как удаленные, а не физически удаляются. Освободившееся пространство не возвращается операционной системе для повторного использования.

Поток очистки (purge thread) физически удалит ключи индексов и строки, но освободившееся пространство всё ещё не возвращается операционной системе. Это может привести к пробелам в страницах. Если у вас строки переменной длины, новые строки могут быть больше старых и не смогут использовать доступное пространство.

Вы можете запустить OPTIMIZE TABLE или ALTER TABLE <table> ENGINE=InnoDB, чтобы перестроить таблицу. К сожалению, выполнение OPTIMIZE TABLE над таблицей InnoDB, хранящейся в файле общего табличного пространства ibdata1, делает два действия:

• Делает данные и индексы таблицы смежными внутри ibdata1.
• Увеличивает размер ibdata1, поскольку смежные страницы данных и индексов добавляются к ibdata1.

Дефрагментация InnoDB

MariaDB 10.1 объединила код дефрагментации Facebook, подготовленный для MariaDB Мэттом, и Сонгом Уком Ли из Kakao. Единственное существенное отличие от кода Facebook и патча Мэтта заключается в том, что MariaDB не вводит новые литералы в SQL и не вносит изменений в код сервера. Вместо этого используется OPTIMIZE TABLE, и все изменения кода находятся внутри хранилищ InnoDB/XtraDB.

Поведение OPTIMIZE TABLE по умолчанию не изменяется, и для включения этой новой функции необходимо установить системную переменную innodb_defragment в значение 1.

[mysqld]
...
innodb-defragment=1

Новые таблицы не создаются, и нет необходимости копировать данные из старых таблиц в новые. Вместо этого эта функция загружает n страницы (определяемые параметром innodb-defragment-n-pages) и пытается переместить записи так, чтобы страницы были заполнены записями, а затем освобождает страницы, которые полностью пустые после операции.

Обратите внимание, что файлы табличного пространства (включая ibdata1) не уменьшатся в результате дефрагментации, но вы получите лучшую загрузку памяти в пуле буферов InnoDB, поскольку используется меньше страниц данных.

Введено несколько новых системных и статусных переменных для управления и мониторинга этой функции.

Системные переменные

• innodb_defragment: Включить дефрагментацию InnoDB.
• innodb_defragment_n_pages: Количество страниц, рассматриваемых одновременно при слиянии нескольких страниц для дефрагментации.
• innodb_defragment_stats_accuracy: Количество изменений статистики дефрагментации, происходящих до записи статистики в постоянное хранилище.
• innodb_defragment_fill_factor_n_recs: Количество записей пространства, которое дефрагментация должна оставить на странице.
• innodb_defragment_fill_factor: Указывает, насколько полной должна заполнить дефрагментация страницу.
• innodb_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 и 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

Удаление трех четвертей записей, оставляя пробелы, а затем оптимизация:

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

Для получения более подробной информации см. Дефрагментация неиспользуемого пространства в табличном пространстве InnoDB в блоге Mariadb.org.