[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [HACKERS] pgbench unable to scale beyond 100 concurrent connections
From: Sachin Kotwal <kotsachin () gmail ! com>
Date: 2016-06-30 7:21:15
Message-ID: CA+N_YAe+mLKTHj8cYY=Q5F7uU7=BwiATCPoKg400W1puEoLJ-A () mail ! gmail ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<div dir="ltr">Hi All,<div><br></div><div>Sorry for trouble you with small \
environment setup for testing.</div><div>I should to test this with large \
machine.</div><div>What I was testing were involved multiple things same time so \
quite confusing .<br></div><div><br></div><div>possible reason for this testing \
failure is :</div><div>1. small hardware</div><div>2. haproxy not able to balance \
connection 100-100 on each server.</div><div>3. postgres_fdw foreign server unable to \
established large number of connection with remote \
server/Shard.</div><div><br></div><div><br></div><div>I was testing multiple \
coordinator using postgres_fdw (sharding) and haproxy on top of it for load \
balancing.</div><div><br></div><div>as below </div><div> \
</div><div><div> \
pg_fdw (conn=100, diff pg instance on diff machine) |</div><div> \
/ \
\ |</div><div>pgbench (haproxy-port)->Haproxy/ (should accept 200 conn) \
\ | Shards/Nodes (1…N)</div><div> \
\ \
/ | remote pg servers</div><div> \
\ \
/ |</div><div> \
pg_fdw(conn=100, diff pg instance on diff machine) \
|</div></div><div><br></div><div><br></div><div>Hope i will test this scenario in \
detail once i get time and good hardware.</div><div><br></div><div>If some one test \
this scenario please let me know.</div><div><br></div><div>Thanks and \
regards,</div><div>Sachin \
Kotwal</div><div><br></div><div><br></div><div><br></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Thu, Jun 30, 2016 at 4:03 AM, \
Craig Ringer <span dir="ltr"><<a href="mailto:craig@2ndquadrant.com" \
target="_blank">craig@2ndquadrant.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div \
class="gmail_quote"><span class="">On 29 June 2016 at 21:49, Sachin Kotwal <span \
dir="ltr"><<a href="mailto:kotsachin@gmail.com" \
target="_blank">kotsachin@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr">Hi,<div><br><div class="gmail_extra"><br><div \
class="gmail_quote"><span>On Wed, Jun 29, 2016 at 6:29 PM, Craig Ringer <span \
dir="ltr"><<a href="mailto:craig@2ndquadrant.com" \
target="_blank">craig@2ndquadrant.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div \
class="gmail_quote"><span>On 29 June 2016 at 18:47, Sachin Kotwal <span \
dir="ltr"><<a href="mailto:kotsachin@gmail.com" \
target="_blank">kotsachin@gmail.com</a>></span> wrote:<br><div> </div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div></div><div>I am testing pgbench with more \
than 100 connections.</div><div>also set max_connection in postgresql.conf more than \
100.</div><div><br></div><div>Initially pgbench tries to scale nearby 150 but later \
it come down to 100 connections and stable there.</div><div><br></div><div>It this \
limitation of pgbench? or bug? or i am doing it wrong \
way?</div></div></blockquote><div><br></div></span><div>What makes you think this is \
a pgbench limitation?</div></div></div></div></blockquote><div> </div></span><div>As \
I mentioned when I tried same thing with sysbench It can give me 200+ concurrent \
connection with same method and same \
machine.</div><span><div></div></span></div></div></div></div></blockquote><div><br></div></span><div>What \
command lines / configs are you using? Details are necessary, talking about this in \
general hand-waving terms is not getting anywhere.</div><span class=""><div> \
<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"><div dir="ltr"><div><div class="gmail_extra"><div \
class="gmail_quote"><span><div> <br></div><blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div \
dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><div>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?</div><div><br></div></div></div></div></blockquote></span><div>I think having \
client and server on same server should not be problem. </div><div>As i can do this \
with different benchmarking tool It should not be limitation of backend PostgreSQL \
server.</div></div></div></div></div></blockquote><div><br></div></span><div>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.</div><span \
class=""><div><br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div \
class="gmail_extra"><div class="gmail_quote"><div>There should not be connection and \
disconnection because I am not using -C option of pgbench which cause connection and \
disconnection for each \
query.</div></div></div></div></blockquote><div><br></div></span><div>OK, in that \
case it's hard to explain the behaviour you're \
seeing.</div><div><br></div><div>More details please.</div><span class=""><div> \
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div \
class="gmail_quote"><div>If I set max_connection of postgresql.conf to 200 and \
testing with -c 150 .</div><div>This should work fine, but it is \
not.</div></div></div></div></blockquote><div><br></div></span><div>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.</div><span class=""><div> </div><div> \
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div \
class="gmail_quote"><span><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div \
class="gmail_extra"><span><font \
color="#888888"><div></div></font></span></div></div></blockquote></span><div>I am \
testing one scenario of multiple coordinator with help of postgres_fdw to enhance \
connection ability of postgres without any connection pooling .</div><div>Setup might \
be difficult to explain here but will explain if \
required.</div></div></div></div></blockquote><div><br></div></span><div>Yes, you \
need to explain it.</div><span class=""><div> </div><blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div \
dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><div>can you test simply \
100 scale database size with pgbench and run pgbench with 200+ connection of small \
virtual box to see same observation \
?<br></div></div></div></div></blockquote><div><br></div></span><div>It works fine - \
of course. There's more to this story than you've explained so \
far.</div></div><span class=""><div><br></div>-- <br><div \
data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"> Craig Ringer \
<a href="http://www.2ndQuadrant.com/" \
target="_blank">http://www.2ndQuadrant.com/</a><br> PostgreSQL Development, 24x7 \
Support, Training & Services<br></div></div></div></div> </span></div></div>
</blockquote></div><br><br clear="all"><div><br></div>-- <br><div \
class="gmail_signature" data-smartmail="gmail_signature"><div \
dir="ltr"><div><br></div>Thanks and Regards,<div>Sachin Kotwal</div></div></div> \
</div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic