Thread: Max_connections limit
Hello team,
We have migrated our database from Oracle 12c to Postgres 11. I need your suggestions , we have sessions limit in Oracle = 3024 . Do we need to set the same connection limit in Postgres as well. How we can decide the max_connections limit for postgres. Are there any differences in managing connections in Oracle and postgres.
SQL> show parameter sessions;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
sessions integer 3024
shared_server_sessions integer
SQL>
Regards,
Daulat
Daulat Ram wrote: > We have migrated our database from Oracle 12c to Postgres 11. I need your suggestions , > we have sessions limit in Oracle = 3024 . Do we need to set the same connection limit > in Postgres as well. How we can decide the max_connections limit for postgres. > Are there any differences in managing connections in Oracle and postgres. I'd say that is way too high in both Oracle and PostgreSQL. Set the value to 50 or 100 and get a connection pooler if the application cannot do that itself. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
You now that Postgres don’t have any shared_pool as Oracle, and the session information ( execution plan, etc..) are onlyavailable for the current session. Therefore I also highly recommend to us a connection poll as Laurent wrote, in orderto have higher chance that some stuff is already cached in the shared session available. Regards Herve Envoyé de mon iPhone > Le 26 juin 2019 à 11:05, Laurenz Albe <laurenz.albe@cybertec.at> a écrit : > > Daulat Ram wrote: >> We have migrated our database from Oracle 12c to Postgres 11. I need your suggestions , >> we have sessions limit in Oracle = 3024 . Do we need to set the same connection limit >> in Postgres as well. How we can decide the max_connections limit for postgres. >> Are there any differences in managing connections in Oracle and postgres. > > I'd say that is way too high in both Oracle and PostgreSQL. > > Set the value to 50 or 100 and get a connection pooler if the > application cannot do that itself. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > >
You now that Postgres don’t have any shared_pool as Oracle, and the session information ( execution plan, etc..) are only available for the current session. Therefore I also highly recommend to us a connection poll as Laurent wrote, in order to have higher chance that some stuff is already cached in the shared session available.
Regards
Herve
From: Daulat Ram [mailto:Daulat.Ram@exponential.com]
Sent: Wednesday, June 26, 2019 3:14 AM
To: pgsql-performance@lists.postgresql.org
Subject: Max_connections limit
Hello team,
We have migrated our database from Oracle 12c to Postgres 11. I need your suggestions , we have sessions limit in Oracle = 3024 . Do we need to set the same connection limit in Postgres as well. How we can decide the max_connections limit for postgres. Are there any differences in managing connections in Oracle and postgres.
SQL> show parameter sessions;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
sessions integer 3024
shared_server_sessions integer
SQL>
Regards,
Daulat
The difference between Oracle and PG is that Oracle has “built-in” connection pooler, and PG does not.
You should use external pooler (i.e. PgBouncer) and reduce number of allowed connections in PG config to about 50, while allowing thousands client connection when configuring PgBouncer.
Regards,
Igor Neyman