Thread: CPU Load question / PgBouncer config
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
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