Re: Postgres performance comments from a MySQL user - Mailing list pgsql-general

On Thu, 12 Jun 2003, Ernest E Vogelsinger wrote:

> 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.

I don't think that's the default.

On my RH 7.2 box it says shmmax is 33554432 which is 32 Megs.  SHMALL is
2097152, but that isn't in bytes, it's in pages, which are 4k on 7.2 for
32 bit intel.  That comes out to something insane like 80 gig

On RedHat boxes since 7.0 (and maybe before) the default max per segment
has been 32 Megs.  While that's big enough for good overall performance in
workgroups, it's still awefully small for a "real server".


> 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*

You may wanna check out what you set and make sure you're using the right
units.  Remember, SHMMAX is in bytes, but SHMALL is in pages.  Depending
on your platform, pages may be 4k or larger, but most installations of
linux, even on 64 bit sparcs and what not, are still configured for 4k
pages.

> 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.

Good idea.  Don't forget to make sure it sets effective_cache_size while
we're at it.  Just add up the kernel cache size and the free memory on a
machine to get an approximation.

Til it gets done, maybe the idea of a couple of different postgresql.conf
files (light, medium, heavy, big_iron) will likely do the trick.  And, if
we provide different default .conf files, then the ones that are for heavy
/ big_iron can have notes on where to go to find information on tuning
your OS to handle those configurations.