Thread: "--tuning" compile and runtime option (?)
Hi guys, Just thinking about the future directions PostgreSQL is taking, and it seems (just a feeling) like most people prefer it to be as self tuning as possible. In trying to think about how it will/would do that I think PostgreSQL will need to know "how much" of the resources of the server its on, it's allowed to take. Can think of three scenario's, 1) Single-purpose PostgreSQL server 2) shared function server (i.e. Apache, Postgres, etc on the same box) 3) Embedded or otherwise resource limited server (Palmtop, etc). When we get around to PostgreSQL's self-tuning ability being actively developed (and I think Bruce has done some of the very start with his monitor program), perhaps having a compile time option to set the default for the server, and a runtime option in case it changes? i.e. --tuning=superserver --tuning=shared --tuning=embedded postmaster -t superserver postmaster -t shared postmaster -t embedded What do people think? Regards and best wishes, Justin Clift P.S. - I'm not on the Hackers mailing list from this account. Can anyone responding please include me directly in their replies? -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
My idea was to have PostgreSQL output tips to help performance. The TODO item is:* Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM ANALYZE, and CLUSTER I also will be writing an article on performance tuning this month. What parameters would these options you suggest control? I usually prefer options that have more concrete effect. > Just thinking about the future directions PostgreSQL is taking, and it > seems (just a feeling) like most people prefer it to be as self tuning > as possible. > > In trying to think about how it will/would do that I think PostgreSQL > will need to know "how much" of the resources of the server its on, it's > allowed to take. > > Can think of three scenario's, 1) Single-purpose PostgreSQL server 2) > shared function server (i.e. Apache, Postgres, etc on the same box) 3) > Embedded or otherwise resource limited server (Palmtop, etc). > > When we get around to PostgreSQL's self-tuning ability being actively > developed (and I think Bruce has done some of the very start with his > monitor program), perhaps having a compile time option to set the > default for the server, and a runtime option in case it changes? > > i.e. > > --tuning=superserver > --tuning=shared > --tuning=embedded > > postmaster -t superserver > postmaster -t shared > postmaster -t embedded > > What do people think? > > Regards and best wishes, > > Justin Clift > > P.S. - I'm not on the Hackers mailing list from this account. Can > anyone responding please include me directly in their replies? > > -- > "My grandfather once told me that there are two kinds of people: those > who work and those who take the credit. He told me to try to be in the > first group; there was less competition there." > - Indira Gandhi > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
[ Charset ISO-8859-1 unsupported, converting... ] > I like this. Ensure that tips can be dumped into a log file -- > preferably separate from the main one -- so it can be run on a live > system for a short period of time, recorded then analyzed later. Yes, they would go into the standard postmaster log. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Justin Clift writes: > When we get around to PostgreSQL's self-tuning ability being actively > developed (and I think Bruce has done some of the very start with his > monitor program), perhaps having a compile time option to set the > default for the server, and a runtime option in case it changes? > i.e. > --tuning=superserver > --tuning=shared > --tuning=embedded > postmaster -t superserver > postmaster -t shared > postmaster -t embedded I'm generally no friend of generic "make it fast", "make it small" options. It is usually hard to decide what settings should go under what heading because everyone is in a different situation. The solution is to provide user guidance to the existing configuration variables that goes beyond what they do by adding why the user should care. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
I like this. Ensure that tips can be dumped into a log file -- preferably separate from the main one -- so it can be run on a live system for a short period of time, recorded then analyzed later. -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: "Justin Clift" <jclift@iprimus.com.au> Cc: <pgsql-hackers@postgresql.org> Sent: Monday, April 09, 2001 12:18 AM Subject: Re: [HACKERS] "--tuning" compile and runtime option (?) > My idea was to have PostgreSQL output tips to help performance. The > TODO item is: > > * Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM > ANALYZE, and CLUSTER > > I also will be writing an article on performance tuning this month. > What parameters would these options you suggest control? I usually > prefer options that have more concrete effect. > > > > Just thinking about the future directions PostgreSQL is taking, and it > > seems (just a feeling) like most people prefer it to be as self tuning > > as possible. > > > > In trying to think about how it will/would do that I think PostgreSQL > > will need to know "how much" of the resources of the server its on, it's > > allowed to take. > > > > Can think of three scenario's, 1) Single-purpose PostgreSQL server 2) > > shared function server (i.e. Apache, Postgres, etc on the same box) 3) > > Embedded or otherwise resource limited server (Palmtop, etc). > > > > When we get around to PostgreSQL's self-tuning ability being actively > > developed (and I think Bruce has done some of the very start with his > > monitor program), perhaps having a compile time option to set the > > default for the server, and a runtime option in case it changes? > > > > i.e. > > > > --tuning=superserver > > --tuning=shared > > --tuning=embedded > > > > postmaster -t superserver > > postmaster -t shared > > postmaster -t embedded > > > > What do people think? > > > > Regards and best wishes, > > > > Justin Clift > > > > P.S. - I'm not on the Hackers mailing list from this account. Can > > anyone responding please include me directly in their replies? > > > > -- > > "My grandfather once told me that there are two kinds of people: those > > who work and those who take the credit. He told me to try to be in the > > first group; there was less competition there." > > - Indira Gandhi > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl >
Hi Bruce, My thought on this is more for an "overall effect". Down The Track (i.e. in a few versions or so) I'm thinking, rightly or wrongly, that PostgreSQL will become Very Good at tuning itself. It would be a good thing if PostgreSQL could know just how fair it can play in regards to the server it's working on. For example, if lets say it's installed on a server in which it's the only important thing. i.e. OS + PostgreSQL and thats about it. Indicating to the PostgreSQL server that's it's allowed to consume all the available resources to its maximum benefit would allow possible future "self-tuning" algorithms to say "well, in these circumstances the best way to deal with the present load is X". And it would do things without regard for other possible services, as it would know that it's running by itself. This would be something like a "--tuning=superserver" compile-time option or run-time flag. Conversely, the PostgreSQL server may be on a box with several other services, like Apache, MySQL, FTP daemons, and so forth. In that case it would possibly select different algorithms, knowing that it had to "play fair" with the server's resources. This may be indicated to it by a "--tuning=shared" compile-time option or run-time flag. And similar for embedded systems, where there is a lower or different resource allocation strategy. This is a general indication of thoughts I was having last night and this morning, and I bring it up more as a point of interest and wondering if others see that it may be of benefit. Presently we have to benchmark and then hand-tune the servers ourselves, and thats good. I'm thinking more about PostgreSQL's internal ways of dealing with queries and handling of resources though, in a second-by-second situation. What do you think? Regards and best wishes, Justin Clift Bruce Momjian wrote: > > My idea was to have PostgreSQL output tips to help performance. The > TODO item is: > > * Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM > ANALYZE, and CLUSTER > > I also will be writing an article on performance tuning this month. > What parameters would these options you suggest control? I usually > prefer options that have more concrete effect. > > > Just thinking about the future directions PostgreSQL is taking, and it > > seems (just a feeling) like most people prefer it to be as self tuning > > as possible. > > > > In trying to think about how it will/would do that I think PostgreSQL > > will need to know "how much" of the resources of the server its on, it's > > allowed to take. > > > > Can think of three scenario's, 1) Single-purpose PostgreSQL server 2) > > shared function server (i.e. Apache, Postgres, etc on the same box) 3) > > Embedded or otherwise resource limited server (Palmtop, etc). > > > > When we get around to PostgreSQL's self-tuning ability being actively > > developed (and I think Bruce has done some of the very start with his > > monitor program), perhaps having a compile time option to set the > > default for the server, and a runtime option in case it changes? > > > > i.e. > > > > --tuning=superserver > > --tuning=shared > > --tuning=embedded > > > > postmaster -t superserver > > postmaster -t shared > > postmaster -t embedded > > > > What do people think? > > > > Regards and best wishes, > > > > Justin Clift > > > > P.S. - I'm not on the Hackers mailing list from this account. Can > > anyone responding please include me directly in their replies? > > > > -- > > "My grandfather once told me that there are two kinds of people: those > > who work and those who take the credit. He told me to try to be in the > > first group; there was less competition there." > > - Indira Gandhi > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
> Hi Bruce, > > My thought on this is more for an "overall effect". > > Down The Track (i.e. in a few versions or so) I'm thinking, rightly or > wrongly, that PostgreSQL will become Very Good at tuning itself. > > It would be a good thing if PostgreSQL could know just how fair it can > play in regards to the server it's working on. OK, what options would you recommend be auto-tuned in each circumstance? I can imagine open files and maybe sortmemory, but even then, other backends can affect the proper value. Share memory usually has a kernel limit which prevents us from auto-tuning that too much. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
An excellent idea. I suspect you'll get a biased resonse from the -hackers folks. This really is an excellent idea. Those options cover I think the main scenarios, with the first two options being the most important. Ideally you'd basically sample server specs (speed, # of procs, mem etc) and set up for that based on profile. It should then be possible to dump the settings that are used (--tuning = these cmdline --options changed from defaults). Novices can use it to get of the ground, intermediate level dba's can use it as a sizing tool, and -hackers can flame each other over its very existence. August ----- Original Message ----- From: "Justin Clift" <jclift@iprimus.com.au> Newsgroups: comp.databases.postgresql.hackers Sent: Sunday, April 08, 2001 11:36 PM Subject: "--tuning" compile and runtime option (?) > Hi guys, > > Just thinking about the future directions PostgreSQL is taking, and it > seems (just a feeling) like most people prefer it to be as self tuning > as possible. > > In trying to think about how it will/would do that I think PostgreSQL > will need to know "how much" of the resources of the server its on, it's > allowed to take. > > Can think of three scenario's, 1) Single-purpose PostgreSQL server 2) > shared function server (i.e. Apache, Postgres, etc on the same box) 3) > Embedded or otherwise resource limited server (Palmtop, etc). > > When we get around to PostgreSQL's self-tuning ability being actively > developed (and I think Bruce has done some of the very start with his > monitor program), perhaps having a compile time option to set the > default for the server, and a runtime option in case it changes? > > i.e. > > --tuning=superserver > --tuning=shared > --tuning=embedded > > postmaster -t superserver > postmaster -t shared > postmaster -t embedded > > What do people think? > > Regards and best wishes, > > Justin Clift >
I'd be happy to see during initial setup a few questions go by that would size the underlying OS properly as well. We all do the same things with a new system, increase filesystem limits etc... Some of these options (on a dedicated postgresql) are gimme's. Why not do them once upfront, prompt the user (share memory, file handles) are to low, should I increase the limits? I'd love it, and some of the "PostgreSQL doesn't scale even the the load is low" complaints would go away. The hitch I can see is that much will be distribution/platform specific, but those don't change that radically that motivated volunteers couldn't keep pace. August "Bruce Momjian" <pgman@candle.pha.pa.us> wrote in message news:200104091744.NAA12563@candle.pha.pa.us... > OK, what options would you recommend be auto-tuned in each circumstance? > I can imagine open files and maybe sortmemory, but even then, other > backends can affect the proper value. Share memory usually has a kernel > limit which prevents us from auto-tuning that too much. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
I can understand why there might be some resistance to the idea of adding performance tuning flags into the server rather than documenting existing settings better, but I think a compromise would be possible. Could we develop a helper application that takes the --tuning superserver argument and translates that into a set of options to pass? That way, fine-tuning by hand is still practical, but for those who just want a good first set of values, a tuning helper application that looks at system memory, processor speed, and a user-supplied indication of the system's purpose and produces a set of postmaster options might be the way to approach this. And we don't bloat the server with extra algorithms. There is no dependency on this utility, either (assuming we continue to use workable defaults for postmaster options!) but it may benefit some people to use it. I really like the performance hints thing too. John -- John Gray Tel +44-7974-100-584 mailto:jgray@beansindustry.co.uk
The problem is that I can't figure out what would be tuned by these options. We only have 2-3 parameters that can be changed. > I can understand why there might be some resistance to the idea of > adding performance tuning flags into the server rather than documenting > existing settings better, but I think a compromise would be possible. > > Could we develop a helper application that takes the --tuning > superserver argument and translates that into a set of options to pass? > That way, fine-tuning by hand is still practical, but for those who just > want a good first set of values, a tuning helper application that looks > at system memory, processor speed, and a user-supplied indication of the > system's purpose and produces a set of postmaster options might be the > way to approach this. And we don't bloat the server with extra > algorithms. There is no dependency on this utility, either (assuming we > continue to use workable defaults for postmaster options!) but it may > benefit some people to use it. > > I really like the performance hints thing too. > > John > > -- > John Gray > Tel +44-7974-100-584 > mailto:jgray@beansindustry.co.uk > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Indeed, as an avid user (and tuner, I suppose) of PostgreSQL, I can't see how any configure option would be faster or better than the existing command line /config file parameters -- it would only serve to make things harder to deal with IMHO. "Tuning" PostgreSQL is pretty simple, and is explained pretty well throughout the manual (especially in the section titled "Understanding Performance"). We have -S -B and the fsync options, <Austin Powers Voice> That's about it.. </Austin Powers Voice> --- right? All are explained in the manual and are as easy to use as anyone could ask... Any OS tuning should be left up to the administrator as that's what administrators are for :-) Just my humble $0.02 worth.. -Mitch Software development : You can have it cheap, fast or working. Choose two. ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: "John Gray" <jgray@beansindustry.co.uk> Cc: <pgsql-hackers@postgresql.org> Sent: Tuesday, April 10, 2001 10:37 AM Subject: Re: Re: "--tuning" compile and runtime option (?) > > The problem is that I can't figure out what would be tuned by these > options. We only have 2-3 parameters that can be changed. > > > > I can understand why there might be some resistance to the idea of > > adding performance tuning flags into the server rather than documenting > > existing settings better, but I think a compromise would be possible. > > > > Could we develop a helper application that takes the --tuning > > superserver argument and translates that into a set of options to pass? > > That way, fine-tuning by hand is still practical, but for those who just > > want a good first set of values, a tuning helper application that looks > > at system memory, processor speed, and a user-supplied indication of the > > system's purpose and produces a set of postmaster options might be the > > way to approach this. And we don't bloat the server with extra > > algorithms. There is no dependency on this utility, either (assuming we > > continue to use workable defaults for postmaster options!) but it may > > benefit some people to use it. > > > > I really like the performance hints thing too. > > > > John > > > > -- > > John Gray > > Tel +44-7974-100-584 > > mailto:jgray@beansindustry.co.uk > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
> I can't see how any configure option would be faster or > better than the existing command line /config file parameters > -- it would only serve to make things harder to deal with IMHO. > "Tuning" PostgreSQL is pretty simple, and is explained pretty > well throughout the manual (especially in the section titled > "Understanding Performance"). We have -S -B and the fsync > options, <Austin Powers Voice> That's about it.. </Austin Powers > Voice> --- right? > All are explained in the manual and are as easy to use as anyone could > ask... Any OS tuning should be left up to the administrator > as that's what administrators are for :-) Someday, maybe other parameters (such as some of the optimizer's default values) will be configurable/changable on the fly. Would seem silly to have to stop/start the server to change those. Just an example, I'm sure there are other internal values that admins would like to tweak without recompiling or restarting. Would be much nicer to login as the superuser to do it rather than shut down my site. darrenk
Well, what is being discussed here would require a *recompile* to change the values, so you sure wouldn't want that! :-) Currently you can set a lot of internal variables with the SET command in any PG session.. It's useful for forcing index or sequential scans and such (and I'm sure a lot more).. -Mitch Software development : You can have it cheap, fast or working. Choose two. ----- Original Message ----- From: "Darren King" <darrenk@insightdist.com> To: <pgsql-hackers@postgresql.org> Sent: Tuesday, April 10, 2001 11:13 AM Subject: RE: Re: Re: "--tuning" compile and runtime option (?) > > I can't see how any configure option would be faster or > > better than the existing command line /config file parameters > > -- it would only serve to make things harder to deal with IMHO. > > "Tuning" PostgreSQL is pretty simple, and is explained pretty > > well throughout the manual (especially in the section titled > > "Understanding Performance"). We have -S -B and the fsync > > options, <Austin Powers Voice> That's about it.. </Austin Powers > > Voice> --- right? > > All are explained in the manual and are as easy to use as anyone could > > ask... Any OS tuning should be left up to the administrator > > as that's what administrators are for :-) > > Someday, maybe other parameters (such as some of the optimizer's default > values) will be configurable/changable on the fly. Would seem silly to > have to stop/start the server to change those. > > Just an example, I'm sure there are other internal values that admins > would like to tweak without recompiling or restarting. Would be much > nicer to login as the superuser to do it rather than shut down my site. > > darrenk > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
We do have postgresql.conf. I am unsure how I would tune those based on a single flag. > > I can't see how any configure option would be faster or > > better than the existing command line /config file parameters > > -- it would only serve to make things harder to deal with IMHO. > > "Tuning" PostgreSQL is pretty simple, and is explained pretty > > well throughout the manual (especially in the section titled > > "Understanding Performance"). We have -S -B and the fsync > > options, <Austin Powers Voice> That's about it.. </Austin Powers > > Voice> --- right? > > All are explained in the manual and are as easy to use as anyone could > > ask... Any OS tuning should be left up to the administrator > > as that's what administrators are for :-) > > Someday, maybe other parameters (such as some of the optimizer's default > values) will be configurable/changable on the fly. Would seem silly to > have to stop/start the server to change those. > > Just an example, I'm sure there are other internal values that admins > would like to tweak without recompiling or restarting. Would be much > nicer to login as the superuser to do it rather than shut down my site. > > darrenk > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > OK, what options would you recommend be auto-tuned in each circumstance? > I can imagine open files and maybe sortmemory, but even then, other > backends can affect the proper value. Share memory usually has a kernel > limit which prevents us from auto-tuning that too much. Share memory might have a kernel limit, but that's no excuse for not allowing this process to auto-tune it. I have truckloads of memory in my server if I am setting it up for a serious database, and I usually edit /etc/sysctl.conf (Debian GNU/Linux, and some other Linux's - possibly other unixen as well) to set the shared memory. Usually I set it to around 90% of the actual RAM in the system. So, if I have 1G RAM, and my database is 600M but my application only ends up hitting 20% of that on a regular basis do I benefit from adjusting my -B beyond 12000 or so? A question that the docs seem to think is 'suck it and see'. I haven't had the time or equipment to benchmark stuff in a wide range of hardware environments, myself, but if an auto-tune option suggested to me that performance increased up to a -B of 4000 or so, and that the server stopped working past there, I'm afraid that only an idiot would cease investigating at that point :-) It would be wonderful if the auto-tuning gave sensible advice in these sorts of situations, and then made some further suggestions that an operator might use to take the tuning to the next level. A mention of kernel shared memory limits would seem appropriate in there somewhere. The problem I usually have with a lot of "auto tuning" (and other sorts of automation) on other software is that it takes the approach that the user knows nothing, and we "don't want to bother their pretty little heads with these sorts of problems". I feel like a total _blonde_ when I use MS SQL Server, because it either hides the possibility of me adjusting it, or it doesn't explain what/how/why to adjust. PostgreSQL should, of course, offer advice. It shouldn't assume that because I've said "auto-tune" that I don't want to know why it is doing what it is doing. What conclusions it has come to, and what decisions it has made as a result. Finally, thanks for pursuing these options. I think they will be a huge help, as well as hopefully providing more data on performance issues back to the core team. That'll be 2c, please :-) Andrew. -- _____________________________________________________________________ Andrew McMillan, e-mail: Andrew@catalyst.net.nz Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
Well, again, I will write a performance tuning article this month, which hopefyully will help people. My recommendation on shared memory is that if you have a machine that is going to be used only for PostgreSQL, the shared memory should be increased to the point where you are not seeing any swap page-ins during normal use. I know you have the kernel buffer cache for all unused memory, but those pages are copied in and out of the PostgreSQL buffer cache for processing, which can be an expensive operation. Now how do you automate something to increase shared memory until there are no page swap-ins under normal use. I think the administrator will have to be involved because a script has no idea what a normal load looks like. The best we could do is to monitor swap-ins as part of the running server and report to the administrator that there is extra memory around that could be used for shared memory. > Bruce Momjian wrote: > > > > OK, what options would you recommend be auto-tuned in each circumstance? > > I can imagine open files and maybe sortmemory, but even then, other > > backends can affect the proper value. Share memory usually has a kernel > > limit which prevents us from auto-tuning that too much. > > Share memory might have a kernel limit, but that's no excuse for not > allowing this process to auto-tune it. > > I have truckloads of memory in my server if I am setting it up for a > serious database, and I usually edit /etc/sysctl.conf (Debian GNU/Linux, > and some other Linux's - possibly other unixen as well) to set the > shared memory. Usually I set it to around 90% of the actual RAM in the > system. > > So, if I have 1G RAM, and my database is 600M but my application only > ends up hitting 20% of that on a regular basis do I benefit from > adjusting my -B beyond 12000 or so? A question that the docs seem to > think is 'suck it and see'. I haven't had the time or equipment to > benchmark stuff in a wide range of hardware environments, myself, but if > an auto-tune option suggested to me that performance increased up to a > -B of 4000 or so, and that the server stopped working past there, I'm > afraid that only an idiot would cease investigating at that point :-) > > It would be wonderful if the auto-tuning gave sensible advice in these > sorts of situations, and then made some further suggestions that an > operator might use to take the tuning to the next level. A mention of > kernel shared memory limits would seem appropriate in there somewhere. > > The problem I usually have with a lot of "auto tuning" (and other sorts > of automation) on other software is that it takes the approach that the > user knows nothing, and we "don't want to bother their pretty little > heads with these sorts of problems". I feel like a total _blonde_ when > I use MS SQL Server, because it either hides the possibility of me > adjusting it, or it doesn't explain what/how/why to adjust. PostgreSQL > should, of course, offer advice. It shouldn't assume that because I've > said "auto-tune" that I don't want to know why it is doing what it is > doing. What conclusions it has come to, and what decisions it has made > as a result. > > Finally, thanks for pursuing these options. I think they will be a huge > help, as well as hopefully providing more data on performance issues > back to the core team. > > That'll be 2c, please :-) > Andrew. > -- > _____________________________________________________________________ > Andrew McMillan, e-mail: Andrew@catalyst.net.nz > Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington > Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267 > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote:
>
> Well, again, I will write a performance tuning article this month, which
> hopefyully will help people.
>
> My recommendation on shared memory is that if you have a machine that is
> going to be used only for PostgreSQL, the shared memory should be
> increased to the point where you are not seeing any swap page-ins during
> normal use. I know you have the kernel buffer cache for all unused
> memory, but those pages are copied in and out of the PostgreSQL buffer
> cache for processing, which can be an expensive operation.
>
> Now how do you automate something to increase shared memory until there
> are no page swap-ins under normal use. I think the administrator will
> have to be involved because a script has no idea what a normal load
> looks like. The best we could do is to monitor swap-ins as part of the
> running server and report to the administrator that there is extra
> memory around that could be used for shared memory.
Brilliant. Thanks for that - it's exactly the sort of information / statistics
stuff that it is useful to know.
I use Progress RDBMS on a few sites. On a Progress database I get this sort of
information which can help me tune things:
Activity - Sampled at 04/11/01 12:32 for 892:23:25.
Event Total Per Sec Event Total Per Sec Commits 50518 0.0
Undos 24 0.0 Record Updates 72407 0.0 Record Reads 121294681 37.7
RecordCreates 37065 0.0 Record Deletes 19807 0.0 DB Writes 25720 0.0
DB Reads 1551040 0.4 BI Writes 14701 0.0 BI Reads 14534 0.0
AIWrites 0 0.0 Record Locks 645952 0.2 Record Waits 0 0.0
Checkpoints 62 0.0 Buffers Flushed 13102 0.0
Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 % Writes by APW 0 % Writes by BIW 0 %
Writes by AIW 0 % Buffer Hits 16 % DB Size 96 MB BI Size 3192 K AI Size 0 K
FRchain 0 blocks RM chain 1 blocks Shared Memory 29864 K Segments 1
8 Servers, 7 Users (0 Local, 7 Remote, 0 Batch),0 Apws
Or, for a more reasonable length of sample:
Activity - Sampled at 04/11/01 12:42 for 0:09:26.
Event Total Per Sec Event Total Per Sec Commits 14 0.0
Undos 0 0.0 Record Updates 7 0.0 Record Reads 90488 159.8
RecordCreates 1 0.0 Record Deletes 0 0.0 DB Writes 38 0.0
DB Reads 1636 2.8 BI Writes 5 0.0 BI Reads 0 0.0
AIWrites 0 0.0 Record Locks 69 0.1 Record Waits 0 0.0
Checkpoints 0 0.0 Buffers Flushed 0 0.0
Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 % Writes by APW 0 % Writes by BIW 0 %
Writes by AIW 0 % Buffer Hits 99 % DB Size 96 MB BI Size 3192 K AI Size 0 K
FRchain 0 blocks RM chain 1 blocks Shared Memory 29864 K Segments 1
8 Servers, 9 Users (0 Local, 9 Remote, 0 Batch),0 Apws
I find this is quite a straightforward and useful set of statistics. Just having
this sort of functionality easily available gets me used to the sorts of numbers I
can expect in different hardware environments. It is then simple to conduct basic
tuning by running reports (or other operations) and seeing the sorts of numbers you
get for the sample period.
Of course Progress has a bunch more stuff you can tune, including separate processes
for asynchronously writing database pages, or their after-image and before-image
files. I don't have any databases that get that arcane though, hence the APW, BIW
and AIW statistics are zero above.
Regards, Andrew.
--
_____________________________________________________________________ Andrew McMillan, e-mail:
Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267