Обсуждение: Missing the point of autovacuum

Greetings,

I've been running Postgresql for many years now and have been more than
happy with its performance and stability.  One of those things I've never
quite understood was vacuuming.  So I've been running 8.1.4 for a while
and enabled 'autovacuum' when I first insalled 8.1.4 ... So in my mind, my
database should stay 'clean'...

As the months have gone by, I notice many of my tables having *lots* of
unused item pointers.  For example,

There were 31046438 unused item pointers.
Total free space (including removable row versions) is 4894537260 bytes.
580240 pages are or will become empty, including 7 at the end of the
table.
623736 pages containing 4893544876 free bytes are potential move
destinations.

Perhaps I shouldn't be concerned with this?  In all, I've got around 400
GB of data on postgresql, but am not sure how much of it is old data.
Many of my tables have 100,000s of updates per day.

Do I need to be running old fashioned 'vacuumdb' routinely as well?  I
guess I just don't understand why autovacuum is not automatically doing
this for me and I have tables with so many unused item pointers.

thanks!
  daryl

[Daryl Herzmann - Sat at 12:59:03PM -0600]
> As the months have gone by, I notice many of my tables having *lots* of
> unused item pointers.  For example,

Probably not the issue here, but we had some similar issue where we had
many long-running transactions - i.e. some careless colleague entering
"begin" into his psql shell and leaving it running for some days without
entering "commit" or "rollback", plus some instances where the
applications started a transaction without closing it.

> Perhaps I shouldn't be concerned with this?  In all, I've got around 400
> GB of data on postgresql, but am not sure how much of it is old data.

I didn't count the zeroes, but autovacuum does have rules saying it will
not touch the table until some percentages of it needs to be vacuumed
off.  This is of course configurable.

> Do I need to be running old fashioned 'vacuumdb' routinely as well?  I
> guess I just don't understand why autovacuum is not automatically doing
> this for me and I have tables with so many unused item pointers.

If you have some period of the day with less activity than else, it is a
good idea running an old-fashionated vacuum as well.  The regular vacuum
process will benefit from any work done by the autovacuum.

Daryl Herzmann <akrherz@iastate.edu> writes:
> As the months have gone by, I notice many of my tables having *lots* of
> unused item pointers.  For example,

> There were 31046438 unused item pointers.
> Total free space (including removable row versions) is 4894537260 bytes.
> 580240 pages are or will become empty, including 7 at the end of the
> table.
> 623736 pages containing 4893544876 free bytes are potential move
> destinations.

This definitely looks like autovac is not getting the job done for you.
The default autovac settings in 8.1 are very un-aggressive and many
people find that they need to change the settings to make autovac vacuum
more often.  Combining autovac with the traditional approach of a cron
job isn't a bad idea, either, if you have known slow times of day ---
autovac doesn't currently have any concept of a maintenance window,
so if you'd rather your vacuuming mostly happened at 2AM, you still need
a cron job for that.

            regards, tom lane