Re: understanding postgres issues/bottlenecks

At 10:36 AM 1/10/2009, Gregory Stark wrote:
>"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
>
> > On Sat, Jan 10, 2009 at 5:40 AM, Ron <rjpeace(at)earthlink(dot)net> wrote:
> >> At 03:28 PM 1/8/2009, Merlin Moncure wrote:
> >>> just be aware of the danger . hard reset (power off) class of failure
> >>> when fsync = off means you are loading from backups.
> >>
> >> That's what redundant power conditioning UPS's are supposed to
> help prevent
> >> ;-)
> >
> > But of course, they can't prevent them, but only reduce the likelihood
> > of their occurrance. Everyone who's working in large hosting
> > environments has at least one horror story to tell about a power
> > outage that never should have happened.
>
>Or a system crash. If the kernel panics for any reason when it has dirty
>buffers in memory the database will need to be restored.
A power conditioning UPS should prevent a building wide or circuit
level bad power event, caused by either dirty power or a power loss,
from affecting the host. Within the design limits of the UPS in
question of course.

So the real worry with fsync = off in a environment with redundant
decent UPS's is pretty much limited to host level HW failures, SW
crashes, and unlikely catastrophes like building collapses, lightning
strikes, floods, etc.
Not that your fsync setting is going to matter much in the event of
catastrophes in the physical environment...

Like anything else, there is usually more than one way to reduce risk
while at the same time meeting (realistic) performance goals.
If you need the performance implied by fsync off, then you have to
take other steps to reduce the risk of data corruption down to about
the same statistical level as running with fsync on. Or you have to
decide that you are willing to life with the increased risk (NOT my
recommendation for most DB hosting scenarios.)

> >> ...and of course, those lucky few with bigger budgets can use
> SSD's and not
> >> care what fsync is set to.
> >
> > Would that prevent any corruption if the writes got out of order
> > because of lack of fsync? Or partial writes? Or wouldn't fsync still
> > need to be turned on to keep the data safe.
>
>I think the idea is that with SSDs or a RAID with a battery backed cache you
>can leave fsync on and not have any significant performance hit since the seek
>times are very fast for SSD. They have limited bandwidth but bandwidth to the
>WAL is rarely an issue -- just latency.
Yes, Greg understands what I meant here. In the case of SSDs, the
performance hit of fsync = on is essentially zero. In the case of
battery backed RAM caches for RAID arrays, the efficacy is dependent
on how the size of the cache compares with the working set of the
disk access pattern.

Ron