Re: WIP: bufmgr rewrite per recent discussions

Hi Tom,

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 16 February 2005 15:01
> To: Mark Cave-Ayland
> Cc: pgsql-patches(at)postgresql(dot)org
> Subject: Re: [PATCHES] WIP: bufmgr rewrite per recent discussions
>
>
> "Mark Cave-Ayland" <m(dot)cave-ayland(at)webbased(dot)co(dot)uk> writes:
> > I compiled and tested your patch on a dual Opteron server with 12GB
> > RAM running FC3. Here are the results I get with pgbench with a
> > scale-factor of 10 over an average of 6 runs.
>
> Thanks for posting these results. What -c and -t settings
> were you using with pgbench? (I like to use -c equal to the
> scale factor and -t of at least 1000 ... much less than that
> gives fairly unstable results in my
> experience.)

Actually I was using the defaults and copied and pasted in Excel to work the
average out across a number of runs ;)

> I think what is probably happening here is the background
> writer is eating too many cycles. As of the patch I posted,
> the bgwriter is still using its 8.0 control parameters, in
> which the minimum scan percentage is 1% of all the buffers
> (so 1000 buffers scanned in each round in your last test) and
> it's willing to write up to 100 dirty buffers per round by
> default. I was looking at this yesterday and thinking it
> seemed clearly excessive. With a default bgwriter_delay of
> 200 msec, this allows the entire buffer array to be scanned
> every 20 sec, so we're in effect keeping the thing under
> constant syncer load.
>
> If you have time to redo your experiment, would you try
> knocking bgwriter_maxpages down to 10 to see if it helps at
> the larger shared_buffer settings?

OK here are some more results with the different settings. These were done
using the following pgbench command line: pgbench -s 10 -c 10 -t 1000 -d
pgbench

CVS tip: shared_buffers = 1000, bgwriter_maxpages = 10

transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 207.315539 (including connections establishing)
tps = 207.417611 (excluding connections establishing)

CVS tip: shared_buffers = 10000, bgwriter_maxpages = 10

transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 356.937045 (including connections establishing)
tps = 357.344680 (excluding connections establishing)

CVS tip: shared_buffers = 100000, bgwriter_maxpages = 10

transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 343.915227 (including connections establishing)
tps = 344.721566 (excluding connections establishing)

CVS tip + bufmgr patch : shared_buffers = 1000, bgwriter_maxpages = 10

transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 206.480332 (including connections establishing)
tps = 206.581087 (excluding connections establishing)

CVS tip + bufmgr patch : shared_buffers = 10000, bgwriter_maxpages = 10

transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 302.185931 (including connections establishing)
tps = 302.430903 (excluding connections establishing)

CVS tip + bufmgr patch : shared_buffers = 100000, bgwriter_maxpages = 10

transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 375.021808 (including connections establishing)
tps = 375.615606 (excluding connections establishing)

Reducing bgwriter_maxpages definitely seems to have helped with the larger
values for shared_buffers. However, during the test I was still seeing large
pauses that occurred at a rate that seemed inversely proportional to the
number of shared buffers. So with shared buffers set to 1000, the pgbench
test would 'pause' roughly every 5s for about 2-3s before continuing as
quickly as before. With shared buffers set to 100000 there were only 2 or 3
2-3s pauses during the entire duration of the test. As a rule of thumb, it
looked like the pauses occurred during update statements of the form "update
a set b = b + 1". Is the bgwriter supposed to eliminate these type of pauses
altogether?

> Since yesterday I've improved my patch by converting the
> bgwriter percentage variable into a float, so that values
> smaller than 1% can be selected, and I've split the two
> variables into four so that people can independently control
> the effort spent on the whole buffer array versus the buffers
> just in front of nextVictimBuffer (see BgBufferSync in the
> patch). I'm not sure how important that is, but I do think
> that the 1% / 100 default settings are way too high for
> larger buffer pools. Once that's in, it will be hard to
> compare the patch directly against CVS tip, so trying it now
> with a smaller maxpages setting for both would be a fairer comparison.
>
> I have another couple of small ideas for improving the patch
> --- I'll try to get those done and post a revised version
> this evening.

OK I'm just about finished for the day now. If you email the details of
exactly which tests/parameters you would like me to run, I'll try and run
them tomorrow morning when I have a few spare minutes.

Kind regards,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk