Re: Reduce maximum error in tuples estimation after vacuum. - Mailing list pgsql-hackers

Hello, 

> I have tried to reproduce the problem in different m/c's, but couldn't
> reproduce it.
> I have ran tests with default configuration.

I think you had reproduced it.

> Output on Windows:
> -------------------
> postgres=# create table t (a int, b int); 
(snip)
> postgres=# select n_live_tup, n_dead_tup from pg_stat_user_tables where
> relname= 
> 't'; 
>  n_live_tup | n_dead_tup 
> ------------+------------ 
>       10001 |     989999 
> (1 row)

Yes, this is the same for me. You should've done this instead,

postgres=# select reltuples from pg_class where relname = 't';reltuples 
-----------    1e+06
(1 row)

This is 100 times larger than n_live_tup, and it is this value
which used for judge the necessity of autovacuum.

autovacuum.c: 2695
|   reltuples = classForm->reltuples;
|   vactuples = tabentry->n_dead_tuples;
....   
|   vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
|   anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;

Although..

> Output on Suse
> ----------------
> postgres=# drop table if exists t; 
> create table t (a int, b int); 
> insert into t (select a, (random() * 100000)::int from
> generate_series((select count(*) from t) + 1, 1000000) a); 
> update t set b = b + 1 where a <  (select count(*) from t) * 0.7; 
> vacuum t; 
> delete from t where a < (select count(*) from t) * 0.99; 
> vacuum t; 
> select c.relpages, s.n_live_tup, c.reltuples, (select count(*) from t) as
> tuples, reltuples::float / (select count(*) from t) as ratio  from
> pg_stat_user_tables s, pg_class c where s.relname = 't' and c.relname =
> 't';DROP TABLE 
> postgres=# CREATE TABLE 
> postgres=# INSERT 0 1000000 
> postgres=# UPDATE 699999 
> postgres=# VACUUM 
> postgres=# DELETE 989999 
> postgres=# VACUUM 
> postgres=# 
>  relpages | n_live_tup | reltuples | tuples | ratio 
> ----------+------------+-----------+--------+------- 
>      4425 |      10001 |     10001 |  10001 |     1 
> (1 row)

... Mmm.. I have following figures for the same operation.

relpages | n_live_tup | reltuples | tuples |      ratio       
----------+------------+-----------+--------+------------------    4425 |     417670 |    417670 |  10001 |
41.7628237176282

I condisider on this for a time..

> When I tried to run vactest.sh, it gives below error:
> linux:~/akapila/vacuum_nlivetup> ./vactest.sh 
> ./vactest.sh: line 11: syntax error near unexpected token `&' 
> ./vactest.sh: line 11: `        psql ${dbname} -c "vacuum verbose t" |&
> egrep "INFO: *\"t\": found " | sed -e 's/^.* versions in \([0-9]*\)
> .*$/\1/''
> 
> 
> Can you help me in reproducing the problem by letting me know if I am doing
> something wrong or results of test are not predictable?

Could you let me know the pg's version you're running?  And it is
appreciated if you're kindly show me the vacuum logs while
testing.

# I found a silly bug in the patch, but I put it off.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center