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
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) |