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
