Hi All,

In order to benchmark the API execution per second for our cloud product we want to test performance for 1200+ client connections and doing some operations. We are using postgres db in system of 32GB RAM. During the test execution after sometime we are
seeing db crash because of out of memory issue.

We tried to use pgbouncer to resolve the issue but no luck.

Do we have any solution to support the above requirement for 1 DB node. Or any configuration setting in pgbouncer which will help us.



Thanks
Sachin

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thu, 2 Aug 2018 01:46:26 -0700 (PDT), Sachin Singh
<notsachin@gmail.com> wrote:

In order to benchmark the API execution per second for
our cloud product we want to test performance for 1200+
client connections and doing some operations. We are
using postgres db in system of 32GB RAM. During the
test execution after sometime we are seeing db crash
because of out of memory issue.

We tried to use pgbouncer to resolve the issue but no luck.

Do we have any solution to support the above requirement
for 1 DB node. Or any configuration setting in pgbouncer
which will help us.

It probably would be better to ask on one of mailing lists: pgsql-general@postgresql.org
pgsql-performance@postgresql.org


You definitely are making too many simultaneous connections - you need
to limit the number to something reasonable: say a few hundred,
maximum. How many connections you can support depends heavily on your
server's configuration [and whether pgbouncer is co-resident. I'm not
a real expert at this, which is why you should ask on the lists.]

Remember that Postgresql creates a new process for every connection:
although the program code and DBMS cache (shared_buffers) are shared,
each connection has its own private state, and extra allocations like work_buffers and temp_buffers, etc. are query dependent.

Unfortunately, server configuration is a black art: it's easy to make configuration choices that run well under certain loads and fail
miserably under others.

George

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Sachin Singh <notsachin@gmail.com> writes:

We tried to use pgbouncer to resolve the issue but no luck.

Which pool_mode did you try? Have you read about the transation
pool_mode yet, and decided if your application is compliant to this one?

--
Dimitri Fontaine

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thursday, August 2, 2018 at 3:55:30 PM UTC+5:30, Dimitri Fontaine wrote:

Sachin Singh <notsachin@gmail.com> writes:

We tried to use pgbouncer to resolve the issue but no luck.

Which pool_mode did you try? Have you read about the transation
pool_mode yet, and decided if your application is compliant to this one?

--
Dimitri Fontaine

With session pool_mode out of memory error comes and transaction mode not compliant with my application and with transaction mode my application is showing some errors

Looks like we need to go for postgreSQL cluster with load balancing.
Please let me know if you have some other suggestions
Sachin Singh

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thursday, August 2, 2018 at 4:11:05 PM UTC+5:30, George Neuner wrote:

On Thu, 2 Aug 2018 01:46:26 -0700 (PDT), Sachin Singh
<notsachin@gmail.com> wrote:

In order to benchmark the API execution per second for
our cloud product we want to test performance for 1200+
client connections and doing some operations. We are
using postgres db in system of 32GB RAM. During the
test execution after sometime we are seeing db crash
because of out of memory issue.

We tried to use pgbouncer to resolve the issue but no luck.

Do we have any solution to support the above requirement
for 1 DB node. Or any configuration setting in pgbouncer
which will help us.

It probably would be better to ask on one of mailing lists: pgsql-general@postgresql.org
pgsql-performance@postgresql.org

Thanks for providing the correct mailing lists.

You definitely are making too many simultaneous connections - you need
to limit the number to something reasonable: say a few hundred,
maximum. How many connections you can support depends heavily on your server's configuration [and whether pgbouncer is co-resident. I'm not
a real expert at this, which is why you should ask on the lists.]

Remember that Postgresql creates a new process for every connection:
although the program code and DBMS cache (shared_buffers) are shared,
each connection has its own private state, and extra allocations like work_buffers and temp_buffers, etc. are query dependent.

Unfortunately, server configuration is a black art: it's easy to make configuration choices that run well under certain loads and fail
miserably under others.

George

My application is running behind load balancer and it is kind of clustered application and it can support more than 2k+ connections. Bottleneck is happening from db side so we are looking solution for postgres db to support this much connection without
db crash.
Planning to try postgreSQL clustering with load balancing option using pgpool-II . Please let me know if any drawbacks or need to know something before proceeding on this.

Sachin

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Sachin Singh <notsachin@gmail.com> writes:

With session pool_mode out of memory error comes and transaction mode not compliant with my application and with transaction mode my application is showing some errors

Fix your app, or see if the errors are limited to certain parts of it.
Then you can use different contexts in pgbouncer for transaction mode
and session mode as you need.

--
Dimitri Fontaine

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)