Re: appropriate sort_mem & shared buffers

Francisco Reyes <lists(at)natserv(dot)com> writes:
>> But since you didn't
>> say how many concurrent backends you expect to run, this is all just
>> theorizing in the dark.

> What is the relation between number of connections and buffers?

You need to ensure you have enough RAM left over (after allocating the
buffers) for all the backends you want to run. Don't forget that
sort_mem is per-backend, too.

My gut feeling is that a few thousand buffers (corresponding to a few
tens of megabytes of RAM) is the right range for production situations
on most modern machines.

As an example, yesterday I was running some tests of pgbench with scale
factor 50 and 64 clients, on a Linux box with 256MB RAM. At -B 4096
I got about 40 transactions/sec; at -B 8192 I got about 55; at -B 16384
it dropped off to about 32 t/s. vmstat showed considerable swapping
activity in the last case, so evidently I had run the machine out of RAM
by allocating too much to buffers.

> I was thinking that the higher the
> buffer size the longer it would take for the database to allocate the
> memory before it can serve the query.

No. The shared buffers are a static allocation that is made once when
the postmaster starts.

regards, tom lane