Re: [PATCHES] [HACKERS] Non-transactional pg_class, try 2
On Wed, 2006-06-28 at 20:08 -0400, Tom Lane wrote: In fact, maybe we should just force an autovac cycle for any DB that appears to be approaching wraparound, rather than waiting for the shutdown-before-wraparound code to kick in. Getting into that state amounts to whacking DBAs upside the head for being stupid, which doesn't really win us any friends ... Yes, please can we have the auto autovacuum cut in rather than the wraparound message? I'd rather have them complain that we did this, than complain that we didn't. Normally, I wouldn't support automatically starting admin tasks without thr sysadmins knowledge. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Non-transactional pg_class, try 2
Tom Lane wrote: In fact, maybe we should just force an autovac cycle for any DB that appears to be approaching wraparound, rather than waiting for the shutdown-before-wraparound code to kick in. Getting into that state amounts to whacking DBAs upside the head for being stupid, which doesn't really win us any friends ... Sounds fine. How far back should we allow databases to go? If we wait too long, pg_clog won't be truncated regularly, so I think we should do it rather early than wait until it's close to wraparound. Implementation-wise, I'd propose that we add another PostmasterSignal event type whereby a backend could request the postmaster to launch an autovac process even if autovac is off. The end-of-VACUUM code that scans pg_database.datminxid would issue the signal if it finds anything seriously old. I think we could give autovac a reason for being started, which would normally be the periodic stuff, but if the postmaster got the signal from a backend, pass that info to autovac and it could use a different database selection algorithm -- say, just select the oldest database, even if it's not in danger of Xid wraparound. So this would allow early database-wide vacuums for non-connectable databases (template0), and normal per-table vacuuming for database that are in actual use. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Non-transactional pg_class, try 2
On Thu, Jun 29, 2006 at 09:39:27AM +0100, Simon Riggs wrote: On Wed, 2006-06-28 at 20:08 -0400, Tom Lane wrote: In fact, maybe we should just force an autovac cycle for any DB that appears to be approaching wraparound, rather than waiting for the shutdown-before-wraparound code to kick in. Getting into that state amounts to whacking DBAs upside the head for being stupid, which doesn't really win us any friends ... Yes, please can we have the auto autovacuum cut in rather than the wraparound message? I'd rather have them complain that we did this, than complain that we didn't. Normally, I wouldn't support automatically starting admin tasks without thr sysadmins knowledge. I think it'd be good to put a big, fat WARNING in the log if we fire up an autovac to avoid an XID wrap, since it's an indication that the vacuuming scheme that's in place probably isn't good enough. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Non-transactional pg_class, try 2
Jim C. Nasby [EMAIL PROTECTED] writes: I think it'd be good to put a big, fat WARNING in the log if we fire up an autovac to avoid an XID wrap, since it's an indication that the vacuuming scheme that's in place probably isn't good enough. No, for nonconnectable databases it'd be expected behavior (given the proposed patch). Warn away if the db is connectable, though ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Non-transactional pg_class, try 2
Alvaro Herrera [EMAIL PROTECTED] writes: I think we could give autovac a reason for being started, which would normally be the periodic stuff, but if the postmaster got the signal from a backend, pass that info to autovac and it could use a different database selection algorithm -- say, just select the oldest database, even if it's not in danger of Xid wraparound. I don't think it's worth the trouble to provide such a signaling mechanism (it'd be a bit of a PITA to make it work in both fork and EXEC_BACKEND cases). ISTM it's sufficient for autovac to look at the GUC state and notice whether it's nominally enabled or not. If not, it should only consider anti-wraparound vacuum operations. If the signal is given just when VACUUM sees a datvacuumxid value that's old enough to cause autovac to decide it had better go prevent wraparound, then this will work without any additional data transfer. We could negotiate exactly how old DBs need to be to cause this; if you want to make it less than a billion xacts so that we can keep pg_clog cut down to size, that's fine with me. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Non-transactional pg_class, try 2
Alvaro Herrera wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: What I'm after is not freezing for read-only media, nor archive, nor read-only tables. What I'm after is removing the requirement that all databases must be vacuumed wholly every 2 billion transactions. Well, if that's the only goal then I hardly think we need to have a discussion, because your alternative #1 is *obviously* the winner: 1. Remove the special case, i.e., process frozen databases in VACUUM like every other database. This is the easiest, because no extra logic is needed. Just make sure they are vacuumed in time. The only problem would be that we'd need to uselessly vacuum tables that we know are frozen, from time to time. But then, those tables are probably small, so what's the problem with that? I'm happy to do at least this for 8.2. We can still try to do the non-transactional catalog later, either in this release or the next; the code is almost there, and it'll be easier to discuss/design because we'll have taken the relminxid stuff out of the way. I attach a patch pursuant to this idea (lacking doc patches for the maintenance section.) This patch has the nasty side effect mentioned above -- people will have to set template0 as connectable and manually run vacuum on it periodically, unless they run autovacuum. A future improvement in this area would be to allow frozen tables and frozen databases, removing this requirement. But I'm inclined to apply it as is, in the spirit of incremental improvement. Objectors please speak up! -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Non-transactional pg_class, try 2
Alvaro Herrera [EMAIL PROTECTED] writes: This patch has the nasty side effect mentioned above -- people will have to set template0 as connectable and manually run vacuum on it periodically, unless they run autovacuum. That's pretty messy --- making template0 connectable is a great way to shoot yourself in the foot. What I'd propose instead is that even if autovacuum is nominally off, the system forces autovacuum when it notices that a non-connectable database is approaching wraparound. In this mode the autovac daemon would be restricted to processing non-connectable databases. (The subtext here is that autovac is the wave of the future anyway.) In fact, maybe we should just force an autovac cycle for any DB that appears to be approaching wraparound, rather than waiting for the shutdown-before-wraparound code to kick in. Getting into that state amounts to whacking DBAs upside the head for being stupid, which doesn't really win us any friends ... Implementation-wise, I'd propose that we add another PostmasterSignal event type whereby a backend could request the postmaster to launch an autovac process even if autovac is off. The end-of-VACUUM code that scans pg_database.datminxid would issue the signal if it finds anything seriously old. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
