Re: Reduce maximum error in tuples estimation after vacuum.

On Tuesday, June 25, 2013 2:06 PM Kyotaro HORIGUCHI wrote:
> 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?

I had used 9.4 Head to run above tests.
Sorry, yesterday I was busy with some other work so could not got time to
check and work on this issue further.
I shall try to work on it today.

> 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