pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCPU Load question / PgBouncer config
2+ messages / 2 participants
[nested] [flat]
* CPU Load question / PgBouncer config
@ 2012-05-14 17:02 Benjamin Krajmalnik <kraj@servoyant.com>
0 siblings, 1 reply; 2+ messages in thread
From: Benjamin Krajmalnik @ 2012-05-14 17:02 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: CPU Load question / PgBouncer config
@ 2012-05-14 17:13 ktm@rice.edu <ktm@rice.edu>
parent: Benjamin Krajmalnik <kraj@servoyant.com>
0 siblings, 0 replies; 2+ messages in thread
From: ktm@rice.edu @ 2012-05-14 17:13 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@servoyant.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-05-14 17:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-05-14 17:02 CPU Load question / PgBouncer config Benjamin Krajmalnik <kraj@servoyant.com>
2012-05-14 17:13 ` ktm@rice.edu <ktm@rice.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox