Re: Memory Issue

Hi Scott,

I followed your advise and I run the test with the changes suggested at
points 1,2 and 3 below and my performance test run for 18 hours without
swapping. I did have a 40% drop in performance but I think that is a different problem. I will run more tests and post the results if anyone is interested.

BTW, I did read Postgresql 9.0 High Performance written by Greg Smith yesterday and he does mention about these parameters. I should have done that earlier, it is a great book.

Thank you for your help,
Ioana

----- Original Message -----
From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Ioana Danes <ioanasoftware(at)yahoo(dot)ca>
Cc: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, November 3, 2011 10:30:27 AM
Subject: Re: [GENERAL] Memory Issue

On Thu, Nov 3, 2011 at 7:34 AM, Ioana Danes <ioanasoftware(at)yahoo(dot)ca> wrote:

> After another half an hour almost the entire swap is used and the system performs really bad 100 TPS or lower.
> It never runs out of memory though!
>
> I would like to ask for your opinion on this issue.
> My concerns are why the memory is not reused earlier and it is using the swapping when the system does only these 2 inserts.
> Is this an OS issue, postgres issue, configuration issue?
> Your advice is greatly appreciated.

You can try a few things.

1: lower your shared_buffers.  It's unlikely you really need 4G
for
them.  A few hundred megs is probably plenty for the type of work
you're doing.  Let the kernel cache the data you're not hitting right
this second.

2: Set swappiness to 0.    I.e. edit /etc/sysctl.conf and add a line
like vm.swappiness = 0 then run sudo sysctl
-p

3: Turn off overcommit.  Same as number 2, set vm.overcommit_memory =
2 which will turn off the ability of linux to overcommit memory and
should then turn off the OOM killer.

4: just turn off swap. With only 16Gigs this is a tad dangerous,
especially if you haven't turned off the OOM in step 3.  Memory is
cheap, throw 32G at least into the machine.  With 1200 users, you
really need plenty of memory.  To turn off swap add something like
/sbin/swapoff -a to the /etc/rc.local file (before the exit line
natch)