Re: Unusually high IO for autovacuum worker

Vlad Bailescu <vlad(at)mojitosoftware(dot)com> wrote:

> Because of our application stack (Hibernate ORM) we use a
> before_insert trigger to insert rows into the right partition and
> in the master table and then an after_insert trigger to delete
> them from master table (we need the original insert to return the
> inserted row info). This means the master table becomes quickly
> populated with dead tuples and triggers the autovacuum frequently
> (we are using default settings for autovacuum).
>
> After upgrading our DB from 8.4 to 9.1 (and moving from Debian 6
> to Ubuntu 12.04) we noticed a large increase in IO and traced it
> back to auto vacuum: iotop and collectd report bursts of 4-6M/s
> taking 90 seconds followed by a 30 seconds pause. That's a lot of
> data to chew on especially since the master table is only a
> couple of megabytes (indexes included).
>
> Can anyone please explain why this is happening and how we can
> get around it?

It sounds like it could be an issue which will be fixed in the next
minor release:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5454344b968d6a189219cfd49af609a3e7d6af33

If this is the issue, a periodic manual VACUUM of the master table
should help for now.  When 9.1.8 is released you might want to
upgrade without a lot of delay, and see if you are then OK without
the manual vacuums.

-Kevin