Thread: 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