On Mon, 2006-06-19 at 16:39, Wes wrote: > On 6/19/06 3:47 PM, "Jim Nasby" <jnasby@xxxxxxxxxxxxx> wrote: > > > FWIW, RAID5 isn't normally a good recipe for good database performance. > > Understood, but RAID 1 isn't always feasible. The database performs very > well with the current hardware configuration - I have no complaints. The > only time we have issues is during index rebuilds or a reload. I think Jim was thinking of RAID 1+0. If you gotta use RAID 5, at least have a contoller with several hundred megs of battery backed cache. Better yet, use a RAID controller with a gig or so of BBU cache and run RAID 1+0 on it. If you can. > > Also, why are you frequently dumping and restoring? > > We have to periodically reindex due to the fact that vacuum processes > indexes in index order instead of disk space order. A 'vacuum full' is > currently taking about 24 hours. After a reindex, I expect that to drop to > 3-4 hours until the index becomes fragmented. I don't think much can be > done about the time to reindex (or the order vacuum processes indexes). Actually, the same question about why your frequently dumping and restoring applies to full vacuums. Why are you doing them? A properly running database should not need vacuum fulls. Nor reindexing. Standard vacuums, scheduled to run often enough, combined with a large enough Free Space Map should mean never needing a full vacuum or reindex. If vacuums slow your system down too much, then you don't have enough I/O bandwidth, and need to see the previous point about a better RAID setup. You should look into slony. You can replicate from one version of pgsql to another, a feature intended to be used for updating. That way, you can put an 8.1 server behind your 7.4 or 8.0 server, replicate to it, wait for it to catch up, shut down your app, fail over your server to the 8.1 machine and viola, you're on 8.1 with minimum downtime.
