Thread: pg_autovacuum and VACUUM FREEZE

pg_autovacuum and VACUUM FREEZE

From
Christopher Kings-Lynne
Date:
Hi,

Was just wondering if pg_autovacuum watches transaction ids and issues a 
vacuum freeze before they roll over?

If not, is it hard to do?

Chris




Re: pg_autovacuum and VACUUM FREEZE

From
Christopher Browne
Date:
In an attempt to throw the authorities off his trail, chriskl@familyhealth.com.au (Christopher Kings-Lynne)
transmitted:
> Was just wondering if pg_autovacuum watches transaction ids and issues
> a vacuum freeze before they roll over?
>
> If not, is it hard to do?

It doesn't do a VACUUM FREEZE; it just does a VACUUM.  VACUUM FREEZE
isn't forcibly necessary, although it would be an interesting idea to
do so.
/* * FIXME: should probably do something better here so that we don't * vacuum all the databases on the server at the
sametime.  We have * 500million xacts to work with so we should be able to spread the * load of full database vacuums a
bit*/if (dbi->age > 1500000000 ){    PGresult   *res = NULL;
 
    res = send_query("VACUUM", dbi);    /* FIXME: Perhaps should add a check for PQ_COMMAND_OK */    PQclear(res);
return1;}
 
-- 
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/
They have finally found the most ultimately useless thing on the web...
Found at the Victoria's Secret website:  "The online shop: Text Only Listing"


Re: pg_autovacuum and VACUUM FREEZE

From
"Matthew T. O'Connor"
Date:
On Thu, 2003-10-16 at 01:34, Christopher Kings-Lynne wrote:
> Hi,
> 
> Was just wondering if pg_autovacuum watches transaction ids and issues a 
> vacuum freeze before they roll over?

Yes pg_autovacuum monitors for xid wraparound, when it sees that you are
getting close, then it issues a database wide vacuum.  I intentionally
chose not to do a vacuum freeze.

The vacuum man page says, "FREEZE is not recommnded for routine use". 
That was enough to keep me away.  However if vacuum freeze was
considerably lighter than normal database wide vacuums then there might
be an advantage to using it.  Especially since when pg_autovaccum
decides it's time to deal with xid wraparound, it does it to all the
databases, which could a several hours of vacuum on large clusters. 

Relevant section of man page below:

FREEZE  is  a  special-purpose  option  that causes tuples to be marked
‘‘frozen’’ as soon as possible, rather  than  waiting  until  they  are
quite old. If this is done when there are no other open transactions in
the same database, then  it  is  guaranteed  that  all  tuples  in  the
database  are  ‘‘frozen’’  and  will  not  be subject to transaction ID
wraparound problems, no matter how long the database is  left  un-vacu-
umed.   FREEZE  is  not  recommended for routine use. Its only intended
usage is  in  connection  with  preparation  of  user-defined  template
databases,  or  other  databases that are completely read-only and will
not receive routine maintenance VACUUM operations.  See the Administra-
tor’s Guide for details.

Matthew




Re: pg_autovacuum and VACUUM FREEZE

From
Tom Lane
Date:
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> The vacuum man page says, "FREEZE is not recommnded for routine use". 
> That was enough to keep me away.  However if vacuum freeze was
> considerably lighter than normal database wide vacuums then there might
> be an advantage to using it.

If anything it would be slower than normal vacuum (more pages touched).
I concur with just using plain VACUUM to deal with impending wraparound.
        regards, tom lane


Re: pg_autovacuum and VACUUM FREEZE

From
Rod Taylor
Date:
> The vacuum man page says, "FREEZE is not recommnded for routine use".
> That was enough to keep me away.  However if vacuum freeze was
> considerably lighter than normal database wide vacuums then there might
> be an advantage to using it.  Especially since when pg_autovaccum
> decides it's time to deal with xid wraparound, it does it to all the
> databases, which could a several hours of vacuum on large clusters.

Each database has it's own last xid. Just because one database is about
to go over the limit doesn't mean they all are.  Why don't you treat
each database independently in this regard (then they wouldn't
necessarily all be kicked off at once).

Re: pg_autovacuum and VACUUM FREEZE

From
"Matthew T. O'Connor"
Date:
On Thu, 2003-10-16 at 10:16, Rod Taylor wrote:
> > The vacuum man page says, "FREEZE is not recommnded for routine use". 
> > That was enough to keep me away.  However if vacuum freeze was
> > considerably lighter than normal database wide vacuums then there might
> > be an advantage to using it.  Especially since when pg_autovaccum
> > decides it's time to deal with xid wraparound, it does it to all the
> > databases, which could a several hours of vacuum on large clusters. 
> 
> Each database has it's own last xid. Just because one database is about
> to go over the limit doesn't mean they all are.  Why don't you treat
> each database independently in this regard (then they wouldn't
> necessarily all be kicked off at once).

My choice of words above was poor, let me try again.

pg_autovacuum does treat each database independently, however assuming
that you never manually run vacuum (which is the eventual goal of
pg_autovacuum), then database wide vacuums will have never been run on
any table in any database, so all databases will approach xid wraparound
at the same time.

So, pg_autovacuum does deal with them separately, but doesn't make an
effort to spread out the vacuums if all / multiple databases happen to
need it at the same time.

In practice, I don't see this as a big problem right now, but it should
still be handled better by pg_autovacuum.



Re: pg_autovacuum and VACUUM FREEZE

From
Rod Taylor
Date:
> So, pg_autovacuum does deal with them separately, but doesn't make an
> effort to spread out the vacuums if all / multiple databases happen to
> need it at the same time.
>
> In practice, I don't see this as a big problem right now, but it should
> still be handled better by pg_autovacuum.

I understand now, and yes, it does make sense to try to level off spikes
in vacuum requests so that there is an even flow.


Re: pg_autovacuum and VACUUM FREEZE

From
Christopher Browne
Date:
I just ran into a new little anomaly in pg_autovacuum...

Note the interesting _negative_ numbers.  Apparently something's
rolling over.  I have no _grand_ problem with what happened, namely
"immediately checking again," as the system in question was doing
heavy offline updates.  But this is likely to be pretty adverse on
other systems where this could turn into a pathological situation of
vacuuming when the system has already been VERY busy from vacuuming
some really big tables.

[Names of objects changed to protect the not-so-innocent :-)]

[2003-10-20 06:05:48 PM] Performing: VACUUM ANALYZE "public"."my_enormous_table"
[2003-10-20 08:13:23 PM]   table name:
anonymous_database."public"."my_enormous_table"
[2003-10-20 08:13:23 PM]      relfilenode: 542631733;   relisshared: 0
[2003-10-20 08:13:23 PM]      reltuples: 5;  relpages: 128332
[2003-10-20 08:13:23 PM]      curr_analyze_count:  959974;
cur_delete_count:   959974
[2003-10-20 08:13:23 PM]      ins_at_last_analyze: 959974;
del_at_last_vacuum: 959974
[2003-10-20 08:13:23 PM]      insert_threshold:    505;
delete_threshold    10010
[2003-10-20 08:13:23 PM] 2 All DBs checked in: -934428113 usec, will
sleep for -3872 secs.
[2003-10-20 08:13:23 PM] updating the database list
[2003-10-20 08:13:23 PM] Performing: VACUUM ANALYZE "public"."my_enormous_table"
[2003-10-20 08:52:39 PM]   table name:
anonymous_database."public"."my_enormous_table"
[2003-10-20 08:52:39 PM]      relfilenode: 542631733;   relisshared: 0
[2003-10-20 08:52:39 PM]      reltuples: 5;  relpages: 164343
[2003-10-20 08:52:39 PM]      curr_analyze_count:  4097974;
cur_delete_count:   4097974
[2003-10-20 08:52:39 PM]      ins_at_last_analyze: 4097974;
del_at_last_vacuum: 4097974
[2003-10-20 08:52:39 PM]      insert_threshold:    505;
delete_threshold    10010
[2003-10-20 08:52:39 PM] 3 All DBs checked in: -1938733385 usec, will
sleep for -8893 secs.
[2003-10-20 08:52:39 PM] Performing: VACUUM ANALYZE "public"."my_enormous_table"
[2003-10-20 09:00:07 PM]   table name:
anonymous_database."public"."my_enormous_table"
[2003-10-20 09:00:07 PM]      relfilenode: 542631733;   relisshared: 0
[2003-10-20 09:00:07 PM]      reltuples: 6;  relpages: 164343
[2003-10-20 09:00:07 PM]      curr_analyze_count:  4661974;
cur_delete_count:   4661974
[2003-10-20 09:00:07 PM]      ins_at_last_analyze: 4661974;
del_at_last_vacuum: 4661974
[2003-10-20 09:00:07 PM]      insert_threshold:    506;
delete_threshold    10012
[2003-10-20 09:00:07 PM] 4 All DBs checked in: 448475756 usec, will
sleep for 3042 secs.
-- 
let name="aa454" and tld="freenet.carleton.ca" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/spreadsheets.html
"I think you ought to know I'm feeling very depressed"
-- Marvin the Paranoid Android