On Wed, 4 Nov 2015 16:43:57 +0100 Bertrand Roos <bertrand.roos@xxxxxxxx> wrote: > > Le 04/11/2015 14:55, Bill Moran a écrit : > > On Wed, 4 Nov 2015 14:32:37 +0100 > > Bertrand Roos <bertrand.roos@xxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
