Re: Auto-analyse on insert operations

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(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).
>
> 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(dot)klaver(at)aklaver(dot)com