Re: Unusually high IO for autovacuum worker

Vlad Bailescu <vlad@mojitosoftware.com> wrote:=0A=0A> Because of our applic=
ation stack (Hibernate ORM) we use a=0A> before_insert trigger to insert ro=
ws into the right partition and=0A> in the master table and then an after_i=
nsert trigger to delete=0A> them from master table (we need the original in=
sert to return the=0A> inserted row info). This means the master table beco=
mes quickly=0A> populated with dead tuples and triggers the autovacuum freq=
uently=0A> (we are using default settings for autovacuum).=0A>=0A> After up=
grading our DB from 8.4 to 9.1 (and moving from Debian 6=0A> to Ubuntu 12.0=
4) we noticed a large increase in IO and traced it=0A> back to auto vacuum:=
 iotop and collectd report bursts of 4-6M/s=0A> taking 90 seconds followed =
by a 30 seconds pause. That's a lot of=0A> data to chew on especially since=
 the master table is only a=0A> couple of megabytes (indexes included).=0A>=
=0A> Can anyone please explain why this is happening and how we can=0A> get=
 around it?=0A=0AIt sounds like it could be an issue which will be fixed in=
 the next=0Aminor release:=0A=0Ahttp://git.postgresql.org/gitweb/?p=3Dpostg=
resql.git;a=3Dcommit;h=3D5454344b968d6a189219cfd49af609a3e7d6af33=0A=0AIf t=
his is the issue, a periodic manual VACUUM of the master table=0Ashould hel=
p for now.=A0 When 9.1.8 is released you might want to=0Aupgrade without a =
lot of delay, and see if you are then OK without=0Athe manual vacuums.=0A=
=0A-Kevin