Thread: pg_autovacuum and VACUUM FREEZE
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
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"
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
"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
> 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).
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.
> 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.
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