Re: Unusually high IO for autovacuum worker

On Fri, Feb 1, 2013 at 1:44 PM, Vlad Bailescu <vlad(at)mojitosoftware(dot)com> wrote:
> Hi everyone,
>
> We are experiencing a strange situation after upgrading our Postgres from
> 8.4 to 9.1 and our OS from Debian 6 to Ubuntu 12.04 LTS.
>
> We have an application where we are collecting timestamp-based data. Since a
> month of data goes over 30M rows (and growing each month), we decided to
> partition the data on an year-and-month basis. We basically have one master
> table and partition tables like master_y2013m01 and so on.
>
> 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?

Do you know for sure that its the master table that generating all the
vacuum traffic ? What about the partition tables ? Do they get any
updates/deletes ? It might be useful if you could turn autovacuum
logging ON and see which tables are getting vacuumed and correlate
that with the increased IO activity that you're seeing.

Did you change any other parameters like checkpoint timeout/segments
etc ? It might be worthwhile to log checkpoint activities as well to
be doubly sure.

Thanks,
Pavan

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