Re: Postgres performance comments from a MySQL user

> -----Original Message-----
> From: Ernest E Vogelsinger [mailto:ernest@vogelsinger.at]
> Sent: Thursday, June 12, 2003 12:38 PM
> To: Justin Clift
> Cc: Joseph Shraibman; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Postgres performance comments from a MySQL user
>
>
> At 15:20 12.06.2003, Justin Clift said:
> --------------------[snip]--------------------
> >Probably it's a good idea to have some mention of this, as
> even though
> >we
> >should alter the source to higher defaults for our next
> release, there are
> >potentially
> >people that would read a message like this and go "wow,
> didn't know that",
> >then tune their existing installations as well.
> --------------------[snip]--------------------
>
> Be careful with increasing memory defaults - I just took over
> a RH7.2 server that still had the SHMALL and SHMMAX settings
> left at their default (2MB), for a 2x1000/1GB machine!
> Turning up the shared_buffers and sort_mem parameters
> immediately caused postmaster to fail, of course. Could turn
> out messy with newbies, IMHO.
>
> Of course I immediately gained a recognizable performance
> boost by stuffing up the 2 OS parameters to 128MB, and
> setting both shared_buffers and sort_mem to 4000, even before
> I dropped in two indexes on some heavily filled tables where
> queries were executing sequential searches for 2 rows out of
> a million... *sigh*
>
> I believe the idea of an intelligent install script would be
> near perfect. It should check the current system hardware and
> OS memory settings, make intelligent decisions for
> manipulation of SHMALL and SHMMAX as well as shared_buffers
> and sort_mem, and ask the user/installer to let it modify
> these values. Should be a valuable tool, even for finetuning
> performance later on.

How about (for POSIX systems) calling sar and vmstat or inqiring against
limits.h and using sysconf() in a C program or something similar to
that?

It seems that it should not be too difficult to collect all the
information necessary to create a nearly optimal or at least fairly well
fitted set of installation parameters.