Пользователь
0,0
рейтинг
21 февраля 2013 в 20:49

Разработка → Table bloat? Не, не слышал…



Думаю многим известна особенность PostgreSQL, которая приводит к эффекту раздувания таблиц, или table bloat. Известно что она проявляет себя в случаях интенсивного обновления данных, как при частых UPDATE так и при INSERT/DELETE операциях. В результате такого раздувания снижается производительность. Рассмотрим почему это происходит и как с этим можно бороться.


Таблицы в PostgreSQL представлены в виде страниц, размером 8Kb, в которых размещены записи. Когда одна страница полностью заполняется записями, к таблице добавляется новая страница. При удалалени записей с помощью DELETE или изменении с помощью UPDATE, место где были старые записи не может быть повторно использовано сразу же. Для этого процесс очистки autovacuum, или команда VACUUM, пробегает по изменённым страницам и помечает такое место как свободное, после чего новые записи могут спокойно записываться в это место. Если autovacuum не справляется, например в результате активного изменения большего количества данных или просто из-за плохих настроек, то к таблице будут излишне добавляться новые страницы по мере поступления новых записей. И даже после того как очистка дойдёт до наших удалённых записей, новые страницы останутся. Получается что таблица становится более разряженной в плане плотности записей. Это и называется эффектом раздувания таблиц, table bloat.

Процедура очистки, autovacuum или VACUUM, может уменьшить размер таблицы убрав полностью пустые страницы, но только при условии что они находятся в самом конце таблицы. Чтобы максимально уменьшить таблицу в PostgreSQL есть VACUUM FULL или CLUSTER, но оба эти способа предполагают установку тяжелых и длительных блокировок на таблицу, что далеко не всегда является подходящим решением.

Рассмотрим одно из решений. При обновлении записи с помощью UPDATE, если в таблице есть свободное место, то новая версия пойдет именно в свободное место, без выделения новых страниц. Предпочтение отдается свободному месту ближе к началу таблицы. Если обновлять таблицу с помощью т.н. fake updates, типа some_column = some_column с последней страницы, в какой-то момент, все записи с последней страницы перейдут в свободное место в предшествующих страницах таблицы. Таким образом, после нескольких таких операций, последние страницы окажутся пустыми и обычный неблокирующий VACUUM сможет отрезать их от таблицы, тем самым уменьшив размер.

В итоге, с помощью такой техники можно максимально сжать таблицу, при этом не вызывая критичных блокировок, а значит без помех для других сессий и нормальной работы базы.
И теперь самое важное)))) Для автоматизации этой процедуры существует утилита pgcompactor.

Её основные характеристики:
  • не требует никаких зависимостей кроме Perl >=5.8.8, т.е. можно просто скопировать pgcompactor на сервер и работать с ним;
  • работает через адаптеры DBD::Pg, DBD::PgPP или даже через стандартную утилиту psql, если первых двух на сервере нет;
  • обработка как отдельных таблиц, так и всех таблиц внутри схемы, базы или всего кластера;
  • возможность исключения баз, схем или таблиц из обработки;
  • анализ эффекта раздувания и обработка только тех таблиц, у которых он присутствует, для более точных расчетов рекомендуется установить расширение pgstattuple;
  • анализ и перестроение индексов с эффектом раздувания;
  • анализ и перестроение уникальных ограничений (unique constraints) и первичных ключей (primary keys) с эффектом раздувания;
  • инкрементальное использование, т.е. можно остановить процесс сжатия без ущерба чему-либо;
  • динамическая подстройка под текущую нагрузку базы данных, чтобы не влиять на производительность пользовательских запросов (с возможностью регулировки при запуске);
  • рекомендации администраторам, сопровождаемые готовым DDL, для перестроения объектов базы, которые не могут быть перестроены в автоматическом режиме.


Пара примеров использования:
запуск на всём кластере с обязательным перестроением индексов:
# pgcompactor --all --reindex

запуск на отдельной таблице (с перестроением индексов):
# pgcompactor --reindex --dbname geodata --table cities --verbose info >pgcompactor.log 2>&1

Результат работы:
Размер таблицы снизился с 9,2GB до 5,6GB. Совокупный размер всех индексов снизился с 7,5GB до 2,8GB


URL проекта: github.com/PostgreSQL-Consulting/pgcompacttable

Большое спасибо авторам утилиты за их работу! Это действительно полезный инструмент.

Спасибо за внимание!
Алексей @lesovsky
карма
74,5
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (25)

  • +2
    С «PG» не работал, но теперь буду знать этот подводный камень. Спасибо lesovsky и хабр :)
  • +3
    Вы не проводили сравнение с pg_reorg? Навскидку мне кажется, что он должен быть намного быстрее.
    • +1
      Там другой принцип.
      Создаётся временная таблица, которая и ужимается. Минусы в том, что надо будет двухкратное место, т.е. если жмём таблицу в 100 гб, то нужно эти 100гб дополнительно ещё где то взять, что не всегда бывает.
      А также нельзя менять DDL во время выполнения pg_reorg, иначе данные завалятся.
      А также он не умеет дисковую нагрузку лимитировать во время работы и долбает по максимуму.
      • +1
        на самом деле не 100 гб надо, а столько, сколько будет после сжатия :) pg_reorg еще может использоваться для выполнения команды cluster и для изменения порядка столбцов в таблице. DDL, на сколько я помню, выполнять не получится, т.к. pg_reorg ставит блокировку. а дисковую нагрузку можно лимитировать через ionice :)
    • +1
      Насколько мне известнт pg_reorg работает через создание новой таблицы и навешивание триггеров на старую и затем переносит данные из старой таблицы в новую (раньше было так, а сейчас может принцип работы тулзы и изменился).
      Если интересно, еще есть compact_table от depesz.
  • +1
    Подозреваю что другие СУБД тоже могут иметь такую же проблему, т.к. почти во всех место выделяется постранично.
    • –1
      у мускуля такая же болезнь. Данные таблиц пухнут как грибы :)
      • 0
        Я имел в виду MySQL с движком MyISAM он вроде full-table-locking
        • 0
          как раз у нас MyISAM и пухнет, и спасет лишь только backup/restore часов эдак на 100… а сервис останавливать нельзя ни на минуту иначе пользователи повернутся задом и мы останемся без хлеба :)
      • 0
        кто там еще не верит?
        прошу сделать следующий эксперимент:
        сделайте 10M INSERT посмотрите место,
        потом 5M рандомных DELETE — посмотрите место,
        потом еще 5M рандомных INSERT
        и наконец 10M DELETE.
        Eсли у вас будет размер файла близким к первоначальному, то это будет просто замечательно…
  • +10
    Ну, значит, начнем поносить:
    Во-первых, INSERT никоим образом не раздувает таблицу, а лишь добавляет туда данные, собственно, как и DELETE — он просто не освобождает.

    Во-вторых, обратите внимание на ваши индексы после фейковых Update: да да он стал в 2 раза толще ибо теперь там ссылка на старую (удаленную и даже подрезанную) и на новую запись. Собственно и механизм фейкового апдейта как правило на серьезной базе не работет — автовакуум тупо не успеет. В итоге надо последовательно и инкрементально апдейтить записи. лучше идя от физического конца.

    В-третих: в pgcompactor не все так тривиально, в том числе из за пухнущего индекса, и он тупо не применим для восьмерок. Ему нужно знать физическое расположение записи в таблице. Иначе можно дров наломать.

    Потом автовакуума и вакуума не достаточно для переиспользования места. Информация о «дырках» должна еще где то храниться и если вам «повезло» использовать версии младше 8.4, то не профукайте max_fsm_pages и max_fsm_relations.
    Но так же вакуум не является строго необходимым для поиска дырок, есть еще также «минивакуум» который выполняется при доступе к странице например во время SELECT, но там много ограничений. Есть еще магический «HOT UPDATE» начиная с 8.3.

    Для понимания откуда растут ноги настоятельно рекомендую презентацию Брюса Момджана «MVCC unmasked» на языке наиболее вероятного противника доступная тут: momjian.us/main/writings/pgsql/mvcc.pdf.
    Единственное что не знаю где достать со звуком. В принципе он выступал на последнем Highload и я могу попробовать сделать что то типа статейки если хабравчанам будет интересно. Там рядом так же много других презенташек на тему глубин PostgreSQL.

    На последнем pg_days на highload, люди, широко известные в узких кругах, настойчиво хвалили pg_reorg, но с некоторой настороженностью, ибо работает на низком уровне и могут быть потери данных, но правда лишь потенциально.

    Сам я в экстренных случаях использую «перекладывалку» — принцип похожий на используемый в pg_reorg, но все исключительно на уровне SQL. При этом я являюсь ярым противником различных утилит/скриптов и прочих «костылей» против блоатинга.

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

    • +3
      > могу попробовать сделать что то типа статейки если хабравчанам будет интересно

      уверен что будет
    • НЛО прилетело и опубликовало эту надпись здесь
    • +1
      >> Во-первых, INSERT никоим образом не раздувает таблицу, а лишь добавляет туда данные, собственно, как и DELETE — он просто не освобождает.
      я не писал что INSERT раздувает таблицу. Раздувание идет за счет сочетающихся INSERT и DELETE.

      >> Во-вторых, обратите внимание на ваши индексы после фейковых
      ой, это известная проблема,… не зря там есть функция перестроения индексов (вы что не читали список возможностей?)

      >> В итоге надо последовательно и инкрементально апдейтить записи. лучше идя от физического конца.
      об этом тоже написано, четвертый абзац: «Если обновлять таблицу с помощью т.н. fake updates, типа some_column = some_column с последней страницы» (да хватит уже читать по диагонали)

      >> Сам я в экстренных случаях использую «перекладывалку»
      Сами то почему pg_reorg не используете? потому что есть риски. А вот погуглите про pgcompactor, о нем вобще интернеты не знают.

      Имхо вы немножко поторопились с ответом и вывали все на горячую голову не осмыслив написаного (сложилось впечатление что читали текст по диагонали). Середина и конец коментария уже более вменяемея и есть конструктив относительно PG 8. (Про него я вообще ничего не могу сказать, т.к. не работал с ним).
      • 0
        Не у всех PG для тривиального веб. У некоторых еще несколько сотен баз постгресса кое где объединенные в MPP кластеры с объемом данных под террабайт на каждом сервере (собственно база там правда порядка 10-20%) по всей России. Профиль работы что то типа «самописец» — кольцевой. Кое куда только на оленях и доедешь. плюс требования 24/7 и надежность хранения данных. Так что кое где и 8.1 осталось. На девятку переходить нам в продакшене еще рановато… Подождем 9.3 тем более что никакого функционала важного для нас в 9 пока нет.

        «Возможность пересоздавать индекс» и «при перекладывании всегда используется пересоздание индекса» несколько разные понятия.
        • 0
          Если не секрет, расскажите поподробнее про «самописец — кольцевой», а то на уме одни догадки и гипотезы. В частности интересует общая схема, взаимосвязь компонентов, версии софта, причины почему используется такая схема.

          >> несколько разные понятия.
          да разные понятия, но цель преследуется общая, снизить размер индексов (кстати на графике в процессе работы над таблицей, незаметно чтобы индексы росли в размерах… хз почему так, такто должны были)
          • 0
            Отвечу ссылкой: www.mfisoft.ru/products/sorm/sorm2/sormovich, так что подробнее рассказывать не буду.
            При использовании предпочтение отдается не свежим версиям, а испытанным ибо очень жесотоки требования 24/7 и работа системы в автономнейшем режиме, ибо она физически не имеет выходов в сеть. А даунтайм затрудним.
    • +2
      pgcompactor в самом конце конкурентно пересоздаёт индексы, так что не распухнут, на картинке в посте красным.

      восьмёрка прошлый век, уже какбэ 9.2

      >> Если автовакуум не справляется — надо настраивать его или менять запросы.
      например?! В таблицу много-много пишем (апдейтим), настроен самый злой автовакуум (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor) в итоге что он вообще никогда не выключается и всё равно таблица пухнет

      >> являюсь ярым противником различных утилит/скриптов и прочих «костылей» против блоатинга
      ваше право, но такова правда жизни и от этого никуда не денешься всё равно я считаю
      • 0
        восьмёрка прошлый век, уже какбэ 9.2

        Повторюсь: не всегда есть возможность обновить. Иногда даже просто физическая отсутствует. Не говоря про организационную. А реально обусловленной необходимости переходить на 9 нет. Тогда смысл?

        например?!

        Ну с ходу приходит в голову использование того же fillfactor. Использование возможности hot update.

        и всё равно таблица пухнет

        Но в первую очередь я бы обратил внимание на max_fsm_pages и вообще fsm (для 9 это должно быть не актуально)
        Затем бы посмотрел есть ли шансы у автовакуума проваакумить эту таблицу — возможно вы используете апдейты в длительных транзакциях и он просто не может добраться до страницы (автовакуум (и просто вакуум) не вакуумирует те страницы таблиц, которые используются в активных снапшотах, собственно этим он и отличается от полного вакуума).

        Если не помогает значит зря используете СУБД с MVCC. Может стоит использовать «блокировщик» тот же MySQL. Некоторые вон для постгресса пишут типа key-value storage (сейчас не вспомню название, вроде hstore) видимо не понимая, что он просто не предназначен для такого рода задач, а потом будут удивлятся чего он в продакшене пухет.
    • 0
      > На последнем pg_days на highload, люди, широко известные в узких кругах, настойчиво хвалили pg_reorg, но с некоторой настороженностью, ибо работает на низком уровне и могут быть потери данных, но правда лишь потенциально.

      Прецедент был: profyclub.ru/docs/153
      • 0
        бага описанная по ссылке была исправлена что-то около года тому назад. лично натыкался, на тестовой системе только.
        • 0
          Да, пофиксили. Я написал к тому, что опасения, не безосновательны: уже один раз было.
  • +1
    Интересная идея. Жаль, что девелоперы постгреса отмазываются, что неблокирующий (точнее, легко блокирующий отдельные страницы) VACUUM FULL очень сильно тяжело сделать. Потратили бы немного времени хотя бы на такой дубовый метод, зато заметно облегчили бы жизнь миллионам пользователей. Все-таки SQL-подход сам диктует: «Подавайте в базу данные и запросы, а остальное — не ваша забота» — большинство программистов не станут разбираться в тонкостях тюнинга базы и неблокирующего пересоздания индексов ;)

    Проблема с раздуванием индекса, как заметил FYR, на самом деле актуальна и без fake updates. При частых обновлениях индексы довольно быстро раздуваются в разы, по сравнению со свежими индексами на тех же данных. Выручает то, что в postgresql есть возможность неблокирующего создания индекса — приходится скриптом периодически проводить такую операцию, с последующим удалением старых индексов. Тоже, кстати, камень в огород разработчков, ведь автоматизировать это действие совсем не сложно, если и обычный скрипт с таким справляется. А ведь компактные индексы ещё и память эффективнее расходуют — тоже бонус к производительности.

    Когда одна страница полностью заполняется записями, к таблице добавляется новая страница.
    Есть очень полезная опция для CREATE|ALTER TABLE — fillfactor. Часть места в странице можно зарезервировать, тогда UPDATE-ы не будут копировать обновленные данные в новую страницу, а используют текущую.

    Стоит также заметить, что заметный вклад в раздувание таблиц вносит фрагментация страниц. Если строчки переменного размера (при активном использовании NULL или текстовых полей), то после удаления строчки в освободившееся место тяжело подобрать новую с таким же размером — часто пишется чуть меньшая по размеру, и тогда остаток места в несколько байт (или десятков байт) останется неиспользованным, возможно — навсегда.
    • 0
      Да, fill factor очень полезен, нужно только заранее спрогнозировать дополнительное место, т.к. таблица будет занимать больше места чем собственно данные. И это дествительно только для UPDATE'ов.
    • 0
      со строчками не всегда правы, не забывайте про pg_toast

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.