 Hello, hello, I Think I'll go ahead and get started He'll be looks nice and informal since I know half of you in the room So my name is Mark Wong. I'm a consultant with second quadrant. I contribute to the Postgres project talk a little bit today about how the 96 branch of looking in terms of performance I'll Look at well go over very briefly what the tests are talk about some of the patches coming into 96 Talk a little bit about some mainframes Postgres XL some column oriented databases and Some replication so throughout most of these these Evaluations we're looking at PG bench if you're already familiar with that Postgres you're probably familiar with PG bench a TPC be derivative online transaction type basic online transaction type workload doing batch processing A open source kit called dbt2, which is just another TPCC derivative another OLTP type workload and then a dbt3 is a TPCH Decision support type workload and Then some various small custom type tests to illustrate specific parts of The changes coming in depending on what the patch is So first off Where is 96 now? Ren a PG bench read only test on a 4-way 8 core 32 thread system just to see what the scalability was looking like so This won't be particularly interesting, but it should be at least not It should be at least encouraging that to see that the performance hasn't the scalability hasn't degraded with 96 yet compared to the 95 release although in this particular chart the Doing a read-only test with prepared statements is showing a little bit of regression when you're getting Towards a higher number of clients Don't have any answers for that yet, but but I think I think Thomas Wandra is trying to figure that out to get more details on what's going on That's what's in the tree now some of the patches highlighting and For this presentation There's a freeze avoidance patch atomic pinning and unpinning of buffers and the buffer management code Little bit about parallel sequential scans unique joins Six decimal data types and shared aggregate states a couple of these have been committed some have been submitted and One extension So who has really large tables that don't change very much Do you find it painful when? Vacuuming or After running a lot of deletes or updates on those large tables so this this patch this freeze avoidance patch is authored by a Sawada Masiko it's It makes a table read only and The benefits for this is that then certain DML statements can be ignored and then Maybe more importantly depending on what you're doing Vacuuming is ignored the Auto-vacuum process won't come along and try to figure out if it needs to do any freezing in a table because it would Already know that that it didn't need to be done now that it's marked read only so with this patch there's a couple of of Extra DDL commands just to mark it read only read write the Atomic pinning and unpinning of buffers and the buffer manager as authored by a Yuri, I don't know if there's anyone help me pronounce my names here Yuri zero love zero love No, thank you Oh, yeah, you know practice my other languages So so this is intended to improve the read scalability by using atomic operations instead of the spin locks that are currently in place the the testing that he that he had done with this was on a Power 8 system for nodes 8 processors 8 cores 8 threads the chart of the results here is against a a I think they call them L bars 256 logical processors for this particular system, so Looking at it from the bottom up as I imagine that text may be hard to read The very bottom number is actually result With 9-4. This is another PG bench test So 9-4 on the very bottom next line up is 9-5 the 9-5 release which which is nice to see a Improvement there the third line Next line up from that is the head of the 9-6 branch so again nice to see a little bit of an improvement compared to 9-5 that's far already and then the five lines above that are various aspects of Implementing these atomic operations and and pulling out some of these spin walks so Compared to 9-5 The or what's even in in 9-6 now the throughput is Looks like it's increasing two to four times as much At various parts of The number of clients that are are running in the system the amount of concurrency that's that's going on so This looks pretty encouraging to see drastic performance and these kind of workloads. He was excited for parallelism so Parallel sequential scan has gone in Author by Amit Kapila This also involves some additional work of assessing parallel safety from Robert Haas and What this patch what this work did was improve the Sequential table scan performance when select queries are are retrieving a Relatively small number of rows compared to how many rows are on the table So it introduces a new configuration parameter to set the number to limit your degree of parallelism so you don't accidentally Overwork your system Some limitations so far is that it's only for select statements and it doesn't handle joins yet so what I wanted to show was a Few more few more data points to what Harry Babu had shown on the mailing list What I'm gonna do is run select here some select queries on a on a single table and show the behavior of a degree of parallelism and Selectivity of the rows coming back So just a quick will illustrate this table has about 16 columns variety of integer floating point and date and and far to our columns and Query is pretty simple. We're just selecting one of the columns. We'll control the selectivity by Using a modulo on one of the key columns so we can pretty much Guarantee that the percent of rows that will be coming back Let me just quote convention say court system 16 thread Intel system and this is what it looks like so the I think what is important to understand with with this parallelism is that It is important to Control the the degree of parallelism that's going on the red line up there on the top is is What happens when you're selecting all the rows from the table that first point on the left is single user single thread single process sorry Selecting all the rows from the table and If you're selecting all the rows if you increase your degree of parallelism and want two processes to go after it You're going to more than double the amount of time it takes to scan that table That green line in the middle Is selecting only a quarter of the rows back increasing the number of processes scanning through that table from from one to two already will improve your Response time by about 25 percent but then increasing the degree of parallelism after that is Start to not look so good anymore. Yes mmm Yeah, so the question is is that all of this data is in memory So on this particular system 64 gigs of RAM the data set of the entire database is a 100 gigabytes so this particular table is going to be about Somewhere between 80 70 and 80 gigabytes. Oh Question was how many shared buffers or how big the shared buffers were set to I? Actually don't recall, but if I were to guess I had probably set them to eight gigabytes Yeah, and then and then that blue line on the bottom is is Just reducing the selectivity and half again selecting 12% of the rows back in this particular case Moving between or increasing the number of processes between two and four seems to improve the response time by By almost having it down another 40 percent, but again You have too many processes scanning that table your response times are going to start to to increase so I Making sure that that degree of parallelism is limited appropriately and this set I think I glossed over this part the set tuple com cost is is basically the parameter that the planner uses to determine at what point should it not use a Parallel sequential scan Next patch is deals with unique joins offer authored by David Rowley The purpose of this is to improve the joint performance when the inner side of a join is known to be unique By either a unique index a group by clause or a distinct clause so Thomas Laundra did some experiments he Created a simple test where he used two tables to simulate sort of a fact dimension relationship star schema type thing and and He wanted to illustrate how well this patch performed when he had 10 more factor of the dimension tables at various Various scales so Pretty simple Table with with dimension table with an integer key and then a fact table with another integer key The query was pretty simple just to select the count one when Selecting all the rows Joint between these two tables And this is what he found so when he had 100,000 rows on the dimension table then 1 million and the fact table See before and after change was 7% as When he grew it the dimension table to 1 million rows That's having 10 million rows on the fact table the joint performance improved by 17% and then Somewhere between that and having 10 million dimension rows 100 million factor rows the the improvement came back down to about 10% a another extension Creates this fixed decimal data type how many folks deal with with numerics Find them slow fast enough so so Yeah, yeah So David Rowley also created an extension to add this fixed decimal data type The purpose of this was to help improve the performance of queries that were aggregating large number of rows of numeric type data The way this was implemented was by using 64-bit integers to provide a predefined precision decimal which I think was was to Places, you know, I'm sorry to forget my math terms So so similar to an American in in trying to be a precision Decimal type, but at the same time because of the fixed decimal type You do have to you may have to be aware that precision is truncated as opposed to rounded and Just to illustrate in that same table. I showed earlier you have a a table even with out of 16 columns if if four of them were numerics let's say we use the fixed data fixed decimal data type and See how our ad hoc decision support queries run and in our TPC age We see four of the queries have fairly significant improvement and ranging from from 35 percent to 55 percent improved response times So the to give you an idea of what these types of queries look like Query one though, which was on the on the far left over here is basically aggregating Those four rows in slightly various ways summing together taking averages and grouping them by a couple of other statuses whether the the order was returned and and whether this was Where in the ordering process these light items were at so So this query is trying to determine the amount of business that was billed shift and return and in the same With with the scale factor of a hundred gigabytes who are already seeing This much improvement on a table. That's between 70 and 80 gigabytes All right, so how many people have considered column stores column or in a databases? Are you still trying to decide whether you want to stick with one or the other using both? So for for those who haven't really considered it just to mention I think a few popular use cases is Call more in a databases or Can be good Good solutions for data warehousing and ad hoc query system or just computing Aggregates over large volumes of data so once again using the TPCH workload What we want to do is? Take a look at how postgres compares to what some of these column stores can do see How much room there is to gain so there So I took a look at a couple of open source column or in a databases Which I hope you won't mind that I anonymized to CS1 and CS2 But here's what some of the things look like so there there is a load test as part of the TPCH We're looking at a hundred gigabytes of data the this hundred gigabytes is spread across eight tables and for postgres the load tests also Takes in consideration how long it takes to build all the indexes you want to have so in this case we're building 34 indexes for the column stores we don't need to necessarily build indexes, but They do some of these do support indexes, but for my initial look I didn't look into indexing the data so Low time is pretty impressive To load a hundred gigabytes of data and build your indexes for it so you can run some queries against it takes about nine hours for postgres But these column store databases can can flip up that that a hundred gigabytes of data in one case and just over an hour the other one and in three and a half hours ish and It'll also be interesting to point out here that in with the float test with postgres we are loading the tables in parallel and Building indexes in parallel across the tables To get it down to nine hours At the same time it was also interesting to find that these particular open source column store databases Actually could not load So one of them one of them could not load data in parallel And it happens to be the one that loaded the the data the fastest you only can load one one table at a time now at the power test the power test in this workload is a series of 22 queries runs consecutively and Point the purpose is to see how fast you can power through all of those queries So the the way that it scored is a weighted geometric mean of all the response times Now higher number is better You can see here that postgres almost gets 4,000 whereas one of these column stores gets over 8,000 in the score and Even over 7,000 So one way to interpret these test results is that these column stars are are able to crunch through The position support ad hoc queries twice as fast twice as much as postgres So a lot of room to gain seems like there's a lot of room to gain if to add or a Large gap that could be closed by having a column store data type in postgres This is just to illustrate the response times of each of the queries the postgres results are the blue ones on the right, but it is Kind of interesting to note that to see where which queries Postgres Or the column stores actually do much better than postgres does Some of them I mean even the column stores are not too consistent one of the column stores happens to run certain types of queries better than the other one But still across the board to be able to crunch through twice as many Twice as many queries with a column store, and you can't with postgres And just to give a couple of examples of where these the types of questions that these column stores seem to answer better than than postgres if you were looking to Look at what your top 10 unshipped orders with the highest revenue or revenue to be realized Listing the revenue volumes of your local suppliers and And identifying customers that have been Having problems and having problems being defined as which customers have been returning your product more than others So anyone use the mainframe? No, any mainframe users anyone thought about it? No All right, so maybe maybe this will be interesting or or is there is everyone a anyone anti-mainframes? No, okay. That's good. That's good so Took a look at some of the latest the mainframe offering from IBM their Z13 We Ran some OLTP type TPCC Take a look at OLTP workload on how this thing does and we wanted to see how it did to a popular competitor If you don't mind me. I'm not amising that also Although these tests were were run against the The nine four release at the time I think these numbers are still actually quite interesting to look at because they certainly wouldn't have gotten any worse so a couple Basic hardware details these the Z13 processor as a five gigahertz clock speed there eight cores per die 16 threads 64 gigs of RAM in this particular LPAR So we tried to match it up against something similar a eight core Processor that also had 16 threads 2.4 gigahertz clock speed again 64 gigabytes around and This is what we found So running an in-memory OLTP workload The lines here are illustrating the Performance of the processors with hyper threading turned on Or not So the very bottom one is our popular competitor with no hyper threading mix one up is the same system with hyper threading turned on and then The top two number top two lines are the the mainframe results. So what we see here is that With or without hyper threading That the Throughput of an OLTP workload seems to run twice as fast on a mainframe, but then we you don't want to see some rights go on and This this particular test is using PG bench. Oh, I think I said sorry. I said this was a TPCC earlier But this is this is a PG bench workload PG bench again with a with a right workload right only workload on the popular competitor the Results with the hyper threading turned on and off didn't seem to make much of a difference The lines are pretty much on top of each other on the bottom but on the mainframe There is a substantial difference of having the hyper threading turned on even for a right only a workload and Once again the the ability to to do a heavy right workload on the mainframe was we were able to push through Twice the throughput now. This one is is that dbt to the TPCC derivative What these lines are showing at is as you scale up the size of the database that the Throughput of the of the tests are staying pretty consistent It's particular workload the mainframe seems to maintain about a 1.7 times throughput I will not repeat that question, but it's yes. Oh, it's another type of system. Yeah. Yeah. Yeah so so Hypothetically, it's it's a different hardware vendor that may be going up against IBM that Yes, yeah, so we're running put Postgres on the same way on two different types of hardware Okay, oh, yeah, sorry. Sorry. So, yeah, so one one fork one fork of Postgres versus another fork of Postgres Yeah, well, I'm sorry for being facetious. It's the same same Postgres Well, I won't say that because of this but alright, so yeah Yeah, I if well didn't seem like anyone was interested in the mainframes in the room today, but There are some interesting pieces of hardware. Yes The question is what the sticker price was on the Z13 I'm afraid that I never got to see a price sheet But I don't think any of those IBM sales folks will shy away from what do you know how much they cost? All right, so next up Postgres XL How many folks have looked at Postgres XL over the years anyone using it now? Hoping to be able to use it. So hopefully hopefully some of these test test results will will keep you interested then so for those who aren't familiar Postgres XL is a Fork of Postgres they a lot of these a lot of the people involved were previously involved with Postgres XC and Stato Stato either one Stato It is an all-purpose fully acid open source scale out database solution and It's been rebased against 9.5 recently So previously it was against the the Postgres 9.2 branch To give you an idea that after a few releases there may be a lot more interesting things to be able to do with with Excel So just to quickly mention that that I did a little comparison on on AWS systems EC2 using setting this up on some EC2 instances These individual instances had 32 Am I reading? Oh Okay, this this one is is just core Postgres at the time the 9.5 alpha 2 was was the what was currently available and This the single instance 32 virtual CPU 244 gigabytes around 8 SSD drives in it then for the Postgres XL cluster put together something with with Similar resources, so we have eight nodes Each of these nodes have I gotta do this math in my head. I think I Yeah, so so each of these nodes had four CPUs What is an 8th of 244 30 ish 30 gigabyte memory Yeah, so four CPUs and in each of these nodes 30 gigabytes of memory and then I Think these little ones had only one SSD attached to them So this is what we sell on low-test again using the dbt3 a the TPCH derivative To load 100 gigabytes of data build 34 indexes Took about two hours on the with Postgres 9.5 on the single instance in the 8-node cluster splitting up those data files eight ways Only took 44 minutes to load all that data and build build those 34 indexes cutting down that load time in half and While we haven't explored it it the the Postgres XL developers have reason to believe that if we spent more time in determining how many Coordinator nodes that we had per data node. We may be able to to improve this load time even further And also to take extra care and sorting your data files so that The data that you're loading in the table all streams over to one node No, got my slides out of order here. Sorry. So these were the individual node details now this This slide illustrates how The response times how much the response have to have improved on each of the individual queries of the power test between 9.5 and and Scaling out an Excel Almost almost across the board. There's pretty significant improvement in the response time If we're looking at the score values Scoring 2400 about 2400 with 9.5 and scoring about almost 6300 and with Postgres XL Almost 160% improvement and it's processing power So hopefully we'll see good things to come from Postgres XL and that won't be a fork forever How many folks have replication in there and they're using replication streaming replication Sony Wendy's More than one or do you got or? Sorry, yeah So we're we're gonna take a look at pgeological is a extension that is newly available for that can be used with 9.4 and newer versions of Postgres Thomas Wander took a look at seeing how well these How will each of these replication solutions worked with with PG bench on a read-only workload? Just to give you an idea of the of the system sizes there They're a pair of two eight of WS instances with four SSD drives attached in a hundred twenty-two gigabytes around So most of these tests are probably gonna be in ending up in memory Just illustrate or to Show a little bit of tuning that's gone on these are that these weren't default parameters Increase checkpoint time opposite 15 minutes Set the high effective IO currency 32 1 gigabyte of maintenance work memory setting the max wall size to 8 gigabytes and Configuring 16 gigabytes of shared buffers and this is what we get so in this two-node setup The the two lines that are are overlapping for the most part on the bottom are the Are the throughput numbers from? Landis Dan Sloanie at the top end, which I guess in some ways would be considered well, I guess the Peak of what you could expect our of kaches replication solution is the streaming replication so that that huge difference From having those trigger-based replication solutions You guys using them find that to be a burden sometimes the performance of Sony or Wendy's so now the the line in the middle is the performance of The throughput that you can get with Pgeological, which is not using triggers the same way that that Landis and Sony are Building off of the Streaming what replication facilities inside Postgres? Yeah The question is whether these numbers are are coming from the master of the plate system I Actually, don't know I guess I would have assumed the master But well because you have to run it must be from the master big based on you have to run PG bench against the master and these numbers are probably coming out of the PG bench Yeah, so it's looking like like streaming if if streaming replication was the right solution for you you you're getting Six times the throughput compared to one days of Sony Pgeological It's not quite that high, but you're still getting Three four times the throughput and that's all I had for you today Thank you for listening