"Ram Ravichandran" <ramkaka@[EMAIL PROTECTED]
> writes:
> Hey,
> I am running a postgresql server on Amazon EC2. My current plan is to
mount
> an Amazon S3 bucket as a drive using PersistentFS which is a
POSIX-compliant
> file system.
> I will be using this for write-ahead-logging. The issue with S3 is that
> though the actual storage is cheap, they charge $1 per 100,000 put
requests
> - so frequent fsyncs will
> cost me a lot.
Wow, this is a fascinating situation. Are you sure the fsyncs are the only
thing to worry about though? Postgres will call write(2) many times even
if
you disabled fsync entirely. Surely the kernel and filesystem will
eventually
send some of them through even if no fsyncs arrive?
Is it only fsyncs on the write-ahead-log that matter? Or on the data as
well?
Checkpoints fsync the data files. The logs are fsynced on every commit and
also whenever a buffer has to be flushed if the logs for the last changes
in
that buffer haven't been synced yet.
> I've been talking to the makers of persistentFS, and one possible
solution
> is for the file system to disobey fsyncs. I am trying to find out the
> implications of this method in case of a crash. Will I only lose
information
> since the last fsync? Or will the earlier data, in general, be corrupted
due
> to some out-of-order writes (I remember seeing this somewhere)?
There actually is an option in Postgres to not call fsync. However your
fear
is justified. If your file system can flush buffers to disk in a different
order than they were written (and most can) then it's possible for a
database
with fsync off to become corrupted. Typical examples would be things like
records missing index pointers (or worse, index pointers to wrong
records), or
duplicate or missing records (consider if an update is only partly
written).
This is only an issue in the event of either a kernel crash or power
failure
(whatever that means for a virtual machine...). In which case the only
safe
course of action is to restore from backup. It's possible that in the
context
of Amazon these would be rare enough events and restoring from backups
easy
enough that that might be worth considering?
However a safer and more interesting option with Postgres 8.3 would be to
disable "synchronous_commit" and set a very large wal_writer_delay.
Effectively this would do the same thing, disabling fsync for every
transaction, but not risk the data integrity.
The default wal_writer_delay is 200ms meaning 5 fsyncs per second but you
could raise that substantially to get fewer fsyncs, possibly into the
range of
minutes. If you raise it *too* far then you'll start observing fsyncs due
to
processing being forced to flush dirty buffers before their changes have
been
logged and synced. The only way to raise that would be to increase the
shared_buffers which would have complex effects.
You'll also have to look at the checkpoint_timeout and checkpoint_segments
parameters. And probably the bgwriter variables as well (lest it start
trying
to flush buffers whose changes haven't been logged yet too).
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL
training!
--
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
|
