Re: Auto-analyse on insert operations

On Wed, 4 Nov 2015 16:43:57 +0100
Bertrand Roos <bertrand(dot)roos(at)areal(dot)fr> wrote:
>
> Le 04/11/2015 14:55, Bill Moran a écrit :
> > On Wed, 4 Nov 2015 14:32:37 +0100
> > Bertrand Roos <bertrand(dot)roos(at)areal(dot)fr> wrote:
> >> I try to configure auto-analyse task with postgresql 9.4.
> >> I have the following configuration (default configuration):
> >> track_counts = on
> >> autovacuum = on
> >> log_autovacuum_min_duration = -1
> >> autovacuum_max_workers = 3
> >> autovacuum_naptime = 300s
> >> autovacuum_vacuum_threshold = 50
> >> autovacuum_analyze_threshold = 50
> >> autovacuum_vacuum_scale_factor = 0.2
> >> autovacuum_analyze_scale_factor = 0.2
> >> autovacuum_freeze_max_age = 200000000
> >> autovacuum_multixact_freeze_max_age = 400000000
> >> autovacuum_vacuum_cost_delay = 20ms
> >> autovacuum_vacuum_cost_limit = -1
> >>
> >> With this configuration, I can observe that some tables are
> >> auto-analysed, but some others are not. Even if there are millions of
> >> insert operations on an empty table (all tables are in cluster mode).
> >> In fact it seems that tables with update operations are the only ones
> >> that are auto-analysed.
> >> I'm quite suprised because the documentation says that daemon check the
> >> count of insert, update and delete operations.
> >> What could it be the reason ? Why tables which have only update
> >> operation, aren't analysed ?
> >> Are update operations really taken into account ?
> > Given that autoanalyze is pretty critical to the way the system functions,
> > it's unlikely that it just doesn't work (someone else would have noticed).
> >
> > A more likely scenario is that you've found some extremely obscure edge
> > case. If that's the case, you're going to have to give very specific
> > details as to how you're testing it before anyone is liable to be able
> > to help you.
> >
> > I get the impression that you're somewhat new to Postgres, in which case
> > it's very likely that the problem is that you're not testing the situation
> > correctly. In that case, we're going to need specific details on how you're
> > observing that tables are or are not being analysed.
> >
> > As a wild-guess theory: the process that does the analyze only wakes up
> > to check tables every 5 minutes (based on the config you show) ... so are
> > you doing the inserts then checking the table without leaving enough time
> > in between for the system to wake up and notice the change?
> >
> Thanks for your answer Bill.
> Indeed, I'm pretty new to Postgres and I don't exclude that I'm doing
> something wrong. But I did my test on a more than 1 day duration, so
> it's not an issue of autovacuum_naptime (I insert 760 lignes each 30
> seconds during 36 hours).
> I can't give all the details of this test because it is to complicated
> with triggers and partman (and your objective is not to solve
> configuration issues of others).

Others have answered some of your other questions, so I'll just throw
out another possibility: have the per-table analyze settings been altered
on the table(s) that are behaving badly? See
http://www.postgresql.org/docs/9.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS

Attaching the output of
pg_dump -s -t $table_name -U postgres $database_name
will probably go a long way toward getting more targeted assistance.
(substitute the actual database name, and the name of a table that is
giving you trouble)

In addition, the output of
SELECT * FROM pg_stat_user_tables WHERE relname = '$table_name';
(Again, substitute an actual table name that's giving you trouble,
preferrably the same table as from the pg_dump)

--
Bill Moran