Re: Autovacuum, too often?

Hi chaps,

Our legacy apps have some permanent tables that they use for tempory data and constantly clear out, I've kicked the developers and I intend to eradicate them eventually (the tables, not the developers).

These tables are constantly being autovacuumed, approximately once a minute, it's not causing any problem and seems to be keeping them vacuumed. But I'm constantly re-assessing our autovacuum settings to make sure they're adequate, and no matter how much I read up on autovacuum I still feel like I'm missing something.

I just wondered what peoples opinions were on handling this sort of vacuuming? Is that too often?

The general autovaccum settings set more for our central tables are threshold 500, scale_factor 0.2. I guess I could set specific settings for the tables in pg_autovacuum, or I could exclude them in there and run a vacuum from cron once a day or something.

Here's a typical log message:

2008-09-19 11:40:10 BST [12917]: [1-1]: [user=]: [host=]: [db=]:: LOG: automatic vacuum of table "TEMP.reports.online": index scans: 1
pages: 21 removed, 26 remain
tuples: 2356 removed, 171 remain
system usage: CPU 0.00s/0.00u sec elapsed 0.08 sec

Any comments would be appreciated.

Hello Glyn,

Our legacy apps have some permanent tables that they use for tempory data
> and constantly clear out, I've kicked the developers and I intend to
> eradicate them eventually (the tables, not the developers).
>

and what is the problem with this usage? That is a perfectly valid thing to
do; PostgreSQL can handle that for centuries; no need to kick the developers
:)

> These tables are constantly being autovacuumed, approximately once a
> minute, it's not causing any problem and seems to be keeping them vacuumed.
>
>

That is the right thing to do.

pages: 21 removed, 26 remain
> tuples: 2356 removed, 171 remain
> system usage: CPU 0.00s/0.00u sec elapsed 0.08 sec
>
> As you described, that temp-tables get filled and cleared regularly ...
that is "insert <a lot of stuff>" "delete <the same stuff again>"; so there
are lots of "unused" i.e. deleted tuples, which get recycled by your
vacuuming. And that with nearly no CPU usage.

Sounds fine to me :)

Best wishes,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
EuroPython 2009 will take place in Birmingham - Stay tuned!

> From: Harald Armin Massa <haraldarminmassa(at)gmail(dot)com>
> Hello Glyn,
>
> Our legacy apps have some permanent tables that they use
> for tempory data
> > and constantly clear out, I've kicked the
> developers and I intend to
> > eradicate them eventually (the tables, not the
> developers).
> >
>
> and what is the problem with this usage? That is a
> perfectly valid thing to
> do; PostgreSQL can handle that for centuries; no need to
> kick the developers
> :)
>

In some cases yes, but most of the time it's because they can't be bothered to sort a list of 100 items in their application...

Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
>
> > From: Harald Armin Massa <haraldarminmassa(at)gmail(dot)com>
> > Hello Glyn,
> >
> > Our legacy apps have some permanent tables that they use
> > for tempory data
> > > and constantly clear out, I've kicked the
> > developers and I intend to
> > > eradicate them eventually (the tables, not the
> > developers).
> > >
> >
> > and what is the problem with this usage? That is a
> > perfectly valid thing to
> > do; PostgreSQL can handle that for centuries; no need to
> > kick the developers
> > :)
> >
>
> In some cases yes, but most of the time it's because they can't be bothered to sort a list of 100 items in their application...

*shrug* Our experience has been that PostgreSQL is much better at sorting
than anything we could write with our high-pressure deadlines. Additionally,
information sometimes needs to be truncated (with LIMIT) after it's
sorted, so having PG do all the work results in less network bandwidth
and less memory usage by the application.

Maybe that's not _always_ the right answer, but it seems to be a good
answer 99% of the time. Sounds like your developers are using the
database for what it was intended for, instead of just doing single
row selects like a lot of amateurs I've come across.

--
Bill Moran
Collaborative Fusion Inc.

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023

> From: Bill Moran <wmoran(at)collaborativefusion(dot)com>

>
> Maybe that's not _always_ the right answer, but it
> seems to be a good
> answer 99% of the time. Sounds like your developers are
> using the
> database for what it was intended for, instead of just
> doing single
> row selects like a lot of amateurs I've come across.
>

In some places I agree it's totally valid, but in a lot of cases here it's just unnecessary. They have a set of really flexible pre written routines to sort datasets in the application, and I'm talking really stupid use here - like having a small list of items in an array where an array sort could be done, but instead writing it all back and reading it again. constantly.