From 03f49f88786c9eefcb850927958646266f11a86d Mon Sep 17 00:00:00 2001
From: arno <arno@renevier.net>
Date: Wed, 15 Sep 2010 16:19:09 +0200
Subject: [PATCH] vacuum tables manually

---
 scripts/crontab.syj       | 4 ++++
 scripts/updategeoip.sh    | 1 +
 scripts/updategeonames.sh | 1 +
 3 files changed, 6 insertions(+)

diff --git a/scripts/crontab.syj b/scripts/crontab.syj
index 78d48be..4dfe865 100644
--- a/scripts/crontab.syj
+++ b/scripts/crontab.syj
@@ -5,3 +5,7 @@ MAILTO=arno@renevier.net
 12 3 3 * * /data/project/syj/scripts/updategeoip.sh
 # every day, delete old sessions files
 47 4 * * * find /tmp/ -name "sess_*" -user syj  -ctime +45 -exec rm -f '{}' \;
+# every day, vacuum tables
+48 4 * * * psql syj syj -c "VACUUM ANALYZE users"
+48 4 * * * psql syj syj -c "VACUUM ANALYZE paths"
+48 4 * * * psql syj syj -c "VACUUM ANALYZE pending_actions"
diff --git a/scripts/updategeoip.sh b/scripts/updategeoip.sh
index eb9327e..1626bb3 100755
--- a/scripts/updategeoip.sh
+++ b/scripts/updategeoip.sh
@@ -81,3 +81,4 @@ unzip ${GEOIPDB##*/} $GEOIPCVS
 
 # insert all values from csv to database
 sed -e 's/"\([^"]\+\)","\([^"]\+\)","\([^"]\+\)","\([^"]\+\)","\([^"]\+\)","\([^"]\+\)"/INSERT INTO geoip (begin_ip, end_ip, country) VALUES ('\''\3'\'','\''\4'\'','\''\5'\'');/' $GEOIPCVS | psql --set "ON_ERROR_STOP=1" -f -
+psql --set "ON_ERROR_STOP=1" -c "VACUUM ANALYZE geoip;"
diff --git a/scripts/updategeonames.sh b/scripts/updategeonames.sh
index ff5c8cd..04712fe 100755
--- a/scripts/updategeonames.sh
+++ b/scripts/updategeonames.sh
@@ -72,4 +72,5 @@ for line in $(cat ${GEONAMESXML##*/}); do
 done
 echo "INSERT INTO geonames (country, minlon, minlat, maxlon, maxlat) VALUES ('EU', -26, 34, 40, 68);" >> $TMPFILE
 echo "INSERT INTO geonames (country, minlon, minlat, maxlon, maxlat) VALUES ('AP', 90, -20, -140, 68);" >> $TMPFILE
+echo "VACUUM ANALYZE geonames;" >> $TMPFILE
 psql --set "ON_ERROR_STOP=1" -f $TMPFILE
-- 
2.39.2