Re: pgbench unable to scale beyond 100 concurrent connections

Hello,

> I am testing it with 9.6-beta1 binaries. For server and client it is same.
> I am using pgbench on top of postgres_fdw.

Hmmm... So pgbench is connected to some pg instance, and this pg instance
is connected to something else on another host? Or to the same instance,
in which case you would double the number of required connections and
processes.

>> On what hardware ? (200 connections => 200 active postgres processes, how
>> many processes per core are you expecting to run?
>
> I am running in small virtual box machine. with 1GB RAM and 2 cores.
> I think there should not be problem with 200 processes on 2 core machines.
> I tested same number of concurrent connections on same machine with
> sysbench it is working fine.

200 processes which do nothing is fine. 200 processes which are actively
competing for resources to process request is not so fine on a 2 core
host.

> I am not sure what is difference between pgbench and sysbench, they might
> be process based or thread based.

Pgbench starts threads (option -j N, default N = 1), but with 2 cores
threads are not very interesting. I do not know about sysbench.

> But I can say if I can create 200+ concurrent connection with sysbench ,
> i should be able create same with pgbench. Thoughts?

If sysbench creates connections which do nothing it is fine. However
Pgbench will require the server to be active.

> I think 2 connections per core is very small value . for 200 i need atleast
> 100 core machine , which is not good.

I'm not sure what you would expect with 200 active connections on 2 cores.

>> What precise command is started?

No answer to this question. I wanted to see what options are passed to
pgbench...

>> How to you know it "comes down to 100 connections"?
>
> I put watch on live connections to database.
> something like : watch -n 1 'ps -ef | grep postgres | grep 192.168.56.101 |
> wc -l'

Ok.

> Also user below query to see active connection.
> # select count(*) from pg_stat_activity;

Ok.

>> Are there error messages from pgbench or postgresql?
>>
>> postgresql does not give any error.
>
> pgbench says:
> client 36 aborted in state 2: ERROR: could not connect to server "server_1"
> DETAIL: FATAL: sorry, too many clients already

As noted by Jeff & Robert, you have reached the maximum number of
connections allowed by the server.

>> My random guess would be that you start too many connections with only one
>> thread client side and/or on a too small hardware client or server-side for
>> the expected scale, so given the load and latency some connections just
>> never get to do anything?
>>
>> This may be reason but it should be able to maintain idle connection for
> that time if never get to do anything.

Probably, it depends. I'm not sure how pgbench starts its connections
depending on the options (which you did not provide), if it is on demand
then maybe.

You could add the log_connection option and see the details & number of
the created connections.

>> I do not have such good hardware for now.

Well, you try to start 200 active servers connected to 200 active clients,
which requires some ressources...

> I feel pgbench should be able to perform well on small hardware.

Sure, with 2 core I would start it with 2 clients.

Maybe you could start by running smaller number of pgbench connections (1,
2, 3, 4...) and check the number of processes created, to check the effect
of the fdw stuff.

--
Fabien.