Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have created a table where I store serialized Java objects. They are frequently accessed and costly to create, so that is the reason. Solution works fine to me, however table size grows to 25GB inside few days! The objects are removed after few mins, so it's not case the case of amount of data really held, but simply vacuum does not do its job. After full vacuum table size drops to approx. 150 MB. In the logs I see that vacuuming process is running, but as table grows in size, it takes over 5 mins and does not help a lot.

I wonder how can I tune the vacuum to make its job better for this table?

share|improve this question
2  
Do you have any connections that are in the "<IDLE> in transaction" state? If those accessed the table, they will prevent vacuum from cleaning up. Another option might be to make vacuum more aggressive to kick in more often. – a_horse_with_no_name Nov 8 '12 at 7:54
2  
PostgreSQL version? PostgreSQL's autovacuum has improved a lot, so the version is crucial. If it isn't at least 8.3 or 8.4, upgrade. Also, what's the output of SHOW max_prepared_transactions; and SELECT count(*) FROM pg_prepared_xacts;? Also, is there any chance table-specific autovacuum settings were ever applied? – Craig Ringer Nov 8 '12 at 8:06
1  
Also, if you're on a Pg older than 8.4, what's your max_fsm_pages and are you getting any warnings about it in the logs? – Craig Ringer Nov 8 '12 at 8:28
It's Postgres 9.1 Yes, there are connections in transaction, but not all the time, so there should be plenty of time for vacuum. I never applied any table-specific autovacuum settings. SHOW max_prepared_transactions = 0 SELECT count(*) FROM pg_prepared_xacts = 0 – Michal Nov 8 '12 at 15:56

1 Answer

up vote 1 down vote

First, take a long, careful look at Aggressive Autovacuum on PostgreSQL as it may very well describe your problem, and check pg_locks to see if there is a locking issue on the table (there may well be one) and also look carefully at Josh Berkus's recommended autovacuum settings for aggressively autovacuuming.

To recover from a bloated table you can run the cluster command from psql (exclusively locks a table, but much faster than vacuum full).

Third, if this is a locking issue, you may try to use a cron job to run a vacuum or even vacuum full (or cluster) during low-utilization times.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.