Re: Handling small inserts from many connections.

[Michael Vitale <michaeldba@xxxxxxxxxxx>]

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=extend) due to many concurrent connections trying to insert into the same table. Each insert request results in an extend lock (8k extension), which blocks other writers. What normally happens is that these extend locks happen so fast that you hardly seem them in the pg_locks table, except in the case where many concurrent 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 = false and locktype = 'extend';

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

Regards,

Michael Vitale

On September 4, 2017 at 4:14 AM 우성민 <dntjdals0513@xxxxxxxxx> 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 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.

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