Re: [PERFORM] Vacuuming problems on TOAST table

2012-02-29 Thread Robert Haas 
On Wed, Feb 8, 2012 at 2:59 PM, Ofer Israeli of...@checkpoint.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 t...@sss.pgh.pa.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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Vacuuming problems on TOAST table

2012-02-08 Thread Tom Lane 
Ofer Israeli of...@checkpoint.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?

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Vacuuming problems on TOAST table

2012-02-08 Thread Ofer Israeli 
Tom Lane wrote:
 Ofer Israeli of...@checkpoint.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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance