По мотивам http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/
Я за недельку написал и отладил в 100 где то раз более быструю реализацию той же идеи с нормальным управлением и не насилующую триггера ON UPDATE (если таковые есть на таблице).
В итоге получилась утилита всего в 4 раза медленне чем VF но не приводящая к полным локам таблицы и распуханию индексов.

брать здесь:
svn checkout http://compacttable.googlecode.com/svn/trunk/ compacttable-read-only
Использовать: запустить vacuum_table.pl --help она все скажет.

Обсуждать или здесь или здесь где удобнее.
Предложения и баг репорты приветствуются.

Приятные особенности:
1)просто в использовании
2)не вызывает распухания индексов
3)можно выполнять инкременатально (т.е. можно прервать работу и запустить заново нормально не с начала)
4)шустрая весьма
5)нормально взаимодествует с наличием триггеров и rules на таблицах (с рулами не тестил но должно)
6)оно работает :)

На тестовом примере созданном следующим образом:

Уже было использовано в боевых условиях для ужатия таблицы распухшей из-за идиотов програмистов до 200Gb (ужалось до 20Gb) (потребовалось всего около 18 часов при этом таблица все это время оставалась доступной для работы что было критично в данном случае).

В общем получилась вполне рабочая тулза которая если бы была под рукой последние 2 года сэкономила бы мне кучу времени и нервов.

Из того что надо отметить из тонкостей:
1)Postgres 8.4 и выше
2)pl/pgsql установленный в базе
3)superuser доступ
4)не работает если есть 'always' или 'replica' триггеры (я за свою жизнь ни одного не видел в реальной задаче)
5)не может ужать распухший TOAST (увы)
6)слегка индексы пухнут но именно слегка а не в 2 раза как от VF
7)на активной таблице мождет deadlock давать иногда но я пока в реальности не видел
8)может давать серьезный index bloat если запустить паралельно с очень долгой транзакцией
9)игнорит fillfactor у таблтицы (увы)
10)чтобы в конце процесса отрезать чистые страницы в конце таблицы всеравно понадобится короткий exclusive lock
11)не работает под windows (хранимка то работает конечно а скрипт управляющий увы)
12)всякие баги невыловленные заранее (тестировал как мог)


Дополнительно выявленные проблемы:

1)vacuum далеко не всегда успевает схватить exclusive lock чтобы уменьшить размер уже упакованной таблицы, в итоге получается ситуация когда последние 10gb таблицы пустые а место все еще занято, для обхода этой ситуации был доработан скрипт и в него было внесено повторение vacuum до 8 раз с увеличивающимися интервалами чтобы всетаки с хорошими шансами таблицу уменьшить.

ToDo:
1)дописать чтобы в конце выводился сгенерированный набор комманд для конкрурентного reindex (через create index concurently) всех индексов где это можно сделать (т.е. primary key так нельзя ужать).
2)перевести на DBD::Pg и постоянный коннект с базой (будет принципиально быстрее, особенно в случае когда упаковка идет мелкими блоками по 1-10 страниц).


<BR>--<BR>Проект с базой но без DBA всеравно что автопарк без штатного автомеханика. Ездит пока все не сломается.<BR> http://mboguk.moikrug.ru

Интересующийся АнонимMaxim Boguk,

А можно вкратце узнать принцип работы тулзы?

А англоязычных ссылках он достаточно неплохо описан в общем то. Если кратко то если в таблице есть свободное место то при update записи новая версия пойдет с это свободное место. При этом безусловное предпочтение отдается свободному месту в начале таблицы при его наличии. В итоге если обновлять таблицу (fake updates вида поле=поле) начиная с последней страницы в какой то момент все записи с последней страницы перейдут в свободное место в начале таблицы. Теперь если тоже самое проделать N раз то последние N страниц базы окажутся пустыми и обычный неблокирующий Vacuum их сможет отрезать от таблицы и освободить (vacuum без full умеет освобождать страницы в конце таблицы если они полностью свободные).
Для прохода с конца таблицы используется доступ по tid (фактически доступ с физическим указанием номеров страниц и записей на страницах http://www.postgresql.org/docs/8.4/interactive/ddl-system-columns.html описание поля ctid). Все остальное уже детали реализации и оптимизация по скорости проще посмотреть в исходниках.
Vacuum fulll делает приблизительно тоже самое вешая полный (exclusive лок) на таблицу и упаковывая все страницы в таблице сразу а не инкрементально по кусочкам.

Теперь что касается pg_reorg:
Знаю плавал пользовался. Прекрасная утилита для своих задач. Но на мой взгляд:
1)слишком уж она сурово в потрохах базы ковыряется (мне не нравится прямой доступ к системным таблицам и тп вещи)
2)ОЧЕНЬ важная особенность: требует 100% от базы или таблицы свободного места на диске что не реально время от времени
3)Не поддается лимитированию по создаваемой нагрузке на сервер
4)Не может использоватся в инкрементальном режиме (например по ночам когда нагрузка на сервер минимальна)

Мое решение заметно медленне это непреложный факт (раз 5-10 точно). Но оно инкрементальное и позволяющее лимитировать нагрузку. И не требующее сборки дополнительного софта на сервере.

Ну и как обычно:
"Пусть расцветают сто цветов"

rockclimberОбъясните пожалуйста махровому чайнику (мне то есть), почему нельзя просто изменить значения параметров vacuum_cost_delay, vacuum_cost_page_hit и vacuum_cost_page_limit в файле postgresql.conf? Результат разве не тот же самый будет?

Какой тот же самый эффект? Vacuum без full не может ужать таблицу распухшую в 10 раз где 90% места пустое, он может только отрезать последние свободные страницы. А vacuum full или cluster которые могут упаковать таблицу - блокируют ее намертво (т.е. никаких операций с ней сделать нельзя будет в процессе... даже select не будет работать), и на большой таблице могут работать часами (а в запущенном случае сутками даже), и все это время ваш проект будет лежать. И никакой настройкой vacuum_* вы это поведение не уберете.

Так что остаются 3 варианта если у вас таблица распухла (из-за неработающего autovacuum, или из-за того что транзакция открытая висела 10 дней, или просто потому что через пол года вспомнили что надо удалять из таблицы данные старше недели и теперь она весит 400gb при том что данных там на 20g, или по еще какой причине...) то у вас остается 4 альтернативы:
1)vacuum full или cluster и длительный downtime проекта
2)перекидывание базы с помощь slony/londiste на другой сервер где она уже будет ужата и переключение туда
3)использовать вышеупомянутый pg_reorg (имеет свои минусы на самом деле так как требует 100% дополнительного свободного места на дисках на время работы)
4)использовать мою утилиту которая не очень быстро но таблицу ужмет и никому при этом не будет мешать.

Я раньше эту проблему через п2 решал но это безумно трудоемкое для DBA занятие к тому же требующее второго сервера под базу.

Maxim Boguk,

Закоммитил большую доработку кода:

1)добавлен вывод набора комманд для выполнения reindex после окончания упаковки таблицы

2)убрано много дублирующего кода и начата подготовка к использованию DBD::Pg при его наличии (будет заметно быстрее работать при небольших значениях --pages-per-round)

3)добавлен более умное определение значений --pages-per-round и --pages-per-vacuum в зависимости от размера таблицы (чем мельче таблица - тем мельче рабочие блоки и чаще routine vacuum)

4)добавлен специальный параметр --delay-ratio который работает следуюшим образом: если пачка в --pages-per-round обработалась за N ms то после этого код будет спать --delay-ratio * N ms (значение по умолчанию = 2), таким образом обеспечивается автоматическая подстройка кода под текущую загрузку сервера чтобы не перегружать дисковую подсистему (так как нагрузка днем и ночью может отличатся на порядки... а скрипт может работать сутками на реально больших таблицах и без такой функциональности может легко устроить 100% disk usage в рабочее время)

WarstoneМакс, помощь в рефакторинге и причесывании кода нужна? Просто Perl/PostgreSQL - это мой конек. Возможно даже перевод всего этого на PL/Perlu и через pgScheduller - организация шадоу вакуумизатора.

Да я думаю помощь пригодится, я на перле лет 5 уже как ничего серьезного не писал. Я доделаю первичный DBD::Pg support и подключу вас в коммитеры проекта.

Дальнейшее обсуждение этой идеи предлагаю перенести на:
jabber maxim.boguk@gmail.com
или (менее предпочтительно)
skype maxim.boguk

eddieWarstoneпропущено...
Да, но "потом".получается после vacuum распухшая база перестанет расти, после vacuum full она ещё и уменьшится, но потом опять начнёт расти.
грубо говоря через месяц использования итоговый размер базы будет одинаковый.

при этом vacuum full намного "тяжелее" обычного vacuum (а предлагаемая процедура, как я понимаю, намного тяжелее и медленнее autovacuum).
а где же профит? ;)

Вот вам наиболее популярные сценарии проблем:

Была себе таблица гигов на 20... вот в нее решили добавить новое поле предположим boolean с default value = FALSE. Если это делать на прямую - на выходе вы получите таблицу размером в 40GB (что в случае если к таблице активно обращаются а памяти у вас скажем 24GB на сервере значит что она перестала помещатся к кещ ОС и shared buffers, у вас пойдут обращения к диску сразу причем сразу очень много и станет грустно весьма).

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

Третий популярный вариант: у вас есть небольшая таблица очереди задач с большим потоком задач (ну скажем там идет 100 insert/update/delete в секунду), autovacuum для нее настроен и обычно там не больше 1000 задач (и ну суммарно 2000-4000 строк включая мертвые). Теперь из-за глюка какого то приложения вы получаете открытую сутки+ транзакцию (не на эту таблицу и возможно даже не на эту базу в пределах одного постгреса). На выходе ваша таблица очереди будет содержать теже 1000 живых строк и около 17280000 мертвых строк, т.е. распухнет где то в 4000 раз и уже никак не будет обеспечивать требуемые 100insert/update/delete в секунду.

Это три наиболее популярных варианта из моей практики когда таблицу надо было упаковывать любой ценой иначе все грозило лечь совсем.

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

eddieMaxim BogukТретий популярный вариант: у вас есть небольшая таблица очереди задач с большим потоком задач (ну скажем там идет 100 insert/update/delete в секунду), autovacuum для нее настроен и обычно там не больше 1000 задач (и ну суммарно 2000-4000 строк включая мертвые). Теперь из-за глюка какого то приложения вы получаете открытую сутки+ транзакцию (не на эту таблицу и возможно даже не на эту базу в пределах одного постгреса). На выходе ваша таблица очереди будет содержать теже 1000 живых строк и около 17280000 мертвых строк
хм... а где прочитать об этом?

Это следует из того как mvcc в postgresql организован.
google: postgresql table bloat long transaction

Если кратко то vacuum/autovacuum не может пометить как свободные для повторного использования удаленные уже записи если они могут быть видны какой то другой активной в базе транзакции (в данном случае очень долгой). В итоге сколько не удаляй записи из маленькой таблицы очереди место в таблице не будет использоватся повторно так как уже удаленные строки на самом деле еще живые так как могут быть видны из совершенно посторонней долгой транзакции. Итого при 100 insert/update/delete в секунду в таблице будут копится строки со скоростью 200записей в секунду (и вместе с этим будет пухнуть сама таблица и ее индексы...).

[quot Andron]Warstone...
Тынц говорит нам следующеепропущено...


Там пишут о взаимосвязи файлового кэша и других структур памяти, но вовсе не о том куда считываются страницы таблиц и индексов.

Хм.. для работы страницы таблиц и индексов считываются и кешируются в shared memory конечно. Если в ней не найдено то идет запрос данных с диска... НО: ОС естественно данные кеширует у себя в памяти и если данные есть в кеше ФС то конечно данные будут взяты оттуда. И именно про это пишут когда говорят что postgres очень сильно (в отличии от оракла который вообще на raw разделах любит жить не используя возможности файловой системы и ее кеша) ориентируется на кеш ФС. Естественно что доступ к кешу ОС несколько медленее чем доступ к данным в shared buffers (изза переключения контекста), но на фоне крайне медленного доступа к физическому диску это как правило незаметно.

[quot Andron]Maxim Bogukпропущено...


Так давайте определимся что кэш ФС является посредником между диском и структурой PostgreSQL которая определяется через параметр shared_buffers. Т.е. вначале данные запрашиваются пользователем в базе, затем PostgreSQL если их не находит в shared_buffers то читает с диска, но не напрямую а используя посредника - кэш ФС. И данные эти опят таки помещает из кэша ФС в shared_buffers для дальнейшей обработки и передачи пользователю. Еще раз выделю свое предположение: PostgreSQL не управляет данными в файловом кэше ОС, только в кэше определенном как shared_buffers. Если это так то имеем интересное следствие с т.з. производительности дискового IO.

Самое интересное следствие выходит если отдать около 50% под shared buffers. В итоге есть шанс получить ситуацию когда у вас данные лежат в 2х местах в памяти и эффективный размер кеша будет половина от наличной памяти.

Из этого следует что по хорошему под shared buffers имеет смысл отдавать или 20-25% или 75-80% наличной памяти (предполагая что сервер выделен исключительно под базу). Из моего опыта оба варианта на 8.3+ вполне работоспособны а какой из них даст большую производительность очень сильно зависит от конкретного проекта.

AndronMaxim Boguk,

Вообще напрашивается такой вопрос: а зачем дополнительно кэшировать данные базы, ведь это приводит к накладным расходам и снижению надежности. Кажется это называется Direct IO и как раз позволяет избежать использования кэширования на уровне ОС в случае использования файлов для базы. Речь идет именно про файлы а не raw device, с которыми подобной проблемы не возникает, но и PostgreSQL их не поддерживает вроде бы.

До 8.3 Postgres очень плохо работал с обьемами shared memory большими чем 1gb. Поэтому так или иначе приходилось использовать кеш ОС.

Сейчас эта проблема устранена в основном (не до конца на самом деле... остались некоторые внутренние накладные расходы которые линейно от обьема shared memory растут), и разговоры о direct IO ведутся но общее мнение разработчиков таково:
текущая схема работы работает вполне прилично и пока никто еще не предоставил test case и результаты где бы direct IO давало бы выйгрыш в скорости работы и смысла в этом направлении двигатся нет (во всяком случае пока нет спонсоров которые бы это оплатили).

К модератору:
если вас не затруднит, пожайлуста, отделите вопросы касащиеся shared buffers/direct IO из этой темы в отдельную.

AlexeyNP Andron

Если ты думаешь, что люди делающие PG делают что-то не так - возьми и помоги, если есть острое желание, время, силы и знания. Что сложного? Вот Maxim Boguk так и поступил.

Maxim Boguk
Насколько добавляется I/O при таком, ленивом вакууме?
Можно как-то лимитировать по этому параметру?
Насколько оцениваете надежность - на фронтальной базе запустить можно?
А то опять эти дампы-копи-пасты между 4-мя серваками :(



IO добавляется приблизительно столько же сколько от vacuum full было бы (+/- 2 раза от ситуации в таблице зависит).
По IO лимитировать можно... смотрите параметры:
--pages-per-round= (сколько страниц чистить за 1 цикл вызова хранимки) по умолчанию 10
--delay= (сколько в секундах ждать после каждой пачки в --pages-per-round принимает float по умолчанию - 0)
--delay-ratio - наиболее полезный для целей лимитирования нагрузки параметр... работает он следующим образом: смотрится за сколько обработалось последняя --pages-per-round= пачка страниц, после спит (время обработки предыдущей пачки)*--delay-ration времени, умолчательное значение 2 (это позволяет одному и тому же скрипту нормально работать и в пиковую нагрузку и ночью в минимуме нагрузки... так как чем медленее идет основной процесс тем больше будут паузы между вызовами... я в при эксплуатации видел до 10 раз разницы в скорости между дневной и ночной работой).

Умолчательные настройки (не быстро но не создавая большой нагрузки на сервер):
--pages-per-round=10 --delay-ratio=2
Настройки упаковать любой ценой максимально быстро:
--pages-per-round=100 --delay-ratio=0
(ставить больше 100 в --pages-per-round=100 не стоит начинаются накладные расходы и с 1000 уже в 1.5 раза медленее идет... меньше 10 тоже не стоит ставить пока нет реализации через DBD::Pg или managed psql).

Обязательно стоит посмотреть на то чтобы у вас vacuum_cost_delay был в базе так настроен чтобы вызов vacuum не вызывал перегрузки дисковой системы, так как скрипт в процессе работы делает обычный vacuum таблицы до 20 раз.

Система тестировалась на ОЧЕНЬ нагруженных базах моих клиентов (список тут: http://mboguk.moikrug.ru/) на очень разных серверах и структурах таблиц. Проблем выявлено не было (лучше не забывать делать svn up перед использованием так как выявленные сложности я оперативно исправяю).

Потери или повреждения данных не было ни разу, 1 раз возникла перегрузка дисковой системы в дневное время (после чего собственно и появилась автоподстройка нагрузки через --delay-ratio).

Комментарии по результатам использования приветствуются.

Warstoneweb_foxпо-любому информация с диска читается страницами. Если в каждой странице будет 90% мусора, то будет полная опа. Если вы сделаете VACUUM FULL. Эта опа переместиться из таблицы в индексы и будет опа, но уже с другой стороны :)В индексах она будет меньше.

Предполагая что индексы были не распухшие при VF их размер строго удвоится. Если распухшие уже то они могут даже и не вырости вообще. На самом деле кроме primary key/unique constraints распухшие индексы лечатся достаточно легко и бескровно в отличии от упаковки самой таблицы.