Anjan Dave wrote:
> Great response, Thanks.
>
> Regarding 12GB memory and 13G db, and almost no I/O, one thing I don't
> understand is that even though the OS caches most of the memory and PG
> can use it if it needs it, why would the system swap (not much, only
> during peak times)? The SHMMAX is set to 512MB, shared_buffers is 150MB,
> effective cache size is 2GB, sort mem is 2MB, rest is default values. It
> also happens that a large query (reporting type) can hold up the other
> queries, and the load averages shoot up during peak times.
In regards to your system going to swap, the only item I see is sort_mem
at 2MB. How many simultaneous transactions do you get? If you get
hundreds or thousands like your first message stated, every select sort
would take up 2MB of memory regardless of whether it needed it or not.
That could cause your swap activity during peak traffic.
The only other item to bump up is the effective cache size -- I'd set it
to 12GB.