MIME-Version: 1.0
References: 
 <CABdtbz0LZOEXYAh6f=PpLvF57jk2B4UOH2y5ETtHpv7ibu_17A@mail.gmail.com>
 <CAOR=d=2PzwsUFh8qZoPH7OuAz2hMmOpNPtJikm9zsNofM4xCXQ@mail.gmail.com>
In-Reply-To: 
 <CAOR=d=2PzwsUFh8qZoPH7OuAz2hMmOpNPtJikm9zsNofM4xCXQ@mail.gmail.com>
From: =?UTF-8?B?7Jqw7ISx66+8?= <dntjdals0513@gmail.com>
Date: Mon, 04 Sep 2017 22:54:03 +0000
Message-ID: 
 <CABdtbz3_cNJvwOho7Z__7DYagchW+zdKuHHTEcV_5PBwLZdgaA@mail.gmail.com>
Subject: Re: Handling small inserts from many connections.
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="001a114a854253be66055864fd6b"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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

I=E2=80=99am already using pgbouncer as a connection pooler and default_poo=
l_size
 =3D 96.

i checked =E2=80=9Cshow pools=E2=80=9D, the max_wait was as high as 70 or m=
ore while INSERT
statement duration is about 3000ms in postgres log.
These numbers increase over time.

I=E2=80=99ll try RAID with more SSDs.

Thank you for your response.

2017=EB=85=84 9=EC=9B=94 5=EC=9D=BC (=ED=99=94) =EC=98=A4=EC=A0=84 3:15, Sc=
ott Marlowe <scott.marlowe@gmail.com>=EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1:

> On Mon, Sep 4, 2017 at 2:14 AM, =EC=9A=B0=EC=84=B1=EB=AF=BC <dntjdals0513=
@gmail.com> wrote:
> > 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 fork=
s
> > backend process for each client connections.
>
> This is a terrible configuration for any kind of performance. Under
> load all 3,000 connections can quickly swamp your server resulting in
> it slowing to a crawl.
>
> Get a connection pooler involved. I suggest pgbouncer unless you have
> very odd pooling needs. It's easy, small, and fast. Funnel those 3,000
> connections down to <100 if you can. It will make a huge difference in
> performance and reliability.
>
> > 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.
>
> If it's still slow after connection pooling is setup, then look at
> throwing more SSDs at the problem. If you're using a HW RAID
> controller, turn off caching with SSDs unless you can prove it's
> faster with it. It almost never is.
>

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

<div dir=3D"auto">I=E2=80=99am already using pgbouncer as a connection pool=
er and default_pool_size =C2=A0=3D 96.</div><div dir=3D"auto"><br></div><di=
v dir=3D"auto">i checked =E2=80=9Cshow pools=E2=80=9D, the max_wait was as =
high as 70 or more while INSERT statement duration is about 3000ms in postg=
res log.</div><div dir=3D"auto">These numbers increase over time.</div><div=
 dir=3D"auto"><br></div><div dir=3D"auto">I=E2=80=99ll try RAID with more S=
SDs.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Thank you for your =
response.</div><div><br><div class=3D"gmail_quote"><div>2017=EB=85=84 9=EC=
=9B=94 5=EC=9D=BC (=ED=99=94) =EC=98=A4=EC=A0=84 3:15, Scott Marlowe &lt;<a=
 href=3D"mailto:scott.marlowe@gmail.com">scott.marlowe@gmail.com</a>&gt;=EB=
=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1:<br></div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"=
>On Mon, Sep 4, 2017 at 2:14 AM, =EC=9A=B0=EC=84=B1=EB=AF=BC &lt;<a href=3D=
"mailto:dntjdals0513@gmail.com" target=3D"_blank">dntjdals0513@gmail.com</a=
>&gt; wrote:<br>
&gt; Hi team,<br>
&gt;<br>
&gt; I&#39;m trying to configure postgres and pgbouncer to handle many inse=
rts from<br>
&gt; many connections.<br>
&gt;<br>
&gt; Here&#39;s some details about what i want to achieve :<br>
&gt;<br>
&gt;=C2=A0 =C2=A0We have more than 3000 client connections, and my server p=
rogram forks<br>
&gt; backend process for each client connections.<br>
<br>
This is a terrible configuration for any kind of performance. Under<br>
load all 3,000 connections can quickly swamp your server resulting in<br>
it slowing to a crawl.<br>
<br>
Get a connection pooler involved. I suggest pgbouncer unless you have<br>
very odd pooling needs. It&#39;s easy, small, and fast. Funnel those 3,000<=
br>
connections down to &lt;100 if you can. It will make a huge difference in<b=
r>
performance and reliability.<br>
<br>
&gt; System information :<br>
&gt;=C2=A0 =C2=A0PGBouncer 1.7.2.<br>
&gt;=C2=A0 =C2=A0PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (=
GCC) 4.4.7<br>
&gt; 20120313 (Red Hat 4.4.7-18), 64-bit on CentOS release 6.9 (Final).<br>
&gt;=C2=A0 =C2=A0Kernel version 2.6.32-696.10.1.el6.x86_64<br>
&gt;=C2=A0 =C2=A0Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz processor.<br>
&gt;=C2=A0 =C2=A032GB ECC/REG-Buffered RAM.<br>
&gt;=C2=A0 =C2=A0128GB Samsung 840 evo SSD.<br>
<br>
If it&#39;s still slow after connection pooling is setup, then look at<br>
throwing more SSDs at the problem. If you&#39;re using a HW RAID<br>
controller, turn off caching with SSDs unless you can prove it&#39;s<br>
faster with it. It almost never is.<br>
</blockquote></div></div>

--001a114a854253be66055864fd6b--