Thread: autovacuum "connections" are hidden
Trying to drop a database, this morning I ran into the not so unusual error: dropdb: database removal failed: ERROR: database "test_seg1" is being accessed by other users however, when I did "select * from pg_stat_activity" on the pg server, it showed no connection to that db. Then I looked at the processes: tmp0% ps ax | grep test_seg1 10317 ? D 0:36 postgres: autovacuum process test_seg1 I imagine this doesn't show up because there is no connection, per se, the autovacuum process is a bon-a-fide backend process in its own right. It's just a bit confusing in this circumstance. I guess this is more of a heads up than a question. Another circumstance where this has bitten me is in doing a slony SUBSCRIBE_SET. At least in 1.1.5 the autovac daemon can deadlock with slony and cause the subscriber operation to fail. When this happened to me it was somewhat baffling at first because I had altered pg_hba.conf to forbid all non-superuser connections and there were no other connections visible at the time (in pg_stat_activity). Turning off autovacuum during the subscribe fixed this, but not without a little head-scratching (and maybe some luck). No way comes to my mind to improve the visiblity of autovac "connections" but I thought I would throw this out here in case anyone had any thoughts on the matter. -Casey
Casey Duncan <casey@pandora.com> writes:
> however, when I did "select * from pg_stat_activity" on the pg
> server, it showed no connection to that db. Then I looked at the
> processes:
> tmp0% ps ax | grep test_seg1
> 10317 ? D 0:36 postgres: autovacuum process test_seg1
Hmm, autovacuum won't show up in pg_stat_activity because it never calls
pgstat_report_activity(). Seems like maybe it should, though.
regards, tom lane
On May 17, 2006, at 12:34 PM, Tom Lane wrote: > Casey Duncan <casey@pandora.com> writes: >> however, when I did "select * from pg_stat_activity" on the pg >> server, it showed no connection to that db. Then I looked at the >> processes: >> tmp0% ps ax | grep test_seg1 >> 10317 ? D 0:36 postgres: autovacuum process test_seg1 > > Hmm, autovacuum won't show up in pg_stat_activity because it never > calls > pgstat_report_activity(). Seems like maybe it should, though. Is this worth filing a bug about for posterity? -Casey
Tom Lane wrote: > Casey Duncan <casey@pandora.com> writes: > > however, when I did "select * from pg_stat_activity" on the pg > > server, it showed no connection to that db. Then I looked at the > > processes: > > tmp0% ps ax | grep test_seg1 > > 10317 ? D 0:36 postgres: autovacuum process test_seg1 > > Hmm, autovacuum won't show up in pg_stat_activity because it never calls > pgstat_report_activity(). Seems like maybe it should, though. If we do that, would it help people who are trying to determine if pg_autovacuum is running? Would the connection still appear while pg_autovacuum is sleeping? -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Tom Lane wrote: >> Casey Duncan <casey@pandora.com> writes: >>> however, when I did "select * from pg_stat_activity" on the pg >>> server, it showed no connection to that db. Then I looked at the >>> processes: tmp0% ps ax | grep test_seg1 >>> 10317 ? D 0:36 postgres: autovacuum process test_seg1 >> >> Hmm, autovacuum won't show up in pg_stat_activity because it never >> calls pgstat_report_activity(). Seems like maybe it should, though. > > If we do that, would it help people who are trying to determine if > pg_autovacuum is running? Would the connection still appear while > pg_autovacuum is sleeping? I believe while autovacuum is sleeping, the process is gone. I.e. it gets spawned anew for each pass looking for work. -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.231.6597 Email: Larry.Rosenman@pervasive.com Web: www.pervasive.com
Larry Rosenman wrote: > Bruce Momjian wrote: > > Tom Lane wrote: > >> Casey Duncan <casey@pandora.com> writes: > >>> however, when I did "select * from pg_stat_activity" on the pg > >>> server, it showed no connection to that db. Then I looked at the > >>> processes: tmp0% ps ax | grep test_seg1 > >>> 10317 ? D 0:36 postgres: autovacuum process test_seg1 > >> > >> Hmm, autovacuum won't show up in pg_stat_activity because it never > >> calls pgstat_report_activity(). Seems like maybe it should, though. Yep. I'll fix it. Should I backpatch to 8.1? > > If we do that, would it help people who are trying to determine if > > pg_autovacuum is running? Would the connection still appear while > > pg_autovacuum is sleeping? > > I believe while autovacuum is sleeping, the process is gone. Yes, which means that really autovacuum does not sleep (it just exits when it's done). Postmaster is the one who sleeps between autovac iterations. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Larry Rosenman wrote: > > Bruce Momjian wrote: > > > Tom Lane wrote: > > >> Casey Duncan <casey@pandora.com> writes: > > >>> however, when I did "select * from pg_stat_activity" on the pg > > >>> server, it showed no connection to that db. Then I looked at the > > >>> processes: tmp0% ps ax | grep test_seg1 > > >>> 10317 ? D 0:36 postgres: autovacuum process test_seg1 > > >> > > >> Hmm, autovacuum won't show up in pg_stat_activity because it never > > >> calls pgstat_report_activity(). Seems like maybe it should, though. > > Yep. I'll fix it. Should I backpatch to 8.1? > > > > If we do that, would it help people who are trying to determine if > > > pg_autovacuum is running? Would the connection still appear while > > > pg_autovacuum is sleeping? > > > > I believe while autovacuum is sleeping, the process is gone. > > Yes, which means that really autovacuum does not sleep (it just exits > when it's done). Postmaster is the one who sleeps between autovac > iterations. Good, anyway, it would allow people to see _when_ autovacuum is processing things. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>>> Hmm, autovacuum won't show up in pg_stat_activity because it never
>>> calls pgstat_report_activity(). Seems like maybe it should, though.
> Yep. I'll fix it. Should I backpatch to 8.1?
Yeah, probably, because this is really a regression from 8.0: the old
contrib autovacuum's activities would show in pg_stat_activity, since
it was just issuing the VACUUM/ANALYZE commands normally.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > Tom Lane wrote: > >>> Hmm, autovacuum won't show up in pg_stat_activity because it never > >>> calls pgstat_report_activity(). Seems like maybe it should, though. > > > Yep. I'll fix it. Should I backpatch to 8.1? > > Yeah, probably, because this is really a regression from 8.0: the old > contrib autovacuum's activities would show in pg_stat_activity, since > it was just issuing the VACUUM/ANALYZE commands normally. Hmm. I had assumed this would be a quickie, but it turned out not to be the case. The problem is that while we can make autovac report quite easily, other backends will ignore the backend entry because it hasn't set a database ID. (In pg_stat_get_backend_dbid, entries with NULL dbid return NULL, so the pg_stat_activity view doesn't show them because it uses a inner join. Also, pgstat_report_autovac sets the database entry, but it doesn't do anything about the backend entry.) The problem is that pgstat_bestart (called in InitPostgres, which autovac calls) deliberately ignores autovacuum, due to not having a client address. We could create a fake client address (which doesn't seem easy to do), or we could change pg_stat_activity to use an OUTER JOIN. Not sure what other options are there. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> The problem is that pgstat_bestart (called in InitPostgres, which
> autovac calls) deliberately ignores autovacuum, due to not having a
> client address. We could create a fake client address (which doesn't
> seem easy to do), or we could change pg_stat_activity to use an OUTER
> JOIN. Not sure what other options are there.
Seems like the client address should read as NULL. Not sure if that's
hard or not. Changing the view definition is no fun (at least for an
8.1 back-patch) because it'd require initdb.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > The problem is that pgstat_bestart (called in InitPostgres, which > > autovac calls) deliberately ignores autovacuum, due to not having a > > client address. We could create a fake client address (which doesn't > > seem easy to do), or we could change pg_stat_activity to use an OUTER > > JOIN. Not sure what other options are there. > > Seems like the client address should read as NULL. Not sure if that's > hard or not. Changing the view definition is no fun (at least for an > 8.1 back-patch) because it'd require initdb. This seems to work for me. I'd appreciate input, as I'm not sure how would other archs (or even my own) cope with the zeroed client address trick (note the memcmp ...) This is the 8.1 version of the patch; I haven't looked at HEAD yet, but I think it's slightly different. (The actual activity reported is a bit bogus ... I'd appreciate suggestions for better wording. Do I waste cycles in obtaining the relname? My answer is yes. What if there are multiple rels (a case currently not exercised)?. Should it explicitly say that it's autovacuum? My answer is no.) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Attachment
Alvaro Herrera <alvherre@commandprompt.com> writes:
> This seems to work for me. I'd appreciate input, as I'm not sure how
> would other archs (or even my own) cope with the zeroed client address
> trick (note the memcmp ...)
AFAICS that should work; I can't imagine all-zeroes being a valid client
address. I'd suggest commenting it as "process has no client" rather
than "don't know".
> (The actual activity reported is a bit bogus ... I'd appreciate
> suggestions for better wording. Do I waste cycles in obtaining the
> relname? My answer is yes. What if there are multiple rels (a case
> currently not exercised)?. Should it explicitly say that it's
> autovacuum? My answer is no.)
What I was expecting was to see one of
VACUUM
ANALYZE
VACUUM ANALYZE
VACUUM foo
ANALYZE foo
VACUUM ANALYZE foo
ie exactly the command being executed if you were to type the manual
equivalent.
Since the multiple-rels case isn't used, there seems no need to debate
how it ought to report...
BTW, patches are probably off-topic for pgsql-general.
regards, tom lane
Moving to -hackers Does this still obey stats_command_string? I think it'd be very handy to either have autovac always report what it's doing (ignoring stats_command_string), or to provide it with it's own option for it. I doubt this should pose a performance issue, since unless you have a whole lot of small tables autovac shouldn't be issuing commands very frequently. On Thu, May 18, 2006 at 07:28:50PM -0400, Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > This seems to work for me. I'd appreciate input, as I'm not sure how > > would other archs (or even my own) cope with the zeroed client address > > trick (note the memcmp ...) > > AFAICS that should work; I can't imagine all-zeroes being a valid client > address. I'd suggest commenting it as "process has no client" rather > than "don't know". > > > (The actual activity reported is a bit bogus ... I'd appreciate > > suggestions for better wording. Do I waste cycles in obtaining the > > relname? My answer is yes. What if there are multiple rels (a case > > currently not exercised)?. Should it explicitly say that it's > > autovacuum? My answer is no.) > > What I was expecting was to see one of > > VACUUM > ANALYZE > VACUUM ANALYZE > VACUUM foo > ANALYZE foo > VACUUM ANALYZE foo > > ie exactly the command being executed if you were to type the manual > equivalent. > > Since the multiple-rels case isn't used, there seems no need to debate > how it ought to report... > > BTW, patches are probably off-topic for pgsql-general. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461