Hi there, First: I must say thanks to authors of this two posts: http://blog.endpoint.com/2010/09/reducing-bloat-without-locking.html and http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/ These two posts was provided me exellent ideas and starting point to create somewhat fast and reliable tool. Second: sorry for long post. I don't have my own blog to post such things. Unfortunatelly, exessive table bloat still can happen in real projects and task of compacting PostgreSQL table without long downtime is very common. So I wrote the tool which can perform incremental vacuum and compact table without completely locking target table. This message has 2 files attached: finished storable procedure and compact table wizard. They must be put in the same directory. Then run vacuum_table.pl --help to see possible options. Usage sample: ./vacuum_table.pl --dbname=billing --table=changes Storable procedure itself can be used stand-alone, but vacuum_table.pl is an easy to work with wizard to perform table compation. Before you choose to try it in production databases, PLEASE read source code and make sure you UNDERSTAND what is my code doing. Good features: 1) plays nice with triggers and rules on table (prevents on update trigger firing with set local session_replication_role to replica), therefore it can be used with active slony/londiste replication (on both master and slave servers). 2) has good performance (on my tests only 3-5 times slower than common VACUUM FULL) 3) can be restarted anytime 4) doesn't produce exessive index bloat (not like as VACUUM FULL) 5) is easy to use Known limitations or problems: 1) Requires PostgreSQL 8.4+ 2) Requires pl/pgsql installed 3) Requires superuser access to DB (such tasks must be done only by DBA anyway) 4) Will not work in presence of 'always' or 'replica' ON UPDATE triggers on target table (I have never seen one used before) 5) Can't reduce bloat of TOAST table (no way to access toast table data outside) 6) Is still producing some index bloat but not much 7) Theoretically can produce deadlocks on heavly updated tables (but I never seen this problem during two week testing in production) 8) Can produce big index bloat in presence of very long running transactions (that is bad situation anyway) 9) Ignores table fillfactor (will try compact table to 100%) 10) Truncating empty pages from end of the table still requires short exclusive lock (which can be hard to acquire on loaded systems) 11) Might have some hidden or unknown for me bug (sorry if you get hit by this one... I did my best during testing). The tool has been tested on some medium and large projects during last two weeks. No serious problems has been found (but see "Known limitations and problems"). Now about performance test: Here is SQL commands to create bloated test table used in my perfromance test: ======================================== 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; =================================================== Test results: 1)VACUUM FULL __test; Table size (pages) 113574 -> 22714 Index size (pages) 26364 -> 51616 (index bloat 95%) Time: 211873,227 ms (3.5 minutes) vs (all tests performed through pgbouncer in session pooling mode to save some connect time) 2)time ./vacuum_table.pl --table=__test --pages-per-round=10 --pages-per-vacuum=1000 (some system load observed because of lot of shell+psql calls... probably good idea move to DBD::Pg persistant connect) Table size (pages) 113574 -> 23576 Index size (pages) 26364 -> 30750 (index bloat: 16%) real 21m0.221s That is somewhat lighter version which don't put too much stress on IO system (6 times slower then VF but no long locks) 3)time ./vacuum_table.pl --table=__test --pages-per-round=100 --pages-per-vacuum=10000 Table size (pages) 113574 -> 23594 Index size (pages) 26364 -> 32242 (index bloat: 22%) real 12m10.300s This run was performed with default options. Only 3.5 time slower then VF. Thank you for your time. I hope my tool can help someone. And of course I would be happy to get some feedback and especially bug reports. -- Maxim Boguk Senior Postgresql DBA. Skype: maxim.boguk Jabber: maxim.boguk@xxxxxxxxx LinkedIn profile: http://nz.linkedin.com/in/maximboguk ÐÐÐÐÑÑÐ: http://mboguk.moikrug.ru/ ÐÐÐÐ ÑÐÐÐÐÑ ÐÐÐÐÑ, ÐÐ ÐÐ ÐÑÐ Ð ÐÐÑÐÐ ÐÐÐÐÐ - ÑÐÐÐÐÐ, ÐÐ Ð ÑÐÐÐ ÐÐÐÐÐÐ ÐÐ ÐÑÐ.
Attachment:
clear_table_tail.sql
Description: Binary data
Attachment:
vacuum_table.pl
Description: Binary data
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
