Thread: pros/cons of using "synchronous commit=off" - AWS in particular
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. -- Larry J. Prikockis System Administrator 240-965-4597 (direct) lprikockis@vecna.com http://www.vecna.com Vecna Technologies, Inc. 6404 Ivy Lane Suite 500 Greenbelt, MD 20770 Phone: (240) 965-4500 Fax: (240) 547-6133 Better Technology, Better World (TM) The contents of this message may be privileged and confidential. Therefore, if this message has been received in error, please delete it without reading it. Your receipt of this message is not intended to waive any applicable privilege. Please do not disseminate this message without the permission of the author.
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
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 :)
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
Larry Prikockis wrote: > 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. [Barring software bugs,j asynchronous commit does not cause data corruption or unpredictable data loss. You can lose the last batch of transactions on a crash, but even if you do, the data written by previous transactions is valid. Worst case of # of transactions lost is determined by 3x wal_writer_delay. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services