Re: Upgrade to dual processor machine?

On Wed, 13 Nov 2002, Henrik Steffen wrote:

>
> Hello Shridhar,
>
> thanks for your answer...
>
> 1) in the docs it says: shared_buffers should be 2*max_connections, min 16.
> now, you suggest to put it to 500-600 MB, which means I will have to
> increase shared_buffers to 68683 -- is this really correct? I mean,
> RAM is allready now almost totally consumed.

Actually, that's not quite correct.  The RAM is already showing as being
in use, but it's being used by the kernel as file cache, and will be
released the second a process asks for more memory, so it really isn't "in
use" in the classic sense.

> 2) the database has a size of 3.6 GB at the moment... about 100 user tables.
>
> 3) ok, I understand: I am not creating any indexes usually. Only once at night
> all user indexes are dropped and recreated, I could imagine to increase the
> sort_mem for this script... so sort_mem with 1024K is ok, or should it be
> lowered to, say, 512K ?

Generally a sort mem of 8 meg or less is pretty safe, as the allocation is
only made WHILE the sort is running and is released right after.  The
danger is that if it is set higher, like say 32 or 64 meg, and a dozen or
so sql statements just happen to all sort at the same time, you can run
out of memory and have a "swap storm" where the machine is swapping out
processes one after the other to give each the amount of swap space it
needs.  Note also that a SQL query with more than one sort in it will use
up to sort_mem for each sort independently, so a dozen SQL queries that
each require say three sorts all running at once can theoretically use
36*sort_mem amount of memory.  Once the machine starts swapping for
sort_mem, things get slow VERY fast.  It's one of those knees you don't
want to hit.