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

Unusually high IO for autovacuum worker

From: Vlad Bailescu <vlad(at)mojitosoftware(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unusually high IO for autovacuum worker
Date: 2013-02-01 08:14:29
Message-ID: CABrmO8rEvvbBfhY-NxW2AkNr+3aWdzXEPgWkgrNFLHvTipSHyw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
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?

Thank you,
Vlad

Responses

pgsql-hackers by date

Next:From: Mark KirkwoodDate: 2013-02-01 08:16:10
Subject: Re: Cascading replication: should we detect/prevent cycles?
Previous:From: Jeevan ChalkeDate: 2013-02-01 08:01:31
Subject: Re: pg_dump --pretty-print-views

pgsql-general by date

Next:From: Pavel StehuleDate: 2013-02-01 08:29:23
Subject: Re: Unusually high IO for autovacuum worker
Previous:From: Pavel StehuleDate: 2013-02-01 05:50:23
Subject: Re: Optimizing query?

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