Re: Why is this system swapping? - Mailing list pgsql-performance

"Anjan Dave" <adave@vantage.com> writes:

> Some background:
>
> This is a quad XEON (yes, Dell) with 12GB of RAM, pg 7.4...pretty heavy
> on concurrent usage. With peak traffic (db allows 1000 connections, in
> line with the number of app servers and connection pools for each)
> following is from 'top' (sorted by mem) Shared_buffers is 170MB,
> sort_mem 2MB. Both WAL and pgdata are on separate LUNs on fibre channel
> storage, RAID10.
>
> 972 processes: 971 sleeping, 1 running, 0 zombie, 0 stopped
>
> CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
>            total   57.2%    0.0%   23.2%   0.0%     3.6%   82.8%  232.4%

This looks to me like most of your server processes are sitting around idle
most of the time.

> 21397 postgres  22   0  181M 180M  175M D    25.9  1.5  85:17   0
> postmaster
>
> 23820 postgres  15   0  178M 177M  175M S     0.0  1.5   1:53   3
> postmaster

So each process is taking up 8-11M of ram beyond the shared memory. 1,000 x
10M is 10G. Add in some memory for page tables and kernel data structures, as
well as the kernel's need to keep some memory set aside for filesystem buffers
(what you really want all that memory being used for anyways) and you've used
up all your 12G.

I would seriously look at tuning those connection pools down. A lot. If your
server processes are sitting idle over half the time I would at least cut it
by a factor of 2.

Working the other direction: you have four processors (I guess you have
hyperthreading turned off?) so ideally what you want is four runnable
processes at all times and as few others as possible. If your load typically
spends about half the time waiting on i/o (which is what that top output says)
then you want a total of 8 connections.

Realistically you might not be able to predict which app server will be
providing the load at any given time, so you might want 8 connections per app
server.

And you might have some load that's more i/o intensive than the 50% i/o load
shown here. Say you think some loads will be 80% i/o, you might want 20
connections for those loads. If you had 10 app servers with 20 connections
each for a total of 200 connections I suspect that would be closer to right
than having 1,000 connections.

200 connections would consume 2G of ram leaving you with 10G of filesystem
cache. Which might in turn decrease the percentage of time waiting on i/o,
which would decrease the number of processes you need even further...

--
greg