Оптимизация таблиц

Описание

В процессе работы с СУБД Pangolin возникает «table bloat» (так называемое «раздувание» таблиц) — ситуация, при которой данные таблиц будут храниться неэффективно. Они фрагментируются, что приводит к ухудшению производительности и нерациональному использованию места на диске.

Примеры ситуаций, при которых может возникать фрагментация:

• непредвиденный скачок запросов UPDATE и/или DELETE, сильно отличающийся от обычного профиля нагрузки;
• наличие долгих транзакций, препятствующих удалению старых версий записей (VACUUM не может удалить запись, если есть хотя бы одна незакрытая транзакция старше записи, удалившей или изменившей эту запись);
• наличие незавершенных PREPARED-транзакций;
• наличие открытых слотов репликации. Это может произойти из-за сильной задержки между лидером и репликой либо в случае недоступности реплики;
• фрагментация может накапливаться естественным образом, если в таблице есть типы данных переменной длины. Это приводит к образованию кусков удаленных данных такого размера, которые сложно будет перезаписать.

Оригинальный механизм работы с данными в PostgreSQL

При первом наполнении таблицы данные добавляются последовательно и равномерно занимают блоки. Пример наполнения записями таблицы bloated_table:

CREATE TABLE bloated_table(id integer, data integer);
INSERT INTO bloated_table SELECT i, random() FROM generate_series(1, 1000000) AS g(i);

Посмотреть статистику по таблице можно с помощью запроса к pg_stat_user_tables командой ANALYZE:

ANALYZE bloated_table;
SELECT n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'bloated_table';

 n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
-----------+-----------+-----------+------------+------------
   1000000 |         0 |         0 |    1000000 |          0

Полученные результаты:

• n_tup_ins, n_tup_upd, n_tup_del — количество вставок, изменений, удалений строк таблицы;
• n_live_tup — актуальные записи;
• n_dead_tup — «мертвые» записи, помеченные на удаление.

Команда pg_size_pretty покажет объем таблицы на диске:

SELECT pg_size_pretty(pg_table_size('bloated_table'));

 pg_size_pretty
----------------
 35 MB

Симуляция фрагментации методом удаления каждой второй строки:

DELETE FROM bloated_table WHERE (id % 2) = 0;

Статистика таблицы после фрагментации:

n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
-----------+-----------+-----------+------------+------------
   1000000 |         0 |    500000 |     500000 |     500000

 pg_size_pretty
----------------
 35 MB

Теперь 500 000 записей считаются «мертвыми» (dead) и могут быть удалены очисткой (VACUUM). При штатной работе это сделает autovacuum, а для таблицы из примера очистка запущена вручную:

VACUUM bloated_table;

...

 n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
-----------+-----------+-----------+------------+------------
   1000000 |         0 |    500000 |     500000 |          0

 pg_size_pretty
----------------
 35 MB

«Мертвых» записей больше нет, но размер таблицы не изменился. VACUUM не возвращает место на диске кроме случаев, когда удаляет последний блок с данными. Свободное место будет переиспользовано СУБД для новых записей.

Сведения

Обновление существующих записей также может привести к фрагментации:UPDATE и DELETE не изменяют значение текущей строки (tuple), а помечают ее как устаревшую.

Анализ фрагментации таблиц

Существует расширение pgstattuple, позволяющее анализировать состояние таблиц. Расширение устанавливается вместе с продуктом по умолчанию, но требуется его активация через команду CREATE EXTENSION.

Пример использования:

SELECT * FROM pgstattuple('bloated_table');

-[ RECORD 1 ]------+-------
table_len          | 458752
tuple_count        | 1470
tuple_len          | 438896
tuple_percent      | 95.67
dead_tuple_count   | 11
dead_tuple_len     | 3157
dead_tuple_percent | 0.69
free_space         | 8932
free_percent       | 1.95

Значение строк вывода:

• free_percent — процент свободных записей. Чем он выше, тем больше фрагментирована таблица. Нормальными считаются значения не более 20%;
• table_len – физическая длина отношения в байтах;
• tuple_count — количество «живых» записей;
• tuple_len — общая длина «живых» записей в байтах;
• tuple_percent – процент «живых» записей;
• dead_tuple_count — количество «мертвых» записей;
• dead_tuple_len — общая длина «мертвых» записей в байтах;
• dead_tuple_percent — процент «мертвых» записей;
• free_space — общий объем свободного пространства в байтах.

Подсказка

Рекомендуется периодически проверять активные и перенесшие всплеск нагрузки таблицы.

Более подробная информация о расширении pgstattuple в разделе pgstattuple документа «Описание расширений продукта СУБД Pangolin».

Стандартные утилиты СУБД

Вернуть освобожденное после VACUUM место на диске можно стандартными средствами:

• VACUUM FULL полностью пересоберет таблицу, освободив все неиспользуемые строки:

  VACUUM FULL bloated_table;
  
  ...
  
   pg_size_pretty
  ----------------
   17 MB

• CLUSTER выполнит все операции VACUUM FULL и упорядочит строки по индексу, уменьшая количество обращений к диску на некоторых запросах:

  CLUSTER bloated_table USING <index_name>;

Использовать VACUUM FULL и CLUSTER на БД под нагрузкой не рекомендуется. Эти команды работают медленно и полностью блокируют обрабатываемую таблицу. Для решения ситуаций «table bloat» без прибегания к блокировке в состав СУБД Pangolin входят инструменты по реорганизации данных pg_squeeze, pg_repack и pgcompacttable.

Расширение pg_repack

Инструмент для реорганизации таблиц без эксклюзивной блокировки. Позволяет реорганизовать таблицы и индексы к ним и переносить их в другое табличное пространство.

Функциональность расширения:

• реорганизация таблиц без блокировки, в отличие от стандартных средств ядра PostgreSQL VACUUM FULL и CLUSTER. Производительность сравнима с CLUSTER;
• удаление пустот в таблицах и индексах;
• восстановление физического порядка кластеризованных индексов.

С информацией об установке и использовании расширения pg_repack можно ознакомиться в одноименном разделе расширения pg_repack документа «Описание расширений продукта СУБД Pangolin».

Внимание!

При включенном прозрачном защитном преобразовании данных (TDE) использовать расширение pg_repack запрещено.

Расширение pgcompacttable

Сведения

Расширениеpgcompacttable – не поддерживается и не рекомендуется к использованию. Удалено из состава продукта в версии 7.x.x.

Инструмент представляет собой скрипт реорганизации данных в «раздутых» таблицах без применения Access Exclusive блокировки.

Отличия от pg_repack:

• не требует много места на диске;
• таблицы обрабатываются «на месте»;
• индексы перестраиваются друг за другом, от меньшего к большему. Максимальное требуемое место на диске равно размеру наибольшего индекса;
• таблицы обрабатываются с настраиваемыми задержками для предотвращения перегрузки IO и всплесков задержки репликации (ключ --delay-ratio);
• не может переносить таблицы или индексы в другое табличное пространство.

Более подробно с информацией о расширении pgcompacttable можно ознакомиться в одноименном разделе расширения pgcompacttable документа «Описание расширений продукта СУБД Pangolin».

Расширение pg_squeeze

Расширение предназначено для оптимизации хранения данных в таблицах и индексах методом переупаковки данных в новый объект.

Предусмотрен обмен данными между расширением и администратором СУБД только через некоторые объекты расширения (таблицы или функции).

Доступные процессы работы с расширением:

• Включение автоматической обработки;
• Настройка существующей базы;
• Регистрация таблицы;
• Отмена регистрации;
• Ручная переупаковка таблицы.

С информацией об установке и использовании расширения pg_squeeze можно ознакомиться в одноименном разделе расширения pg_squeeze документа «Описание расширений продукта СУБД Pangolin».

Сравнение расширений и встроенных в СУБД стандартных утилит

Далее приведена таблица сравнения расширений и встроенных утилит в PostgreSQL:

	CLUSTER/VACUUM	pg_repack	pg_squeeze
Нагрузка на диск и WAL меньше, чем у CLUSTER			-
Скорость работы выше по сравнению с CLUSTER		+	-
Позволяет отдельно работать с индексами	-	+	+
Позволяет указать задержку между этапами реорганизации	-	-	-
Может выполнять операцию CLUSTER		+	+
Не требует наличия основных (primary) или уникальных (unique) ключей	Только VACUUM	-	-
Не требует дополнительного дискового пространства	-	-	-
Может переносить таблицы и/или индексы в другие табличные пространства	-	+	+
Не блокирует операции чтения и записи по таблице	-	+	+
Минимальный уровень журналирования	Любой	Любой	logical

Работу каждого из этих инструментов могут замедлить:

• долгие транзакции;
• постоянная вставка и изменение данных.

Расширение pg_repack рекомендуется использовать для переноса таблицы в другое табличное пространство и использования CLUSTER без блокировки, а расширение pg_squeeze для переупаковки таблицы с минимальной недоступностью по расписанию, автоматически и с учетом порогов на доли «мертвых» записей.

Настройка

Настройка данной функциональности будет отличаться в зависимости от выбранного инструмента.

О настройке поставляемых расширений читайте в следующих разделах:

• pg_squeeze;
• pg_repack.

Использование

С примерами использования функциональности можно ознакомиться в соответствующем разделе выбранного инструмента:

• расширение pg_squeeze;
• расширение pg_repack.