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 :)
