On 06/20/2014 09:41 AM, Merlin Moncure wrote:
On Thu, Jun 19, 2014 at 9:24 AM, Larry J Prikockis <lprikockis@xxxxxxxxx> 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 :)
