Re: pgbench unable to scale beyond 100 concurrent connections - Mailing list pgsql-hackers

From Sachin Kotwal
Subject Re: pgbench unable to scale beyond 100 concurrent connections
Date
Msg-id CA+N_YAe+mLKTHj8cYY=Q5F7uU7=BwiATCPoKg400W1puEoLJ-A@mail.gmail.com
Whole thread Raw
In response to Re: pgbench unable to scale beyond 100 concurrent connections  (Craig Ringer <craig@2ndquadrant.com>)
List pgsql-hackers
Hi All,

Sorry for trouble you with small environment setup for testing.
I should to test this with large machine.
What I was testing were involved multiple things same time so quite confusing .

possible reason for this testing failure is :
1. small hardware
2. haproxy not able to balance connection 100-100 on each server.
3. postgres_fdw foreign server unable to established large number of connection with remote server/Shard.


I was testing  multiple coordinator using postgres_fdw (sharding) and haproxy on top of it for load balancing.

as below 
            
                                                pg_fdw (conn=100, diff pg instance on diff machine)          |
                                                        /                                                                           \        |
pgbench (haproxy-port)->Haproxy/ (should accept 200 conn)                                      \     | Shards/Nodes (1…N)
                                                      \                                                                                 /     | remote pg servers
                                                        \                                                                           /         |
                                               pg_fdw(conn=100, diff pg instance on diff machine)             |


Hope i will test this scenario in detail once i get time and good hardware.

If some one test this scenario please let me know.

Thanks and regards,
Sachin Kotwal




On Thu, Jun 30, 2016 at 4:03 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
On 29 June 2016 at 21:49, Sachin Kotwal <kotsachin@gmail.com> wrote:
Hi,


On Wed, Jun 29, 2016 at 6:29 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
On 29 June 2016 at 18:47, Sachin Kotwal <kotsachin@gmail.com> wrote:
 
I am testing pgbench with more than 100 connections.
also set max_connection in postgresql.conf more than 100.

Initially pgbench tries to scale nearby 150 but later it come down to 100 connections and stable there.

It this limitation of pgbench? or bug? or i am doing it wrong way?

What makes you think this is a pgbench limitation?
 
As I mentioned when I tried same thing with sysbench It can give me 200+ concurrent connection with same method and same machine.

What command lines / configs are you using? Details are necessary, talking about this in general hand-waving terms is not getting anywhere.
 
 
It sounds like you're benchmarking the client and server on the same system. Couldn't this be a limitation of the backend PostgreSQL server?

I think having client and server on same server should not be problem. 
As i can do this with different benchmarking tool It should not be limitation of backend PostgreSQL server.

OK, so your sysbench use is actually talking to PostgreSQL as well. Then yes. Assuming they're testing roughly the same thing, which I somewhat doubt.

There should not be connection and disconnection because I am not using -C option of pgbench which cause connection and disconnection for each query.

OK, in that case it's hard to explain the behaviour you're seeing.

More details please.
 
If I set max_connection of postgresql.conf to 200 and testing with -c 150 .
This should work fine, but it is not.

If you're using FDWs to connect to the same server again, you'll need a max_connections slot for each FDW connection as well.
 
 
I am testing one scenario of multiple coordinator with help of postgres_fdw to enhance connection ability of postgres without any connection pooling .
Setup might be difficult to explain here but will explain if required.

Yes, you need to explain it.
 
can you test simply 100 scale database size with pgbench and run pgbench with 200+ connection of small virtual box to see same observation ?

It works fine - of course. There's more to this story than you've explained so far.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services



--

Thanks and Regards,
Sachin Kotwal

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: Reviewing freeze map code
Next
From: Yury Zhuravlev
Date:
Subject: Re: WIP: About CMake v2