Thread: Re: Re: [Testperf-general] BufferSync and bgwriter
Josh Berkus <josh@agliodbs.com> wrote on 15.12.2004, 18:36:53: > Hmmm, I've not seen this. For example, with people who are having trouble > with checkpoint spikes on Linux, I've taken to recommending that they call > sync() (via cron) every 5-10 seconds (thanks, Bruce, for suggestion!). > Believe it or not, this does help smooth out the spikes and give better > overall performance in a many-small-writes situation. Yes, but bgwriter needs to issue the writes first before the kernel cache can be flushed, which is the activity I've been focusing on. If the bgwriter isn't writing enough, flushing the cache is pointless. If the bgwriter is writing too much, then thats a waste and likely causing buffer list contention. > Simon, one of the problems with the OSDL-DBT2 test is that it's too steady. > DBT2 gives a constant stream of small writes at a regular, predictable rate. > This does not, in fact, match any real-world application I know. Clearly, OSDL-DBT2 is not a real world test! That is its benefit, since it is heavily instrumented and we are able to re-run it many times without different parameter settings. The application is well known and doesn't suffer that badly from factors that would allow certain effects to be swamped. If it had too much randomness or variation, it would be difficult to interpret. My goal has been to tune the server, not to derive marketing numbers. What DBT-2 does reveal is where contention occurs within the PostgreSQL server. If things are bad in a static, well known workload then they will be much more erratic in the choppy waters of the real world. Simulating reality isn't what any of us need to do - there's always a system to look at and be confused by its peaks and troughs, user complaints and hardware idiosyncracies. DBT2 is just one workload amongst many you can choose as your "tuning goal". The investigations on that have, IMHO, been genuinely useful in discovering performance problems in the server. Mark's efforts to improve the instrumentation of the tests will be useful on other workloads also. I'd encourage you to develop variations of DBT2 that can also be used to tune the server, hopefully running on OSDL. I support open testing methods as much as I support open development methods and projects. DBT3 next... Best Regards, Simon Riggs
Simon, > Clearly, OSDL-DBT2 is not a real world test! That is its benefit, since > it is heavily instrumented and we are able to re-run it many times > without different parameter settings. The application is well known and > doesn't suffer that badly from factors that would allow certain effects > to be swamped. If it had too much randomness or variation, it would be > difficult to interpret. I don't think you followed me. The issue is that for parameters designed to "smooth out spikes" like bgwriter and vacuum delay, it helps to have really bad spikes to begin with. There's a possibility that the parameters (and calculations) that work well for for a "steady-state" OLTP application are actually bad for an application with much more erratic usage, just as a high sort_mem is good for DSS and bad for OLTP. > Mark's efforts to > improve the instrumentation of the tests will be useful on other > workloads also. Yep, it's been a lot of help. Heck, this is the first time we've had parameters based on planned tests and not just anecdotes. That's a huge step forward. I'm just suggesting that we can improve the test still further specifically for testing things like bgwriter. > I'd encourage you to develop variations of DBT2 that can also be used to > tune the server, hopefully running on OSDL. I support open testing > methods as much as I support open development methods and projects. Yeah, I'll just have to do it in a different programming language ;-b > > DBT3 next... Yes, I started setting up a 200GB DBT3 database on one of OSDL's machines. You're welcome to it, I don't see myself completeting those tests before the holidays. Want login? --Josh -- __Aglio Database Solutions_______________ Josh Berkus Consultant josh@agliodbs.com www.agliodbs.com Ph: 415-752-2500 Fax: 415-752-2387 2166 Hayes Suite 200 San Francisco, CA
Josh Berkus wrote: > Simon, > > >>Clearly, OSDL-DBT2 is not a real world test! That is its benefit, since >>it is heavily instrumented and we are able to re-run it many times >>without different parameter settings. The application is well known and >>doesn't suffer that badly from factors that would allow certain effects >>to be swamped. If it had too much randomness or variation, it would be >>difficult to interpret. > > > I don't think you followed me. The issue is that for parameters designed to > "smooth out spikes" like bgwriter and vacuum delay, it helps to have really > bad spikes to begin with. There's a possibility that the parameters (and > calculations) that work well for for a "steady-state" OLTP application are > actually bad for an application with much more erratic usage, just as a high > sort_mem is good for DSS and bad for OLTP. I'm a little concerned that in an erratic, or even just a changing environment there isn't going to be any set of values that are "correct". If I've got this right, the behaviour we're trying to get is: 1. Starting from the oldest dirty block, 2. Write as many dirtyblocks as you can, but don't... 3. Re-write frequently used blocks too much (wasteful) So, can we not just keep track of two numbers: 1. Change in the number of dirty blocks this time vs last 2. Number of re-writeswe perform (count collisions in a hash or similar - doesn't need to be perfect). If #1 is increasing, then we need to become more active (reduce bgwriter_delay, increase bgwriter_maxpages). If #2 starts to go up, or goes past some threshold then we reduce activity (increase bgwriter_delay, decrease bgwriter_maxpages). If of the last N blocks written, C have been collisions then assume we've run out of low-activity blocks to write, stop and sleep. This has a downside that the figures will never be completely accurate, but has the advantage that it will automatically track activity. I'm clearly beyond my technical knowledge here, so if I haven't understood / it's impractical / will never work, then don't be afraid to step up and let me know. If it helps, you could always think of me as an idiot savant who failed his savant exams :-) -- Richard Huxton Archonet Ltd
On Thu, 2004-12-16 at 17:54, Richard Huxton wrote: > Josh Berkus wrote: > >>Clearly, OSDL-DBT2 is not a real world test! That is its benefit, since > >>it is heavily instrumented and we are able to re-run it many times > >>without different parameter settings. The application is well known and > >>doesn't suffer that badly from factors that would allow certain effects > >>to be swamped. If it had too much randomness or variation, it would be > >>difficult to interpret. > > > > I don't think you followed me. The issue is that for parameters designed to > > "smooth out spikes" like bgwriter and vacuum delay, it helps to have really > > bad spikes to begin with. There's a possibility that the parameters (and > > calculations) that work well for for a "steady-state" OLTP application are > > actually bad for an application with much more erratic usage, just as a high > > sort_mem is good for DSS and bad for OLTP. > > I'm a little concerned that in an erratic, or even just a changing > environment there isn't going to be any set of values that are "correct". I think this expresses my own thoughts most clearly, however: There have been many good ideas expressed on this thread, though none of them, including my own, are IMHO suitable for inclusion in 8.0, given the stage of the release process we are now at. *** Please give your support now to the addition of Neil's recent bgwriter patch to the 8.0 release. It simplifies tuning, is proven to remove a clear performance blockage, yet does so without changing the underlying algorithm used by the bgwriter - so there is no case to answer along the lines that this might not apply in some situations. Neil's bgwriter does the same thing, just avoids holding a critical lock for longer than it needs to. *** I will happily discuss further ideas for 8.1 at a later stage. I'll be around tomorrow for further discussion and better replies to different individual points. Please excuse my slow answers during this debate. -- Best Regards, Simon Riggs
On Thu, 2004-12-16 at 17:54, Richard Huxton wrote: > Josh Berkus wrote: > >>Clearly, OSDL-DBT2 is not a real world test! That is its benefit, since > >>it is heavily instrumented and we are able to re-run it many times > >>without different parameter settings. The application is well known and > >>doesn't suffer that badly from factors that would allow certain effects > >>to be swamped. If it had too much randomness or variation, it would be > >>difficult to interpret. > > > > > > I don't think you followed me. The issue is that for parameters designed to > > "smooth out spikes" like bgwriter and vacuum delay, it helps to have really > > bad spikes to begin with. There's a possibility that the parameters (and > > calculations) that work well for for a "steady-state" OLTP application are > > actually bad for an application with much more erratic usage, just as a high > > sort_mem is good for DSS and bad for OLTP. > > I'm a little concerned that in an erratic, or even just a changing > environment there isn't going to be any set of values that are "correct". > > If I've got this right, the behaviour we're trying to get is: > 1. Starting from the oldest dirty block, > 2. Write as many dirty blocks as you can, but don't... > 3. Re-write frequently used blocks too much (wasteful) > > So, can we not just keep track of two numbers: > 1. Change in the number of dirty blocks this time vs last > 2. Number of re-writes we perform (count collisions in a hash or > similar - doesn't need to be perfect). > > If #1 is increasing, then we need to become more active (reduce > bgwriter_delay, increase bgwriter_maxpages). > If #2 starts to go up, or goes past some threshold then we reduce > activity (increase bgwriter_delay, decrease bgwriter_maxpages). > If of the last N blocks written, C have been collisions then assume > we've run out of low-activity blocks to write, stop and sleep. > > This has a downside that the figures will never be completely accurate, > but has the advantage that it will automatically track activity. > > I'm clearly beyond my technical knowledge here, so if I haven't > understood / it's impractical / will never work, then don't be afraid to > step up and let me know. If it helps, you could always think of me as an > idiot savant who failed his savant exams :-) Richard, I like your ideas very much. For 8.1 or beyond, it seems clear to me that a self-adapting bgwriter with no/few parameters is the way forward. My first step will be to instrument the bgwriter, so we have more input about the dynamic behaviour of the ARC lists and their effect. Then use that information to trial an adaptive mechanism along the general lines you suggest. -- Best Regards, Simon Riggs