Re :Re: Wrong stat on pg_stat_user_tables

That's an excellent question !

Nothing kill the autovacuum worker, but autovacuum process can take more
than 30 hours to finish !

During this time, table have a lot of updates, and so it is never clean...

Thomas

-------- Message transféré --------
Sujet : Re: [ADMIN] Wrong stat on pg_stat_user_tables
Date : Wed, 5 Oct 2016 12:08:14 +0000
De : Aislan Luiz Wendling <aislanluiz(at)hotmail(dot)com>
Pour : Thomas SIMON <tsimon(at)neteven(dot)com>

Hi Thomas,

What concerns me is why do you have so many dead rows using
autovacuum with a low scale factor. Are something killing the
autovacuum process? Or did you set this value too late? Because with the
total estimated rows like 89M, a scale factor of 5% is not so good. You
should use autovacuum_vacuum_threshold (integer) instead, with a number
of rows next to your day reality.

------------------------------------------------------------------------
*From:* pgsql-admin-owner(at)postgresql(dot)org
<pgsql-admin-owner(at)postgresql(dot)org> on behalf of Thomas SIMON
<tsimon(at)neteven(dot)com>
*Sent:* Wednesday, October 5, 2016 7:14 AM
*To:* Glyn Astill; Tom Lane
*Cc:* pgsql-admin(at)postgresql(dot)org
*Subject:* Re: [ADMIN] Wrong stat on pg_stat_user_tables
Hi guys,
Thanks for your replies, and sorry for the late answer.

I have grouped your messages to have more visibility.

> Do you have any long running transactions? The value returned by
n_live_tup is an estimate of all the live rows in the table, vs the
value you see from count is the number rows visible to the current
transaction.
no, I havent got particulary long transactions on this table, or at
least not more than on other tables

> How long between sending the email and running the queries? What
output do you see if you analyze the table? And what pg version is this?
If I analyse table, I have following output :

analyse verbose items;
INFO: analyzing "public.items"
INFO: "items": scanned 30000 of 11676017 pages, containing 65181 live
rows and 132481 dead rows; 30000 rows in sample, 89400829 estimated
total rows
ANALYZE

It is 9.5 postgresql version

>
> I think (I may be wrong) the value you see in pg_stat_user_tables is
only an estimate based on a sample of tuples per page in the table, so
if you've an uneven distribution of live rows you could see an incorrect
value.
I think you hit something, indeed this is a sample, and indeed when I
see postgres's answer, I see something wrong, I see way too much dead
rows, and planner is probably cheated by this.

I've made some search about analyze, and seen that we can tell to
analyze to use greater sample, so I tried (x4) but I have
approximatively the same result.

#analyse verbose items;
INFO: analyzing "public.items"
INFO: "items": scanned 30000 of 11676017 pages, containing 65853 live
rows and 133172 dead rows; 30000 rows in sample, 89236981 estimated
total rows
ANALYZE

# ALTER TABLE items ALTER item_id SET STATISTICS 400;
ALTER TABLE

# analyse verbose items;
INFO: analyzing "public.items"
INFO: "items": scanned 120000 of 11676017 pages, containing 260685 live
rows and 527336 dead rows; 120000 rows in sample, 88580538 estimated
total rows
ANALYZE

>
>
> What do the planner stats show you? Try:
>
> select reltuples::integer from pg_class where oid =
'items'::regclass;
I'm far away from reality too.

# select reltuples::integer from pg_class where oid = 'items'::regclass;
-[ RECORD 1 ]-------
reltuples | 89400832

>
>
> You might get a better response from the pgsql-general list.
>
> Glyn
>

------------

> The pgstats stats are only approximate, but ideally they'd converge
> towards reality over time when they're wrong. Seems like maybe this one
> table has a usage pattern that's odd enough to throw things off. Maybe
> you have unusual autovacuum/autoanalyze parameters for it, for example?
> Is there anything strange about the way you insert/update/delete in it?
Indeed I have special autovacuum parameters on this table (
autovacuum_vacuum_scale_factor=0.05 ), because there are lot of updates
on this table, and I did not want it to bloat too much. And so
autovacuum is almost always running on this table. But I don't
understand why it should change something ?
Should i downgrade scale_factor ?
>
> A simpler explanation might be that this table has inheritance children
> --- the pgstats count would consider only the table itself, but
> "select count(1) from items" would scan the child tables too.

I understand your meaning about inheritance, but in my case select count
(25m) is lower than pgstats (110m).

Thanks
--
Thomas