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é --------
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@xxxxxxxxxxxxxx
<pgsql-admin-owner@xxxxxxxxxxxxxx> on behalf of
Thomas SIMON <tsimon@xxxxxxxxxxx>
Sent: Wednesday, October 5, 2016 7:14 AM To: Glyn Astill; Tom Lane Cc: pgsql-admin@xxxxxxxxxxxxxx Subject: Re: 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin |