Re: pros/cons of using "synchronous commit=off" - AWS in particular - Mailing list pgsql-general

On Fri, Jun 20, 2014 at 8:49 AM, Larry Prikockis <lprikockis@vecna.com> wrote:
>
> On 06/20/2014 09:41 AM, Merlin Moncure wrote:
>>
>> On Thu, Jun 19, 2014 at 9:24 AM, Larry J Prikockis <lprikockis@vecna.com>
>> wrote:
>>>
>>> so from the much-loved
>>> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server page, we
>>> have
>>> this:
>>>
>>> "
>>> PostgreSQL can only safely use a write cache if it has a battery backup.
>>> See
>>> WAL reliability for an essential introduction to this topic. No, really;
>>> go
>>> read that right now, it's vital to understand that if you want your
>>> database
>>> to work right.
>>> ...
>>> For situations where a small amount of data loss is acceptable in return
>>> for
>>> a large boost in how many updates you can do to the database per second,
>>> consider switching synchronous commit off. This is particularly useful in
>>> the situation where you do not have a battery-backed write cache on your
>>> disk controller, because you could potentially get thousands of commits
>>> per
>>> second instead of just a few hundred.
>>> ...
>>> "
>>>
>>> My question is-- does it make sense to switch synchronous commit off for
>>> EBS-backed EC2 instances running postgresql at Amazon? Has anyone done
>>> any
>>> benchmarking of this change on AWS? Since EBS is a "black box" to us as
>>> end
>>> users, I have no clue what type of caching- volatile or not-- may be
>>> going
>>> on behind the scenes.
>>
>> I don't have a lot of experience with EC2, but disabling synchronous
>> commit does wonders if you have a lots of small transactions
>> (basically, OLTP workload) and are syncing to slow hardware without a
>> write cache.   It's particularly wonderful when you writing lots of
>> changes to the same general location in a table -- for example insert
>> heavy loads.
>>
>> Caching raid controllers tend to optimize in the same way so if you
>> are using them the benefit is less.  However, unless my requirements
>> call for zero loss of transactions this is the very first thing to
>> disable in terms of optimizing write performance.
>>
>> merlin
>
> ok... so maybe a better question would be: other than cases of power failure
> or something else that caused the server to shut down unexpectedly and
> uncleanly, what is the actual risk of data loss? In my case, the possibility
> of losing a recent transaction or two in the hopefully uncommon case of some
> catastrophic failure might very well be acceptable in exchange for increased
> write performance.
>
> random/unpredictable risk of data loss or corruption for other reasons is
> probably NOT acceptable to me though, regardless of the performance gain.
>
> thanks for the wisdom :)

Well, with fsync=off the operating system is in complete control and
is free to cache writes using its own heuristics with no participation
from the database.  So file data could get written in the wrong order,
etc. so upon hard stop you have to assume the whole database is
corrupt.

synchronous_commit OTOH witholds sync in some cases but the database
is still in control; to the extent that data is written, it is at
least consistent.  But I wouldn't coach things in terms of 'a couple
of transactions'.  In most real world cases it would only be a couple
but always plan for the worst case.  However, the database should boot
up and come out of recovery without further action.

Being paranoid pays, so as always take good backups and all that.

merlin