Date: Mon, 14 May 2012 12:13:54 -0500
From: "ktm@rice.edu" <ktm@rice.edu>
To: Benjamin Krajmalnik <kraj@servoyant.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: CPU Load question / PgBouncer config
Message-ID: <20120514171354.GF6020@aart.rice.edu>
References: <F4E6A2751A2823418A21D4A160B68988A5D331@fletch.stackdump.local>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <F4E6A2751A2823418A21D4A160B68988A5D331@fletch.stackdump.local>
User-Agent: Mutt/1.5.20 (2009-12-10)

On Mon, May 14, 2012 at 11:02:30AM -0600, Benjamin Krajmalnik wrote:
> I am experiencing a little higher CPU load than I would like to see, and
> was wondering if it has to do with the number of connections (although
> many are idle).
> 
> I am running PG 9.0.4/amd64 on FreeBSD 8.1., dual boxes running
> streaming replication.
> 
> Hardware is a 16 core box with 96GB RAM, using 6GB of shared buffers.
> 
> RAID 1 for OS, 12 drive RAID 10 for data, RAID 1 for logs.
> 
> I am using pgbouncer as a connection pooler, with 200 max client
> connections, a default pool size of 100, and a reserve of 80.
> 
> The number of backends running are between 40 and 70.  I monitor the
> number of non-idle connections.  Average is about 20, with non-frequent
> spikes going to about 40.
> 
> My gut feeling is that I can probably reduce the pool size somewhat.
> 
> Would reducing the number of backends relieve some of the load on the
> server (I am seeing a 5 min load of 12 more often than I would like to)?
> 
> Also, for this size setup, what would be the recommended values for the
> pool sizes?  I am certain the values are way above what they should be,
> but nbot certain.
> 
>  
> 
> I posted here instead of the pgbouncer group hoping someone has
> experienced similar things.
> 
>  
> 
> Prresently, I have the following load, 
> 
>  
> 
>  
> 
> last pid: 96231;  load averages: 12.18, 11.07, 10.97
> up 112+19:23:29 11:02:05
> 
>  
> 
> and this is the result from checking the state of pgbouncer:
> 
>  
> 
> pgbouncer=# SHOW POOLS;
> 
> database  |   user    | cl_active | cl_waiting | sv_active | sv_idle |
> sv_used | sv_tested | sv_login | maxwait 
> 
> -----------+-----------+-----------+------------+-----------+---------+-
> --------+-----------+----------+---------
> 
> ishield   | xxxxxxx  |        48 |          0 |        48 |       4 |
> 2 |         0 |        0 |       0
> 
> pgbouncer | pgbouncer |         1 |          0 |         0 |       0 |
> 0 |         0 |        0 |       0
> 
> (2 rows)
> 
>  
> 
>  
> 
> pgbouncer=# SHOW POOLS;
> 
> database  |   user    | cl_active | cl_waiting | sv_active | sv_idle |
> sv_used | sv_tested | sv_login | maxwait 
> 
> -----------+-----------+-----------+------------+-----------+---------+-
> --------+-----------+----------+---------
> 
> ishield   | xxxxxxx  |        55 |          0 |        55 |       0 |
> 2 |         0 |        0 |       0
> 
> pgbouncer | pgbouncer |         1 |          0 |         0 |       0 |
> 0 |         0 |        0 |       0
> 
> (2 rows)
> 
>  
> 
>  
> 
>  
> 
> Thanks in advance,
> 
>  
> 
> Benjamin
> 

Hi Benjamin,

For a 16-core box a load of 12 is only at 75% capacity, so it sounds like you
are doing just fine. It does seem like your connection pool is a bit large
for your hardware. A common recommendation is:

(number_of_cores * 2) + effective_spindle_count

or for your box: (16 * 2) + 6 = 38

It looks like you are about 2.5X that on your system.

Regards,
Ken