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. > This won't help with constraint checking, but you should consider > turning fsync off during your restore. I didn't think about doing that, but I wonder how much it would help? The actual data load only takes about 6 hours or so. It's all the index building and constraint checking that takes the time. This time seems to be spent mostly writing temp files and sorting. > 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). I wouldn't say we dump/reload frequently. Right now I'm doing a trial run for the 8.x upgrade - I have to make sure I can complete it in the allotted time. However, about a year ago, I had to do multiple reloads in a 2 week period. We had database corruption, and had to reload from source to get a good database - multiple times. We had a defective disk array that wasn't reporting any errors but kept corrupting the database. Besides PG upgrades, I have to consider how long it takes to recover from system failures if I can't use the offline file system backup. I'll be switching to online backups once we get upgraded, but if a reload fails there, I'll again have to fall back to the weekly source backup. Wes
