Обсуждение: pgbench Comparison of 7.4.7 to 8.0.2
I'm in the fortunate position of having a newly built database server that's pre-production. I'm about to run it through the ringer with some simulations of business data and logic, but I wanted to post the results of some preliminary pgbench marking. http://www.sitening.com/pgbench.html To me, it looks like basic transactional performance is modestly improved at 8.0 across a variety of metrics. I think this bodes well for more realistic loads, but I'll be curious to see the results of some of the simulations. I've still got a little bit of preparatory time with this box, so I can continue to do some experimentation. I'd be curious to see whether these numbers meet developer expectations and to see whether the developer and user community have insight into other pgbench options that would be useful to see. Thanks! -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
"Thomas F.O'Connell" <tfo@sitening.com> writes: > http://www.sitening.com/pgbench.html You need to run *many* more transactions than that to get pgbench numbers that aren't mostly noise. In my experience 1000 transactions per client is a rock-bottom minimum to get repeatable numbers; 10000 per is better. Also, in any run where #clients >= scaling factor, what you're measuring is primarily contention to update the "branches" rows. Which is not necessarily a bad thing to check, but it's generally not the most interesting performance domain (if your app is like that you need to redesign the app...) > To me, it looks like basic transactional performance is modestly > improved at 8.0 across a variety of metrics. That's what I would expect --- we usually do some performance work in every release cycle, but there was not a huge amount of it for 8.0. However, these numbers don't prove much either way. regards, tom lane
Tom, People's opinions on pgbench may vary, so take what I say with a grain of salt. Here are my thoughts: 1) Test with no less than 200 transactions per client. I've heard with less than this, your results will vary too much with the direction of the wind blowing. A high enough value will help rule out some "noise" factor. If I am wrong, please let me know. 2) How is the database going to be used? What percentage will be read/write if you had to guess? Pgbench is like a TPC-B with will help guage the potential throughput of your tps. However, it may not stress the server enough to help you make key performance changes. However, benchmarks are like statistics...full of lies <g>. 3) Run not just a couple pgbench runs, but *many* (I do between 20-40 runs) so you can rule out noise and guage improvement on median results. 4) Find something that you test OLTP-type transactions. I used OSDB since it is simple to implement and use. Although OSDL's OLTP testing will closer to reality. Steve Poe
Okay. I updated my benchmark page with new numbers, which are the result of extensive pgbench usage over this past week. In fact, I modified pgbench (for both of the latest version of postgres) to be able to accept multiple iterations as an argument and report the results of each iteration as well as a summary of mean tps at the end. The modifications of the source are included on the new page, and I'd be happy to submit them as patches if this seems like useful functionality to the developers and the community. I find it nicer to have pgbench be the authoritative source of iterative results rather than a wrapper script, but it'd be nice to have an extra set of eyes guarantee that I've included in the loop everything that ought to be there. A couple of notes: * There was some interesting oscillation behavior in both version of postgres that occurred with 25 clients and 1000 transactions at a scaling factor of 100. This was repeatable with the distribution version of pgbench run iteratively from the command line. I'm not sure how to explain this. * I'm not really sure why the single client run at 1000 transactions seemed so much slower than all successive iterations, including single client with 10000 transactions at a scaling factor of 100. It's possible that I should be concerned about how throughput was so much higher for 10000 transactions. Anyway, the code changes, the configuration details, and the results are all posted here: http://www.sitening.com/pgbench.html Once again, I'd be curious to get feedback from developers and the community about the results, and I'm happy to answer any questions. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 15, 2005, at 4:23 PM, Tom Lane wrote: > "Thomas F.O'Connell" <tfo@sitening.com> writes: >> http://www.sitening.com/pgbench.html > > You need to run *many* more transactions than that to get pgbench > numbers that aren't mostly noise. In my experience 1000 transactions > per client is a rock-bottom minimum to get repeatable numbers; 10000 > per > is better. > > Also, in any run where #clients >= scaling factor, what you're > measuring > is primarily contention to update the "branches" rows. Which is not > necessarily a bad thing to check, but it's generally not the most > interesting performance domain (if your app is like that you need to > redesign the app...) > >> To me, it looks like basic transactional performance is modestly >> improved at 8.0 across a variety of metrics. > > That's what I would expect --- we usually do some performance work in > every release cycle, but there was not a huge amount of it for 8.0. > > However, these numbers don't prove much either way. > > regards, tom lane
Steve, Per your and Tom's recommendations, I significantly increased the number of transactions used for testing. See my last post. The database will have pretty heavy mixed use, i.e., both reads and writes. I performed 32 iterations per scenario this go-round. I'll look into OSDB for further benchmarking. Thanks for the tip. Since pgbench is part of the postgres distribution and I had it at hand and it seems to be somewhat widely referenced, I figured I go ahead and post preliminary results from it. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 15, 2005, at 4:24 PM, Steve Poe wrote: > Tom, > > People's opinions on pgbench may vary, so take what I say with a grain > of salt. Here are my thoughts: > > 1) Test with no less than 200 transactions per client. I've heard with > less than this, your results will vary too much with the direction of > the wind blowing. A high enough value will help rule out some "noise" > factor. If I am wrong, please let me know. > > > 2) How is the database going to be used? What percentage will be > read/write if you had to guess? Pgbench is like a TPC-B with will help > guage the potential throughput of your tps. However, it may not stress > the server enough to help you make key performance changes. However, > benchmarks are like statistics...full of lies <g>. > > 3) Run not just a couple pgbench runs, but *many* (I do between 20-40 > runs) so you can rule out noise and guage improvement on median > results. > > 4) Find something that you test OLTP-type transactions. I used OSDB > since it is simple to implement and use. Although OSDL's OLTP testing > will closer to reality. > > Steve Poe
>There was some interesting oscillation behavior in both version of postgres that occurred with 25 >clients and 1000 transactions at a scaling factor of 100. This was repeatable with the distribution >version of pgbench run iteratively from the command line. I'm not sure how to explain this. Tom, When you see these oscillations, do they occur after so many generated results? Some oscillation is normal, in my opinion, from 10-15% of the performance is noise-related. The key is to tune the server that you either 1) minimize the oscillation and/or 2)increase your overall performance above the 10-15% baseline, and 3) find out what the mean and standard deviation between all your results. If your results are within that range, this maybe "normal". I follow-up with you later on what I do. Steve Poe
Interesting. I should've included standard deviation in my pgbench iteration patch. Maybe I'll go back and do that. I was seeing oscillation across the majority of iterations in the 25 clients/1000 transaction runs on both database versions. I've got my box specs and configuration files posted. If you see anything obvious about the tuning parameters that should be tweaked, please let me know. Thanks for the feedback! -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 25, 2005, at 1:58 AM, Steve Poe wrote: > >There was some interesting oscillation behavior in both version of > postgres that occurred with 25 >clients and 1000 transactions at a > scaling factor of 100. This was repeatable with the distribution > >version of pgbench run iteratively from the command line. I'm not > sure how to explain this. > > Tom, > > When you see these oscillations, do they occur after so many generated > results? Some oscillation is normal, in my opinion, from 10-15% of the > performance is noise-related. > > The key is to tune the server that you either 1) minimize the > oscillation and/or 2)increase your overall performance above the > 10-15% baseline, and 3) find out what the mean and standard deviation > between all your results. > > If your results are within that range, this maybe "normal". I > follow-up with you later on what I do. > > Steve Poe
Tom, Just a quick thought: after each run/sample of pgbench, I drop the database and recreate it. When I don't my results become more skewed. Steve Poe Thomas F.O'Connell wrote: > Interesting. I should've included standard deviation in my pgbench > iteration patch. Maybe I'll go back and do that. > > I was seeing oscillation across the majority of iterations in the 25 > clients/1000 transaction runs on both database versions. > > I've got my box specs and configuration files posted. If you see > anything obvious about the tuning parameters that should be tweaked, > please let me know. > > Thanks for the feedback! > > -tfo > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > > Strategic Open Source: Open Your iâ„¢ > > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-260-0005 > > On Apr 25, 2005, at 1:58 AM, Steve Poe wrote: > >> >There was some interesting oscillation behavior in both version of >> postgres that occurred with 25 >clients and 1000 transactions at a >> scaling factor of 100. This was repeatable with the distribution >> >version of pgbench run iteratively from the command line. I'm not >> sure how to explain this. >> >> Tom, >> >> When you see these oscillations, do they occur after so many >> generated results? Some oscillation is normal, in my opinion, from >> 10-15% of the performance is noise-related. >> >> The key is to tune the server that you either 1) minimize the >> oscillation and/or 2)increase your overall performance above the >> 10-15% baseline, and 3) find out what the mean and standard deviation >> between all your results. >> >> If your results are within that range, this maybe "normal". I >> follow-up with you later on what I do. >> >> Steve Poe > > >
Considering the default vacuuming behavior, why would this be? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 25, 2005, at 12:18 PM, Steve Poe wrote: > Tom, > > Just a quick thought: after each run/sample of pgbench, I drop the > database and recreate it. When I don't my results become more skewed. > > Steve Poe
Tom, Honestly, you've got me. It was either comment from Tom Lane or Josh that the os is caching the results (I may not be using the right terms here), so I thought it the database is dropped and recreated, I would see less of a skew (or variation) in the results. Someone which to comment? Steve Poe Thomas F.O'Connell wrote: > Considering the default vacuuming behavior, why would this be? > > -tfo > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > > Strategic Open Source: Open Your iâ„¢ > > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-260-0005 > > On Apr 25, 2005, at 12:18 PM, Steve Poe wrote: > >> Tom, >> >> Just a quick thought: after each run/sample of pgbench, I drop the >> database and recreate it. When I don't my results become more skewed. >> >> Steve Poe > >