Postgres Vacuum Update

[Toshio Kuratomi]

* vacstats has collected stats on all of our databases except koji

* vacstats.py now has an analyze mode.  You can use it to create a 
reasonable default vacuum policy.

* I've used vacstats to construct vacuum scripts for all of our 
databases except koji.  I've pushed them out to db2 and am watching them 
to make sure that they run fine today.  I don't anticipate a problem 
but I'll have to evaluate whether to add a weekly script or turn the 
dailies into every other day vacuumings once we add the koji tables.

* We've added three tables from koji to the hourlies since we know that 
they are high update tables that need to be vacuumed frequently.

* The vacstat.py check mode now checks if we're in danger of running out 
of transaction ids and prints a warning that should be emailed to 
admin at fp.o if so.  Dealing with that is a matter of performing a vacuum 
of that particular db (rather than table by table).  If the database 
happens to be koji we might want to schedule that for the weekend as the 
weekend is the lowest use time for koji.  (Note -- it will result in a 
slow down, not in an outage.)

Thanks to mbonnet for the information on the tables and queries for the 
latter two points.

At this point the things left to do are:

* Get the rest of koji vacuuming integrated into the scripts.  I'll be 
doing this in the next few days.

* Find a time when we can do a vacuum full of some tables.  The current 
vacstats algorithm suggests the following tables:

Vacuum full mirrormanager host_category_dir: Freespace Percent 
96.8641231524%, 735480624.0 Bytes
   vacuumdb -zfd mirrormanager -t host_category_dir
Vacuum full mirrormanager host: Freespace Percent 99.9622089944%, 
81626948.0 Bytes
   vacuumdb -zfd mirrormanager -t host
Vacuum full mirrormanager directory: Freespace Percent 89.7076945051%, 
7515728.0 Bytes
   vacuumdb -zfd mirrormanager -t directory
Vacuum full bodhi package_update: Freespace Percent 17.634592656%, 
311544.0 Bytes
   vacuumdb -zfd bodhi -t package_update
Vacuum full fassession visit: Freespace Percent 88.6180520143%, 554520.0 
Bytes
   vacuumdb -zfd fassession -t visit

* Decide whether to upgrade postgres from 8.1 to 8.3.  This has been 
recommended by several postgres people but there's extremely little 
chance that 8.3 will be going into RHEL5 so we'll have to decide whether 
to move to F9, backport a package to RHEL5 or just stay with 8.1 for now.

-Toshio

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: OpenPGP digital signature
URL: <http://listman.redhat.com/archives/fedora-infrastructure-list/attachments/20080206/d763506a/attachment.sig>