Skip site navigation (1) Skip section navigation (2)

Re: Unusually high IO for autovacuum worker

From: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
To: Vlad Bailescu <vlad(at)mojitosoftware(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Unusually high IO for autovacuum worker
Date: 2013-02-01 12:40:53
Message-ID: CABOikdMKZydF00ZsnRn4rwFbKAUref-ffxQQDfTess8+_CcOvA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On Fri, Feb 1, 2013 at 5:19 PM, Vlad Bailescu <vlad(at)mojitosoftware(dot)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

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

In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2013-02-01 12:47:41
Subject: proposal: enable new error fields in plpgsql (9.4)
Previous:From: Vlad BailescuDate: 2013-02-01 11:49:26
Subject: Re: Unusually high IO for autovacuum worker

pgsql-general by date

Next:From: Martin GaintyDate: 2013-02-01 13:16:40
Subject: Postgres version number support
Previous:From: Thomas KellererDate: 2013-02-01 11:55:44
Subject: Windows documentation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group