Обсуждение: wal_buffer tests in

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

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

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

> 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 will want the log write to write up to at least 256kb (>= 256kb you
reach
the max Mb/s a disk can write) (if that is possible, no commit/fsync
inbetween).
You will also want enough buffers, that can be filled while the 256kb
are written.
Thus a value of at least 64 with 8k pages seems reasonable.

Andreas

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

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