nightly vacuum

From: "Lee Keel" <lee_keel(at)trimble(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: nightly vacuum
Date: 2008-09-16 14:36:12
Message-ID: 944DCD961F4CD649BCEBB52E3C929F6502708179@usd-am-xch-01.am.trimblecorp.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all,

I have been playing around with the automated vacuum, but I can't seem
to get it to perform as desired.

My Situation:

I have 15 or so databases that I am deleting all the data in them and
re-importing on a nightly basis. (Long story here, but basically I
found using the copy command was the fastest way to get the data into
this read-only system from Sql Server 2000.) A couple of the databases
are small and only take about 15 minutes to copy all the data, but
others are much larger and take 45 minutes or more. So, I found that
the databases run a lot faster if I perform a full vacuum on them. Duh!
This got me to thinking I should set this up to automatically perform
this vacuum each night after I got done with the import. Of course I
then figured out that the built in automated vacuums were not based on
time, they were more intelligently based on data change. I have been
playing with different settings, but can't seem to get the databases to
automatically backup.

My Setup:

Postgres 8.2 w\ postgis ext

Windows Server 2003 Service Pack 2

My Settings:

vacuum_cost_delay 20
enabled

vacuum_cost_limit 200
disabled

vacuum_cost_page_dirty 20 disabled

vacuum_cost_page_hit 1
disabled

vacuum_cost_page_miss 10 disabled

autovacuum on
enabled

autovacuum_analyze_scale_factor 0.02 enabled

autovacuum_analze_threshold 25 enabled

autovacuum_freeze_max_age 200,000,000 enabled

autovacuum_naptime 120min
enabled

autovacuum_vacuum_cost_delay 20 enabled

autovacuum_vacuum_cost_limit 20 enabled

autovacuum_vacuum_scale_factor 0.02 enabled

autovacuum_vacuum_threshold 50 enabled

My Question:

Could someone point me to which settings I should be looking at
(especially if not in the list above) that I should be changing to get
this full vacuum to perform?

Thanks in advance,

Lee Keel

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2008-09-16 14:39:20 Re: client_encoding / postgresql strange behaviour
Previous Message Andrew Sullivan 2008-09-16 14:31:46 Re: Obfuscated stored procedures (was Re: Oracle and Postgresql)