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: 20070601093356.8C07.ITAGAKI.TAKAHIRO@oss.ntt.co.jp (view raw or flat)
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. 
http://momjian.us/main/writings/pgsql/sgml/routine-vacuuming.html#AUTOVACUUM

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.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center



In response to

Responses

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-2014 The PostgreSQL Global Development Group