Re: Adding foreign key constraints without integrity

On 6/19/06 3:47 PM, "Jim Nasby" <jnasby(at)pervasive(dot)com> 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