The CANDIS Blog

Well, the PostgreSQL upgrade was a snap, sorta. I needed to do a full dump and restore as this was a major version change – no surprises there. What pissed me off though, is that when using the binary data type for dump files when using pg_dump (“-T c”) the resulting backup file is of no use for remote workers who aren’t at the actual console.

Let me expand on this;

This type of backup file is advertised as “more convenient” and offers more options for restore time selective data restores, data re-ording, index tricks and the like. However no matter WHAT I did, it reported and sent a copy of the current pg_restore process and all the data being restored to standard output too!! This means that basically, I was going to have the same full text of 20GB worth of database data shoved down my SSH session!

Yes – this makes the whole affair much slower!

(more…)

Well this week was fun. For some reason one of our main clusters that runs client ASP software for general office, file, email, collaboration, etc… went crazy.

First I noticed that the usual night time “Vacuum’s” that are needed to keep the PostgreSQL planar at it’s most efficient and indexes clean, was running right into the day time! It usually needed less than an hour for the 20GB database we currently have.

So after many failed attempts to get an online vacuum done. I stayed up really late, took the cluster down and did FULL vacuum. Full vacuum’s are slow, and you can’t run anything while they happen because they do full table locks, where as online vacuum’s do quick tuple/row level locks.

Anyway, database seemed speedier, but system was still sluggish. I have all data separated. Database files are on large RAID10 arrays with U320 SCSI drives spinning at 15K – split over TWO SCSI buses! Yeah tis fast. Big disks are used because it means relative to the size of the disk, more data is on the outer edge of the platters, that spin faster than the centre of the platters. I also keep PostgreSQL’s transaction log on a separate RAID1 array with 73GB 15K U320 drives as well with a 256MB battery backed cache.

(more…)