Manual Vaccum very slow with Autovaccum enabled

From: "Andy Dale" <andy(dot)dale(at)gmail(dot)com>
To: "pgsql general" <pgsql-general(at)postgresql(dot)org>
Subject: Manual Vaccum very slow with Autovaccum enabled
Date: 2007-07-31 07:05:11
Message-ID: faa313130707310005o5f931a15p98aacde4ad3ad48b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I am working with a 3 Postgresql databases, 1 is configured with autovaccum
enabled with the following settings:

vacuum_cost_delay = 200 # 0-1000 milliseconds
vacuum_cost_page_hit = 6 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 100 # 0-10000 credits

===========================================================================

autovacuum = on # enable autovacuum
subprocess?
autovacuum_naptime = 30 # time between autovacuum
runs, in secs
autovacuum_vacuum_threshold = 400 # min # of tuple updates before
vacuum
autovacuum_analyze_threshold = 100 # min # of tuple updates before
analyze
autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before vacuum
autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
analyze
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovac, -1
means use
#
vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovac, -1
means use
#
vacuum_cost_limit

and the other 2 without it enabled (all the setting commented out in
postgresql.conf).

All 3 databases hold the same data (roughly 250,000 rows spread over 82
tables), this data was deleted via a java process (within an application
server) using the EntityManager (not the quickest or best way, and it will
never have to delete this much data usually). After the deletion has taken
place all the estimated rows (via pgadmin) are still as if no delete has
taken place !! but performing a count on the table reveals the true figure
0. I decided that a vaccum (analyze) might solve the problem of the
estimated rows being way off, and on the 2 machines with autovaccum not
enabled this analyze takes only a few minutes at most, but on the machine
with it enabled it takes roughly 40 - 50 mins (2882336 ms.), i do not know
why ?? does the autovaccum slow down a manually invoked vaccum ?

And the analyze does not solve pgadmins problem with the estimated rows (but
disconnecting and the reconnecting does :-))

Can anyone offer me some advice on why the vaccum takes so long on the
autovaccum machine.

Thanks in advance,

Andy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gregory Stark 2007-07-31 07:16:28 Re: Manual Vaccum very slow with Autovaccum enabled
Previous Message Ronald Rojas 2007-07-31 06:37:28 Re: [NOVICE] alter table table add column