Re: Unusually high IO for autovacuum worker

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