[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [HACKERS] Autovacuum versus rolled-back transactions
From:       Alvaro Herrera <alvherre () commandprompt ! com>
Date:       2007-05-31 21:32:17
Message-ID: 20070531213217.GH18911 () alvh ! no-ip ! org
[Download RAW message or body]

Tom Lane wrote:

> It may boil down to whether we would like the identity
> 	n_live_tup = n_tup_ins - n_tup_del
> to continue to hold, or the similar one for n_dead_tup.  The problem
> basically is that pgstats is computing n_live_tup and n_dead_tup
> using those identities rather than by tracking what really happens.

Thanks for fixing this.  For the record, I don't think I ever actually
*considered* the effect of rolled back transactions in the tuple counts;
at the time I wrote the code, I was just mirroring what the old autovac
code did, and I didn't stop to think whether the assumptions were
actually correct.

I think the committed fix was the most appropriate -- changing the
semantics of n_ins_tup etc would defeat the original purpose they were
written for, I think.


Regarding the idea of counting dead tuples left behind by vacuum to
update pgstats at the end, I think the idea of counting them
individually is good, but it doesn't account for dead tuples created in
areas that were scanned earlier.  So I think that Takahiro-san idea of
using the value accumulated in pgstats is better.

If we apply Heikki's idea of advancing OldestXmin, I think what we
should do is grab the value from pgstats when vacuum starts, and each
time we're going to advance OldestXmin, grab the value from pgstats
again; accumulate the differences from the various pgstat grabs.  At the
end we send the accumulated differences as the new dead tuple count.

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
[prev in list] [next in list] [prev in thread] [next in thread]