Thread: 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