------=_Part_4563_8311695.1212465859287
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
>
>
> 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?
>
Given that I am only worried about WAL being persistent, are these other
issues
still pertinent? I am sorry I am such a newbie.
>
> 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 was talking only of WAL. Basically, I am just trying to make sure if my
EC2 instance goes down,
I will be able to recover by replaying my write-ahead-logs. I am assuming
checkpoints are for the
actual tables on the disk (And not for logging / backup). Am I correct?
> 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.
>
This seems like a much better idea. So, I should
a) disable synchronous_commit
b) set wal_writer_delay to say 1 minute (and leave fsync on)
c) symlink pg_xlog to the PersistentFS on S3.
If there is a crash, I should be able to restore entirely from the WAL
logs.
Although, doesn't
this have the same problem as disabling the fsyncs?
BTW, if the wal_writer_delay is too long, then the fsyncs to flush dirty
buffers would also fsync the
WAL right? Is that bad (as far as data integrity), or is it just that the
fsyncs would be more frequent?
Thanks everyone for all the help.
Ram
------=_Part_4563_8311695.1212465859287
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
<div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0
0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div
class="Ih2E3d"><br>
</div>Wow, this is a fascinating situation. Are you sure the fsyncs are
the only<br>
thing to worry about though? Postgres will call write(2) many times even
if<br>
you disabled fsync entirely. Surely the kernel and filesystem will
eventually<br>
send some of them through even if no fsyncs
arrive?<br></blockquote><div><br></div><div>Given that I am only worried
about WAL being persistent, are these other issues </div><div>still
pertinent? I am sorry I am such a newbie.</div>
<div> </div><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex;">
<br>
Is it only fsyncs on the write-ahead-log that matter? Or on the data as
well?<br>
Checkpoints fsync the data files. The logs are fsynced on every commit
and<br>
also whenever a buffer has to be flushed if the logs for the last changes
in<br>
that buffer haven't been synced yet.</blockquote><div><br></div><div>I
was talking only of WAL. Basically, I am just trying to make sure if my EC2
instance goes down, </div><div>I will be able to recover by replaying
my write-ahead-logs. I am assuming checkpoints are for
the </div>
<div>actual tables on the disk (And not for logging / backup). Am I
correct?</div><div><br></div><div><br></div><blockquote
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex;"><div class="Ih2E3d">
<br>
</div>There actually is an option in Postgres to not call fsync. However
your fear<br>
is justified. If your file system can flush buffers to disk in a
different<br>
order than they were written (and most can) then it's possible for a
database<br>
with fsync off to become corrupted. Typical examples would be things
like<br>
records missing index pointers (or worse, index pointers to wrong
records), or<br>
duplicate or missing records (consider if an update is only partly
written).<br>
<br>
This is only an issue in the event of either a kernel crash or power
failure<br>
(whatever that means for a virtual machine...). In which case the only
safe<br>
course of action is to restore from backup. It's possible that in the
context<br>
of Amazon these would be rare enough events and restoring from backups
easy<br>
enough that that might be worth considering?<br>
<br>
However a safer and more interesting option with Postgres 8.3 would be
to<br>
disable "synchronous_commit" and set a very large
wal_writer_delay.<br>
Effectively this would do the same thing, disabling fsync for every<br>
transaction, but not risk the data integrity.<br>
<br>
The default wal_writer_delay is 200ms meaning 5 fsyncs per second but
you<br>
could raise that substantially to get fewer fsyncs, possibly into the
range of<br>
minutes. If you raise it *too* far then you'll start observing fsyncs
due to<br>
processing being forced to flush dirty buffers before their changes have
been<br>
logged and synced. The only way to raise that would be to increase the<br>
shared_buffers which would have complex
effects.<br></blockquote><div><br></div><div>This seems like a much better
idea. So, I should </div><div>a) disable
synchronous_commit </div><div>b) set wal_writer_delay to
say 1 minute (and leave fsync on)</div>
<div>c) symlink <span class="Apple-style-span" style="font-family:
-webkit-monospace; font-size: 14px; line-height: 18px;
">pg_xlog <span class="Apple-style-span" style="font-family: arial;
font-size: 13px; line-height: normal; ">to the PersistentFS on
S3.</span></span></div>
<div><br></div><div>If there is a crash, I should be able to restore
entirely from the WAL logs. Although, doesn't </div><div>this
have the same problem as disabling the
fsyncs? </div><div><br></div><div>BTW, if the wal_writer_delay is too
long, then the fsyncs to flush dirty buffers would also fsync
the</div>
<div>WAL right? Is that bad (as far as data integrity), or is it just that
the fsyncs would be more frequent?</div><div><br></div><div>Thanks everyone
for all the help.</div><div><br></div><div>Ram<br></div><div><br></div>
<div><br></div></div>
------=_Part_4563_8311695.1212465859287--
|
