Thread: go for a script! / ex: PostgreSQL vs. MySQL

go for a script! / ex: PostgreSQL vs. MySQL

From
"Oliver Scheit"
Date:
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

Re: go for a script! / ex: PostgreSQL vs. MySQL

From
Paul Thomas
Date:
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   |
+------------------------------+---------------------------------------------+

Re: go for a script! / ex: PostgreSQL vs. MySQL

From
Josh Berkus
Date:
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

Re: go for a script! / ex: PostgreSQL vs. MySQL

From
Dror Matalon
Date:
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

Re: go for a script! / ex: PostgreSQL vs. MySQL

From
Sean Chittenden
Date:
> 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

Re: go for a script! / ex: PostgreSQL vs. MySQL

From
Vivek Khera
Date:
>>>>> "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/