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

Re: Vacuuming problems on TOAST table

From: Ofer Israeli <oferi(at)checkpoint(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "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-08 19:59:52
Message-ID: 217DDBC2BB1E394CA9E7446337CBDEF20102C056BFC9@il-ex01.ad.checkpoint.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Ofer Israeli <oferi(at)checkpoint(dot)com> writes:
>> During our testing we see that the table size increases
>> substantially.  When looking at the autovacuum log, set with default
>> configuration, it seems that it ran for around 60 seconds (see below
>> and note that this was a 1-minute test, i.e. only 100 updates)!   
> 
> autovacuum is intended to run fairly slowly, so as to not consume too
> much resources.  If you think it's too slow you can adjust the
> autovacuum_cost tunables.  
> 
>> When setting a higher cost for the autovacuum, tried values of 2000,
>> it ran for even longer: ~400 seconds! 
> 
> That's the wrong direction, no?

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?


Thanks,
Ofer


In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2012-02-08 20:33:08
Subject: Re: pl/pgsql functions outperforming sql ones?
Previous:From: Kevin TrasterDate: 2012-02-08 19:58:57
Subject: Re: index scan forward vs backward = speed difference of 357X slower!

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