Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1doq23-0007LK-TV for pgsql-performance@arkaria.postgresql.org; Mon, 04 Sep 2017 11:59:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1doq23-0001pj-Bl for pgsql-performance@arkaria.postgresql.org; Mon, 04 Sep 2017 11:59:11 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1doq0I-0007Am-Hw for pgsql-performance@postgresql.org; Mon, 04 Sep 2017 11:57:22 +0000 Received: from mout.perfora.net ([74.208.4.194]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1doq07-0008IW-QW for pgsql-performance@postgresql.org; Mon, 04 Sep 2017 11:57:21 +0000 Received: from oxuslxaltgw03.schlund.de ([10.72.76.59]) by mrelay.perfora.net (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0MWhiv-1e07MT02tX-00XuoW; Mon, 04 Sep 2017 13:57:10 +0200 Date: Mon, 4 Sep 2017 07:57:09 -0400 (EDT) From: Michael Vitale Reply-To: Michael Vitale To: =?UTF-8?B?7Jqw7ISx66+8?= , pgsql-performance@postgresql.org Message-ID: <1119236489.400430.1504526229815@email.1and1.com> In-Reply-To: References: Subject: Re: Handling small inserts from many connections. MIME-Version: 1.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Medium X-Mailer: Open-Xchange Mailer v7.8.3-Rev30 X-Originating-Client: open-xchange-appsuite X-Provags-ID: V03:K0:hc+2er+2uo8EIq8MKsZweKQ+3qAHebgyvZBI+s4dsAlXo4UD/dc CzZBvPiPHCxXrhNNg0n0R7caX2I5tgypT4NH6TxTRF9H7n7SnTxGRyhzb8hTADQuYPOrNz2 MJLFCXmgHyI0XgQlvopuSNanmxq6fmX9xbYVa9J6wxNWlmGNK6Cdj6TGRvuep0iA92Einu2 NlNrDdFLzFBjG22szIcag== X-UI-Out-Filterresults: notjunk:1;V01:K0:C5XGEGfmgl8=:2EdClh+AlzSIq2iJRT+SLr I7ObIl3SiDGunn6RmZ57XbxWb9rUkamyCoCsd/rloqRe5uinKlwrJrpAIlmllHy0Q8WmjQQSq ejxdEU98ySavGcmkqE8C3h+Gkz5B0GF1Ra7RhYehfVBuZ0+PoJJlsdlr3HnCYbKhQ4a6XmWT9 kahttL19kXLf3nFL4EjJdvs4g7nKZ9ViVgWWNmMyk0e2TrDuu74sD5rP1bvRrsENU5UKRhFfx gp0MwzmsCGmqd1OmWXodj12G8doE+weN8Ju2Dlsj+bIUe0+vDgyeVY+hV81QTRpWGdsuHmIzD wLfjhxgeYxogPEyptUTqZrpp870BkRybPSPw0RoTQ+kfi8oZAsTspB2Q2wQmKIhKmLdUkYJ/k k4fT+vuJmgITlxqb5/m2DwnWsnQjpNIHk5j2Lj+np+kO3BYBmwKA6L7Q4p+HAcd+sFqZO6XxJ Sbg7e1LZZPTEv81A/I6PprlZRY2ThFJu2zUJITDVExOj4dqF3l5IYJIO50ImJZEyF6okMd3Xm w5HGCmG+6yfWgObXTcGnN4VkQOMdkaBi8MxAa1AlvDBzxGNP8q1rvHXBpd82IA8YgbrIJPSd5 mqbpc0qPQnKgDNj19nHhWsdAvS/6y5Ir8ipjOA6e+YZVwQ+I2gL9uT+aPtV3V9z3VpI2NF3cl 8nvrPO77og7X1SxZmbK8tlsvaUqTv8PP7AXRNMKMbYO2LpgCNIq1qOjTvLQFOQLt8gqCxLXRR t/LUuY6/f/TZByc0YXbka+p9Rg+3nwGZCFBhdAKKvoo7AIxjIqtt1691DIM= List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org

Without more information, this is my initial guess at your = insert slowness problem:

The symptom of this insert slowness/delayed = action is delayed, granted, extend locks (locktype=3Dextend) due to many co= ncurrent connections trying to insert into the same table. Each insert requ= est results in an extend lock (8k extension), which blocks other writers. W= hat normally happens is that these extend locks happen so fast that you har= dly seem them in the pg_locks table, except in the case where many concurre= nt connections are trying to do inserts into the same table. The following = query will show if this is the case:

select * from pg_locks where= granted =3D false and locktype =3D 'extend';

If this is your= problem, then some kind of re-architecture is necessary to reduce the numb= er of connections trying to do the inserts at the same time into the same t= able. My first hand problem like this goes back to 9.2, so perhaps so= me good stuff has happened in the newer versions of PG. Let's &#= 160;see what other good ideas come down the pike for this thread...

R= egards,

Michael Vitale

On Sep= tember 4, 2017 at 4:14 AM =EC=9A=B0=EC=84=B1=EB=AF=BC <dntjdals0513@gma= il.com> wrote:

Hi team,

I'm trying to configure postgres and pgbouncer to handle many in= serts from many connections.

Here's some detai= ls about what i want to achieve :

We have m= ore than 3000 client connections, and my server program forks backend proce= ss for each client connections.
If backend processes send = a request to its connected client, the client send some text data(about 300= 0 bytes) to the backend process and wait for
next req= uest.
The backend process execute insert text data usi= ng PQexec from libpq lbirary, if PQexec is done, backend process send reque= st to
client again.

A= ll the inserts using one, same table.

The prob= lem 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 h= ours,
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 Cen= tOS release 6.9 (Final).
Kernel version 2.6.32-696.10.1.el= 6.x86_64
Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz processor.
3= 2GB ECC/REG-Buffered RAM.
128GB Samsung 840 evo SSD.

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

=20