Re: pgbench unable to scale beyond 100 concurrent connections

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(at)2ndquadrant(dot)com> wrote:

> On 29 June 2016 at 21:49, Sachin Kotwal <kotsachin(at)gmail(dot)com> wrote:
>
>> Hi,
>>
>>
>> On Wed, Jun 29, 2016 at 6:29 PM, Craig Ringer <craig(at)2ndquadrant(dot)com>
>> wrote:
>>
>>> On 29 June 2016 at 18:47, Sachin Kotwal <kotsachin(at)gmail(dot)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