В Postgresql вы можете настроить всё! часть 3
Глава 3. Вроде бы всё настроено, но таблицы всё равно «разбухают».
Мы рассмотрели процедуру настройки автоочистки, выявление фрагментированных таблиц и их перестройку и сжатие. Но в процессе работы выявляется, что некоторые таблицы в базе опять выросли, и процедуру по их перестройке требуется повторить. Когда автоочистка не помогает? Возможных причин может быть несколько:
1. Долгие транзакции.
2. Логические дампы pg_dump и pg_dumpall.
3. Массовое изменение или удаление строк.
Начнём по порядку: 1. Долгие транзакции.
У механизма многоверсионности PostgreSQL, и правила видимости строк, основанном на снимках данных есть одна неприятная особенность. Если есть довольно старая транзакция, которой должна быть видна версия строки TUPLE_1 , но актуальная версия уже TUPLE_4, то версии TUPLE_2 и TUPLE_3 , всё равно не могут быть очищены, даже если нет активных транзакций, в снимки которых эти версии попадают. Другими словами, все промежуточные версии строк, которые уже не видны ни в каких транзакциях, не могут быть очищены, пока в БД висит старая транзакция. Связано это с тем, что PostgreSQL оперирует понятием «горизонт очистки», которым является номер самой ранней из активных транзакций, который встречается в каком-либо снимке данных. Данная проблема проиллюстрирована на рисунке:
Как мы видим, текущий актуальный номер по счётчику транзакций это txid=150. Но в системе всё ещё висит активной транзакция под номером 120. А это значит, что любые строки удалённые из любых таблиц в любой базе данных на сервере PostgreSQL транзакциями со 121 по 149, смогут быть очищены только тогда, когда txid=120 завершится(commit,rollback), т.к. она удерживает горизонт очистки.
После того, как «долгая» транзакция наконец завершается (был это commit или rollback в данном случае не важно), горизонт очистки сдвигается до следующей активной транзакции. А это значит, что все строки, которые удерживались, наконец, смогут быть очищены. Но как мы уже знаем, то место, которое потребовалось для новых версий этих строк, при операциях UPDATE, уже не будет возвращено ОС. И после очистки снова появляется bloat, и таблица становится кандидатом на VACUUM FULL.
Каким способом можно попытаться побороть данную проблему? Одним из решений будет установить значение параметров idle_in_transaction_session_timeout и transaction_timeout (до PG17 этот параметр назывался old_snapshot_threshold) в ненулевое значение. idle_in_transaction_session_timeout позволяет защититься от "повисших" открытых транзакций, в том числе по невнимательности dba. Позволяет не допустить "разбухания" таблиц из-за долгой бездействующей транзакции. Второй параметр transaction_timeout служит для тех же целей, но если транзакция не бездействует, а в ней действительно что-то выполняется, хотя бы периодически, с интервалами простаивания менее, чем idle_in_transaction_session_timeout. К сожалению, оба параметра можно задать в терминах времени (миллисекунды), но логично было бы иметь аналогичные параметры и в терминах транзакций (отставание от текущего значения счётчика транзакций), ведь угроза разрастания таблиц присутствует, при наличии большого числа других транзакций в базе. (Уточнение: readonly транзакции получают только виртуальный номер, т.е. счётчик транзакций увеличивают именно пишущие транзакции). Не исключаем, что данная возможность появится в будущих версиях PG, но пока такой возможности нет.
Если же такое решение кажется слишком радикальным, то вот другое решение. Тот факт, что PostgreSQL ввиду своей архитектуры плохо сочетает OLAP и OLTP нагрузку, давно известен, как раз потому, что долгие запросы удерживают горизонт очистки, а большое количество OLTP запросов в это время успевают наплодить большое количество версий строк. Выходом из ситуации будет создание физической реплики и перенос заведомо долгих аналитических запросов на standby (квартальный отчёт, расчёт сводной таблицы и тд.) Параметр max_standby_streaming_delay установленный на реплике позволяет задать задержку воспроизведения WAL-записей в случае возникновения конфликта, например, если на мастере автоматическая очистка удалила версия строки, которая должна быть ещё видна в активной транзакции на реплике.
Можно и перенести все читающие транзакции на реплику, это несложно делается настройками пула соединений, но тогда в случае возникновения любого конфликта при применении WAL записи, применение журналов будет приостанавливаться до момента устранения конфликта или истечении времени max_standby_streaming_delay. Это означает что выполнив обновление строки транзакцией на мастере, мы рискуем не увидеть эти изменения в транзакции на реплике, потому что информация о произведённых изменениях завила в WAL и не применяется на реплике. А аналитические запросы обычно не требовательны к актуальности данных.
2. Логические дампы pg_dump и pg_dumpall.
Выполняя логическое резервирование БД, мы ожидаем получить в нём согласованные данные. Так происходит за счёт того, что pg_dump и pg_dumpall при подключении к серверу на самом деле формируют снимок данных и далее выгружают всё содержимое таблиц, применяя данный снимок. Другими словами для сервера это выглядит как долгая транзакция. Если в этот момент в системе будет совершаться большое количество апдейтов, то мёртвые версии строк не будут удаляться до тех пор, пока pg_dump не доработает. Как результат мы получаем «разбухшую» БД после бэкопирования.
Решение:
· делать pg_dump в рамках технологического окна (ночь), пока нет пользовательской нагрузки.
· распараллеливать работу утилиты: pg_dump --jobs=n , где n – число процессов, которое будет запущено. Снимок данных экспортируется всем параллельным процессам, поэтому резервная копия получится согласованной.
· делать резервирование с реплики.
3. Массовое изменение или удаление строк.
При массовом удалении или обновлении строк в рамках одной транзакции bloat в таблице неизбежен. После таких операций придётся перестраивать таблицу. Но вот если обновления выполняются небольшими порциями и в разных транзакциях, то здесь с разрастанием можно побороться, заранее установив для таблиц, которые часто обновляются, значение fillfactor < 100%. Это табличный параметр, который определяет плотность первоначального заполнения таблицы. Другими словами fillfactor=80 означает, что когда табличная страница будет заполнена на 80%, то INSERT будет выполнять вставку новых строк уже на следующую страницу, а данной резервируется 20% под UPDATE, т.е. под новые версии строк на этой же странице. Наличие такого «резерва» пространства внутри блока позволяет PostgreSQl выполнять такую оптимизацию как HOT-UPDATE (Heap-Only Tuple Update) или внутристраничное обновление. И как следствие, позволяем постгресу также выполнять и внутристраничные очистки. Идея тут в том, что мы изначально жертвуем дисковое пространство, резервируя место под update, и при начальном заполнении таблица будет уже фрагментированной и занимать больше места, но выигрываем на том, что таблица не разрастается настолько сильно, как если бы значение fillfactor было 100%. Рассмотрим простой пример:
Указывать fillfactor=100 нет необходимости, это и так является значением по умолчанию, здесь это делается исключительно для наглядности. Затем заполним таблицу миллионом строк и посмотрим на её размер.
В данный момент в таблице все строки актуальны. Размер таблицы составляет 50Мб, можно сказать, что это эталонный размер таблицы. После чего «повесим» активную транзакцию и выполним несколько раз обновление 10% содержимого таблицы.
Как видим, действительно размер таблицы после каждого обновления прибавляется на 10% = 5Мб. По хорошему, на размере ~ 60Мб должна была запуститься автоочистка, что соответствует значению autovacuum_vacuum_scale_factor = 20%. Но этого не произошло, и таблица продолжила расти в размере. Возможных причины этому может быть две: либо таблица выросла быстрее, чем происходит проверка статистик по мёртвым табличным строкам демоном автоочистки (по умолчанию autovacuum_naptime = 1 min) , либо же эта таблица встала в очередь на очистку не первой и сейчас все воркеры заняты очисткой других таблиц. В любом случае, таблицы сильно нагруженные обновлениями, требуют отдельного внимания и настройки параметра fillfactor (это могут быть таблицы time_last_login которые обновляются при каждом логине пользователя и тд.) Выявить в БД такие таблицы, которые чаще всего обновляются, достаточно просто следующим запросом:
SELECT * FROM pg_stat_all_tables ORDER BY n_tup_newpage_upd DESC ;
Здесь мы увидим отсортированный список из таблиц в БД, у которых наибольшее число обновлений потребовали размещение новой версии строки на новом блоке. Первой в списке оказывается таблица ‘t’, в случае с этой таблицей практически для всех обновлений новая версия строки попала на новый блок и оптимизация HOT-UPDATE не выполнялась.
Теперь изменим fillfactor таблицы на 80% и повторим тест с обновлением по 10% строк.
При изменении параметра fillfactor он применяется для новых создаваемых табличных блоков, но для того, чтобы он применился для уже существующих, придётся перестроить таблицу. Как видим после VACUUM FULL эталонный размер таблицы(без мёртвых кортежей) стал не 50Мб, а 63Мб, т.к. в каждом блоке резервируется 20% места под новые версии строк, которые в нём содержатся. Эта та затрата, которую мы жертвуем вначале, но которая должна сэкономить место в будущем за счёт возможности внутристраничных очисток. Повторим тестирование обновлениями:
Как видим, «раздуть» таблицу больше 75Мб не получается, хотя в первом случае с fillfactor = 100% таблица росла в просто бесконечность. Таблицу получится «раздуть» ещё больше, увеличив порции обновляемых строк, например, обновлять не по 10%, а по 20% строк в одной транзакции, тогда потребуется подобрать другое значение fillfactor ещё меньше.
Автор: Владимир Пудовченко Обратная связь: vladimir.pudovchenko@fors.ru
