Thread: go for a script! / ex: PostgreSQL vs. MySQL
Hi guys, I followed the discussion and here are my 0.2$: I think instead of thinking about where to put the information about tuning, someone should provide a "pgsql-autotune". Maybe even a shell script would do the trick. It's not so hard to find out, how much memory is installed, and IMHO SHARED_BUFFERS, SORT_MEM and EFFECTIVE_CACHE_SIZE depend heavily on this. a "cat /proc/sys/kernel/shmmax" would give some valuable information on linux boxes, there is probably other stuff for different OSes. random_page_cost could be set after probing the harddisks, maybe even do a hdparm -tT if they seem to be ATA, not SCSI. Now, let's pretend the script finds out there is 1 GB RAM, it could ask something like "Do you want to optimize the settings for postgres (other applications may suffer from having not enough RAM) or do you want to use moderate settings?" Something like this, you get the idea. This would give new users a much more usable start than the current default settings and would still leave all the options to do fine-tuning later. I guess my point is simply this: instead of saying: "okay we use default settings that will run on _old_ hardware too" we should go for a little script that creates a "still save but much better" config file. There's just no point in setting SHARED_BUFFERS to something like 16 (what's the current default?) if the PC has >= 1 GB of RAM. Setting it to 8192 would still be save, but 512 times better... ;-) (IIRC 8192 would take 64 MB of RAM, which should be save if you leave the default MAX_CONNECTIONS.) As said before: just my $0.2 My opinion on this case is Open Source. Feel free to modify and add. :-) regards, Oli
On 09/10/2003 09:29 Oliver Scheit wrote: > Hi guys, > > I followed the discussion and here are my 0.2$: > > I think instead of thinking about where to put the > information about tuning, someone should provide a > "pgsql-autotune". Maybe even a shell script would do the > trick. > > It's not so hard to find out, how much memory is installed, > and IMHO SHARED_BUFFERS, SORT_MEM and EFFECTIVE_CACHE_SIZE > depend heavily on this. a "cat /proc/sys/kernel/shmmax" > would give some valuable information on linux boxes, > there is probably other stuff for different OSes. > > random_page_cost could be set after probing the harddisks, > maybe even do a hdparm -tT if they seem to be ATA, not SCSI. > > Now, let's pretend the script finds out there is 1 GB RAM, > it could ask something like "Do you want to optimize the > settings for postgres (other applications may suffer from > having not enough RAM) or do you want to use moderate > settings?" > > Something like this, you get the idea. ISR reading that 7.4 will use a default of shared_beffers = 1000 if the machine can support it (most can). This alone should make a big difference in out-of-the-box performance. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Oliver, > I think instead of thinking about where to put the > information about tuning, someone should provide a > "pgsql-autotune". Maybe even a shell script would do the > trick. Well, you see, there's the issue. "I think someone." Lots of people have spoken in favor of an "auto-conf" script; nobody so far has stepped forward to get it done for 7.4, and I doubt we have time now. I'll probably create a Perl script in a month or so, but not before that .... -- Josh Berkus Aglio Database Solutions San Francisco
Yeah, I had similar thought to Oliver's and suspected that this would be the answer. Also, while it's not too hard to do this for a single platform, it gets complecated once you start looking at different ones. Josh, let me know when you're ready to do this. I'll try to help, although my perl's kind of rusty. Also, can you even assume perl for a postgres install? Does Solaris, for instance come with perl? Dror On Thu, Oct 09, 2003 at 09:56:11AM -0700, Josh Berkus wrote: > Oliver, > > > I think instead of thinking about where to put the > > information about tuning, someone should provide a > > "pgsql-autotune". Maybe even a shell script would do the > > trick. > > Well, you see, there's the issue. "I think someone." Lots of people have > spoken in favor of an "auto-conf" script; nobody so far has stepped forward > to get it done for 7.4, and I doubt we have time now. > > I'll probably create a Perl script in a month or so, but not before that .... > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com
> Yeah, I had similar thought to Oliver's and suspected that this > would be the answer. Also, while it's not too hard to do this for a > single platform, it gets complecated once you start looking at > different ones. > > Josh, let me know when you're ready to do this. I'll try to help, > although my perl's kind of rusty. Also, can you even assume perl for > a postgres install? Does Solaris, for instance come with perl? Um, why not wait until the C version of initdb is committed, then steak out a section that'll allow us to submit patches to have initdb autotune to our hearts content? There's a tad bit of precedence with having shared buffer's automatically set in initdb, why not continue with it? I know under FreeBSD initdb will have some #ifdef's to wrap around the syscall sysctl() to get info about kernel bits. Talking about how to expand handle this gracefully for a gazillion different platforms might be a more useful discussion at this point because I'm sure people from their native OS will be able to contrib the necessary patches to extract info from their OS so that initdb can make useful decisions. Or, lastly, does anyone think that this should be in a different, external program? -sc -- Sean Chittenden
>>>>> "SC" == Sean Chittenden <sean@chittenden.org> writes: SC> patches to extract info from their OS so that initdb can make useful SC> decisions. Or, lastly, does anyone think that this should be in a SC> different, external program? -sc Well, there should definitely be a way to run a "get current best tuning advice" for those times when I go and do something like add a Gig of RAM. ;-) Also, I'm sure the tuning advice will change over time, so having to do initdb to get that advice would be a bit onerous. As long as initdb has an option for just getting the tuning info, I see no reason to make it separate. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/