Change Request: database cleanup

[Toshio Kuratomi]

A couple weeks ago we were having problems with xen13.  db2 was on
xen13.  We moved the databases that lived on db2 onto db3.  That's been
working out pretty well as db3 was sized to run koji before the latest
round of koji optimizations so it's a pretty powerful box.

However, when we moved the databases we left out the scripts that clean
up the session tables for FAS.  This means that everytime a user hits
one of our websites, it makes the FAS database grow.  Currently the FAS
db is over 2GB in size with 860MB of that in the visit table.

We can't reclaim the space without running a vacuum full (or dropping
and recreating that table) which would mean downtime.  However, we don't
seem to have any performance issues at this time so I'm mostly concerned
with the table continuing to grow rather than getting the space back.

I'd like to apply the following change in puppet which just installs the
cleanup script and cron job onto db3 where it can start working to keep
the visit table at its present level.  When we switch back to having db2
 separate from db3 we can disable this script on db3 and drop the fas2
database there (which will recover the space).

diff --git a/manifests/services/db.pp b/manifests/services/db.pp
index 8e7587f..e5a6409 100644
--- a/manifests/services/db.pp
+++ b/manifests/services/db.pp
@@ -52,6 +52,17 @@ class kojiDb inherits postgresServer {
         source => "system/koji-cleanup-sessions.cron"
     }

+    cron { db-cleanup-sessions:
+        command => "/usr/local/bin/db-cleanup-sessions",
+        user => postgres,
+        minute => 10,
+        ensure => present,
+    }
+
+    script { '/usr/local/bin/db-cleanup-sessions':
+        source => 'db/db-cleanup-sessions',
+        require => Package['postgresql8.3-server'],
+    }
 }

 class appDB inherits postgresServer {


-Toshio

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: OpenPGP digital signature
Url : http://lists.fedoraproject.org/pipermail/infrastructure/attachments/20090422/3a189ba9/attachment.bin