Re: understanding postgres issues/bottlenecks

On Sat, Jan 10, 2009 at 2:56 PM, Ron <rjpeace(at)earthlink(dot)net> 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.

We had an electrician working who was supposed to have a tray
underneath their work. They didn't. A tiny bit of copper flew into a
power conditioner. The power conditioner blew out, fed back to the
other two power conditionsers, which blew, they fed back to the UPSs
and blew them up, the power surge blew out the switch to allow the
diesel generator to take over. We were running 100ft extension cables
from dirty wall power sockets all over the building to get the hosting
center back up. There were about 12 or so database servers. The only
one that came back up without data loss or corruption was mine,
running pgsql. The others, running Oracle, db2, Ingress and a few
other databases all came back up with corrupted data on their drives
and forced nearly day long restores.

There is no protection against a kernel crash or a power loss that is
absolute. And don't ever believe there is. Human error is always a
possibility you have to be prepared to deal with.

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

Sure it will. SCSI cable gets pulled out, power supply fails, mobo
just dies outright, the above mentioned situation with the power being
lost to the data center. Meteor strikes, not so much.