public inbox for [email protected]
help / color / mirror / Atom feedHandling small inserts from many connections.
6+ messages / 5 participants
[nested] [flat]
* Handling small inserts from many connections.
@ 2017-09-04 08:14 우성민 <[email protected]>
0 siblings, 3 replies; 6+ messages in thread
From: 우성민 @ 2017-09-04 08:14 UTC (permalink / raw)
To: pgsql-performance
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
[text/plain] pgbouncer.ini.txt (82B, 3-pgbouncer.ini.txt)
download | inline:
pool_mode = transaction
max_client_conn = 100000
default_pool_size = 200
[text/plain] postgresql.conf.txt (1.0K, 4-postgresql.conf.txt)
download | inline:
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling small inserts from many connections.
@ 2017-09-04 11:57 Michael Vitale <[email protected]>
parent: 우성민 <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: Michael Vitale @ 2017-09-04 11:57 UTC (permalink / raw)
To: 우성민 <[email protected]>; pgsql-performance
<!DOCTYPE html>
<html><head>
<meta charset="UTF-8">
</head><body><p>Without more information, this is my initial guess at your insert slowness problem:</p><p>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:<br></p><p>select * from pg_locks where granted = false and locktype = 'extend';</p><p>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...</p><p>Regards,</p><p>Michael Vitale</p><p><br></p><blockquote type="cite">On September 4, 2017 at 4:14 AM 우성민 <[email protected]> wrote:<br><br><div dir="ltr"><div>Hi team,</div><div><br></div><div>I'm trying to configure postgres and pgbouncer to handle many inserts from many connections.</div><div><br></div><div>Here's some details about what i want to achieve :</div><div><br></div><div>  We have more than 3000 client connections, and my server program forks backend process for each client connections.</div><div>  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 <br></div><div>  next request.<br></div><div>  The backend process execute insert text data using PQexec from libpq lbirary, if PQexec is done, backend process send request to <br></div><div>  client again.</div><div><br></div><div>  All the inserts using one, same table.<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><br></div><div>Need some help to figure out an actual causes of this problem.<br></div><div><br></div><div>System information :</div><div>  PGBouncer 1.7.2.<br></div><div>  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).</div><div>  Kernel version 2.6.32-696.10.1.el6.x86_64<br></div><div>  Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz<span class="ox-1ffa921873-gmail-"> processor.</span></div><div><span class="ox-1ffa921873-gmail-">  </span><span class="ox-1ffa921873-gmail-">32GB ECC/REG-Buffered RAM.<br></span></div><div><span class="ox-1ffa921873-gmail-">  128GB Samsung 840 evo SSD.<br></span></div><div><span class="ox-1ffa921873-gmail-"><br></span></div><div><span class="ox-1ffa921873-gmail-"><br></span></div><div><br></div><div><br></div><div><br></div><div><br></div><br></div><br>-- <br>Sent via pgsql-performance mailing list ([email protected])<br>To make changes to your subscription:<br>http://www.postgresql.org/mailpref/pgsql-performance<br></blockquote></body></h...;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling small inserts from many connections.
@ 2017-09-04 18:15 Scott Marlowe <[email protected]>
parent: 우성민 <[email protected]>
2 siblings, 2 replies; 6+ messages in thread
From: Scott Marlowe @ 2017-09-04 18:15 UTC (permalink / raw)
To: 우성민 <[email protected]>; +Cc: pgsql-performance
On Mon, Sep 4, 2017 at 2:14 AM, 우성민 <[email protected]> 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.
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.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling small inserts from many connections.
@ 2017-09-04 22:06 [email protected] <[email protected]>
parent: Scott Marlowe <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: [email protected] @ 2017-09-04 22:06 UTC (permalink / raw)
To: Scott Marlowe <[email protected]>; +Cc: 우성민 <[email protected]>; pgsql-performance
Jumping on Scott's observation, assuming you really do have a lot of active connections (idle ones usually are not a problem) a general rule of thumb for not overloading your system is keep your active connections less than (2-3) * (number so cpus).
Sent from my iPad
> On Sep 4, 2017, at 2:15 PM, Scott Marlowe <[email protected]> wrote:
>
>> On Mon, Sep 4, 2017 at 2:14 AM, 우성민 <[email protected]> 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.
>
> 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.
>
>
> --
> Sent via pgsql-performance mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling small inserts from many connections.
@ 2017-09-04 22:27 Jeff Janes <[email protected]>
parent: 우성민 <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: Jeff Janes @ 2017-09-04 22:27 UTC (permalink / raw)
To: 우성민 <[email protected]>; +Cc: pgsql-performance
On Mon, Sep 4, 2017 at 1:14 AM, 우성민 <[email protected]> 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.
>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Handling small inserts from many connections.
@ 2017-09-04 22:54 우성민 <[email protected]>
parent: Scott Marlowe <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: 우성민 @ 2017-09-04 22:54 UTC (permalink / raw)
To: Scott Marlowe <[email protected]>; +Cc: pgsql-performance
I’am already using pgbouncer as a connection pooler and default_pool_size
= 96.
i checked “show pools”, the max_wait was as high as 70 or more while INSERT
statement duration is about 3000ms in postgres log.
These numbers increase over time.
I’ll try RAID with more SSDs.
Thank you for your response.
2017년 9월 5일 (화) 오전 3:15, Scott Marlowe <[email protected]>님이 작성:
> On Mon, Sep 4, 2017 at 2:14 AM, 우성민 <[email protected]> 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.
>
> 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.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2017-09-04 22:54 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-09-04 08:14 Handling small inserts from many connections. 우성민 <[email protected]>
2017-09-04 11:57 ` Michael Vitale <[email protected]>
2017-09-04 18:15 ` Scott Marlowe <[email protected]>
2017-09-04 22:06 ` [email protected] <[email protected]>
2017-09-04 22:54 ` 우성민 <[email protected]>
2017-09-04 22:27 ` Jeff Janes <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox