pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwal_buffer tests in
5+ messages / 4 participants
[nested] [flat]
* wal_buffer tests in
@ 2005-07-27 20:30 Josh Berkus <josh@agliodbs.com>
2005-07-27 21:42 ` Re: wal_buffer tests in Mark Wong <markw@osdl.org>
2005-07-27 22:00 ` Re: wal_buffer tests in Simon Riggs <simon@2ndquadrant.com>
2005-07-28 11:49 ` Re: wal_buffer tests in Greg Stark <gsstark@mit.edu>
0 siblings, 3 replies; 5+ messages in thread
From: Josh Berkus @ 2005-07-27 20:30 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
Folks,
I ran a wal_buffer test series. It appears that increasing the
wal_buffers is indeed very important for OLTP applications, potentially
resulting in as much as a 15% average increase in transaction processing.
What's interesting is that this is not just true for 8.1, it's true for
8.0.3 as well.
More importantly, 8.1 performance is somehow back up to above-8.0 levels.
Something was broken in June that's got fixed (this test series is based
on July 3 CVS) but I don't know what. Clues?
Test results are here:
http://pgfoundry.org/docman/view.php/1000041/79/wal_buffer_test.pdf
As always, detailed test results are available from OSDL, just use:
http://khack.osdl.org/stp/#
where # is the test number.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal_buffer tests in
2005-07-27 20:30 wal_buffer tests in Josh Berkus <josh@agliodbs.com>
@ 2005-07-27 21:42 ` Mark Wong <markw@osdl.org>
2 siblings, 0 replies; 5+ messages in thread
From: Mark Wong @ 2005-07-27 21:42 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-hackers@postgresql.org
On Wed, 27 Jul 2005 13:30:01 -0700
Josh Berkus <josh@agliodbs.com> wrote:
> Folks,
>
> I ran a wal_buffer test series. It appears that increasing the
> wal_buffers is indeed very important for OLTP applications, potentially
> resulting in as much as a 15% average increase in transaction processing.
> What's interesting is that this is not just true for 8.1, it's true for
> 8.0.3 as well.
>
> More importantly, 8.1 performance is somehow back up to above-8.0 levels.
> Something was broken in June that's got fixed (this test series is based
> on July 3 CVS) but I don't know what. Clues?
>
> Test results are here:
> http://pgfoundry.org/docman/view.php/1000041/79/wal_buffer_test.pdf
>
> As always, detailed test results are available from OSDL, just use:
> http://khack.osdl.org/stp/#
> where # is the test number.
The increase could actually be higher than 15% as 1800 notpm is about
the max throughput you can have with 150 warehouses with the default
thinktimes. The rule of thumb is about 12 * warehouses, for the
throughput.
Mark
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal_buffer tests in
2005-07-27 20:30 wal_buffer tests in Josh Berkus <josh@agliodbs.com>
@ 2005-07-27 22:00 ` Simon Riggs <simon@2ndquadrant.com>
2 siblings, 0 replies; 5+ messages in thread
From: Simon Riggs @ 2005-07-27 22:00 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-hackers@postgresql.org
On Wed, 2005-07-27 at 13:30 -0700, Josh Berkus wrote:
> I ran a wal_buffer test series. It appears that increasing the
> wal_buffers is indeed very important for OLTP applications, potentially
> resulting in as much as a 15% average increase in transaction processing.
> What's interesting is that this is not just true for 8.1, it's true for
> 8.0.3 as well.
The most important thing about these tests is that for the first time we
have eliminated much of the post checkpoint noise-and-delay.
Look at the response time charts between
http://khack.osdl.org/stp/302959/results/0/rt.html
and
http://khack.osdl.org/stp/302963/results/0/rt.html
This last set of results is a thing of beauty and I must congratulate
everybody involved for getting here after much effort.
The graphs are smooth, which shows a balanced machine. I'd like to
repeat test 302963 with full_page_writes=false, to see if those response
time spikes at checkpoint drop down to normal level.
I think these results are valid for large DW data loads also.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal_buffer tests in
2005-07-27 20:30 wal_buffer tests in Josh Berkus <josh@agliodbs.com>
@ 2005-07-28 11:49 ` Greg Stark <gsstark@mit.edu>
2005-07-28 21:57 ` Re: wal_buffer tests in Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 5+ messages in thread
From: Greg Stark @ 2005-07-28 11:49 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-hackers@postgresql.org
Josh Berkus <josh@agliodbs.com> writes:
> Folks,
>
> I ran a wal_buffer test series. It appears that increasing the
> wal_buffers is indeed very important for OLTP applications, potentially
> resulting in as much as a 15% average increase in transaction processing.
> What's interesting is that this is not just true for 8.1, it's true for
> 8.0.3 as well.
You have wal_buffer set to 2048? That's pretty radical compared to the default
of just 5. Your tests shows you had to go to this large a value to see the
maximum effect?
--
greg
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal_buffer tests in
2005-07-27 20:30 wal_buffer tests in Josh Berkus <josh@agliodbs.com>
2005-07-28 11:49 ` Re: wal_buffer tests in Greg Stark <gsstark@mit.edu>
@ 2005-07-28 21:57 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2005-07-28 21:57 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: pgsql-hackers@postgresql.org
Greg,
> You have wal_buffer set to 2048? That's pretty radical compared to the
> default of just 5. Your tests shows you had to go to this large a value
> to see the maximum effect?
No, take a look at the graph. It looks like we got the maximum effect
from a wal_buffers somewhere between 64 and 256. On the DBT2 runs, any
variation less than 5% is just noise.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-07-28 21:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-27 20:30 wal_buffer tests in Josh Berkus <josh@agliodbs.com>
2005-07-27 21:42 ` Mark Wong <markw@osdl.org>
2005-07-27 22:00 ` Simon Riggs <simon@2ndquadrant.com>
2005-07-28 11:49 ` Greg Stark <gsstark@mit.edu>
2005-07-28 21:57 ` Josh Berkus <josh@agliodbs.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