Re: Unusually high IO for autovacuum worker - Mailing list pgsql-general

On Fri, Feb 1, 2013 at 2:40 PM, Pavan Deolasee <pavan.deolasee@gmail.com>wrote:

>
>
> On Fri, Feb 1, 2013 at 5:19 PM, Vlad Bailescu <vlad@mojitosoftware.com>wrote:
>
>>
>>
>> Pavan, it seems there's a small issue with copy-paste and column
>> text-align. Table sizes are:
>>
>>   4136 kB  2448 kB  2336 kB
>
>
> Ah OK. I see.
>
>
>>
>> 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);
>
> Thanks,
> Pavan
>

Great insight Pavan,

I see this change in ANALYZE is documented since 9.0:

If the table being analyzed has one or more children, ANALYZE will gather
> statistics twice: once on the rows of the parent table only, and a second
> time on the rows of the parent table with all of its children. The
> autovacuum daemon, however, will only consider inserts or updates on the
> parent table when deciding whether to trigger an automatic analyze. If that
> table is rarely inserted into or updated, the inheritance statistics will
> not be up to date unless you run ANALYZE manually.


 We will try setting a higher threshold for autovacuum on the parent table.

As for design, we could have gone this way or use a rule to redirect
inserts from master to children. My understanding is that performance for
rules is not so good (especially since we're doing mostly single INSERTs).
Unfortunately, the normal trigger will not work for us at the moment.

I saw your proposal on -hackers, I will keep an eye on it!

Thanks you,
Vlad