MIME-Version: 1.0
From: =?UTF-8?B?7Jqw7ISx66+8?= <dntjdals0513@gmail.com>
Date: Mon, 4 Sep 2017 17:14:39 +0900
Message-ID: 
 <CABdtbz0LZOEXYAh6f=PpLvF57jk2B4UOH2y5ETtHpv7ibu_17A@mail.gmail.com>
Subject: Handling small inserts from many connections.
To: pgsql-performance@postgresql.org
Content-Type: multipart/mixed; boundary="94eb2c0595022dced3055858b6fa"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--94eb2c0595022dced3055858b6fa
Content-Type: multipart/alternative; boundary="94eb2c0595022dcebf055858b6f8"

--94eb2c0595022dcebf055858b6f8
Content-Type: text/plain; charset="UTF-8"

Hi team,

I'm trying to configure postgres and pgbouncer to handle many inserts from
many connections.

Here's some details about what i want to achieve :

  We have more than 3000 client connections, and my server program forks
backend process for each client connections.
  If backend processes send a request to its connected client, the client
send some text data(about 3000 bytes) to the backend process and wait for
  next request.
  The backend process execute insert text data using PQexec from libpq
lbirary, if PQexec is done, backend process send request to
  client again.

  All the inserts using one, same table.

The problem is, clients wait too long due to insert process is too slow.
It seems to working fine at first, but getting slows down after couple of
hours,
each insert query takes 3000+ ms and keep growing.

Need some help to figure out an actual causes of this problem.

System information :
  PGBouncer 1.7.2.
  PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-18), 64-bit on CentOS release 6.9 (Final).
  Kernel version 2.6.32-696.10.1.el6.x86_64
  Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz processor.
  32GB ECC/REG-Buffered RAM.
  128GB Samsung 840 evo SSD.

--94eb2c0595022dcebf055858b6f8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi team,</div><div><br></div><div>I&#39;m trying to c=
onfigure postgres and pgbouncer to handle many inserts from many connection=
s.</div><div><br></div><div>Here&#39;s some details about what i want to ac=
hieve :</div><div><br></div><div>=C2=A0 We have more than 3000 client conne=
ctions, and my server program forks backend process for each client connect=
ions.</div><div>=C2=A0 If backend processes send a request to its connected=
 client, the client send some text data(about 3000 bytes) to the backend pr=
ocess and wait for <br></div><div>=C2=A0 next request.<br></div><div>=C2=A0=
 The backend process execute insert text data using PQexec from libpq lbira=
ry, if PQexec is done, backend process send request to <br></div><div>=C2=
=A0 client again.</div><div><br></div><div>=C2=A0 All the inserts using one=
, same table.<br></div><div><br></div><div>The problem is, clients wait too=
 long due to insert process is too slow.</div><div>It seems to working fine=
 at first, but getting slows down after couple of hours,</div><div>each ins=
ert query takes 3000+ ms and keep growing.<br></div><div><br></div><div>Nee=
d some help to figure out an actual causes of this problem.<br></div><div><=
br></div><div>System information :</div><div>=C2=A0 PGBouncer 1.7.2.<br></d=
iv><div>=C2=A0 PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GC=
C) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit on CentOS release 6.9 (Final).=
</div><div>=C2=A0 Kernel version 2.6.32-696.10.1.el6.x86_64<br></div><div>=
=C2=A0  Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz<span class=3D"gmail-"> pro=
cessor.</span></div><div><span class=3D"gmail-">=C2=A0 </span><span class=
=3D"gmail-">32GB ECC/REG-Buffered RAM.<br></span></div><div><span class=3D"=
gmail-">=C2=A0 128GB Samsung 840 evo SSD.<br></span></div><div><span class=
=3D"gmail-"><br></span></div><div><span class=3D"gmail-"><br></span></div><=
div><br></div><div><br></div><div><br></div><div><br></div><br></div>

--94eb2c0595022dcebf055858b6f8--

--94eb2c0595022dced3055858b6fa
Content-Type: text/plain; charset="US-ASCII"; name="pgbouncer.ini.txt"
Content-Disposition: attachment; filename="pgbouncer.ini.txt"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_j75vwcg00

cG9vbF9tb2RlID0gdHJhbnNhY3Rpb24NCg0KDQptYXhfY2xpZW50X2Nvbm4g
PSAxMDAwMDANCg0KZGVmYXVsdF9wb29sX3NpemUgPSAyMDANCg==

--94eb2c0595022dced3055858b6fa
Content-Type: text/plain; charset="US-ASCII"; name="postgresql.conf.txt"
Content-Disposition: attachment; filename="postgresql.conf.txt"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_j75vwcgh1

bWF4X2Nvbm5lY3Rpb25zID0gOTAwMCAgICAgICAgICAgICAgICAgICMgKGNo
YW5nZSByZXF1aXJlcyByZXN0YXJ0KQ0Kc3VwZXJ1c2VyX3Jlc2VydmVkX2Nv
bm5lY3Rpb25zID0gMSAgICAgICMgKGNoYW5nZSByZXF1aXJlcyByZXN0YXJ0
KQ0KDQpzaGFyZWRfYnVmZmVycyA9IDhHQg0KDQp3b3JrX21lbSA9IDJNQg0K
DQptYWludGVuYW5jZV93b3JrX21lbSA9IDY0TUIgICAgICAgICAgICAgIyBt
aW4gMU1CDQoNCmR5bmFtaWNfc2hhcmVkX21lbW9yeV90eXBlID0gcG9zaXgg
ICAgICAjIHRoZSBkZWZhdWx0IGlzIHRoZSBmaXJzdCBvcHRpb24NCiAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAjIHN1cHBvcnRl
ZCBieSB0aGUgb3BlcmF0aW5nIHN5c3RlbToNCiAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAjICAgcG9zaXgNCiAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAjICAgc3lzdg0KICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICMgICB3aW5kb3dz
DQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIyAg
IG1tYXANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAjIHVzZSBub25lIHRvIGRpc2FibGUgZHluYW1pYyBzaGFyZWQgbWVtb3J5
DQpzeW5jaHJvbm91c19jb21taXQgPSBvZmYNCg0Kd2FsX2J1ZmZlcnMgPSAy
NTZNQg0KDQpjaGVja3BvaW50X3RpbWVvdXQgPSAxMG1pbiAgICAgICAgICAg
ICAgIyByYW5nZSAzMHMtMWQNCg0KbWF4X3dhbF9zaXplID0gNDA5Nk1CDQoN
Cm1pbl93YWxfc2l6ZSA9IDMwNzJNQg0KDQpjaGVja3BvaW50X2NvbXBsZXRp
b25fdGFyZ2V0ID0gMS4wICAgICAgIyBjaGVja3BvaW50IHRhcmdldCBkdXJh
dGlvbiwgMC4wIC0gMS4wDQoNCnJhbmRvbV9wYWdlX2Nvc3QgPSA0LjAgICAg
ICAgICAgICAgICAgICAjIHNhbWUgc2NhbGUgYXMgYWJvdmUNCg0KZGVmYXVs
dF9zdGF0aXN0aWNzX3RhcmdldCA9IDUwMCAgICAgICAgICMgcmFuZ2UgMS0x
MDAwMA0KDQoNCg==

--94eb2c0595022dced3055858b6fa
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--94eb2c0595022dced3055858b6fa--