Re: understanding postgres issues/bottlenecks

On Sat, 10 Jan 2009, Ron wrote:

> 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.

I've seen datacenters with redundant UPSs go dark unexpectedly. it's less
common, but it does happen.

> Not that your fsync setting is going to matter much in the event of
> catastrophes in the physical environment...

questionable, but sometimes true. in the physical environment disasters
you will loose access to your data for a while, but after the drives are
dug out of the rubble (or dried out from the flood) the data can probably
be recovered.

for crying out loud, they were able to recover most of the data from the
hard drives in the latest shuttle disaster.

> Like anything else, there is usually more than one way to reduce risk while
> at the same time meeting (realistic) performance goals.

very true.

>> >> ...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.

this is definantly not the case.

fsync off the data stays in memory and may never end up being sent to the
drive. RAM speeds are several orders of magnatude faster than the
interfaces to the drives (or even to the RAID controllers in high-speed
slots)

it may be that it's fast enough (see the other posts disputing that), but
don't think that it's the same.

David Lang

> 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
>
>