По мотивам 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 (изза переключения контекста), но на фоне крайне медленного доступа к физическому диску это как правило незаметно.