Обсуждение: pg_stat_activity xact_start and autovacuum

Hello.

I'm using 8.3.0 and I see that autovacuum processes in
pg_stat_activity have xact_start.

As far as I know, since at least 8.2.x the VACUUM does not start a new
transaction.
If that statement is correct, the xact_start column in
pg_stat_activity should be NULL...

Why does it matter?  Monitoring.  It's good to know the age of oldest
running transaction,
and autovacuuming is well, adding noise.

   Regards,
      Dawid

Dawid Kuroczko escribió:

> I'm using 8.3.0 and I see that autovacuum processes in
> pg_stat_activity have xact_start.
>
> As far as I know, since at least 8.2.x the VACUUM does not start a new
> transaction.
> If that statement is correct, the xact_start column in
> pg_stat_activity should be NULL...
> Why does it matter?  Monitoring.  It's good to know the age of oldest
> running transaction, and autovacuuming is well, adding noise.

Autovacuum certainly uses transactions ...  ??

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

On Feb 11, 2008 2:27 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Dawid Kuroczko escribió:
> > I'm using 8.3.0 and I see that autovacuum processes in
> > pg_stat_activity have xact_start.
> >
> > As far as I know, since at least 8.2.x the VACUUM does not start a new
> > transaction.
> > If that statement is correct, the xact_start column in
> > pg_stat_activity should be NULL...
> > Why does it matter?  Monitoring.  It's good to know the age of oldest
> > running transaction, and autovacuuming is well, adding noise.
> Autovacuum certainly uses transactions ...  ??

I am referrring to the E.8.3.5 Release 8.2 Release Notes:

* Allow VACUUM to expire rows without being affected by other
concurrent VACUUM operations (Hannu Krossing, Alvaro, Tom)

I have probably oversimplifed my statement above.  What I am monitoring
is the age of the oldest transaction, to be alerted before tables accumulate
too many dead rows.  From this point of view long running VACUUM is not
a problem (since relese 8.2).

Right now I am using:
SELECT extract('epoch' from min(xact_start)) AS oldest_xact_age
  FROM pg_stat_activity
 WHERE current_query NOT LIKE 'autovacuum:%';

...which works fine but somehow I feel that if xact_age would be NULL, it would
ring more true.  Since VACUUM does not prevent VACUUMING it can take
days to complete and still I wouldn't need to worry. ;-)

Let me know if I mixed things up horribly. :-)

  Regards,
   Dawid

Dawid Kuroczko escribió:

> > Dawid Kuroczko escribió:
> > > I'm using 8.3.0 and I see that autovacuum processes in
> > > pg_stat_activity have xact_start.
> > >
> > > As far as I know, since at least 8.2.x the VACUUM does not start a new
> > > transaction.

> I am referrring to the E.8.3.5 Release 8.2 Release Notes:
>
> * Allow VACUUM to expire rows without being affected by other
> concurrent VACUUM operations (Hannu Krossing, Alvaro, Tom)

Oh, I see.  Well, it is certainly running in a transaction, even though
that transaction does not prevent other vacuums from removing old rows.

> Right now I am using:
> SELECT extract('epoch' from min(xact_start)) AS oldest_xact_age
>   FROM pg_stat_activity
>  WHERE current_query NOT LIKE 'autovacuum:%';
>
> ...which works fine but somehow I feel that if xact_age would be NULL, it would
> ring more true.  Since VACUUM does not prevent VACUUMING it can take
> days to complete and still I wouldn't need to worry. ;-)

Actually it's not just autovacuum; it's any lazy vacuum.  It's hard to
tell those processes apart in pg_stat_activity.  Perhaps we could have
added a column in pg_stat_activity indicating processes that don't hold
old tuples, but I feel that would have been a little too much.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Feb 11, 2008, at 8:14 AM, Alvaro Herrera wrote:
> Actually it's not just autovacuum; it's any lazy vacuum.  It's hard to
> tell those processes apart in pg_stat_activity.  Perhaps we could have
> added a column in pg_stat_activity indicating processes that don't
> hold
> old tuples, but I feel that would have been a little too much.


I don't think it'd be too hard to construct a regex that would catch
all vacuums, after which you could throw out FULLs. I'm thinking
something like

\s*vacuum((\s+full){0,1}\s+\S+){0,1};{0,1}

Where \s indicates whitespace and \S indicates not whitespace (sorry,
don't have a regex manual handy...)

You could probably even simplify that to

\s*vacuum(\s+full){0}

Of course, you'd want to perform all of those in a case-insensitive
manner.
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828