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

Re: Unusually high IO for autovacuum worker

From: Pavan Deolasee <pavan(dot)deolasee(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 11:08:43
Message-ID: CABOikdOnvVxTtUQkn7-knknPSSubrHv-innssAj1TEdtSSkDeg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On Fri, Feb 1, 2013 at 3:24 PM, Vlad Bailescu <vlad(at)mojitosoftware(dot)com>wrote:

>
>
> Stats show only 3 tables get frequently autovacuumed
>
>     Table Name Tuples inserted Tuples updated Tuples deleted Tuples HOT
> updated Live Tuples Dead Tuples Last vacuum Last autovacuum Last analyze Last
> autoanalyze Vacuum counter Autovacuum counter Analyze counter Autoanalyze
> counter Size  vehicle_position 4689127 0 4689127 0 0 1985
> 2013-02-01 11:46:46.441227+02 2013-01-31 14:48:00.775864+02 2013-02-01
> 11:46:46.494234+02 0 3761 1 3754 4016 kB  vehicle 2124 934640 0 924003
> 2124 390
> 2013-02-01 11:47:46.067695+02 2013-02-01 11:47:46.325444+02 0 1293 0 2038 2448
> kB  input_current 10032 1649206 0 1635902 10032 1728
> 2013-02-01 11:45:46.0681+02 2013-02-01 11:45:46.215048+02 0 442 0 1294 2336
> kB
>

So sure there are more than just one table involved here. Also, the last
column in the
output above shows that these are fairly large tables. In fact, if the last
column is to be
trusted, they are as large 37GB, 20GB and 12GB respectively. Note the kB at
the end of
those values. Also given the large number of inserts/deletes, I would doubt
if the table can
really remain that small. May be auto-vacuum is truncating most of it and
thats why you
see only a few MBs before it swells again by the time next vacuum cycles
runs.

Whats your rate of insertion/deletion in the first table ? You also seem to
be updating the
last two tables quite heavily, but most of them are HOT updates, so your
tables should not
bloat too much.

Thanks,
Pavan

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

In response to

Responses

pgsql-hackers by date

Next:From: Craig RingerDate: 2013-02-01 11:12:24
Subject: Re: Visual Studio 2012 RC
Previous:From: Kohei KaiGaiDate: 2013-02-01 10:34:38
Subject: Re: [v9.3] writable foreign tables

pgsql-general by date

Next:From: Alexander FarberDate: 2013-02-01 11:30:32
Subject: Re: Deleting 173000 records takes forever, blocks async queries for unrelated records
Previous:From: Bèrto ëd SèraDate: 2013-02-01 10:53:43
Subject: Re: Deleting 173000 records takes forever, blocks async queries for unrelated records

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