Re: autovacuum "connections" are hidden

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.

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

Jim C. Nasby wrote:

Moving to -hackers

You forgot to actually do it apparently?

Sorry about posting the patch to -general, BTW. Anyway it was committed
to the 8.1 branch, so it is included in the new release (8.1.4?)

Does this still obey stats_command_string?

Yes.

I considered having the ps display show the info, but it's not as useful
because you can only get the info if you have access to the process list
(i.e. not a remote client).

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.

Hmm. While I understand your point, I wouldn't want to clutter
postgresql.conf with an option just for this though; seems to be very
unimportant.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On May 22, 2006, at 2:37 PM, Alvaro Herrera wrote:

Jim C. Nasby wrote:

Moving to -hackers

You forgot to actually do it apparently?

Sorry about posting the patch to -general, BTW. Anyway it was
committed
to the 8.1 branch, so it is included in the new release (8.1.4?)

Does this still obey stats_command_string?

Yes.

I considered having the ps display show the info, but it's not as
useful
because you can only get the info if you have access to the process
list
(i.e. not a remote client).

It would be useful for dba's watching the box directly, via ps or top
(which I find myself doing fairly often). This has always been a
great feature of postgres IMO. I'd put in a vote for having that for
that for the autovac backend as well FWIW.

In any event thanks a lot for the current fix, as is it's a big
improvement! 8^)

-Casey

On Mon, May 22, 2006 at 02:45:30PM -0700, Casey Duncan wrote:

On May 22, 2006, at 2:37 PM, Alvaro Herrera wrote:

Jim C. Nasby wrote:

Moving to -hackers

You forgot to actually do it apparently?

Yup, I are SMRT.

Sorry about posting the patch to -general, BTW. Anyway it was
committed
to the 8.1 branch, so it is included in the new release (8.1.4?)

Does this still obey stats_command_string?

Yes.

I considered having the ps display show the info, but it's not as
useful
because you can only get the info if you have access to the process
list
(i.e. not a remote client).

Well, there's now been 2 calls for seperate options for the autovacuum
process; this one and the ability to give it a different log level.
Perhaps what would be best is having autovac read a second set of config
options that would over-ride settings in the main postgresql.conf. This
could be a seperate GUC, or perhaps a seperate config file (which would
allow for removing all the autovac controls from postgresql.conf).

In any event thanks a lot for the current fix, as is it's a big
improvement! 8^)

Ditto.
--
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