По мотивам http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/
Я за недельку написал и отладил в 100 где то раз более быструю реализацию той же идеи с нормальным управлением и не насилующую триггера ON UPDATE (если таковые есть на таблице).
брать здесь:
svn checkout
http://compacttable.googlecode.com/svn/trunk/ compacttable-read-only
На тестовом примере созданном следующим образом:
DROP TABLE IF EXISTS __test;
CREATE TABLE __test as select id,random() as f1,random() as
f2,random()::text as f3,now() as mtime,(random()>1)::boolean as flag
FROM generate_series(1,10000000) as t(id);
DELETE FROM __test where id%5<>0;
ALTER TABLE __test add primary key (id);
CREATE INDEX __test_f1_key ON __test(f1);
CREATE INDEX __test_f2_key ON __test(f2);
CREATE INDEX __test_f3_key ON __test(f3);
VACUUM __test;
CREATE OR REPLACE FUNCTION __set_mtime() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.mtime = clock_timestamp();
return NEW;
END;
$$;
CREATE TRIGGER set_mtime
BEFORE UPDATE ON __test
FOR EACH ROW
EXECUTE PROCEDURE __set_mtime();
SELECT sum(pg_relation_size('public.'
||indexname))::bigint/current_
setting('block_size')::bigint
FROM pg_indexes WHERE schemaname='public' AND tablename='__test';
SELECT pg_relation_size('__test')/
current_setting('block_size'):
:bigint;
По итогам сравнительного тестирования получилось у меня:
1)VACUUM FULL __test;
Table size (pages) 113574 -> 22714
Index size (pages) 26364 -> 51616 (index bloat 95%)
Time: 211873,227 ms (3.5 minutes)
(3.5 минуты таблица недоступна вообще ни для чего ну и индексы в двое распухли)
vs
2)time ./
vacuum_table.pl --table=__test
Table size (pages) 113574 -> 23594
Index size (pages) 26364 -> 32242 (index bloat: 22%)
real 12m10.300s
(в 4 раза медленее зато никакой блокировки таблицы и индексы не пухнут).
Уже было использовано в боевых условиях для ужатия таблицы распухшей из-за идиотов програмистов до 200Gb (ужалось до 20Gb) (потребовалось всего около 18 часов при этом таблица все это время оставалась доступной для работы что было критично в данном случае).
В общем получилась вполне рабочая тулза (8.4+ only) к сожалению которая если бы была под рукой последние 2 года сэкономила бы мне кучу времени и мороки.
![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
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)всякие баги невыловленные заранее (тестировал как мог)
Последние обновления:
1)Добавлен ключ --all для обработки всех таблиц в базе за 1 вызов
2)Добавлена эвристика которая оценивает насколько таблица распухла (и не мучает ее почем зря если там нечего ужимать (меньше 30% свободного места). Отключается через --force
3)Добавлена возможность проводить конкурентный reindex через --perform-reindex
4)поддержка работы через DBD::Pg (может ускорять до 2х раз процесс)
5)полностью переделана структура программы так чтобы ее можно было дальше развивать (теперь это не скрипт сваяный на коленке за 1 день а что то похожее на нормальный код)
6)добавлено много уровней verbosibility (--verobse-level=) (by default =1 наиболее удобный 2 но он говорливый)
7)исправлена куча мелких багов вылезающих на всяких нестандартных таблицах и базах
ToDo:
1)обработка всех баз оптом в пределах одного кластера (по аналогии с vacuumdb -a)
пока можно эту штуку эмулировать через:
psql -t -c 'SELECT datname from pg_database where datallowconn is true order by pg_database_size(datname)' | perl -ne 'chomp; if (/^\s(\S+)/) {my $cmd="time ./vacuum_table.pl --host=127.0.0.1 --all --force --perform-reindex --verbose-level=2 --dbname=$1 > $1.log"; print "executing cmd: $cmd\n"; system $cmd;}'
2)попробовать еще ускорить (есть пара идей) но уже сейчас raid10 из 4х дисков при --delay-ratio=0 оно на 100% может нагрузить
3)добавить побольше отладочной иформации при --verbose-level > 2
4)сделать наконец нормальную документацию на английском и описание логики работы
5)(сложно весьма) - сделать возможность при упаковке таблицы задать индекс по которому будет произведена автоматическая кластеризация (задача более чем нетривиальная на самом деле но у меня есть идеи как к ней подобраться)
(Reply) (Thread)