Skip site navigation (1) Skip section navigation (2)

Re: Autovacuum versus rolled-back transactions

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Autovacuum versus rolled-back transactions
Date: 2007-06-01 01:08:27
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:

> 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.

On a relevant note, there is a variance in the calculation of auto-analyze
threshold between documentation and implementation in HEAD.
(Only HEAD; It is ok in 8.2 or before)

Our documentation says
| analyze threshold = analyze base threshold
|                       + analyze scale factor * number of tuples
| is compared to the total number of tuples inserted, updated, or deleted
| since the last ANALYZE.

but deleted tuples are not considered in the total number, because the delta
of {n_live_tuples + n_dead_tuples} is not changed by DELETE. We add the number
of DELETE into n_live_tuples and subtract it from n_dead_tuples.

| pgstat.c
|   t_new_live_tuples += tuples_inserted - tuples_deleted;
|   t_new_dead_tuples += tuples_deleted;
| autovacuum.c
|   anltuples = n_live_tuples + n_dead_tuples - last_anl_tuples;

There is no delete-only database in the real world, so this is not so serious
problem probably. We'd better to fix the documentation if it is intention.

ITAGAKI Takahiro
NTT Open Source Software Center

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2007-06-01 01:19:28
Subject: Re: Autovacuum versus rolled-back transactions
Previous:From: Joshua D. DrakeDate: 2007-05-31 23:04:31
Subject: Re: Changing checkpoint_timeout to another name?

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group