pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpgbench Comparison of 7.4.7 to 8.0.2
10+ messages / 3 participants
[nested] [flat]
* pgbench Comparison of 7.4.7 to 8.0.2
@ 2005-04-15 21:02 Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 2 replies; 10+ messages in thread
From: Thomas F.O'Connell @ 2005-04-15 21:02 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgbench Comparison of 7.4.7 to 8.0.2
@ 2005-04-15 21:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas F.O'Connell <tfo@sitening.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2005-04-15 21:23 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgbench Comparison of 7.4.7 to 8.0.2
@ 2005-04-15 21:24 Steve Poe <spoe@sfnet.cc>
parent: Thomas F.O'Connell <tfo@sitening.com>
1 sibling, 1 reply; 10+ messages in thread
From: Steve Poe @ 2005-04-15 21:24 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgbench Comparison of 7.4.7 to 8.0.2
@ 2005-04-24 02:31 Thomas F.O'Connell <tfo@sitening.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Thomas F.O'Connell @ 2005-04-24 02:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgbench Comparison of 7.4.7 to 8.0.2
@ 2005-04-24 02:33 Thomas F.O'Connell <tfo@sitening.com>
parent: Steve Poe <spoe@sfnet.cc>
0 siblings, 0 replies; 10+ messages in thread
From: Thomas F.O'Connell @ 2005-04-24 02:33 UTC (permalink / raw)
To: Steve Poe <spoe@sfnet.cc>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgbench Comparison of 7.4.7 to 8.0.2
@ 2005-04-25 06:58 Steve Poe <spoe@sfnet.cc>
parent: Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 10+ messages in thread
From: Steve Poe @ 2005-04-25 06:58 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-performance
>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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgbench Comparison of 7.4.7 to 8.0.2
@ 2005-04-25 15:44 Thomas F.O'Connell <tfo@sitening.com>
parent: Steve Poe <spoe@sfnet.cc>
0 siblings, 1 reply; 10+ messages in thread
From: Thomas F.O'Connell @ 2005-04-25 15:44 UTC (permalink / raw)
To: Steve Poe <spoe@sfnet.cc>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgbench Comparison of 7.4.7 to 8.0.2
@ 2005-04-25 17:18 Steve Poe <spoe@sfnet.cc>
parent: Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 10+ messages in thread
From: Steve Poe @ 2005-04-25 17:18 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-performance
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
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgbench Comparison of 7.4.7 to 8.0.2
@ 2005-04-26 06:26 Thomas F.O'Connell <tfo@sitening.com>
parent: Steve Poe <spoe@sfnet.cc>
0 siblings, 1 reply; 10+ messages in thread
From: Thomas F.O'Connell @ 2005-04-26 06:26 UTC (permalink / raw)
To: Steve Poe <spoe@sfnet.cc>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pgbench Comparison of 7.4.7 to 8.0.2
@ 2005-04-26 17:49 Steve Poe <spoe@sfnet.cc>
parent: Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 0 replies; 10+ messages in thread
From: Steve Poe @ 2005-04-26 17:49 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-04-26 17:49 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-15 21:02 pgbench Comparison of 7.4.7 to 8.0.2 Thomas F.O'Connell <tfo@sitening.com>
2005-04-15 21:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-24 02:31 ` Thomas F.O'Connell <tfo@sitening.com>
2005-04-25 06:58 ` Steve Poe <spoe@sfnet.cc>
2005-04-25 15:44 ` Thomas F.O'Connell <tfo@sitening.com>
2005-04-25 17:18 ` Steve Poe <spoe@sfnet.cc>
2005-04-26 06:26 ` Thomas F.O'Connell <tfo@sitening.com>
2005-04-26 17:49 ` Steve Poe <spoe@sfnet.cc>
2005-04-15 21:24 ` Steve Poe <spoe@sfnet.cc>
2005-04-24 02:33 ` Thomas F.O'Connell <tfo@sitening.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox