Thread: nightly vacuum
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
"Lee Keel" <lee_keel@trimble.com> writes:
> 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!
Not so much "duh" as "maybe you should change your data import process".
It sounds like you're deleting old data with DELETE and then loading new.
Can you use TRUNCATE instead of DELETE?
Autovacuum won't *ever* do VACUUM FULL, and in a well-run database you
shouldn't need to do it manually either.
regards, tom lane
>-----Original Message----- >From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > >"Lee Keel" <lee_keel@trimble.com> writes: >> 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! > >Not so much "duh" as "maybe you should change your data import process". >It sounds like you're deleting old data with DELETE and then loading new. >Can you use TRUNCATE instead of DELETE? > >Autovacuum won't *ever* do VACUUM FULL, and in a well-run database you >shouldn't need to do it manually either. > > regards, tom lane > ------------------------------------------------- Tom, Thanks for your reply. I have found that I am using delete instead of truncate and I can fix this. As for the vacuum full, I have reread the help and realize where I was under the wrong impression, but even the vacuum analyze does not appear to be running with my current settings. And according to the help and all that I do know about dbs, I should at least perform a vacuum analyze and\or reindex on a regular basis. So can you point me to any of the settings that I may have wrong to have this done for me automatically? Please know that I know I am not a dba, but I do consider myself a little db savy. So I am not trying to be a smarta~~, I just want to learn more about databases and better my knowledge in that area. Thanks for the help, Lee
In response to "Lee Keel" <lee_keel@trimble.com>: > >-----Original Message----- > >From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > > >"Lee Keel" <lee_keel@trimble.com> writes: > >> 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! > > > >Not so much "duh" as "maybe you should change your data import > process". > >It sounds like you're deleting old data with DELETE and then loading > new. > >Can you use TRUNCATE instead of DELETE? > > > >Autovacuum won't *ever* do VACUUM FULL, and in a well-run database you > >shouldn't need to do it manually either. > > > > regards, tom lane > > > ------------------------------------------------- > Tom, > > Thanks for your reply. I have found that I am using delete instead of > truncate and I can fix this. > > As for the vacuum full, I have reread the help and realize where I was > under the wrong impression, but even the vacuum analyze does not appear > to be running with my current settings. And according to the help and > all that I do know about dbs, I should at least perform a vacuum analyze > and\or reindex on a regular basis. So can you point me to any of the > settings that I may have wrong to have this done for me automatically? If this database is read-only aside from your massive uploads, then the following process will suffice: 1) Truncate all tables 2) Upload new data 3) analyze database You only need periodic vacuum/analyze if you're modifying the data. It's likely that autovacuum isn't doing anything because there's nothing to do. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023