Waldo Nell <pwnell@telkomsa.net> wrote:
> The fsync = off was because the production system runs on a uber
> expensive SAN system with multipathing over Fibre Channel, it is
> on UPS and backup generators in a secure datacenter, and we have
> daily backups we can fall back to.
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.
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.
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.
-Kevin