On Thu, 2006-07-06 at 10:11, Eugeny N Dzhurinsky wrote: > On Thu, Jul 06, 2006 at 09:28:39AM -0500, Scott Marlowe wrote: > > On Thu, 2006-07-06 at 01:40, Eugeny N Dzhurinsky wrote: > > Do you add / remove tables a lot? Could be you've got system catalog > > bloat. > > Yes, almost each table is dropped and re-created in 3-5 days. > > do you have autovacuum running? > > Yes. Hopefully, that will keep the system catalogs from getting overly fat. You still need to check their size every so often to make sure they're not getting out of line. you might wanna run a vacuum verbose and see what it has to say. > > What version of pgsql are you running? > > psql -V > psql (PostgreSQL) 8.0.0 You should update. x.0.0 versions often have the nastiest of the data loss bugs of any release versions of postgresql. 8.0 is up to 8.0.8 now. The upgrades are generally painless (backup anyway, just in case) and can be done in place. just down postgres, rpm -Uvh the packages and restart postgres > > What OS? > > CentOS release 3.7 (Final) > > > What file system? > > ext3 > > > What kind of machine are you using? > > Pentium IV, 1.8 GHz > > > How much memory does it have? > > 512 Mb RAM That's kind of small for a database server. If your data set is fairly small it's alright, but if you're working on gigs of data in your database, the more memory the better. > > How many disk drives? > > single OTOH, if you're doing a lot of committing / writing to the hard drives, a single disk drive is suboptimal Is this SCSI, PATA or SATA? If it's [SP]ATA, then you've likely got no real fsyncing, and while performance won't be a problem, reliability should the machine crash would be. If it's SCSI, then it could be a bottle neck for writes. > > Are you using RAID? hardware / software? battery backed cache or no? > > no I'd recommend looking into it, unless you're CPU bound. A decent RAID controller with battery backed cache and a pair of drives in a mirror setup can be a marked improved to start with, and you can add more drives as time goes by if needs be. My guess is that you've got sys catalog bloat. You might have to down the database to single user mode and run a vacuum on the system catalogs from there. That's how it was in the old days of 7.x databases anyway. If you don't have sys cat bloat, then you're probably CPU / memory bound right now. unless you're writing a lot, then you're likely disk i/o bound, but I kinda doubt it.
