"Ram Ravichandran" <ramkaka@xxxxxxxxx> 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!
