On 11/04/2015 07:43 AM, Bertrand Roos 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).
In fact, I was telling the question because I have read on some forums
that the auto vacuum deamon only count dead tuple so only update and
delete operations can cause the scheduling of auto-analyse.
So if it's the case it perfectly explain why my test case doesn't work.
But in the other hand the documentation says that delete, update and
insert operations are counted.
Is it an know issue that insert operations are not counted for the
trigger of auto-analyse ?
No, see below:
http://www.postgresql.org/docs/9.4/interactive/routine-vacuuming.html#AUTOVACUUM
23.1.3. Updating Planner Statistics
"The autovacuum daemon, if enabled, will automatically issue ANALYZE
commands whenever the content of a table has changed sufficiently.
However, administrators might prefer to rely on manually-scheduled
ANALYZE operations, particularly if it is known that update activity on
a table will not affect the statistics of "interesting" columns. The
daemon schedules ANALYZE strictly as a function of the number of rows
inserted or updated; it has no knowledge of whether that will lead to
meaningful statistical changes."
Is the partman you refer to this?:
https://github.com/keithf4/pg_partman
Can you give an outline view of what you are doing and how you are
determining the status of analyze?
If it's not, I can try to reproduce this weird behaviour with a simpler
test and give you all the details of the test.
Bertrand
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
