Комментарии 25
С «PG» не работал, но теперь буду знать этот подводный камень. Спасибо lesovsky и хабр :)
Вы не проводили сравнение с pg_reorg? Навскидку мне кажется, что он должен быть намного быстрее.
Там другой принцип.
Создаётся временная таблица, которая и ужимается. Минусы в том, что надо будет двухкратное место, т.е. если жмём таблицу в 100 гб, то нужно эти 100гб дополнительно ещё где то взять, что не всегда бывает.
А также нельзя менять DDL во время выполнения pg_reorg, иначе данные завалятся.
А также он не умеет дисковую нагрузку лимитировать во время работы и долбает по максимуму.
Создаётся временная таблица, которая и ужимается. Минусы в том, что надо будет двухкратное место, т.е. если жмём таблицу в 100 гб, то нужно эти 100гб дополнительно ещё где то взять, что не всегда бывает.
А также нельзя менять DDL во время выполнения pg_reorg, иначе данные завалятся.
А также он не умеет дисковую нагрузку лимитировать во время работы и долбает по максимуму.
на самом деле не 100 гб надо, а столько, сколько будет после сжатия :) pg_reorg еще может использоваться для выполнения команды cluster и для изменения порядка столбцов в таблице. DDL, на сколько я помню, выполнять не получится, т.к. pg_reorg ставит блокировку. а дисковую нагрузку можно лимитировать через ionice :)
Насколько мне известнт pg_reorg работает через создание новой таблицы и навешивание триггеров на старую и затем переносит данные из старой таблицы в новую (раньше было так, а сейчас может принцип работы тулзы и изменился).
Если интересно, еще есть compact_table от depesz.
Если интересно, еще есть compact_table от depesz.
Подозреваю что другие СУБД тоже могут иметь такую же проблему, т.к. почти во всех место выделяется постранично.
у мускуля такая же болезнь. Данные таблиц пухнут как грибы :)
Я имел в виду MySQL с движком MyISAM он вроде full-table-locking
кто там еще не верит?
прошу сделать следующий эксперимент:
сделайте 10M INSERT посмотрите место,
потом 5M рандомных DELETE — посмотрите место,
потом еще 5M рандомных INSERT
и наконец 10M DELETE.
Eсли у вас будет размер файла близким к первоначальному, то это будет просто замечательно…
прошу сделать следующий эксперимент:
сделайте 10M INSERT посмотрите место,
потом 5M рандомных DELETE — посмотрите место,
потом еще 5M рандомных INSERT
и наконец 10M DELETE.
Eсли у вас будет размер файла близким к первоначальному, то это будет просто замечательно…
Ну, значит, начнем поносить:
Во-первых, 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. При этом я являюсь ярым противником различных утилит/скриптов и прочих «костылей» против блоатинга.
Если ваша база пухнет — надо лечить болезнь, а не заниматься купированием симптомов. Если автовакуум не справляется — надо настраивать его или менять запросы.
Во-первых, 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. При этом я являюсь ярым противником различных утилит/скриптов и прочих «костылей» против блоатинга.
Если ваша база пухнет — надо лечить болезнь, а не заниматься купированием симптомов. Если автовакуум не справляется — надо настраивать его или менять запросы.
> могу попробовать сделать что то типа статейки если хабравчанам будет интересно
уверен что будет
уверен что будет
НЛО прилетело и опубликовало эту надпись здесь
>> Во-первых, INSERT никоим образом не раздувает таблицу, а лишь добавляет туда данные, собственно, как и DELETE — он просто не освобождает.
я не писал что INSERT раздувает таблицу. Раздувание идет за счет сочетающихся INSERT и DELETE.
>> Во-вторых, обратите внимание на ваши индексы после фейковых
ой, это известная проблема,… не зря там есть функция перестроения индексов (вы что не читали список возможностей?)
>> В итоге надо последовательно и инкрементально апдейтить записи. лучше идя от физического конца.
об этом тоже написано, четвертый абзац: «Если обновлять таблицу с помощью т.н. fake updates, типа some_column = some_column с последней страницы» (да хватит уже читать по диагонали)
>> Сам я в экстренных случаях использую «перекладывалку»
Сами то почему pg_reorg не используете? потому что есть риски. А вот погуглите про pgcompactor, о нем вобще интернеты не знают.
Имхо вы немножко поторопились с ответом и вывали все на горячую голову не осмыслив написаного (сложилось впечатление что читали текст по диагонали). Середина и конец коментария уже более вменяемея и есть конструктив относительно PG 8. (Про него я вообще ничего не могу сказать, т.к. не работал с ним).
я не писал что INSERT раздувает таблицу. Раздувание идет за счет сочетающихся INSERT и DELETE.
>> Во-вторых, обратите внимание на ваши индексы после фейковых
ой, это известная проблема,… не зря там есть функция перестроения индексов (вы что не читали список возможностей?)
>> В итоге надо последовательно и инкрементально апдейтить записи. лучше идя от физического конца.
об этом тоже написано, четвертый абзац: «Если обновлять таблицу с помощью т.н. fake updates, типа some_column = some_column с последней страницы» (да хватит уже читать по диагонали)
>> Сам я в экстренных случаях использую «перекладывалку»
Сами то почему pg_reorg не используете? потому что есть риски. А вот погуглите про pgcompactor, о нем вобще интернеты не знают.
Имхо вы немножко поторопились с ответом и вывали все на горячую голову не осмыслив написаного (сложилось впечатление что читали текст по диагонали). Середина и конец коментария уже более вменяемея и есть конструктив относительно PG 8. (Про него я вообще ничего не могу сказать, т.к. не работал с ним).
Не у всех PG для тривиального веб. У некоторых еще несколько сотен баз постгресса кое где объединенные в MPP кластеры с объемом данных под террабайт на каждом сервере (собственно база там правда порядка 10-20%) по всей России. Профиль работы что то типа «самописец» — кольцевой. Кое куда только на оленях и доедешь. плюс требования 24/7 и надежность хранения данных. Так что кое где и 8.1 осталось. На девятку переходить нам в продакшене еще рановато… Подождем 9.3 тем более что никакого функционала важного для нас в 9 пока нет.
«Возможность пересоздавать индекс» и «при перекладывании всегда используется пересоздание индекса» несколько разные понятия.
«Возможность пересоздавать индекс» и «при перекладывании всегда используется пересоздание индекса» несколько разные понятия.
Если не секрет, расскажите поподробнее про «самописец — кольцевой», а то на уме одни догадки и гипотезы. В частности интересует общая схема, взаимосвязь компонентов, версии софта, причины почему используется такая схема.
>> несколько разные понятия.
да разные понятия, но цель преследуется общая, снизить размер индексов (кстати на графике в процессе работы над таблицей, незаметно чтобы индексы росли в размерах… хз почему так, такто должны были)
>> несколько разные понятия.
да разные понятия, но цель преследуется общая, снизить размер индексов (кстати на графике в процессе работы над таблицей, незаметно чтобы индексы росли в размерах… хз почему так, такто должны были)
Отвечу ссылкой: www.mfisoft.ru/products/sorm/sorm2/sormovich, так что подробнее рассказывать не буду.
При использовании предпочтение отдается не свежим версиям, а испытанным ибо очень жесотоки требования 24/7 и работа системы в автономнейшем режиме, ибо она физически не имеет выходов в сеть. А даунтайм затрудним.
При использовании предпочтение отдается не свежим версиям, а испытанным ибо очень жесотоки требования 24/7 и работа системы в автономнейшем режиме, ибо она физически не имеет выходов в сеть. А даунтайм затрудним.
pgcompactor в самом конце конкурентно пересоздаёт индексы, так что не распухнут, на картинке в посте красным.
восьмёрка прошлый век, уже какбэ 9.2
>> Если автовакуум не справляется — надо настраивать его или менять запросы.
например?! В таблицу много-много пишем (апдейтим), настроен самый злой автовакуум (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor) в итоге что он вообще никогда не выключается и всё равно таблица пухнет
>> являюсь ярым противником различных утилит/скриптов и прочих «костылей» против блоатинга
ваше право, но такова правда жизни и от этого никуда не денешься всё равно я считаю
восьмёрка прошлый век, уже какбэ 9.2
>> Если автовакуум не справляется — надо настраивать его или менять запросы.
например?! В таблицу много-много пишем (апдейтим), настроен самый злой автовакуум (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor) в итоге что он вообще никогда не выключается и всё равно таблица пухнет
>> являюсь ярым противником различных утилит/скриптов и прочих «костылей» против блоатинга
ваше право, но такова правда жизни и от этого никуда не денешься всё равно я считаю
восьмёрка прошлый век, уже какбэ 9.2
Повторюсь: не всегда есть возможность обновить. Иногда даже просто физическая отсутствует. Не говоря про организационную. А реально обусловленной необходимости переходить на 9 нет. Тогда смысл?
например?!
Ну с ходу приходит в голову использование того же fillfactor. Использование возможности hot update.
и всё равно таблица пухнет
Но в первую очередь я бы обратил внимание на max_fsm_pages и вообще fsm (для 9 это должно быть не актуально)
Затем бы посмотрел есть ли шансы у автовакуума проваакумить эту таблицу — возможно вы используете апдейты в длительных транзакциях и он просто не может добраться до страницы (автовакуум (и просто вакуум) не вакуумирует те страницы таблиц, которые используются в активных снапшотах, собственно этим он и отличается от полного вакуума).
Если не помогает значит зря используете СУБД с MVCC. Может стоит использовать «блокировщик» тот же MySQL. Некоторые вон для постгресса пишут типа key-value storage (сейчас не вспомню название, вроде hstore) видимо не понимая, что он просто не предназначен для такого рода задач, а потом будут удивлятся чего он в продакшене пухет.
> На последнем pg_days на highload, люди, широко известные в узких кругах, настойчиво хвалили pg_reorg, но с некоторой настороженностью, ибо работает на низком уровне и могут быть потери данных, но правда лишь потенциально.
Прецедент был: profyclub.ru/docs/153
Прецедент был: profyclub.ru/docs/153
Интересная идея. Жаль, что девелоперы постгреса отмазываются, что неблокирующий (точнее, легко блокирующий отдельные страницы) VACUUM FULL очень сильно тяжело сделать. Потратили бы немного времени хотя бы на такой дубовый метод, зато заметно облегчили бы жизнь миллионам пользователей. Все-таки SQL-подход сам диктует: «Подавайте в базу данные и запросы, а остальное — не ваша забота» — большинство программистов не станут разбираться в тонкостях тюнинга базы и неблокирующего пересоздания индексов ;)
Проблема с раздуванием индекса, как заметил FYR, на самом деле актуальна и без fake updates. При частых обновлениях индексы довольно быстро раздуваются в разы, по сравнению со свежими индексами на тех же данных. Выручает то, что в postgresql есть возможность неблокирующего создания индекса — приходится скриптом периодически проводить такую операцию, с последующим удалением старых индексов. Тоже, кстати, камень в огород разработчков, ведь автоматизировать это действие совсем не сложно, если и обычный скрипт с таким справляется. А ведь компактные индексы ещё и память эффективнее расходуют — тоже бонус к производительности.
Стоит также заметить, что заметный вклад в раздувание таблиц вносит фрагментация страниц. Если строчки переменного размера (при активном использовании NULL или текстовых полей), то после удаления строчки в освободившееся место тяжело подобрать новую с таким же размером — часто пишется чуть меньшая по размеру, и тогда остаток места в несколько байт (или десятков байт) останется неиспользованным, возможно — навсегда.
Проблема с раздуванием индекса, как заметил FYR, на самом деле актуальна и без fake updates. При частых обновлениях индексы довольно быстро раздуваются в разы, по сравнению со свежими индексами на тех же данных. Выручает то, что в postgresql есть возможность неблокирующего создания индекса — приходится скриптом периодически проводить такую операцию, с последующим удалением старых индексов. Тоже, кстати, камень в огород разработчков, ведь автоматизировать это действие совсем не сложно, если и обычный скрипт с таким справляется. А ведь компактные индексы ещё и память эффективнее расходуют — тоже бонус к производительности.
Когда одна страница полностью заполняется записями, к таблице добавляется новая страница.Есть очень полезная опция для CREATE|ALTER TABLE — fillfactor. Часть места в странице можно зарезервировать, тогда UPDATE-ы не будут копировать обновленные данные в новую страницу, а используют текущую.
Стоит также заметить, что заметный вклад в раздувание таблиц вносит фрагментация страниц. Если строчки переменного размера (при активном использовании NULL или текстовых полей), то после удаления строчки в освободившееся место тяжело подобрать новую с таким же размером — часто пишется чуть меньшая по размеру, и тогда остаток места в несколько байт (или десятков байт) останется неиспользованным, возможно — навсегда.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Table bloat? Не, не слышал…