pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMax_connections limit
5+ messages / 5 participants
[nested] [flat]
* Max_connections limit
@ 2019-06-26 07:13 Daulat Ram <Daulat.Ram@exponential.com>
0 siblings, 2 replies; 5+ messages in thread
From: Daulat Ram @ 2019-06-26 07:13 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Max_connections limit
@ 2019-06-26 09:05 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Daulat Ram <Daulat.Ram@exponential.com>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2019-06-26 09:05 UTC (permalink / raw)
To: Daulat Ram <Daulat.Ram@exponential.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Max_connections limit
@ 2019-06-26 09:15 Hervé Schweitzer (HER) <herve.schweitzer@dbi-services.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Hervé Schweitzer (HER) @ 2019-06-26 09:15 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Daulat Ram <Daulat.Ram@exponential.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
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
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Max_connections limit
@ 2019-06-26 12:38 Rick Otten <rottenwindfish@gmail.com>
parent: Hervé Schweitzer (HER) <herve.schweitzer@dbi-services.com>
0 siblings, 0 replies; 5+ messages in thread
From: Rick Otten @ 2019-06-26 12:38 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Wed, Jun 26, 2019 at 5:16 AM Hervé Schweitzer (HER) <
herve.schweitzer@dbi-services.com> wrote:
> 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
>
>
The most popular stand-alone connection pooler for PostgreSQL is the oddly
named "pgbouncer": https://wiki.postgresql.org/wiki/PgBouncer
There are others, of course.
PgPool is also very popular:
https://www.pgpool.net/mediawiki/index.php/Main_Page
Some applications can also manage a connection pool efficiently entirely
within the application itself.
Configuring the maximum number of concurrent connections your database
supports incurs significant overhead in the running database. New
connections and disconnections also have a high overhead as they occur. By
moving the connecting/disconnecting logic to a connection pooler you remove
a lot of overhead and load from the database - letting it focus on the
important stuff -- your queries.
It is amazing how many fewer actual connections you need to the database
when you configure a pooler. Most connections from applications and users
are idle most of the time. Even on busy web servers. They just keep that
pathway open in case they need to run a query to save on the overhead of
having to open a new one every time. By using a pooler you only need to
configure connections for the number of concurrent _queries_ rather than
concurrent application and user open but idle connections.
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Max_connections limit
@ 2019-06-26 14:29 Igor Neyman <ineyman@perceptron.com>
parent: Daulat Ram <Daulat.Ram@exponential.com>
1 sibling, 0 replies; 5+ messages in thread
From: Igor Neyman @ 2019-06-26 14:29 UTC (permalink / raw)
To: Daulat Ram <Daulat.Ram@exponential.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-06-26 14:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-06-26 07:13 Max_connections limit Daulat Ram <Daulat.Ram@exponential.com>
2019-06-26 09:05 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-06-26 09:15 ` Hervé Schweitzer (HER) <herve.schweitzer@dbi-services.com>
2019-06-26 12:38 ` Rick Otten <rottenwindfish@gmail.com>
2019-06-26 14:29 ` Igor Neyman <ineyman@perceptron.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox