Re: turning fsync off for WAL - Mailing list pgsql-general

"Ram Ravichandran" <ramkaka@gmail.com> 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!