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 <cr...@2ndquadrant.com> wrote: > On 29 June 2016 at 21:49, Sachin Kotwal <kotsac...@gmail.com> wrote: > >> Hi, >> >> >> On Wed, Jun 29, 2016 at 6:29 PM, Craig Ringer <cr...@2ndquadrant.com> >> wrote: >> >>> On 29 June 2016 at 18:47, Sachin Kotwal <kotsac...@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