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