VACUUM на часто обновляемых таблицах
В прошлой статье я описал общие настройки AUTOVACUUM, подходящие для обычной работы. Параметры агрессивные, но позволяют работать другим процессам в нормальном режиме.
Часто обновляемые таблицы — это очень интересный случай работы с VACUUM, которому нужно уделить особое внимание.
К счастью, PostgreSQL позволяет настроить параметры AUTOVACUUM для каждой таблицы особо. Таким образом при достаточно агрессивной общей настройке AUTOVACUUM отдельные таблицы можно настроить еще более агрессивно.
Что такое часто обновляемые таблицы?
Часто обновлямые таблицы содержат данные, которые в связи с бизнес-процессом требуют постоянного перерасчета для поддержания в актуальном состоянии. Например: текущий остаток на счету клиента, количество пассажиров на поезде, свободные билеты в театр и т.д.
Почему часто обновляемые таблицы требуют особого внимания?
При обычной работе MVCC на каждую запись в таблице остается ее прежняя версия. Эти версии по-прежнему занимают место в таблице, хотя не видны для транзакций. Процессы VACUUM (и AUTOVACUUM) предназначены для возвращения места, занятого устаревшими версиями данных в дальнейшую работу. Очень коротко: процесс VACUUM помечает место, занятое прежней версией данных, как свободное и доступное для использования. Так же выполняется еще несколько других вспомогательных процессов: обновляются таблицы видимости, карты свободного пространства и т.п.
Процесс вставки новой или измененной версии записи происходит следующим образом: просматривается страница, на которой находится запись на свободное место подходящего размера. Если место найдено, новая версия вставляется на ту же страницу. Если не найдено, то просматривается таблица, начиная с головы, на наличие свободного участка подходящего размера. Если участок найден, то запись вставляется туда. Если не найден, то данные вставляются в хвост таблицы. При необходимости выделяется новая страница данных. При этом для прежней версии данных размечается служебное поле для транзакции, удалившей запись. Для последующих транзакций эта прежняя версия запись становится не видна.
Процесс VACUUM проходит данные и проверяет их на видимость. Если данные устарели, то место с этими данными помечается как свободное. Так же процесс выполняет несколько других действий, но сейчас важно то, что он помечает место как доступное.
Для обычных таблиц VACUUM успевает обработать данные, поэтому новая запись или версия записи будет вставлена в «дырку», помеченную для использования.
Для часто обновляемых таблиц VACUUM может не успевать обработать данные, поэтому новая запись или версия записи будет записана в конец. Таким образом мы можем получить увеличение объема таблицы при относительно небольшом количестве данных внутри ее. Таблица распухает (bloat). Сказанное относится не только к основной таблице, но и к данным TOAST и индексам. Таким образом, любая последующая операция замедляется, так как для выполнения данных нужно обрабатывать больший объем данных.
Оптимизация VACUUM для часто обновляемых таблиц
Для часто обновляемых таблиц VACUUM (точнее, AUTOVACUUM) нужно настроить еще агрессивнее. То есть на эти таблицы процессы AUTOVACUUM должны приходить еще чаще, чем на остальные. Достигается это выставлением параметров для конкретной таблицы. Для уже имеющейся таблицы выполняется команда вида:
ALTER TABLE table_name SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 1000);
Указанная выше команда приводит к срабатыванию AUTOVACUUM после изменения или удаления 1000 строк вне зависимости от того сколько всего строк в таблице. Так же эти параметры можно указать и в момент создания таблицы.
Полный список параметров можно посмотреть здесь. Благодаря большому списку параметров, AUTOVACUUM на таблицу можно настроить очень тонко.
Fillfactor
Обращаю внимание на интересный параметр: fillfactor. Этот параметр отвечает за степень заполненности страницы данных, прежде чем будет создана новая. Таким образом, при обычной вставке таблица будет расти опережающими темпами, так как будут запланированные пропуски на странице. Но в момент выполнения обновления будет сделана попытка разместить новую версию данных на той же странице при наличии достаточного места. Так как свободное место запланировано заранее, вероятность вставки на той же странице существенно увеличивается. Это ускоряет работу в сравнении со вставкой в свободное пространство на другой странице или вставка по вновь добавленную страницу. Для частообновляемых таблиц этот параметр можно выставить в значение меньше 100.
Рекомендация
Комплексная настройка параметров таблицы и проведение периодической проверки таблиц на вздутие (bloat) позволяет держать таблицы компактными и быстрыми в работе.
Дополнительное чтение
Настройка VACUUM для таблицы не единственное рекомендуемое действие для частообновляемых таблиц. Так как они имеют склонность к вздутию, это нужно выявлять и устранять. По этому вопросу у нас в блоге есть хорошая статья (англ).
Данная статья была написана чтобы ответить нашим читателям на комент в статье блога. Если у вас возник вопрос, ответа на который вы не можете найти, пишите в комментариях ниже. Хотите поделиться своим опытом работы с частообновляемыми таблицами? Делитесь в коментариях!
