Re: autovacuum "connections" are hidden
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_seg1Hmm, 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_seg1Hmm, 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_seg1Hmm, 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.
Attachments:
activity.patchtext/plain; charset=us-asciiDownload
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.5.2.5
diff -c -r1.5.2.5 autovacuum.c
*** src/backend/postmaster/autovacuum.c 20 Jan 2006 15:17:13 -0000 1.5.2.5
--- src/backend/postmaster/autovacuum.c 18 May 2006 23:02:56 -0000
***************
*** 108,113 ****
--- 108,115 ----
List **toast_table_ids);
static void autovacuum_do_vac_analyze(List *relids, bool dovacuum,
bool doanalyze, bool freeze);
+ static void autovac_report_activity(VacuumStmt *vacstmt,
+ List *relids);
/*
***************
*** 892,897 ****
--- 894,902 ----
VacuumStmt *vacstmt;
MemoryContext old_cxt;
+ /* Let pgstat know what we're doing */
+ autovac_report_activity(vacstmt, relids);
+
/*
* The node must survive transaction boundaries, so make sure we create it
* in a long-lived context
***************
*** 922,927 ****
--- 927,951 ----
MemoryContextSwitchTo(old_cxt);
}
+ static void
+ autovac_report_activity(VacuumStmt *vacstmt, List *relids)
+ {
+ #define MAX_AUTOVAC_ACTIV_LEN 64
+ char activity[MAX_AUTOVAC_ACTIV_LEN];
+
+ if (list_length(relids) == 0)
+ snprintf(activity, MAX_AUTOVAC_ACTIV_LEN,
+ "whole database vacuum");
+ else if (list_length(relids) == 1)
+ snprintf(activity, MAX_AUTOVAC_ACTIV_LEN,
+ "VACUUM rel %u", linitial_oid(relids));
+ else
+ snprintf(activity, MAX_AUTOVAC_ACTIV_LEN,
+ "VACUUM rels %u ...", linitial_oid(relids));
+
+ pgstat_report_activity(activity);
+ }
+
/*
* AutoVacuumingActive
* Check GUC vars and report whether the autovacuum process should be
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.111.2.2
diff -c -r1.111.2.2 pgstat.c
*** src/backend/postmaster/pgstat.c 18 Jan 2006 20:35:16 -0000 1.111.2.2
--- src/backend/postmaster/pgstat.c 18 May 2006 23:05:10 -0000
***************
*** 691,707 ****
/*
* We may not have a MyProcPort (eg, if this is the autovacuum process).
! * For the moment, punt and don't send BESTART --- would be better to work
! * out a clean way of handling "unknown clientaddr".
*/
if (MyProcPort)
- {
- pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_BESTART);
- msg.m_databaseid = MyDatabaseId;
- msg.m_userid = GetSessionUserId();
memcpy(&msg.m_clientaddr, &MyProcPort->raddr, sizeof(msg.m_clientaddr));
! pgstat_send(&msg, sizeof(msg));
! }
/*
* Set up a process-exit hook to ensure we flush the last batch of
--- 691,707 ----
/*
* We may not have a MyProcPort (eg, if this is the autovacuum process).
! * Send an all-zeroes client address, which is dealt with specially in
! * pg_stat_get_backend_client_addr and pg_stat_get_backend_client_port.
*/
+ pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_BESTART);
+ msg.m_databaseid = MyDatabaseId;
+ msg.m_userid = GetSessionUserId();
if (MyProcPort)
memcpy(&msg.m_clientaddr, &MyProcPort->raddr, sizeof(msg.m_clientaddr));
! else
! MemSet(&msg.m_clientaddr, 0, sizeof(msg.m_clientaddr));
! pgstat_send(&msg, sizeof(msg));
/*
* Set up a process-exit hook to ensure we flush the last batch of
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.475.2.4
diff -c -r1.475.2.4 postmaster.c
*** src/backend/postmaster/postmaster.c 18 Mar 2006 22:10:11 -0000 1.475.2.4
--- src/backend/postmaster/postmaster.c 18 May 2006 20:50:33 -0000
***************
*** 2135,2140 ****
--- 2135,2143 ----
{
AutoVacPID = 0;
autovac_stopped();
+ /* Tell the collector about process termination */
+ pgstat_beterm(pid);
+
if (exitstatus != 0)
HandleChildCrash(pid, exitstatus,
_("autovacuum process"));
Index: src/backend/utils/adt/pgstatfuncs.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/adt/pgstatfuncs.c,v
retrieving revision 1.26
diff -c -r1.26 pgstatfuncs.c
*** src/backend/utils/adt/pgstatfuncs.c 17 Oct 2005 16:24:19 -0000 1.26
--- src/backend/utils/adt/pgstatfuncs.c 18 May 2006 23:10:41 -0000
***************
*** 389,394 ****
--- 389,395 ----
pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS)
{
PgStat_StatBeEntry *beentry;
+ SockAddr zero_clientaddr;
int32 beid;
char remote_host[NI_MAXHOST];
int ret;
***************
*** 405,410 ****
--- 406,417 ----
if (!superuser() && beentry->userid != GetUserId())
PG_RETURN_NULL();
+ /* A zeroed client addr means we don't know */
+ memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
+ if (memcmp(&(beentry->clientaddr), &zero_clientaddr,
+ sizeof(zero_clientaddr) == 0))
+ PG_RETURN_NULL();
+
switch (beentry->clientaddr.addr.ss_family)
{
case AF_INET:
***************
*** 432,437 ****
--- 439,445 ----
pg_stat_get_backend_client_port(PG_FUNCTION_ARGS)
{
PgStat_StatBeEntry *beentry;
+ SockAddr zero_clientaddr;
int32 beid;
char remote_port[NI_MAXSERV];
int ret;
***************
*** 448,453 ****
--- 456,467 ----
if (!superuser() && beentry->userid != GetUserId())
PG_RETURN_NULL();
+ /* A zeroed client addr means we don't know */
+ memset(&zero_clientaddr, 0, sizeof(zero_clientaddr));
+ if (memcmp(&(beentry->clientaddr), &zero_clientaddr,
+ sizeof(zero_clientaddr) == 0))
+ PG_RETURN_NULL();
+
switch (beentry->clientaddr.addr.ss_family)
{
case AF_INET:
Index: src/interfaces/libpq/fe-connect.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/interfaces/libpq/fe-connect.c,v
retrieving revision 1.323.2.1
diff -c -r1.323.2.1 fe-connect.c
*** src/interfaces/libpq/fe-connect.c 22 Nov 2005 18:23:29 -0000 1.323.2.1
--- src/interfaces/libpq/fe-connect.c 18 May 2006 22:04:15 -0000
***************
*** 2081,2087 ****
* A copy is needed to be able to cancel a running query from a different
* thread. If the same structure is used all structure members would have
* to be individually locked (if the entire structure was locked, it would
! * be impossible to cancel a synchronous query becuase the structure would
* have to stay locked for the duration of the query).
*/
PGcancel *
--- 2081,2087 ----
* A copy is needed to be able to cancel a running query from a different
* thread. If the same structure is used all structure members would have
* to be individually locked (if the entire structure was locked, it would
! * be impossible to cancel a synchronous query because the structure would
* have to stay locked for the duration of the query).
*/
PGcancel *
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 fooie 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?
--
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