Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Mon, Aug 25, 2008 at 1:07 PM, Dave Cramer [EMAIL PROTECTED] wrote: On 25-Aug-08, at 10:43 AM, Alvaro Herrera wrote: Dave Cramer wrote: Well, I go the extra mile and kill any remaing autovac procs Here are the logs 2008-08-25 04:00:01 EDT [32276] LOG: autovacuum launcher shutting

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Dave Cramer wrote: Ok, here are the logs from last night 2008-08-26 04:00:02 EDT [25407] LOG: received SIGHUP, reloading configuration files 2008-08-26 04:00:02 EDT [22649] LOG: autovacuum launcher shutting down 2008-08-26 04:00:02 EDT [30438] LOG: autovacuum launcher started

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Tue, Aug 26, 2008 at 9:37 AM, Alvaro Herrera [EMAIL PROTECTED]wrote: Dave Cramer wrote: Ok, here are the logs from last night 2008-08-26 04:00:02 EDT [25407] LOG: received SIGHUP, reloading configuration files 2008-08-26 04:00:02 EDT [22649] LOG: autovacuum launcher shutting

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Tue, Aug 26, 2008 at 9:59 AM, Dave Cramer [EMAIL PROTECTED] wrote: On Tue, Aug 26, 2008 at 9:37 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Dave Cramer wrote: Ok, here are the logs from last night 2008-08-26 04:00:02 EDT [25407] LOG: received SIGHUP, reloading configuration

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Dave Cramer wrote: On Tue, Aug 26, 2008 at 9:59 AM, Dave Cramer [EMAIL PROTECTED] wrote: On Tue, Aug 26, 2008 at 9:37 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Certainly not, and that's not what I see here either. I assume process 25407 is (was) the postmaster, yes? If you show

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Tue, Aug 26, 2008 at 10:56 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Dave Cramer wrote: On Tue, Aug 26, 2008 at 9:59 AM, Dave Cramer [EMAIL PROTECTED] wrote: On Tue, Aug 26, 2008 at 9:37 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Certainly not, and that's not what I see

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Dave Cramer wrote: On Tue, Aug 26, 2008 at 10:56 AM, Alvaro Herrera [EMAIL PROTECTED] The only possible explanation for this behavior is that somebody is signalling the postmaster due to Xid wraparound issues. This is keyed on some GUC vars -- Perhaps you have autovacuum_freeze_max_age

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Tue, Aug 26, 2008 at 11:41 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Dave Cramer wrote: On Tue, Aug 26, 2008 at 10:56 AM, Alvaro Herrera [EMAIL PROTECTED] The only possible explanation for this behavior is that somebody is signalling the postmaster due to Xid wraparound issues.

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Tue, Aug 26, 2008 at 11:51 AM, Dave Cramer [EMAIL PROTECTED] wrote: On Tue, Aug 26, 2008 at 11:41 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Dave Cramer wrote: On Tue, Aug 26, 2008 at 10:56 AM, Alvaro Herrera [EMAIL PROTECTED] The only possible explanation for this behavior is

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Dave Cramer wrote: On Tue, Aug 26, 2008 at 11:41 AM, Alvaro Herrera [EMAIL PROTECTED] What's the max age(pg_database.datfrozenxid)? select datfrozenxid from pg_database ; datfrozenxid -- 201850617 101850961 86039359 21522712 Well, the first one is over

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Dave Cramer wrote: AutoVacuumShmem-av_signal[AutoVacForkFailed] = false; pg_usleep(10L); /* 100ms */ SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_WORKER); continue; Do these signals get cleaned up on a reload

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Tue, Aug 26, 2008 at 12:10 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Dave Cramer wrote: AutoVacuumShmem-av_signal[AutoVacForkFailed] = false; pg_usleep(10L); /* 100ms */ SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_WORKER);

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Dave Cramer wrote: Ok, back to why turns out template0 is the culprit, why is autovac not vacuuming this ? Hmm ... template0 is not supposed to need vacuuming, because it is frozen ... is it marked with datallowconn=false? -- Alvaro Herrera

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Tue, Aug 26, 2008 at 12:21 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Dave Cramer wrote: Ok, back to why turns out template0 is the culprit, why is autovac not vacuuming this ? Hmm ... template0 is not supposed to need vacuuming, because it is frozen ... is it marked with

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes: Dave Cramer wrote: turns out template0 is the culprit, why is autovac not vacuuming this ? Hmm ... template0 is not supposed to need vacuuming, because it is frozen ... is it marked with datallowconn=false? 8.3's autovac doesn't care about that, does

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Dave Cramer wrote: Yes select * from pg_database where datname='template0'; datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig | datacl

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Tue, Aug 26, 2008 at 12:50 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Dave Cramer wrote: Yes select * from pg_database where datname='template0'; datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace |

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes: If it doesn't ignore them, then it should be properly vacuuming template0 as any other database. We've changed autovac's behavior on this area back and forth so I may be misremembering what's our rationale du jour. AFAICS, the only way in which

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Joshua Drake
On Tue, 26 Aug 2008 12:58:59 -0400 Dave Cramer [EMAIL PROTECTED] wrote: Well, I'm willing to help debug this, however this is a busy production database and I need to be able to turn it off for a few hours a day. Would changing autovacuum_freeze_max_age be a solution ? Populate the table

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Dave Cramer wrote: Well, I'm willing to help debug this, however this is a busy production database and I need to be able to turn it off for a few hours a day. Would changing autovacuum_freeze_max_age be a solution ? Yes. -- Alvaro Herrera

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: If it doesn't ignore them, then it should be properly vacuuming template0 as any other database. We've changed autovac's behavior on this area back and forth so I may be misremembering what's our rationale du jour. AFAICS, the

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes: Dave Cramer wrote: Well, I'm willing to help debug this, however this is a busy production database and I need to be able to turn it off for a few hours a day. Would changing autovacuum_freeze_max_age be a solution ? Yes. Could we first see a cycle

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Tue, Aug 26, 2008 at 1:33 PM, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Dave Cramer wrote: Well, I'm willing to help debug this, however this is a busy production database and I need to be able to turn it off for a few hours a day. Would changing

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Dave Cramer wrote: On Tue, Aug 26, 2008 at 1:33 PM, Tom Lane [EMAIL PROTECTED] wrote: Could we first see a cycle of autovac log output with log_autovacuum_min_duration = 0? Otherwise we're not going to get closer to understanding why it's not cleaning up template0 for you. I actually

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Dave Cramer
On Tue, Aug 26, 2008 at 1:45 PM, Alvaro Herrera [EMAIL PROTECTED]wrote: Dave Cramer wrote: On Tue, Aug 26, 2008 at 1:33 PM, Tom Lane [EMAIL PROTECTED] wrote: Could we first see a cycle of autovac log output with log_autovacuum_min_duration = 0? Otherwise we're not going to get closer

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Dave Cramer wrote: On Tue, Aug 26, 2008 at 1:45 PM, Alvaro Herrera [EMAIL PROTECTED]wrote: My guess is that autovacuum is skipping the database for some reason, so there's no log entry at all. Seems like a viable explanation, but doesn't advance us any further ? Nope, it doesn't -- we

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-26 Thread Alvaro Herrera
Alvaro Herrera wrote: Nope, AFAICS it's harmless; what it means is that on those databases, all tuples will be frozen immediately. I'll try to reproduce the problem here. No luck :-( It works as expected for me. -- Alvaro Herrera

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-25 Thread Dave Cramer
On 24-Aug-08, at 10:12 PM, Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: I'd like to stop autovac by changing the conf file then sending the server a HUP Uh ... why should that stop an autovac already in progress? I'd only expect it to affect future launches. Well, I go the

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-25 Thread Dave Cramer
On 24-Aug-08, at 10:17 PM, Alvaro Herrera wrote: Hi Dave, Dave Cramer wrote: I'd like to stop autovac by changing the conf file then sending the server a HUP This appears to work, the logs show autovac terminated by administrative command. Then a few minutes later I see a vacuum process

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-25 Thread Alvaro Herrera
Dave Cramer wrote: Since a number of people actually do this it would seem like a better way to temporarily suspend autovac should be on the todo ? No -- what you are doing is documented to work. However, it only stops the autovac launcher, not a currently-running worker. If this isn't

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-25 Thread Alvaro Herrera
Dave Cramer wrote: Well, I go the extra mile and kill any remaing autovac procs Here are the logs 2008-08-25 04:00:01 EDT [32276] LOG: autovacuum launcher shutting down 2008-08-25 04:00:01 EDT [20526] LOG: autovacuum launcher started What did you SIGHUP, the launcher or postmaster?

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-25 Thread Dave Cramer
On 25-Aug-08, at 10:43 AM, Alvaro Herrera wrote: Dave Cramer wrote: Well, I go the extra mile and kill any remaing autovac procs Here are the logs 2008-08-25 04:00:01 EDT [32276] LOG: autovacuum launcher shutting down 2008-08-25 04:00:01 EDT [20526] LOG: autovacuum launcher started

[HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-24 Thread Dave Cramer
I'd like to stop autovac by changing the conf file then sending the server a HUP This appears to work, the logs show autovac terminated by administrative command. Then a few minutes later I see a vacuum process spawned. Is it possible that there are timers that aren't being properly

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-24 Thread Tom Lane
Dave Cramer [EMAIL PROTECTED] writes: I'd like to stop autovac by changing the conf file then sending the server a HUP Uh ... why should that stop an autovac already in progress? I'd only expect it to affect future launches. regards, tom lane -- Sent via

Re: [HACKERS] can't stop autovacuum by HUP'ing the server

2008-08-24 Thread Alvaro Herrera
Hi Dave, Dave Cramer wrote: I'd like to stop autovac by changing the conf file then sending the server a HUP This appears to work, the logs show autovac terminated by administrative command. Then a few minutes later I see a vacuum process spawned. Is it possible that there are timers