Обсуждение: [GENERAL] Maximum of connections in PG

Поиск
Список
Период
Сортировка

[GENERAL] Maximum of connections in PG

От
Durumdara
Дата:
Dear Members!

In a very strong Linux machine (with many 16-30 GB RAM) what is the limit of the PGSQL server (9.4-9.5) "maximum connections"?

1000?
2000?

The clients are native applications (Windows executables) with persistent connections, with more than 100 databases (every client have only one database connection).

Now we must determine where is the upper limit to know when we must buy a new machine to customers clients (which have to migrate in future).

I know my question is too common without precise numbers, but what is I need is your experiences in this theme?
What areas are problematic when we increase the "max_connection" number?

Thanks for any info!

Best wishes
   dd

Re: [GENERAL] Maximum of connections in PG

От
Melvin Davidson
Дата:


On Tue, Mar 14, 2017 at 10:15 AM, Durumdara <durumdara@gmail.com> wrote:
Dear Members!

In a very strong Linux machine (with many 16-30 GB RAM) what is the limit of the PGSQL server (9.4-9.5) "maximum connections"?

1000?
2000?

The clients are native applications (Windows executables) with persistent connections, with more than 100 databases (every client have only one database connection).

Now we must determine where is the upper limit to know when we must buy a new machine to customers clients (which have to migrate in future).

I know my question is too common without precise numbers, but what is I need is your experiences in this theme?
What areas are problematic when we increase the "max_connection" number?

Thanks for any info!

Best wishes
   dd

>In a very strong Linux machine (with many 16-30 GB RAM) what is the limit of the PGSQL server (9.4-9.5) "maximum connections"?
It all depends on the amount of shared memory, which in turn is dependent on the O/S memory.

But if you are going to have thousands of users, you are better off using a connection pooler. My preference is for PgBouncer.
https://pgbouncer.github.io/

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Maximum of connections in PG

От
Adrian Klaver
Дата:
On 03/14/2017 07:15 AM, Durumdara wrote:
> Dear Members!
>
> In a very strong Linux machine (with many 16-30 GB RAM) what is the
> limit of the PGSQL server (9.4-9.5) "maximum connections"?
>
> 1000?
> 2000?
>
> The clients are native applications (Windows executables) with
> persistent connections, with more than 100 databases (every client have
> only one database connection).
>
> Now we must determine where is the upper limit to know when we must buy
> a new machine to customers clients (which have to migrate in future).
>
> I know my question is too common without precise numbers, but what is I
> need is your experiences in this theme?
> What areas are problematic when we increase the "max_connection" number?


https://wiki.postgresql.org/wiki/Number_Of_Database_Connections

>
> Thanks for any info!
>
> Best wishes
>    dd


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: [GENERAL] Maximum of connections in PG

От
Durumdara
Дата:
Dear Melvin!

What is the meaning of PgBouncer with persistent, non-interruptable connections? To I know it (for learn).

They are non web connections (request, get connection, result, drop connection), they are pure, native applications which are keeping connection from the start to the termination.

Thank you!

dd




2017-03-14 15:29 GMT+01:00 Melvin Davidson <melvin6925@gmail.com>:


On Tue, Mar 14, 2017 at 10:15 AM, Durumdara <durumdara@gmail.com> wrote:
Dear Members!

In a very strong Linux machine (with many 16-30 GB RAM) what is the limit of the PGSQL server (9.4-9.5) "maximum connections"?

1000?
2000?

The clients are native applications (Windows executables) with persistent connections, with more than 100 databases (every client have only one database connection).

Now we must determine where is the upper limit to know when we must buy a new machine to customers clients (which have to migrate in future).

I know my question is too common without precise numbers, but what is I need is your experiences in this theme?
What areas are problematic when we increase the "max_connection" number?

Thanks for any info!

Best wishes
   dd

>In a very strong Linux machine (with many 16-30 GB RAM) what is the limit of the PGSQL server (9.4-9.5) "maximum connections"?
It all depends on the amount of shared memory, which in turn is dependent on the O/S memory.

But if you are going to have thousands of users, you are better off using a connection pooler. My preference is for PgBouncer.
https://pgbouncer.github.io/

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Maximum of connections in PG

От
Melvin Davidson
Дата:

On Tue, Mar 14, 2017 at 12:08 PM, Durumdara <durumdara@gmail.com> wrote:
Dear Melvin!

What is the meaning of PgBouncer with persistent, non-interruptable connections? To I know it (for learn).

They are non web connections (request, get connection, result, drop connection), they are pure, native applications which are keeping connection from the start to the termination.

Thank you!

dd




2017-03-14 15:29 GMT+01:00 Melvin Davidson <melvin6925@gmail.com>:


On Tue, Mar 14, 2017 at 10:15 AM, Durumdara <durumdara@gmail.com> wrote:
Dear Members!

In a very strong Linux machine (with many 16-30 GB RAM) what is the limit of the PGSQL server (9.4-9.5) "maximum connections"?

1000?
2000?

The clients are native applications (Windows executables) with persistent connections, with more than 100 databases (every client have only one database connection).

Now we must determine where is the upper limit to know when we must buy a new machine to customers clients (which have to migrate in future).

I know my question is too common without precise numbers, but what is I need is your experiences in this theme?
What areas are problematic when we increase the "max_connection" number?

Thanks for any info!

Best wishes
   dd

>In a very strong Linux machine (with many 16-30 GB RAM) what is the limit of the PGSQL server (9.4-9.5) "maximum connections"?
It all depends on the amount of shared memory, which in turn is dependent on the O/S memory.

But if you are going to have thousands of users, you are better off using a connection pooler. My preference is for PgBouncer.
https://pgbouncer.github.io/

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


>What is the meaning of PgBouncer with persistent, non-interruptable connections?
Adrian has provided you with the most pertinent link. I strongly suggest you rtfm.
If that does not answer your question, then start here https://wiki.postgresql.org/wiki/PgBouncer


--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Maximum of connections in PG

От
Adrian Klaver
Дата:
On 03/14/2017 09:08 AM, Durumdara wrote:
> Dear Melvin!
>
> What is the meaning of PgBouncer with persistent, non-interruptable
> connections? To I know it (for learn).
>
> They are non web connections (request, get connection, result, drop
> connection), they are pure, native applications which are keeping
> connection from the start to the termination.

So just so we are same page:

1) When you say Web connections you are referring to HTTP:

        *connection*
    client request --> Web Server
                                |
         client     <-- reponse |
    *connection*

    repeat *connection* section for each request

2) Postgres connections

    *connection*
    client  connect -->
        query   -->   Postgres server
        query   -->
         client disconnect <--
    *connection*

In either case you are connecting/disconnecting it is just a matter of
when. So if I am following correctly you do not actually need a
connection to the Postgres server that is always on you just need one to
be available when you run a query or set of queries.

>
> Thank you!
>
> dd
>
>
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com