Re: PostgreSQL Performance on OpenBSD

On Mon, 19 May 2003, Martin Foster wrote:

> scott.marlowe wrote:
> > I'm pretty sure sort buffer is measured in k, not 8k units. i.e.
> >
> > 16384 sort mem is 16 Megs.
> >
> > Or are you referring to something other than sort_mem?
> >
>
> It seems that you are correct. Just took a look at the configuration
> file and while shared_buffers are in 8K units, or seem to be the
> sort_mem is in K units meaning that I am allocating less then expected.
>
> 128 x 1024 / 8 = 16384 (shared_buffers)
> 20 x 1024 = 20480 (sort_mem)
>
> Are these numbers normal? Since they start you out at considerably
> higher then the default/minimum values that PostgreSQL advertises in
> it's configuration file.

Yeah, those numbers are fairly typical.

Unless you have truly huge sorts going on, 16 megs or so is a good
compromise number. note that you can change sort_mem for the current
session with set sort_mem=nnnn; so if you need a big sort mem every now
and then, you can do it for just that one query, and leave it smaller.

Since shared_buffers are fixed and don't change during operation, they
don't have the same danger that sort_mem does of dynamically running the
machine into the ground should too much memory gets allocated to sorts.
sort_mem is a limit per sort, so theorectically, a complex query could
generate more than one sort, and a handful of clients running large sorts
could run the machine of out RAM and into a swap storm as tries to service
all the backend process sorts. So, while having sort_mem too small costs
a little in performance, having it set too high can result in your server
coming to a crawl under load, which is definitely worse.

On servers with lots of memory, the real limit to shared_buffers is that
postgresql becomes less efficient at handling its cache as shared_buffers
gets to be a pretty big chunk of memory.

On my machine with 1.5 gig ram, I allocate 256 Megs ram to shared_buffers,
but only 16 Meg for sort_mem.