Hi Tom, > -----Original Message----- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: 16 February 2005 15:01 > To: Mark Cave-Ayland > Cc: pgsql-patches@postgresql.org > Subject: Re: [PATCHES] WIP: bufmgr rewrite per recent discussions > > > "Mark Cave-Ayland" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
