Введение

Операция VACUUM FULL полностью пересоздаёт таблицу, предельно оптимизируя её. Она требует полной блокировки таблицы, поэтому высоконагруженные таблицы обрабатывать ею без простоя системы нельзя. Вместо VACUUM FULL можно использовать расширение pg_repack. Оно создаёт на обрабатываемой таблице триггер, отслеживающей модификации, создаёт копию таблицы, догоняет набежавшие изменения. В конце работы берётся короткая блокировка, старая таблица удаляется, новая становится на её место.

Недостатком pg_repack является то, что она работает СЛИШКОМ БЫСТРО – фактически данные копируются и удваиваются в объёме (по отношению к исходной таблице). Каталог pg_wal забивается с такой скоростью, что архиватор не успевает обрабатывать файлы.

Далее описывается способ замедления работы pg_repack с использованием механизма cgroup.

Система:

• Linux RedOS.

• PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-6), 64-bit.

• pg_repack версия 1.4.8.

Подготовка

Экспериментальная таблица [data.usage] 17gb – 13gb данные, 4gb индекс.

Если в системе работает только pg_repack и нет другой нагрузки, при ограничениях IO она может подолгу ожидать заполнения WAL-файла, чтобы переключиться на новый. Чтобы имитировать загрузку системы и оперативное переключение на новый WAL, создаём таблицу t1 (CREATE TABLE t1(i INTEGER);) и запускаем вечный скрипт [series.sh] – с интервалом 1 раз в секунду удаляет из неё строки и записывает 1000 строк. Скрипт [series.sh]:

#!/bin/bash
while true
do
  psql -d db01 -c "DELETE FROM t1; INSERT INTO t1 SELECT generate_series(1,1000,1);" > /dev/null
  sleep 1
done

Если операции оптимизации таблиц выполняются на работающей под нагрузкой базе данных, запускать этот скрипт ни к чему – WAL-файлы постоянно наполняются и сменяются.

Скрипт [run.sh] – перестроение таблицы с замером времени:

#!/bin/bash

t1=$(date +%s)
pg_repack -d db01 -p 5432 -t data.usage
t2=$(date +%s)

echo "time: $(($t2-$t1))"

Создание группы cgroup

Добавляем в корневую группу cgroup возможность управления ограничениями IO и создаём подгруппу g1.

# echo "+io" >> /sys/fs/cgroup/cgroup.subtree_control
# mkdir /sys/fs/cgroup/g1

Смотрим блоки major и minor раздела, который хотим ограничить (sdb, 8:16):

# lsblk
NAME                       MAJ:MIN RM  SIZE RO TYPE MOUNTPOINTS
fd0                          2:0    1    4K  0 disk
sda                          8:0    0  100G  0 disk
├─sda1                       8:1    0  500M  0 part /boot
...
sdb                          8:16   0    4T  0 disk
├─sdb1                       8:17   0  500M  0 part /boot
...

Запуски с замедлением

Будем смотреть время обработки таблицы, количество WAL-файлов в каталоге pg_wal к завершению отработки.

Ограничиваем 20mb/sec скорость чтения и записи раздела sdb процессам группы g1.

# echo "8:16 rbps=$((20*1024*1024)) wbps=$((20*1024*1024))" > /sys/fs/cgroup/g1/io.max

Запускаем run.sh и смотрим PID-ы процессов в PostgreSQL:

SELECT * FROM pg_stat_activity WHERE application_name = 'pg_repack';
>>> 50447, 50448

Помещаем их в группу g1:

# echo 50447 > /sys/fs/cgroup/g1/cgroup.procs
# echo 50448 > /sys/fs/cgroup/g1/cgroup.procss

Ожидаем завершения работы скрипта run.sh. Наблюдать за работой процесса можно командой iotop -p 50447. Завершивший работу процесс группы g1 удаляется из неё автоматически.

Аналогично проводим измерения для ограничений 40mb, 80mb, 160mb и без ограничения скорости.

Итог

Скорость обработки считается как размер файла (17gb), делённый на количество секунд, ушедших на его обработку.

Можно, например, начинать замедление с 80mb/sec. Смотреть количество WAL в каталоге pg_wal. Если архиватор не успевает их переносить – уменьшить скорость IO группы cgroup. Оптимальное значение подбирать индивидуально, оно определяется загрузкой системы, пропускной способностью диска, свободным местом.

Очистка

Удаление таблицы (если создавалась):

DROP TABLE t1;

Удаление группы cgroup:

# rmdir /sys/fs/cgroup/g1