Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)

On Fri, Feb 1, 2013 at 6:10 PM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>wrote:

>
>
>
>>
>> 2012-12-05 00:44:23 EET LOG: automatic analyze of table
>> "fleet.fleet.vehicle_position" system usage: CPU 4.46s/0.61u sec elapsed
>> 465.09 sec
>>
>
> This is the interesting piece of information. So its the auto analyze
> thats causing all
> the IO activity. That explains why it was a read only IO that we noticed
> earlier. Whats
> happening here, and something that changed from 8.4 to 9.1, is that
> whenever the parent
> table is analyzed, the child tables are also automatically analyzed. I
> don't remember the
> rational for doing this change, but in your case the analyze on the parent
> table itself is
> quite useless because even though you inserting a large number of new
> tuples, you are
> also immediately deleting them. I don't want to comment on the design
> aspect of that,
> but you should be able to fix this problem by disabling auto-analyze on
> the parent table.
>
> Having said that, I don't see an easy way to just disable auto-analyze on
> a table. You can
> run ALTER TABLE foo SET (autovacuum_enabled = false), but that would also
> disable
> auto-vacuum, which you certainly don't want to do because the parent table
> would just
> keep growing.
>
> You can set autovacuum_analyze_threshold to an artificially high value
> to mitigate the
> problem and reduce the frequency of auto-analyze on the table or see if
> you can completely
> avoid insert/delete on the parent table.
>
> ALTER TABLE vehicle_position SET (autovacuum_analyze_threshold =
> 2000000000);
>
>
While looking at this particular case on -general, I realized that there is
no way to *only* disable auto-analyze on a table. While one can cheat like
what I suggested to the OP by setting threshold very high, I think it will
be useful to be able to just off analyze. In this particular case, the OP
is inserting and then deleting the same rows from the parent table, thus
keeping it almost empty. Of course, he would want to run auto-vacuum on the
table to remove the dead rows. Usually auto-analyze would have returned
quite fast, especially because we vacuum a table first and then analyze it.
But in this case, since the table is a parent of a number of large child
tables, we end up analyzing the child tables too, which takes significantly
longer time and is quite unnecessary because in this case the activity on
the parent table must not have changed any stats for the child tables.

A new reloption such as autovacuum_analyze_enabled is what we need.

Comments ?

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee