MIME-Version: 1.0
In-Reply-To: 
 <CABdtbz0LZOEXYAh6f=PpLvF57jk2B4UOH2y5ETtHpv7ibu_17A@mail.gmail.com>
References: 
 <CABdtbz0LZOEXYAh6f=PpLvF57jk2B4UOH2y5ETtHpv7ibu_17A@mail.gmail.com>
From: Jeff Janes <jeff.janes@gmail.com>
Date: Mon, 4 Sep 2017 15:27:06 -0700
Message-ID: 
 <CAMkU=1yjYi93b9BnAn_mD8tJnnbRZmqK6jn0RzYQRu-pW_x=8Q@mail.gmail.com>
Subject: Re: Handling small inserts from many connections.
To: =?UTF-8?B?7Jqw7ISx66+8?= <dntjdals0513@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="001a113f19765a01f50558649c40"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a113f19765a01f50558649c40
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Sep 4, 2017 at 1:14 AM, =EC=9A=B0=EC=84=B1=EB=AF=BC <dntjdals0513@g=
mail.com> wrote:

> Hi team,
>
> I'm trying to configure postgres and pgbouncer to handle many inserts fro=
m
> 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.
>

If it takes a couple hours for it to slow down, then it sounds like you
have a leak somewhere in your code.

Run "top" and see who is using the CPU time (or the io wait time, if that
is what it is, and the memory)

Cheers,

Jeff

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

<div dir=3D"ltr"><div class=3D"gmail_extra"><div class=3D"gmail_quote">On M=
on, Sep 4, 2017 at 1:14 AM, =EC=9A=B0=EC=84=B1=EB=AF=BC <span dir=3D"ltr">&=
lt;<a href=3D"mailto:dntjdals0513@gmail.com" target=3D"_blank">dntjdals0513=
@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=
=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=
=3D"ltr"><div>Hi team,</div><div><br></div><div>I&#39;m trying to configure=
 postgres and pgbouncer to handle many inserts from many connections.</div>=
<div><br></div><div>Here&#39;s some details about what i want to achieve :<=
/div><div><br></div><div>=C2=A0 We have more than 3000 client connections, =
and my server program forks backend process for each client connections.</d=
iv><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 process an=
d wait for <br></div><div>=C2=A0 next request.<br></div><div>=C2=A0 The bac=
kend process execute insert text data using PQexec from libpq lbirary, if P=
Qexec 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 tab=
le.<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 insert query =
takes 3000+ ms and keep growing.<br></div></div></blockquote><div><br></div=
><div>If it takes a couple hours for it to slow down, then it sounds like y=
ou have a leak somewhere in your code.</div><div><br></div><div>Run &quot;t=
op&quot; and see who is using the CPU time (or the io wait time, if that is=
 what it is, and the memory)</div><div>=C2=A0</div><div>Cheers,</div><div><=
br></div><div>Jeff</div></div></div></div>

--001a113f19765a01f50558649c40--