Re: PostgreSQL Performance on OpenBSD

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Martin Foster <martin(at)ethereal-realms(dot)org>
Cc: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: PostgreSQL Performance on OpenBSD
Date: 2003-05-19 20:13:58
Message-ID: Pine.LNX.4.33.0305191406450.19223-100000@css120.ihs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Wieck 2003-05-19 20:19:43 Re: Opposite value for RESTRICT in foreign keys?
Previous Message Martin Foster 2003-05-19 20:09:44 Re: PostgreSQL Performance on OpenBSD