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.
pool_mode = transaction max_client_conn = 100000 default_pool_size = 200
max_connections = 9000 # (change requires restart)
superuser_reserved_connections = 1 # (change requires restart)
shared_buffers = 8GB
work_mem = 2MB
maintenance_work_mem = 64MB # min 1MB
dynamic_shared_memory_type = posix # the default is the first option
# supported by the operating system:
# posix
# sysv
# windows
# mmap
# use none to disable dynamic shared memory
synchronous_commit = off
wal_buffers = 256MB
checkpoint_timeout = 10min # range 30s-1d
max_wal_size = 4096MB
min_wal_size = 3072MB
checkpoint_completion_target = 1.0 # checkpoint target duration, 0.0 - 1.0
random_page_cost = 4.0 # same scale as above
default_statistics_target = 500 # range 1-10000
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
