Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 112E1137D6F6 for ; Mon, 14 May 2012 14:02:46 -0300 (ADT) Received: from [64.207.29.137] (helo=mail.illumen.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1STyfU-0003yn-Oh for pgsql-admin@postgresql.org; Mon, 14 May 2012 17:02:45 +0000 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CD31F3.58FF1E80" Subject: CPU Load question / PgBouncer config X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Mon, 14 May 2012 11:02:30 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: CPU Load question / PgBouncer config Thread-Index: Ac0x81jWV+2GVr/WQFqmjZBLiSdjPA== From: "Benjamin Krajmalnik" To: "pgsql-admin" X-Host-Lookup-Failed: Reverse DNS lookup failed for 64.207.29.137 (failed) X-Pg-Spam-Score: 0.8 (/) X-Archive-Number: 201205/62 X-Sequence-Number: 39551 This is a multi-part message in MIME format. ------_=_NextPart_001_01CD31F3.58FF1E80 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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. =20 I posted here instead of the pgbouncer group hoping someone has experienced similar things. =20 Prresently, I have the following load,=20 =20 =20 last pid: 96231; load averages: 12.18, 11.07, 10.97 up 112+19:23:29 11:02:05 =20 and this is the result from checking the state of pgbouncer: =20 pgbouncer=3D# SHOW POOLS; database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait=20 -----------+-----------+-----------+------------+-----------+---------+- --------+-----------+----------+--------- ishield | xxxxxxx | 48 | 0 | 48 | 4 | 2 | 0 | 0 | 0 pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (2 rows) =20 =20 pgbouncer=3D# SHOW POOLS; database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait=20 -----------+-----------+-----------+------------+-----------+---------+- --------+-----------+----------+--------- ishield | xxxxxxx | 55 | 0 | 55 | 0 | 2 | 0 | 0 | 0 pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (2 rows) =20 =20 =20 Thanks in advance, =20 Benjamin =20 ------_=_NextPart_001_01CD31F3.58FF1E80 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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           &n= bsp;           &nb= sp;           &nbs= p;            = ;       up 112+19:23:29 = 11:02:05

 

and this is the result from checking the state of = pgbouncer:

 

pgbouncer=3D# 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=3D# 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

 

------_=_NextPart_001_01CD31F3.58FF1E80--