Thread: Process title for autovac

Process title for autovac

From
Jeff Janes
Date:
I've often wanted to know what the autovacuum worker was doing.  The process title seems like the best place to get this information, but the process title tells me what database it is in, but not what table it is working on.

The attached patch demonstrates the concept of what I want.  I put the code in table_recheck_autovac not because I think that is the best location, but just because it was the easiest point at which I knew how to get the table name easily before classTup gets destroyed.

Example output:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
16392 jjanes    20   0  229m  19m 6948 S  3.6  1.0   0:06.85 postgres: autovacuum worker process   jjanes.public.pgbench_accounts


I never reset the process title back to the initial state of just having a database name and no table.  Which I can get away with temporarily because the autovac worker never dilly-dallies between tables, it either goes to the next one, or exits.  A real implementation would probably want to reset it anyway, though.

Is this functionality something we want?  If so should it include explicit vacuum as well as autovac?  Any opinion about where in the code base it properly belongs (which obviously depends on whether it should cover manual vacuum as well)?  And does the string need to distinguish between an autovac and an autoanalyze?
 
Cheers,

Jeff
Attachment

Re: Process title for autovac

From
Mark Kirkwood
Date:
On 07/04/13 08:20, Jeff Janes wrote:
> I've often wanted to know what the autovacuum worker was doing.  The
> process title seems like the best place to get this information, but the
> process title tells me what database it is in, but not what table it is
> working on.
>
> The attached patch demonstrates the concept of what I want.  I put the
> code in table_recheck_autovac not because I think that is the best
> location, but just because it was the easiest point at which I knew how
> to get the table name easily before classTup gets destroyed.
>
> Example output:
>
>    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
> 16392 jjanes    20   0  229m  19m 6948 S  3.6  1.0   0:06.85 postgres:
> autovacuum worker process   jjanes.public.pgbench_accounts
>
>
> I never reset the process title back to the initial state of just having
> a database name and no table.  Which I can get away with temporarily
> because the autovac worker never dilly-dallies between tables, it either
> goes to the next one, or exits.  A real implementation would probably
> want to reset it anyway, though.
>
> Is this functionality something we want?  If so should it include
> explicit vacuum as well as autovac?  Any opinion about where in the code
> base it properly belongs (which obviously depends on whether it should
> cover manual vacuum as well)?  And does the string need to distinguish
> between an autovac and an autoanalyze?
> Cheers,
>
> Jeff
>

Knowing whether it is vacuuming or analyzing seems like a nice idea +1

Cheers

Mark




Re: Process title for autovac

From
Robert Haas
Date:
On Sat, Apr 6, 2013 at 4:20 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> I've often wanted to know what the autovacuum worker was doing.  The process
> title seems like the best place to get this information, but the process
> title tells me what database it is in, but not what table it is working on.
>
> The attached patch demonstrates the concept of what I want.  I put the code
> in table_recheck_autovac not because I think that is the best location, but
> just because it was the easiest point at which I knew how to get the table
> name easily before classTup gets destroyed.
>
> Example output:
>
>   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
> 16392 jjanes    20   0  229m  19m 6948 S  3.6  1.0   0:06.85 postgres:
> autovacuum worker process   jjanes.public.pgbench_accounts
>
> I never reset the process title back to the initial state of just having a
> database name and no table.  Which I can get away with temporarily because
> the autovac worker never dilly-dallies between tables, it either goes to the
> next one, or exits.  A real implementation would probably want to reset it
> anyway, though.
>
> Is this functionality something we want?  If so should it include explicit
> vacuum as well as autovac?  Any opinion about where in the code base it
> properly belongs (which obviously depends on whether it should cover manual
> vacuum as well)?  And does the string need to distinguish between an autovac
> and an autoanalyze?

I'm not sure whether it's a good idea to do this for manual vacuum
(the answer may depend on what the code ends up looking like), but it
seems good to do it at least for autovac.  I don't think it's
absolutely necessary to distinguish between autovac and autoanalyze,
but I think it would be nice.  Generally, +1 for doing something along
these lines.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: Process title for autovac

From
Alvaro Herrera
Date:
Jeff Janes escribió:

> Is this functionality something we want?  If so should it include explicit
> vacuum as well as autovac?

Yes.  No.

> Any opinion about where in the code base it
> properly belongs (which obviously depends on whether it should cover manual
> vacuum as well)?  And does the string need to distinguish between an
> autovac and an autoanalyze?

autovacuum_do_vac_analyze() is probably the place to add it.  I think we
should include the wraparound, dovacuum and doanalyze flags in there
somehow, yes.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services



Re: Process title for autovac

From
Peter Eisentraut
Date:
On Sat, 2013-04-06 at 13:20 -0700, Jeff Janes wrote:
> I've often wanted to know what the autovacuum worker was doing.  The
> process title seems like the best place to get this information, but
> the process title tells me what database it is in, but not what table
> it is working on. 

Because the process title is publicly visible, you shouldn't put any
"interesting" information in it.

I think what you want might be better kept in pg_stat_activity.





Re: Process title for autovac

From
Jeff Janes
Date:
On Sat, Apr 13, 2013 at 5:56 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
On Sat, 2013-04-06 at 13:20 -0700, Jeff Janes wrote:
> I've often wanted to know what the autovacuum worker was doing.  The
> process title seems like the best place to get this information, but
> the process title tells me what database it is in, but not what table
> it is working on.

Because the process title is publicly visible, you shouldn't put any
"interesting" information in it.

OK. I did not think that the existence of a table name would be interesting, but I can see that some would consider it so.
 
I think what you want might be better kept in pg_stat_activity.


And in fact it is already there.  I had just never thought of looking there for background process stuff.  It even includes the notice "(to prevent wraparound)" when applicable.

Thanks!

I'd still like it in the process title, because I'm not worried about exposing table names and I always have 'top' running while I don't monitor pg_stat_activity as a matter of routine.  But I guess the security concern wins.

I'll mark it as rejected.

Thanks,

Jeff