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