Thread
-
"--tuning" compile and runtime option (?)
Justin Clift <jclift@iprimus.com.au> — 2001-04-09T01:33:50Z
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 -
Re: "--tuning" compile and runtime option (?)
Bruce Momjian <pgman@candle.pha.pa.us> — 2001-04-09T04:18:32Z
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
-
Re: "--tuning" compile and runtime option (?)
Rod Taylor <rbt@barchord.com> — 2001-04-09T04:22:20Z
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 >
-
Re: "--tuning" compile and runtime option (?)
Bruce Momjian <pgman@candle.pha.pa.us> — 2001-04-09T04:32:58Z
[ 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, Pennsylvania 19026
-
Re: "--tuning" compile and runtime option (?)
Peter Eisentraut <peter_e@gmx.net> — 2001-04-09T16:28:53Z
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/
-
Re: "--tuning" compile and runtime option (?)
August Zajonc <augustz@bigfoot.com> — 2001-04-09T16:46:25Z
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 >
-
Re: "--tuning" compile and runtime option (?)
Justin Clift <jclift@iprimus.com.au> — 2001-04-09T17:16:43Z
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 -
Re: "--tuning" compile and runtime option (?)
Bruce Momjian <pgman@candle.pha.pa.us> — 2001-04-09T17:44:40Z
> 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, Pennsylvania 19026
-
Re: "--tuning" compile and runtime option (?)
August Zajonc <junk-postgre@aontic.com> — 2001-04-10T01:21:57Z
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
-
Re: "--tuning" compile and runtime option (?)
Andrew McMillan <andrew@catalyst.net.nz> — 2001-04-10T12:04:20Z
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 -
Re: "--tuning" compile and runtime option (?)
Bruce Momjian <pgman@candle.pha.pa.us> — 2001-04-10T21:06:23Z
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, Pennsylvania 19026
-
Re: "--tuning" compile and runtime option (?)
Andrew McMillan <andrew@catalyst.net.nz> — 2001-04-11T03:02:46Z
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 Record Creates 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 AI Writes 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 FR chain 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 Record Creates 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 AI Writes 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 FR chain 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