Re: Recommended optimisations slows down PostgreSQL 8.4

On 2011-08-12, at 10:10 , Kevin Grittner wrote:

> Turning fsync off in production may be OK as long as those daily
> backups aren't in the same building as the uber expensive SAN, and
> it's really OK to fall back on a daily backup if the database server
> crashes or locks up. By the way, I never trust a backup until I
> have successfully restored from it; you should probably do that at
> least on some periodic basis if you can't do it every time.

Yes we have daily tape backups that are taken off site. And since we refresh QA from prod at least 4 times a month, we know the backups are good on a frequent basis. Very valid points.

>
> The other thing I would point out is that if you are tuning with
> different table sizes, RAM sizes, or I/O performance characteristics
> from production, the database tuning in one environment may not have
> much to do with what works best in the other environment.

My DB is an exact duplicate I took from production, however my testing is on different hardware and since I cannot afford a SAN for testing purposes, I am testing on a 7200rpm SATA drive so yeah I guess that is true... I will need to performance test on production environment.

>
> As for why the recommended settings are having paradoxical effects
> in this environment -- this advice is generally based on systems
> with fsync on and a RAID controller with battery-backed cache
> configured for write-back. I don't know how well the advice
> generalizes to a single spindle with fsync off.

Thanks, I will be sure to carry on testing on production.