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

Re: Vacuuming problems on TOAST table

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Ofer Israeli <oferi(at)checkpoint(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, Netta Kabala <nettak(at)checkpoint(dot)com>, Olga Vingurt <olgavi(at)checkpoint(dot)com>
Subject: Re: Vacuuming problems on TOAST table
Date: 2012-02-29 18:45:25
Message-ID: CA+Tgmoa-yJAPtrXMRak3P7vWMYCZ-78rHp-9Ec8JmSno14s9ZQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Feb 8, 2012 at 2:59 PM, Ofer Israeli <oferi(at)checkpoint(dot)com> wrote:
> The settings we used were not in the postgresql.conf file, but rather an update of the pg_autovacuum table where we set the vac_cost_limit to 2000.  The reason for this being that we wanted this definition only for the big (TOASTed) table I was referring to.
>
> The logged settings in the ~400 second case were:
> autovac_balance_cost(pid=6224 db=16385, rel=17881, cost_limit=10, cost_delay=1)
>
> Which comes as quite a surprise as it seems that the cost_limit is not set or am I missing something?

That doesn't look right, but without step-by-step directions it will
be hard for anyone to reproduce this.  Also, what version are you
testing on?  pg_autovacuum was removed in PostgreSQL 8.4, so you must
be using PostgreSQL 8.3 or earlier.

You might at least want to make sure you're running a late enough
minor version to have this fix:

Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Branch: master Release: REL9_1_BR [b58c25055] 2010-11-19 22:29:44 -0500
Branch: REL9_0_STABLE Release: REL9_0_2 [b5efc0940] 2010-11-19 22:28:25 -0500
Branch: REL8_4_STABLE Release: REL8_4_6 [fab2af30d] 2010-11-19 22:28:30 -0500
Branch: REL8_3_STABLE Release: REL8_3_13 [6cb9d5113] 2010-11-19 22:28:35 -0500

    Fix leakage of cost_limit when multiple autovacuum workers are active.

    When using default autovacuum_vac_cost_limit, autovac_balance_cost relied
    on VacuumCostLimit to contain the correct global value ... but after the
    first time through in a particular worker process, it didn't, because we'd
    trashed it in previous iterations.  Depending on the state of other autovac
    workers, this could result in a steady reduction of the effective
    cost_limit setting as a particular worker processed more and more tables,
    causing it to go slower and slower.  Spotted by Simon Poole (bug #5759).
    Fix by saving and restoring the GUC variables in the loop in do_autovacuum.

    In passing, improve a few comments.

    Back-patch to 8.3 ... the cost rebalancing code has been buggy since it was
    put in.

Also:

> And one more thing that seems a bit strange - after a 1-minute run, we would
> expect to see 1700 Tuples Updated (100*17), but instead we see 1700 Tuples
> Inserted (and no deletes).

I don't think TOAST ever updates chunks in place.  It just inserts and
deletes; or at least I think that's what it does.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

pgsql-performance by date

Next:From: Igor SchteinDate: 2012-02-29 20:37:56
Subject: Performance of SQL Function versus View
Previous:From: Robert HaasDate: 2012-02-29 18:35:37
Subject: Re: How to improve insert speed with index on text column

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