Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Wed, Oct 12, 2011 at 10:50:13AM +0300, Peter Eisentraut wrote: Actually, I'm currently personally more concerned about the breakage we introduce in minor releases. We'd need to solve that problem before we can even begin to think about dealing with the major release issue. +1 This bit me the other day. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgprM9aFgot2o.pgp Description: PGP signature
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On tis, 2011-10-11 at 21:50 +0100, Simon Riggs wrote: I'm keen to ensure people enjoy the possibility of upgrading to the latest release. The continual need to retest applications mean that very few users upgrade quickly or with anywhere near the frequency with which we put out new releases. What is the point of rushing out software that nobody can use? pg_upgrade doesn't change your applications, so there isn't a fast path to upgrade in the way you seem to think. This is a valid concern, which I share, but I think adding a few configuration parameters of the nature, this setting really means what this setting meant in the old release is only the tip of the iceberg. Ensuring full compatibility between major releases would require an extraordinary amount of effort, including a regression test suite that would be orders of magnitude larger than what we currently have. I frankly don't see the resources to do that. The workaround strategy is that we maintain backbranches, so that users are not forced to upgrade to incompatible releases. Actually, I'm currently personally more concerned about the breakage we introduce in minor releases. We'd need to solve that problem before we can even begin to think about dealing with the major release issue. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Wed, Oct 12, 2011 at 6:34 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 11.10.2011 23:21, Simon Riggs wrote: If the normal default_transaction_isolation = read committed and all transactions that require serializable are explicitly marked in the application then there is no way to turn off SSI without altering the application. That is not acceptable, since it causes changes in application behaviour and possibly also performance issues. I don't get that. If all the transactions that require serializability are marked as such, why would you disable SSI for them? That would just break the application, since the transactions would no longer be serializable. If they don't actually need serializability, but repeatable read is enough, then mark them that way. Obviously, if apps require serializability then turning serializability off would break them. That is not what I have said, nor clearly not what I would mean by turning off SSI. The type of serializability we had in the past is now the same as repeatable read. So the request is for a parameter that changes a request for serializable into a request for repeatable read, so that applications are provided with exactly what they had before, in 9.0 and earlier. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Wed, Oct 12, 2011 at 8:50 AM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-10-11 at 21:50 +0100, Simon Riggs wrote: I'm keen to ensure people enjoy the possibility of upgrading to the latest release. The continual need to retest applications mean that very few users upgrade quickly or with anywhere near the frequency with which we put out new releases. What is the point of rushing out software that nobody can use? pg_upgrade doesn't change your applications, so there isn't a fast path to upgrade in the way you seem to think. This is a valid concern, which I share, but I think adding a few configuration parameters of the nature, this setting really means what this setting meant in the old release is only the tip of the iceberg. Ensuring full compatibility between major releases would require an extraordinary amount of effort, including a regression test suite that would be orders of magnitude larger than what we currently have. I frankly don't see the resources to do that. The workaround strategy is that we maintain backbranches, so that users are not forced to upgrade to incompatible releases. Actually, I'm currently personally more concerned about the breakage we introduce in minor releases. We'd need to solve that problem before we can even begin to think about dealing with the major release issue. Thanks, these look like reasonable discussion points with no personal comments added. I agree that config parameters don't solve the whole problem, though much of the iceberg is already covered with them. Currently about half of our parameters are either on/off behaviour switches. Right now we are inconsistent about whether we add a parameter for major features: sync_scans, hot_standby, partial vacuum all had ways of disabling them if required - virtually all features can be disabled, bgwriter, autovacuum etc even though it is almost never a recommendation to do so. I can't see a good argument for including some switches, but not others. SSI is a complex new feature and really should have an off switch. Right now, we've had one report and a benchmark that show severe performance degradation and that might have benefited from turning it off. That is not sufficient at this point to convince some people, so I am happy to wait to see if further reports emerge. SSI doesn't affect everybody. Yes, I agree that the only really good answer in the general case is to maintain back branches, or provide enhanced versions as some vendors choose to do. That is not my first thought, and try quite hard to put my (/our) best work into mainline Postgres. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Oct11, 2011, at 23:35 , Simon Riggs wrote: On Tue, Oct 11, 2011 at 10:30 PM, Florian Pflug f...@phlo.org wrote: That experience has taught me that backwards compatibility, while very important in a lot of cases, has the potential to do just as much harm if overdone. Agreed. Does my suggestion represent overdoing it? I ask for balance, not an extreme. It's my belief that an off switch for true serializability is overdoing it, yes. With such a switch, every application that relies on true serializability for correctness would be prone to silent data corruption should the switch ever get set to off accidentally. Without such a switch, OTOH, all that will happen are a few more aborts due to serialization errors in application who request SERIALIZABLE when they really only need REPEATABLE READ. Which, in the worst case, is a performance issue, but never an issue of correctness. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Wed, Oct 12, 2011 at 8:44 AM, Florian Pflug f...@phlo.org wrote: With such a switch, every application that relies on true serializability for correctness would be prone to silent data corruption should the switch ever get set to off accidentally. Agreed. Without such a switch, OTOH, all that will happen are a few more aborts due to serialization errors in application who request SERIALIZABLE when they really only need REPEATABLE READ. Which, in the worst case, is a performance issue, but never an issue of correctness. Right. And, in fairness: 1. The benchmark that I did was probably close to a worst-case scenario for SSI. Since there are no actual writes, there is no possibility of serialization conflicts, but the system must still be prepared for the possibility of a write (and, thus, potentially, a conflict) at any time. In addition, all of the transactions are very short, magnifying the effect of transaction start and cleanup overhead. In real life, people who have this workload are unlikely to use serializable mode in the first place. The whole point of serializability (not just SSI) is that it helps prevent anomalies when you have complex transactions that could allow subtle serialization anomalies to creep in. Single-statement transactions that read (or write) values based on a primary key are not the workload where you have that problem. You'd probably be happy to turn off MVCC altogether if we had an option for that. 2. Our old SERIALIZABLE behavior (now REPEATABLE READ) is a pile of garbage. Since Kevin started beating the drum about SSI, I've come across (and posted about) situations where REPEATABLE READ read causes serialization anomalies that don't exist at the READ COMMITTED level (which is exactly the opposite of what is really supposed to happen - REPEATABLE READ is supposed to provide more isolation, not less); and Kevin's pointed out many situations where REPEATABLE READ utterly fails to deliver serializable behavior. I'm not exactly thrilled with these benchmark results, but going back to a technology that doesn't work is not better. If individual users want to request that defective behavior for their applications, I am fine with giving them that option, and we have. But if people actually want serializability and we given them REPEATABLE READ, then they're going to get wrong behavior. The fact that we've been shipping that wrong behavior for years and years for lack of anything better is not a reason to continue doing it. I agree with Tom's comment upthread that the best thing to do here is put some effort into improving SSI. I think it's probably going to perform adequately for the workloads where people actually need it, but I'd certainly like to see us make it better. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Wed, Oct 12, 2011 at 1:44 PM, Florian Pflug f...@phlo.org wrote: On Oct11, 2011, at 23:35 , Simon Riggs wrote: On Tue, Oct 11, 2011 at 10:30 PM, Florian Pflug f...@phlo.org wrote: That experience has taught me that backwards compatibility, while very important in a lot of cases, has the potential to do just as much harm if overdone. Agreed. Does my suggestion represent overdoing it? I ask for balance, not an extreme. It's my belief that an off switch for true serializability is overdoing it, yes. With such a switch, every application that relies on true serializability for correctness would be prone to silent data corruption should the switch ever get set to off accidentally. Without such a switch, OTOH, all that will happen are a few more aborts due to serialization errors in application who request SERIALIZABLE when they really only need REPEATABLE READ. Which, in the worst case, is a performance issue, but never an issue of correctness. That's a good argument and I accept it. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Mon, Oct 10, 2011 at 3:59 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: I do have some concern about whether the performance improvements from reduced LW locking contention elsewhere in the code may (in whack-a-mole fashion) cause the percentages to go higher in SSI. The biggest performance issues in some of the SSI benchmarks were on LW lock contention, so those may become more noticeable as other contention is reduced. I've been trying to follow along on the threads regarding Robert's work in that area, with hopes of applying some of the same techniques to SSI, but it's not clear whether I'll have time to work on that for the 9.2 release. (It's actually looking improbably at this point.) I ran my good old pgbench -S, scale factor 100, shared_buffers = 8GB test on Nate Boley's box. I ran it on both 9.1 and 9.2dev, and at all three isolation levels. As usual, I took the median of three 5-minute runs, which I've generally found adequate to eliminate the noise. On both 9.1 and 9.2dev, read committed and repeatable read have basically identical performance; if anything, repeatable read may be slightly better - which would make sense, if it cuts down the number of snapshots taken. Serializable mode is much slower on this test, though. On REL9_1_STABLE, it's about 8% slower with a single client. At 8 clients, the difference rises to 43%, and at 32 clients, it's 51% slower. On 9.2devel, raw performance is somewhat higher (e.g. +51% at 8 clients) but the performance when not using SSI has improved so much that the performance gap between serializable and the other two isolation levels is now huge: with 32 clients, in serializable mode, the median result was 21114.577645 tps; in read committed, 218748.929692 tps - that is, read committed is running more than ten times faster than serializable. Data are attached, in text form and as a plot. I excluded the repeatable read results from the plot as they just clutter it up - they're basically on top of the read committed results. I haven't run this with LWLOCK_STATS, but my seat-of-the-pants guess is that there's a single lightweight lock that everything is bottlenecking on. One possible difference between this test case and the ones you may have used is that this case involves lots and lots of really short transactions that don't do much. The effect of anything that only happens once or a few times per transaction is really magnified in this type of workload (which is why the locking changes make so much of a difference here - in a longer or heavier-weight transaction that stuff would be lost in the noise). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company # clients PG91.read-committed PG91.repeatable-read PG91.serializable PG92dev.read-committed PG92dev.repeatable-read PG92dev.serializable 1 4309.029470 4309.786279 3997.033815 4420.435075 4432.991835 4158.783631 8 26881.573137 27105.962897 15604.313328 33369.189146 33744.015354 23607.824697 16 44578.177731 44347.193447 21687.135630 62690.803360 63391.583338 23308.801395 32 38725.876808 38773.523347 17995.772382 218748.929692 218928.089856 21114.577645 attachment: isolation-scaling.png -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Robert Haas robertmh...@gmail.com wrote: I ran my good old pgbench -S, scale factor 100, shared_buffers = 8GB test on Nate Boley's box. I ran it on both 9.1 and 9.2dev, and at all three isolation levels. As usual, I took the median of three 5-minute runs, which I've generally found adequate to eliminate the noise. On both 9.1 and 9.2dev, read committed and repeatable read have basically identical performance; if anything, repeatable read may be slightly better - which would make sense, if it cuts down the number of snapshots taken. Right. Thanks for running this. Could you give enough details to allow reproducing on this end (or point to a previous post with the details)? Serializable mode is much slower on this test, though. On REL9_1_STABLE, it's about 8% slower with a single client. At 8 clients, the difference rises to 43%, and at 32 clients, it's 51% slower. On 9.2devel, raw performance is somewhat higher (e.g. +51% at 8 clients) but the performance when not using SSI has improved so much that the performance gap between serializable and the other two isolation levels is now huge: with 32 clients, in serializable mode, the median result was 21114.577645 tps; in read committed, 218748.929692 tps - that is, read committed is running more than ten times faster than serializable. Yeah. I was very excited to see your numbers as you worked on that, but I've been concerned that with the Performance Whack A Mole nature of things (to borrow a term from Josh Berkus), SSI lightweight locks might be popping their heads up. Data are attached, in text form and as a plot. I excluded the repeatable read results from the plot as they just clutter it up - they're basically on top of the read committed results. That was kind, but really the REPEATABLE READ results are probably the more meaningful comparison, even if they are more embarrassing. :-( I haven't run this with LWLOCK_STATS, but my seat-of-the-pants guess is that there's a single lightweight lock that everything is bottlenecking on. The lock in question is SerializableXactHashLock. A secondary problem is SerializableFinishedListLock, which is used for protecting cleanup of old transactions. This is per Dan's reports, who had a better look at in on a 16 core machine, but is consistent with what I saw on fewer cores. Early in development we had a bigger problem with SerializablePredicateLockListLock, but Dan added a local map to eliminate contention during lock promotion decision, and I reworked that lock from the SHARED read and EXCLUSIVE write approach to the SHARED for accessing your own data and EXCLUSIVE for accessing data for another process technique. Combined, that made the problems with that negligible. One possible difference between this test case and the ones you may have used is that this case involves lots and lots of really short transactions that don't do much. I did some tests like that, but not on a box with that many processors, and I probably didn't try using a thread count more than double the core count, so I probably never ran into the level of contention you're seeing. The differences at the low connection counts are surprising to me. Maybe it will make more sense when I see the test case. There's also some chance that late elimination of some race conditions found in testing affected this, and I didn't re-run those tests late enough to see that. Not sure. The effect of anything that only happens once or a few times per transaction is really magnified in this type of workload (which is why the locking changes make so much of a difference here - in a longer or heavier-weight transaction that stuff would be lost in the noise). Did these transactions write anything? If not, were they declared to be READ ONLY? If they were, in fact, only reading, it would be interesting to see what the performance looks like if the recommendation to use the READ ONLY attribute is followed. That's at the top of the list of performance tips for SSI at: http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html#XACT-SERIALIZABLE Anyway, this isolates a real issue, even if the tests exaggerate it beyond what anyone is likely to see in production. Once this CF is over, I'll put a review of this at the top of my PG list. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Mon, Oct 10, 2011 at 11:31 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Simon Riggs si...@2ndquadrant.com wrote: How do we turn it on/off to allow the overhead to be measured? User REPEATABLE READ transactions or SERIALIZABLE transactions. The easiest way, if you're doing it for all transactions (which I recommend) is to set default_transaction_isolation. Most apps use mixed mode serializable/repeatable read and therefore can't be changed by simple parameter. Rewriting the application isn't a sensible solution. I think it's clear that SSI should have had and still needs an off switch for cases that cause performance problems. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 12:46 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: I ran my good old pgbench -S, scale factor 100, shared_buffers = 8GB test on Nate Boley's box. I ran it on both 9.1 and 9.2dev, and at all three isolation levels. As usual, I took the median of three 5-minute runs, which I've generally found adequate to eliminate the noise. On both 9.1 and 9.2dev, read committed and repeatable read have basically identical performance; if anything, repeatable read may be slightly better - which would make sense, if it cuts down the number of snapshots taken. Right. Thanks for running this. Could you give enough details to allow reproducing on this end (or point to a previous post with the details)? Sure, it's pretty much just a vanilla pgbench -S run, but the scripts I used are attached here. I build the head of each branch using the test-build script and then used the runtestiso script to drive the test runs. These scripts are throwaway so they're not really documented, but hopefully it's clear enough what it's doing. The server itself is a 32-core AMD 6128. Data are attached, in text form and as a plot. I excluded the repeatable read results from the plot as they just clutter it up - they're basically on top of the read committed results. That was kind, but really the REPEATABLE READ results are probably the more meaningful comparison, even if they are more embarrassing. :-( They're neither more nor less embarrassing - they're pretty much not different at all. I just didn't see any point in making a graph with 6 lines on it when you could only actually see 4 of them. Did these transactions write anything? If not, were they declared to be READ ONLY? If they were, in fact, only reading, it would be interesting to see what the performance looks like if the recommendation to use the READ ONLY attribute is followed. pgbench -S doesn't do any writes, or issue any transaction control statements. It just fires off SELECT statements against a single table as fast as it can, retrieving values from rows chosen at random. Each SELECT implicitly begins and ends a transaction. Possibly the system could gaze upon the SELECT statement and infer that the one-statement transaction induced thereby can't possibly write any tuples, and mark it read-only automatically, but I'm actually not that excited about that approach - trying to fix the lwlock contention that's causing the headache in the first place seems like a better use of time, assuming it's possible to make some headway there. My general observation is that, on this machine, a lightweight lock that is taken in exclusive mode by a series of lockers in quick succession seems to max out around 16-20 clients, and the curve starts to bend well before that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company test-build Description: Binary data runtestiso Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 1:11 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Oct 10, 2011 at 11:31 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Simon Riggs si...@2ndquadrant.com wrote: How do we turn it on/off to allow the overhead to be measured? User REPEATABLE READ transactions or SERIALIZABLE transactions. The easiest way, if you're doing it for all transactions (which I recommend) is to set default_transaction_isolation. Most apps use mixed mode serializable/repeatable read and therefore can't be changed by simple parameter. Rewriting the application isn't a sensible solution. I think it's clear that SSI should have had and still needs an off switch for cases that cause performance problems. Is it possible that you are confusing the default level, which is READ COMMITTED, with REPEATABLE READ? I can't see why anyone would code up their application to use REPEATABLE READ for some things and SERIALIZABLE for other things unless they were explicitly trying to turn SSI off for a subset of their transactions. In all releases prior to 9.0, REPEATABLE READ and SERIALIZABLE behaved identically, so there wouldn't be any reason for a legacy app to mix-and-match between the two. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 6:14 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Oct 11, 2011 at 1:11 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Oct 10, 2011 at 11:31 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Simon Riggs si...@2ndquadrant.com wrote: How do we turn it on/off to allow the overhead to be measured? User REPEATABLE READ transactions or SERIALIZABLE transactions. The easiest way, if you're doing it for all transactions (which I recommend) is to set default_transaction_isolation. Most apps use mixed mode serializable/repeatable read and therefore can't be changed by simple parameter. Rewriting the application isn't a sensible solution. I think it's clear that SSI should have had and still needs an off switch for cases that cause performance problems. Is it possible that you are confusing the default level, which is READ COMMITTED, with REPEATABLE READ? I can't see why anyone would code up their application to use REPEATABLE READ for some things and SERIALIZABLE for other things unless they were explicitly trying to turn SSI off for a subset of their transactions. In all releases prior to 9.0, REPEATABLE READ and SERIALIZABLE behaved identically, so there wouldn't be any reason for a legacy app to mix-and-match between the two. Yes, I mistyped read when I meant committed. You are right to point out there is no problem if people were using repeatable read and serializable. Let me retype, so there is no confusion: It's common to find applications that have some transactions explicitly coded to use SERIALIZABLE mode, while the rest are in the default mode READ COMMITTED. So common that TPC-E benchmark has been written as a representation of such workloads. The reason this is common is that some transactions require SERIALIZABLE as a fix for transaction problems. If you alter the default_transaction_isolation then you will break applications like this, so it is not a valid way to turn off SSI. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Robert Haas robertmh...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: Did these transactions write anything? If not, were they declared to be READ ONLY? If they were, in fact, only reading, it would be interesting to see what the performance looks like if the recommendation to use the READ ONLY attribute is followed. pgbench -S doesn't do any writes, or issue any transaction control statements. It just fires off SELECT statements against a single table as fast as it can, retrieving values from rows chosen at random. Each SELECT implicitly begins and ends a transaction. So that test could be accomplished by setting default_transaction_read_only to on. That's actually what we're doing, because we have a lot more of them than of read-write transactions. But, with the scripts I can confirm the performance of that on this end. It should be indistinguishable from the repeatable read line; if not, there's something to look at there. Possibly the system could gaze upon the SELECT statement and infer that the one-statement transaction induced thereby can't possibly write any tuples, and mark it read-only automatically, but I'm actually not that excited about that approach I wasn't intending to suggest that. In fact I hadn't really thought of it. It might be a fun optimization, although it would be well down my list, and it wouldn't be trivial because you couldn't use if for any statements with volatile functions -- so the statement would need to be planned far enough to know whether that was the case before making this decision. In fact, I'm not sure the community would want to generate an error if a user marked a function other than volatile and ran it in this way. Definitely not something to even look at any time soon. trying to fix the lwlock contention that's causing the headache in the first place seems like a better use of time, assuming it's possible to make some headway there. Absolutely. I just thought the timings with READ ONLY would make for an interesting data point. For one thing, it might reassure people that even this artificial use cases doesn't perform that badly if the advice in the documentation is heeded. For another, a result slower than repeatable read would be a surprise that might point more directly to the problem. My general observation is that, on this machine, a lightweight lock that is taken in exclusive mode by a series of lockers in quick succession seems to max out around 16-20 clients, and the curve starts to bend well before that. OK, I will keep that in mind. Thanks, -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs si...@2ndquadrant.com wrote: It's common to find applications that have some transactions explicitly coded to use SERIALIZABLE mode, while the rest are in the default mode READ COMMITTED. So common that TPC-E benchmark has been written as a representation of such workloads. I would be willing to be that any such implementations assume S2PL, and would not prevent anomalies as expected unless all transactions are serializable. The reason this is common is that some transactions require SERIALIZABLE as a fix for transaction problems. That is a mode of thinking which doesn't work if you only assume serializable provides the guarantees required by the standard. Many people assume otherwise. It does *not* guarantee blocking on conflicts, and it does not require that transactions appear to have executed in the order of successful commit. It requires only that the result of concurrently running any mix of serializable transactions produce a result consistent with some one-at-a-time execution of those transactions. Rollback of transactions to prevent violations of that guarantee are allowed. I don't see any guarantees about how serializable transactions interact with non-serializable transactions beyond each transaction not seeing any of the phenomena prohibited for its isolation level. If you alter the default_transaction_isolation then you will break applications like this, so it is not a valid way to turn off SSI. I don't follow you here. What would break? In what fashion? Since the standard allows any isolation level to provide more strict transaction isolation than required, it would be conforming to *only* support serializable transactions, regardless of the level requested. Not a good idea for some workloads from a performance perspective, but it would be conforming, and any application which doesn't work correctly with that is not written to the standard. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 6:44 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: If you alter the default_transaction_isolation then you will break applications like this, so it is not a valid way to turn off SSI. I don't follow you here. What would break? In what fashion? Since the standard allows any isolation level to provide more strict transaction isolation than required, it would be conforming to *only* support serializable transactions, regardless of the level requested. Not a good idea for some workloads from a performance perspective, but it would be conforming, and any application which doesn't work correctly with that is not written to the standard. If the normal default_transaction_isolation = read committed and all transactions that require serializable are explicitly marked in the application then there is no way to turn off SSI without altering the application. That is not acceptable, since it causes changes in application behaviour and possibly also performance issues. We should provide a mechanism to allow people to upgrade to 9.1+ without needing to change the meaning and/or performance of their apps. I strongly support the development of SSI, but I don't support application breakage. We can have SSI without breaking anything for people that can't or don't want to use it. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Robert Haas: Serializable mode is much slower on this test, though. On REL9_1_STABLE, it's about 8% slower with a single client. At 8 clients, the difference rises to 43%, and at 32 clients, it's 51% slower. Bummer. Thanks for putting some numbers out there; glad I was able to jump start a deeper look at this. Based on this thread so far, I am probably going to avoid serializable in this particular case, and stick to repeatable read. Once things are in place, perhaps I'll be able to try switching to serializable and get some measurements, but I wanted to see if the impact was minor enough to safely start with serializable. Seems not. :) Keep in mind this is not even a formal proposal yet for our client, so any benchmarks from me may be quite a while. Kevin Grittner: Did these transactions write anything? If not, were they declared to be READ ONLY? If they were, in fact, only reading, it would be interesting to see what the performance looks like if the recommendation to use the READ ONLY attribute is followed. Yes, I'll definitely look into that, but the great majority of the things done in this case are read/write. Simon Riggs: Most apps use mixed mode serializable/repeatable read and therefore can't be changed by simple parameter. Rewriting the application isn't a sensible solution. I think it's clear that SSI should have had and still needs an off switch for cases that cause performance problems. Eh? It has an off switch: repeatable read. Thanks for all replying to this thread, it's been very helpful. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpkFVkl3Xl3T.pgp Description: PGP signature
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
If the normal default_transaction_isolation = read committed and all transactions that require serializable are explicitly marked in the application then there is no way to turn off SSI without altering the application. That is not acceptable, since it causes changes in application behaviour and possibly also performance issues. Performance, perhaps. What application behavior changes? Less serialization conflicts? We should provide a mechanism to allow people to upgrade to 9.1+ without needing to change the meaning and/or performance of their apps. That ship has sailed. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpsMUli41Pnm.pgp Description: PGP signature
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 9:21 PM, Greg Sabino Mullane g...@endpoint.com wrote: Simon Riggs: Most apps use mixed mode serializable/repeatable read and therefore can't be changed by simple parameter. Rewriting the application isn't a sensible solution. I think it's clear that SSI should have had and still needs an off switch for cases that cause performance problems. Eh? It has an off switch: repeatable read. You mean: if we recode the application and retest it, we can get it to work same way as it used to. To most people that is the same thing as it doesn't work with this release, ask any application vendor. There is no off switch and there should be. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Greg Sabino Mullane wrote: -- Start of PGP signed section. If the normal default_transaction_isolation = read committed and all transactions that require serializable are explicitly marked in the application then there is no way to turn off SSI without altering the application. That is not acceptable, since it causes changes in application behaviour and possibly also performance issues. Performance, perhaps. What application behavior changes? Less serialization conflicts? We should provide a mechanism to allow people to upgrade to 9.1+ without needing to change the meaning and/or performance of their apps. That ship has sailed. Simon seems to value backward-compatibility more than the average hackers poster. The lack of complaints about 9.1 I think means that the hackers decision of _not_ providing a swich was the right one. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs wrote: On Tue, Oct 11, 2011 at 6:44 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: If you alter the default_transaction_isolation then you will break applications like this, so it is not a valid way to turn off SSI. I don't follow you here. ?What would break? ?In what fashion? ?Since the standard allows any isolation level to provide more strict transaction isolation than required, it would be conforming to *only* support serializable transactions, regardless of the level requested. ?Not a good idea for some workloads from a performance perspective, but it would be conforming, and any application which doesn't work correctly with that is not written to the standard. If the normal default_transaction_isolation = read committed and all transactions that require serializable are explicitly marked in the application then there is no way to turn off SSI without altering the application. That is not acceptable, since it causes changes in application behaviour and possibly also performance issues. We should provide a mechanism to allow people to upgrade to 9.1+ without needing to change the meaning and/or performance of their apps. I strongly support the development of SSI, but I don't support application breakage. We can have SSI without breaking anything for people that can't or don't want to use it. The problem is that all the switches needed to allow for no application breakage makes configuration of the server and source code more complicated. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs si...@2ndquadrant.com wrote: Greg Sabino Mullane g...@endpoint.com wrote: Eh? It has an off switch: repeatable read. You mean: if we recode the application and retest it, we can get it to work same way as it used to. To most people that is the same thing as it doesn't work with this release, ask any application vendor. There is no off switch and there should be. This was discussed at some length, and nobody seemed to favor a behavior-changing GUC. One example of such a thread is here: http://archives.postgresql.org/pgsql-hackers/2009-05/msg01165.php It came up at least a couple other times, and the outcome was always the same -- after discussion, nobody was in favor of a GUC to make the semantics of these statement variable. I'm sorry if you missed those discussions. It would certainly be a trivial change to implement; the problem is convincing others that it's a good idea. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 04:32:45PM -0400, Bruce Momjian wrote: ... Simon seems to value backward-compatibility more than the average hackers poster. The lack of complaints about 9.1 I think means that the hackers decision of _not_ providing a swich was the right one. I wouldn't go that far: 9.1 is very new. Certainly the release notes do not explain the change enough: part of the reason I wrote: http://blog.endpoint.com/2011/09/postgresql-allows-for-different.html Simon has a point, but I think that having applications switch from serializable to repeatable read is a pain point people should pay when going to 9.1, rather than adding some switch now. -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgp7jN6DSMohw.pgp Description: PGP signature
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 9:32 PM, Bruce Momjian br...@momjian.us wrote: Greg Sabino Mullane wrote: -- Start of PGP signed section. If the normal default_transaction_isolation = read committed and all transactions that require serializable are explicitly marked in the application then there is no way to turn off SSI without altering the application. That is not acceptable, since it causes changes in application behaviour and possibly also performance issues. Performance, perhaps. What application behavior changes? Less serialization conflicts? If you change default_transaction_isolation then the behaviour of the application will change. We should provide a mechanism to allow people to upgrade to 9.1+ without needing to change the meaning and/or performance of their apps. That ship has sailed. Simon seems to value backward-compatibility more than the average hackers poster. The lack of complaints about 9.1 I think means that the hackers decision of _not_ providing a swich was the right one. So its been out 1 month and you think that is sufficient time for us to decide that there are no user complaints about SSI? I doubt it. Longer term I have every confidence that it will be appreciated. I'm keen to ensure people enjoy the possibility of upgrading to the latest release. The continual need to retest applications mean that very few users upgrade quickly or with anywhere near the frequency with which we put out new releases. What is the point of rushing out software that nobody can use? pg_upgrade doesn't change your applications, so there isn't a fast path to upgrade in the way you seem to think. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs si...@2ndquadrant.com writes: There is no off switch and there should be. As Greg said, that ship has sailed. I believe that we specifically discussed the notion of an off switch via a GUC or similar during 9.1 development, and rejected it on the grounds that GUCs changing fundamental transactional behavior are dangerous. I don't believe that you've made a case for changing that decision, and even if you had, it's too late; 9.1 is what it is. Can we end this subthread please, and concentrate on something actually useful, like improving SSI's performance? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Greg Sabino Mullane g...@endpoint.com wrote: Kevin Grittner: Did these transactions write anything? If not, were they declared to be READ ONLY? If they were, in fact, only reading, it would be interesting to see what the performance looks like if the recommendation to use the READ ONLY attribute is followed. Yes, I'll definitely look into that, but the great majority of the things done in this case are read/write. But it is precisely *because* those were fully cached read-only transactions that the numbers came out so bad. As Robert pointed out, in other loads the difference in time per transaction could be lost in the noise. Now, I know SSI won't be good fit for all applications, but you might not want to write it off on performance grounds for an application where the great majority of the things done ... are read/write based on a test which ran only read-only transactions without declaring them READ ONLY. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs wrote: Simon seems to value backward-compatibility more than the average hackers poster. ?The lack of complaints about 9.1 I think means that the hackers decision of _not_ providing a swich was the right one. So its been out 1 month and you think that is sufficient time for us to decide that there are no user complaints about SSI? I doubt it. Longer term I have every confidence that it will be appreciated. I'm keen to ensure people enjoy the possibility of upgrading to the latest release. The continual need to retest applications mean that very few users upgrade quickly or with anywhere near the frequency with which we put out new releases. What is the point of rushing out software that nobody can use? pg_upgrade doesn't change your applications, so there isn't a fast path to upgrade in the way you seem to think. Simon, I basically think you are swimming up-stream on this issue, and on the recovery.conf thread as well. You can keep arguing that backward compatibility warrants more effort, but until there is more general agreement in the group, you are going to lose these arguments, and frankly, the arguments are getting tiring. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs si...@2ndquadrant.com wrote: You'll have to retest your apps just isn't a good answer For which major PostgreSQL releases have you recommended that people deploy their apps without retesting? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 9:37 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: It would certainly be a trivial change to implement; the problem is convincing others that it's a good idea. I don't want it, I just think we need it now. You'll have to retest your apps just isn't a good answer and we should respect the huge cost that causes our users. Probably as a matter of policy all new features that effect semantics should have some kind of compatibility or off switch, if easily possible. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 9:53 PM, Bruce Momjian br...@momjian.us wrote: Simon Riggs wrote: Simon seems to value backward-compatibility more than the average hackers poster. ?The lack of complaints about 9.1 I think means that the hackers decision of _not_ providing a swich was the right one. So its been out 1 month and you think that is sufficient time for us to decide that there are no user complaints about SSI? I doubt it. Longer term I have every confidence that it will be appreciated. I'm keen to ensure people enjoy the possibility of upgrading to the latest release. The continual need to retest applications mean that very few users upgrade quickly or with anywhere near the frequency with which we put out new releases. What is the point of rushing out software that nobody can use? pg_upgrade doesn't change your applications, so there isn't a fast path to upgrade in the way you seem to think. Simon, I basically think you are swimming up-stream on this issue, and on the recovery.conf thread as well. You can keep arguing that backward compatibility warrants more effort, but until there is more general agreement in the group, you are going to lose these arguments, and frankly, the arguments are getting tiring. I speak when it is important that someone does so, and only on specific, real issues. When I speak, I do so on behalf of my clients and other Postgres users that suffer the problems created by those issues. I've never given a viewpoint on list that I know to be the opposite of the majority of people I represent. How could I change the viewpoint of the group without making rational arguments when it matters? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs si...@2ndquadrant.com writes: How could I change the viewpoint of the group without making rational arguments when it matters? Well, you make your arguments, and you see if you convince anybody. On these specific points, you've failed to sway the consensus AFAICT, and at some point you have to accept that you've lost the argument. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 10:00 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Simon Riggs si...@2ndquadrant.com wrote: You'll have to retest your apps just isn't a good answer For which major PostgreSQL releases have you recommended that people deploy their apps without retesting? None. People don't always follow my advice, regrettably. They ask comparative questions like What is the risk of upgrade?, How much testing is required? I never met a customer yet that has an automated test suite designed to stress the accuracy of results under concurrent workloads, so the inability to control the way a new feature operates makes such questions more likely to be given an answer that indicates greater effort and higher risk. That is exactly what I personally would wish to avoid. An off switch encourages people to use new features. It is not a punishment or an admonition to the developer. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Oct11, 2011, at 22:55 , Simon Riggs wrote: Probably as a matter of policy all new features that effect semantics should have some kind of compatibility or off switch, if easily possible. There's a huge downside to that, though. After a while, you end up with a gazillion settings, each influencing behaviour in non-obvious, subtle ways. Plus, every new code we add would have to be tested against *all* combinations of these switches. Or, maybe, we'd punt and make some features work only with reasonable settings. And by doing so cause much frustration of the kind I need to set X to Y to use feature Z, but I can't because our app requires X to be set to Y2. I've recently had to use Microsoft SQL Server for a project, and they fell into *precisely* this trap. Nearly *everything* is a setting there, like whether various things follow the ANSI standard (NULLS, CHAR types, one setting for each), whether identifiers are double-quoted or put between square brackets, whether loss of precision is an error, ... And, some of their very own features depend on specific combination of these settings. Sometimes on the values in effect when the object was created, sometimes when it's used. For example, their flavour of materialized views (called indexed views) requires a bunch of options to be set correctly to be able to create such an object. Some of these must even be in effect to update the view's base tables, once the view is created... That experience has taught me that backwards compatibility, while very important in a lot of cases, has the potential to do just as much harm if overdone. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 10:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: How could I change the viewpoint of the group without making rational arguments when it matters? Well, you make your arguments, and you see if you convince anybody. On these specific points, you've failed to sway the consensus AFAICT, and at some point you have to accept that you've lost the argument. I'm happy to wait more than 4 hours before trying to judge any meaningful consensus. Rushing judgement on such points is hardly likely to encourage people to speak up, even assuming they have the opportunity. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs si...@2ndquadrant.com wrote: They ask comparative questions like What is the risk of upgrade?, How much testing is required? I never met a customer yet that has an automated test suite designed to stress the accuracy of results under concurrent workloads I'll try to provide some information here to help you answer those questions. I hope it is helpful. The only behavioral difference an unchanged application could see (short of some as-yet-undiscovered bug) is that they could get more serialization failures when using serializable isolation level than they previously got, and that there could be a performance impact. It really does nothing except run exactly what serializable mode was before, while monitoring for conditions which are present when a race condition between transactions might cause odd results, and generate a serialization failure as needed to prevent that. It kind of hangs off the side of legacy behavior and watches things. No new blocking. No new deadlocks. No chance of results you didn't get before. It might also be worth reviewing this page: http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Tue, Oct 11, 2011 at 10:30 PM, Florian Pflug f...@phlo.org wrote: That experience has taught me that backwards compatibility, while very important in a lot of cases, has the potential to do just as much harm if overdone. Agreed. Does my suggestion represent overdoing it? I ask for balance, not an extreme. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs wrote: On Tue, Oct 11, 2011 at 10:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: How could I change the viewpoint of the group without making rational arguments when it matters? Well, you make your arguments, and you see if you convince anybody. On these specific points, you've failed to sway the consensus AFAICT, and at some point you have to accept that you've lost the argument. I'm happy to wait more than 4 hours before trying to judge any meaningful consensus. Rushing judgement on such points is hardly likely to encourage people to speak up, even assuming they have the opportunity. This is an issue you have been pushing for a very long time on many fronts --- four hours is not going to change anything. Have you considered developing a super-backward-compatible version of Postgres, or a patch set which does this, and seeing if there is any interest from users? If you could get major uptake, it would certainly bolster your argument. But, until I see that, I am unlikely to listen to further protestations. I am much more likely to just ignore your suggestions as Oh, it is just Simon on the backward-compatibility bandwagon again, and it causes me to just mentally filter your ideas, which isn't productive, and I am sure others will do the same. Read the MS-SQL post about backward compatibility knobs as proof that many of us know the risks of too much backward compatibility. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs wrote: On Tue, Oct 11, 2011 at 10:30 PM, Florian Pflug f...@phlo.org wrote: That experience has taught me that backwards compatibility, while very important in a lot of cases, has the potential to do just as much harm if overdone. Agreed. Does my suggestion represent overdoing it? I ask for balance, not an extreme. Well, balance is looking at what everyone else in the group is suggesting, and realizing you might not have all the answers, and listening. As far as I can see, you are the _only_ one who thinks it needs an option. In that light, your suggestion seems extreme, not balanced. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On 11.10.2011 23:21, Simon Riggs wrote: If the normal default_transaction_isolation = read committed and all transactions that require serializable are explicitly marked in the application then there is no way to turn off SSI without altering the application. That is not acceptable, since it causes changes in application behaviour and possibly also performance issues. I don't get that. If all the transactions that require serializability are marked as such, why would you disable SSI for them? That would just break the application, since the transactions would no longer be serializable. If they don't actually need serializability, but repeatable read is enough, then mark them that way. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Overhead cost of Serializable Snapshot Isolation
I'm looking into upgrading a fairly busy system to 9.1. They use serializable mode for a few certain things, and suffer through some serialization errors as a result. While looking over the new serializable/SSI documentation, one thing that stood out is: http://www.postgresql.org/docs/current/interactive/transaction-iso.html The monitoring of read/write dependencies has a cost, as does the restart of transactions which are terminated with a serialization failure, but balanced against the cost and blocking involved in use of explicit locks and SELECT FOR UPDATE or SELECT FOR SHARE, Serializable transactions are the best performance choice for some environments. I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant overhead? Minor? Is it measurable? Hard to say without knowing the number of txns, number of locks, etc.? -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpXfFQOk4fgH.pgp Description: PGP signature
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On 10.10.2011 21:25, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant overhead? Minor? Is it measurable? Hard to say without knowing the number of txns, number of locks, etc.? I'm sure it does depend heavily on all of those things, but IIRC Kevin ran some tests earlier in the spring and saw a 5% slowdown. That feels like reasonable initial guess to me. If you can run some tests and measure the overhead in your application, it would be nice to hear about it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Mon, Oct 10, 2011 at 02:25:59PM -0400, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant overhead? Minor? Is it measurable? Hard to say without knowing the number of txns, number of locks, etc.? I'd expect that in most cases the main cost is not going to be overhead from the lock manager but rather the cost of having transactions aborted due to conflicts. (But the rollback rate is extremely workload-dependent.) We've seen CPU overhead from the lock manager to be a few percent on a CPU-bound workload (in-memory pgbench). Also, if you're using a system with many cores and a similar workload, SerializableXactHashLock might become a scalability bottleneck. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 10.10.2011 21:25, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant overhead? Minor? Is it measurable? Hard to say without knowing the number of txns, number of locks, etc.? I'm sure it does depend heavily on all of those things, but IIRC Kevin ran some tests earlier in the spring and saw a 5% slowdown. That feels like reasonable initial guess to me. If you can run some tests and measure the overhead in your application, it would be nice to hear about it. Right: the only real answer is it depends. At various times I ran different benchmarks where the overhead ranged from lost in the noise to about 5% for one variety of worst case. Dan ran DBT-2, following the instructions on how to measure performance quite rigorously, and came up with a 2% hit versus repeatable read for that workload. I rarely found a benchmark where the hit exceeded 2%, but I have a report of a workload where they hit was 20% -- for constantly overlapping long-running transactions contending for the same table. I do have some concern about whether the performance improvements from reduced LW locking contention elsewhere in the code may (in whack-a-mole fashion) cause the percentages to go higher in SSI. The biggest performance issues in some of the SSI benchmarks were on LW lock contention, so those may become more noticeable as other contention is reduced. I've been trying to follow along on the threads regarding Robert's work in that area, with hopes of applying some of the same techniques to SSI, but it's not clear whether I'll have time to work on that for the 9.2 release. (It's actually looking improbably at this point.) If you give it a try, please optimize using the performance considerations for SSI in the manual. They can make a big difference. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Mon, Oct 10, 2011 at 02:59:04PM -0500, Kevin Grittner wrote: I do have some concern about whether the performance improvements from reduced LW locking contention elsewhere in the code may (in whack-a-mole fashion) cause the percentages to go higher in SSI. The biggest performance issues in some of the SSI benchmarks were on LW lock contention, so those may become more noticeable as other contention is reduced. I've been trying to follow along on the threads regarding Robert's work in that area, with hopes of applying some of the same techniques to SSI, but it's not clear whether I'll have time to work on that for the 9.2 release. (It's actually looking improbably at this point.) I spent some time thinking about this a while back, but didn't have time to get very far. The problem isn't contention in the predicate lock manager (which is partitioned) but the single lock protecting the active SerializableXact state. It would probably help things a great deal if we could make that lock more fine-grained. However, it's tricky to do this without deadlocking because the serialization failure checks need to examine a node's neighbors in the dependency graph. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Dan Ports d...@csail.mit.edu wrote: I spent some time thinking about this a while back, but didn't have time to get very far. The problem isn't contention in the predicate lock manager (which is partitioned) but the single lock protecting the active SerializableXact state. It would probably help things a great deal if we could make that lock more fine-grained. However, it's tricky to do this without deadlocking because the serialization failure checks need to examine a node's neighbors in the dependency graph. Did you ever see much contention on SerializablePredicateLockListLock, or was it just SerializableXactHashLock? I think the former might be able to use the non-blocking techniques, but I fear the main issue is with the latter, which seems like a harder problem. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Mon, Oct 10, 2011 at 04:10:18PM -0500, Kevin Grittner wrote: Did you ever see much contention on SerializablePredicateLockListLock, or was it just SerializableXactHashLock? I think the former might be able to use the non-blocking techniques, but I fear the main issue is with the latter, which seems like a harder problem. No, not that I recall -- if SerializablePredicateLockListLock was on the list of contended locks, it was pretty far down. SerializableXactHashLock was the main bottleneck, and SerializableXactFinishedListLock was a lesser but still significant one. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
On Mon, Oct 10, 2011 at 8:30 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 10.10.2011 21:25, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant overhead? Minor? Is it measurable? Hard to say without knowing the number of txns, number of locks, etc.? I'm sure it does depend heavily on all of those things, but IIRC Kevin ran some tests earlier in the spring and saw a 5% slowdown. That feels like reasonable initial guess to me. If you can run some tests and measure the overhead in your application, it would be nice to hear about it. How do we turn it on/off to allow the overhead to be measured? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhead cost of Serializable Snapshot Isolation
Simon Riggs si...@2ndquadrant.com wrote: How do we turn it on/off to allow the overhead to be measured? User REPEATABLE READ transactions or SERIALIZABLE transactions. The easiest way, if you're doing it for all transactions (which I recommend) is to set default_transaction_isolation. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
