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

Re: Unusually high IO for autovacuum worker

From: Pavel Stehule <pavel(dot)stehule(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 08:29:23
Message-ID: CAFj8pRA+iHOhqM=7xFSj+k+zUNnRGBh=XdVO5DUScde+TMP4oQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
2013/2/1 Vlad Bailescu <vlad(at)mojitosoftware(dot)com>:
> 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).

we had similar problem - you can try to move stat file to ramdisk with
setting stats_temp_directory with target on somewhere on ramdisk

http://www.postgresql.org/message-id/CAFj8pRCRPv3i-9WwsSCa=my9M+J5B7K1i1c7TWi0pFLRyjkpWQ@mail.gmail.com

Regards

Pavel Stehule


>
> Can anyone please explain why this is happening and how we can get around
> it?
>
> Thank you,
> Vlad


In response to

pgsql-hackers by date

Next:From: Pavan DeolaseeDate: 2013-02-01 09:01:46
Subject: Re: Unusually high IO for autovacuum worker
Previous:From: Mark KirkwoodDate: 2013-02-01 08:16:10
Subject: Re: Cascading replication: should we detect/prevent cycles?

pgsql-general by date

Next:From: Pavan DeolaseeDate: 2013-02-01 09:01:46
Subject: Re: Unusually high IO for autovacuum worker
Previous:From: Vlad BailescuDate: 2013-02-01 08:14:29
Subject: Unusually high IO for autovacuum worker

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