On 29/09/2010 8:16 PM, stagirus wrote:
>
> My team is testing with Postgresql 9.0 again. They posted me mixed (some
> positive) updates.
>
> Now I have a better feel for the type of information you need to resolve
> these kinds of issues. We will get more specifics (stack trace, possibly the
> test code). Let us all hold off on this until then.
>
> You are all a wonderful group - very responsive. I feel we are in the good
> hands for choosing Postgresql as the primary platform for our Cloud
> deployment. Thank you very much for all your help so far.
Beware of the Cloud™ fad. PostgreSQL is a robust database, but it
requires the hardware to be honest and reliable. Hardware/operating
systems that lie and claim that fsync() has completed and all data is on
permanent storage when in fact that's not true *will* cause data loss if
that system ever suffers from an OS crash, power loss, etc.
This is particularly critical because of the way people treat cloud
instances as somewhat disposable. You *must* test to make sure that your
hosting provider honours the basic rules for system calls like fsync();
they might well cheat because not honouring the rules makes the instance
seem "faster" and reduces load on their storage systems. Pity that's at
the expense of your data.
I've seen enough reports about Amazon EC2 to be particularly suspicious
of that platform. I'd actually be interested to know if anyone with EC2
access has benchmarked fsync() to see if they get faintly sane values or
unrealistically high "OS/hardware/SAN is lying" values.
Note that PostgreSQL isn't unique in requiring reliable fsync() and disk
storage. However, because it's capable of quite concurrent write loads
and uses an MVCC storage design with write-ahead log, it's certainly
more prone to being bitten by bad OS/hardware than simpler storage
implementations might be.
Test carefully.
On normal "real" hardware, you'd chuck together a trivial C program to
run on your machine. This program would:
0) Open a file
1) Write a couple of bytes to the file
2) Record the current wall clock time
3) call fsync() on the file handle
4) Record the wall clock time when fsync() returns
5) Compare the difference in wall clock times, and print that
to stdout or a log file
6) Repeat from 1
You can then examine the results to see what the min/mean/max completion
times for fsync() were, and how many fsync() calls you're getting done
per second on average. If it's more than a few hundred then either you
have a fast write cache on your storage (hope it's battery backed!) or
the fsync() call is extremely likely to be doing nothing.
Unfortunately, this approach may not tell you everything on virtualized
"cloudy" hardware. If it's backed by some kind of power-protected
write-caching SAN, your VM might well get hundreds of fsync()s done per
second and still be able to guarantee recovery.
The only real way to be sure is to repeatedly crash the VM in the middle
of write activity, and make sure the database comes back up in a
consistent state.
--
Craig Ringer
Tech-related writing at http://soapyfrogs.blogspot.com/
