Обсуждение: 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