Thursday, August 10, 2006

Postgresql: don't rely on autovacuum

I have a pg database that just suffered through a multiple-hour vacuum of one of its tables. It was pretty painful. Autovacuum is configured to run every 12 hours, but for whatever reason it didn't see fit to vacuum this very busy table for substantially longer than that. (Not sure exactly how long. Over a week is my guess.)

The problem is that one size does not fit all. On smaller tables, the default autovacuum_vacuum_scale_factor of 0.4 is just fine. On this table waiting that long is unacceptable.

9 comments:

Jarno said...

We've had, and still have, quite annoying problems with vacuuming in postgres 7.3. We have a database roughly size of 5 gigabytes. It used to grow several hundred megabytes a day, nowadays a bit less, so we (full-)vacuum it every night. Vacuuming takes around half an hour during which practically nothing goes in or out the database.

Once in few months we've had major catastrophes when our vacuuming problem is combined with some other related problems. Vacuuming might have then taken hours and hours and have caused nasty side-effects.

We've kinda been hoping that Postgres 8.x will fix this annoyance, but apparently vacuuming is still a problem. But I gather that you can do _some_ database operations while vacuuming in Postgres 8.x?

The last time I investigated related issues, there wasn't much useful information available on the web. I've sometimes wondered if we're the only ones having serious problems with vacuuming. ;-)

Jonathan Ellis said...

"I gather that you can do _some_ database operations while vacuuming in Postgres 8.x?"

Yes, you only need an exclusive lock for a full vacuum, so avoid those unless absolutely necessary. But that's been the case since they split vacuum into "normal" and "full." (7.2?)

I do seem to remember seeing vacuum stuff in the 8.x changelogs, but I don't remember details. 7.3 is hella ancient at this point... There's lots of good reasons to upgrade b/s vacuum.

Jarno said...

"Yes, you only need an exclusive lock for a full vacuum, so avoid those unless absolutely necessary. But that's been the case since they split vacuum into "normal" and "full." (7.2?)"

Yeah, I know. It's just that soft vacuum didn't shrink the database size _at all_. Daily full vacuum has kept the size in control.

And I know it's about time we switched to 8.x series, but takes a lot of effort in our application. (For example, we didn't manage to just simply export/import the database from 7.3 to 8.0.)

The current situation is quite OK insofar as our Postgres doesn't go all wild and crazy.

Jonathan Ellis said...

"It's just that soft vacuum didn't shrink the database size _at all_."

No, it just marks the space as ready for re-use. So you should see it hold steady at "max space ever used by this table" after a couple days.

Jarno said...

"No, it just marks the space as ready for re-use. So you should see it hold steady at "max space ever used by this table" after a couple days."

That's what we were hoping, but it didn't happen in a week. It just kept growing at pretty much a steady rate.

Adding insult to injury, we had a much too small disk for the Postgres data and weren't brave enough to stick with soft vacuuming for long enough.

Maybe I need to re-visit the idea of switching to soft vacuum then. The database size doesn't even grow as fast as it used to.

Jonathan Ellis said...

I remember something like this with, I think, 7.2. I'd suggest upgrading, I haven't seen this in a long time.

Andrew said...

I know this post is a year old, so you've probably already discovered this, but you can fine-tune autovac parameters for particular tables by putting entries in the pg_autovacuum table. For example

INSERT INTO pg_autovacuum (oid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit) VALUES (
(SELECT oid FROM pg_class WHERE relname = 'bigtable'), 't'::boolean, 1000, 0.0, 0.0, 0.01);
Which means vacuum bigtable after 1000 rows have been obsoleted, and analyze it after 1% of rows have changed.

For details, please Read The Fine Manual. Actually, I'm continually surprised that people have problems with this since it's pretty well documented. Perhaps we should err in the direction of being more aggressive with the avd settings by default.

jarno, when you did the pg_dump from 7.3 to load into 8.0 did you follow the explicitly documented requirement to use the 8.0 pg_dump binary?

Jonathan Ellis said...

Yeah, I did discover pg_autovacuum. The UI could use some work there. :)

Thanks for the comment.

Buy Viagra said...

Marc Fournier at Hub.Org Networking Services provided the first non-university development server for the open-source development effort. Along with Bruce Momjian and Vadim B.