Thread: Too many clients to Postgresql server

Too many clients to Postgresql server

From
Siva Kumar
Date:
We have a website using postgresql and php, hosted in a shared server. The
settings for the maximum connections allowed is 32.

I have used the persistent connection function pg_pconnect for all the
database connections. For the past one week I have started getting the
maximum number of clients reached message now and then. Today one of my
customers also called up with the same problem.

The question is, should I change the way of connecting to the server to
pg_connect? What will be the implications? Otherwise, how can the persistent
connections be closed down?

Thanks and best regards,


Siva Kumar





--
Ma Siva Kumar,
BSG LeatherLink,
Chennai, India.
Phone: 6215110
Email: siva@leatherlink.net
URL: http://www.leatherlink.net/

Re: Too many clients to Postgresql server

From
Robert Treat
Date:
Since you don't have the ability to bump up the maximum number of
connections, your best bet is to switch to pg_connect. The implications
would be that each page load will require a connection to the server,
but unless you are doing multiple database *connections* per page, this
impact is usually insignificant.

Robert Treat

On Thu, 2002-07-18 at 15:09, Siva Kumar wrote:
> This message uses a character set that is not supported by the Internet
> Service.  To view the original message content,  open the attached
> message. If the text doesn't display correctly, save the attachment to
> disk, and then open it using a viewer that can display the original
> character set. <<message.txt>>
> ----
>
> We have a website using postgresql and php, hosted in a shared server. The
> settings for the maximum connections allowed is 32.
>
> I have used the persistent connection function pg_pconnect for all the
> database connections. For the past one week I have started getting the
> maximum number of clients reached message now and then. Today one of my
> customers also called up with the same problem.
>
> The question is, should I change the way of connecting to the server to
> pg_connect? What will be the implications? Otherwise, how can the persistent
> connections be closed down?
>
> Thanks and best regards,
>
>
> Siva Kumar



Re: Too many clients to Postgresql server

From
Thomas Beutin
Date:
On Thu, Jul 18, 2002 at 10:48:22AM -0400, Robert Treat wrote:
> Since you don't have the ability to bump up the maximum number of
> connections, your best bet is to switch to pg_connect. The implications
> would be that each page load will require a connection to the server,
> but unless you are doing multiple database *connections* per page, this
> impact is usually insignificant.
If You don't need the performance go to pg_connect, but You can reach the
limit this way too. You can adjust the max pg_pconnect's in the php.ini.

Greetings,
-tb

> On Thu, 2002-07-18 at 15:09, Siva Kumar wrote:
> > We have a website using postgresql and php, hosted in a shared server. The
> > settings for the maximum connections allowed is 32.
> >
> > I have used the persistent connection function pg_pconnect for all the
> > database connections. For the past one week I have started getting the
> > maximum number of clients reached message now and then. Today one of my
> > customers also called up with the same problem.
> >
> > The question is, should I change the way of connecting to the server to
> > pg_connect? What will be the implications? Otherwise, how can the persistent
> > connections be closed down?
> >
> > Thanks and best regards,
--
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

Re: Too many clients to Postgresql server

From
"scott.marlowe"
Date:
On Thu, 18 Jul 2002, Thomas Beutin wrote:

> On Thu, Jul 18, 2002 at 10:48:22AM -0400, Robert Treat wrote:
> > Since you don't have the ability to bump up the maximum number of
> > connections, your best bet is to switch to pg_connect. The implications
> > would be that each page load will require a connection to the server,
> > but unless you are doing multiple database *connections* per page, this
> > impact is usually insignificant.

> If You don't need the performance go to pg_connect, but You can reach the
> limit this way too. You can adjust the max pg_pconnect's in the php.ini.

This isn't true, the max_persistant connections in the php.ini sets the
max number of persistant connections per apache/php backend.  I.e. setting
max persistant to 16 would limit each apache child to 16 persistant
connections.  If apache has the default of 150 children set, that's 150*16
max persistant connections.

The best bet here if one HAS to use persistant connections is to limit the
apache server to some smaller number (max_psql_connections-4 or
somethning)

Scott Marlowe

> Greetings,
> -tb
>
> > On Thu, 2002-07-18 at 15:09, Siva Kumar wrote:
> > > We have a website using postgresql and php, hosted in a shared server. The
> > > settings for the maximum connections allowed is 32.
> > >
> > > I have used the persistent connection function pg_pconnect for all the
> > > database connections. For the past one week I have started getting the
> > > maximum number of clients reached message now and then. Today one of my
> > > customers also called up with the same problem.
> > >
> > > The question is, should I change the way of connecting to the server to
> > > pg_connect? What will be the implications? Otherwise, how can the persistent
> > > connections be closed down?
> > >
> > > Thanks and best regards,
>



Re: Too many clients to Postgresql server

From
Ralph Graulich
Date:
> If You don't need the performance go to pg_connect, but You can reach the
> limit this way too. You can adjust the max pg_pconnect's in the php.ini.

Whereas one has to obey, that this limit is a "per httpd child"-limit,
rather than a global limit counting the the parent httpd process and all
its children.

So if you have a MAX_SERVERS of 50 and limited pg_connect to 5, it counts
for 50 times 5 => 250 connections at maximum.


Kind regards
... Ralph ...



Re: Too many clients to Postgresql server

From
Siva Kumar
Date:
Thanks for the advice.

I went through the configuraiton files at the server (the service provider is
not of much help) and found the following:

MaxClients in httpd.conf = 2048
MaxRequestsPerChild in httpd.conf = 0
pgsql.allow.persistent in php.ini =On
pgsql.max_persistent = -1 (Unlimited)
pgsql.max_links = -1 (Unlimited)

The support person at the provider told me that they are just using the
default file for postgresql.conf (version 7.2.1). I could not read the file
as the read access to the file is denied.

When I faced the "maximum clients reached" message yesterday, I checked the
number of postmaster processes running, it was 32 connections made by my user
name. This morning this has come down to 8. Therefore I guess, the maximum is
reached at 32.

Can you please suggest what is to be changed to increase the 32 connection
limit. (I am just learning as I go along with all these, so please bear with
me).

Best regards,


Siva Kumar



Ma Siva Kumar,
BSG LeatherLink,
Chennai, India.
Phone: 6215110
Email: siva@leatherlink.net
URL: http://www.leatherlink.net/

Re: Too many clients to Postgresql server

From
Ericson Smith
Date:
Your hosting provider needs to change their "postgresql.conf" and
increase the number of connections there. My system is set to:

max_connections = 512

In your case, this would still be well below the 2048 possible
connections that you could get from Apache. So its up to them to decide
how they want to proceed.

- Ericson Smith
eric@did-it.com


On Fri, 2002-07-19 at 05:52, Siva Kumar wrote:
> Thanks for the advice.
>
> I went through the configuraiton files at the server (the service provider is
> not of much help) and found the following:
>
> MaxClients in httpd.conf = 2048
> MaxRequestsPerChild in httpd.conf = 0
> pgsql.allow.persistent in php.ini =On
> pgsql.max_persistent = -1 (Unlimited)
> pgsql.max_links = -1 (Unlimited)
>
> The support person at the provider told me that they are just using the
> default file for postgresql.conf (version 7.2.1). I could not read the file
> as the read access to the file is denied.
>
> When I faced the "maximum clients reached" message yesterday, I checked the
> number of postmaster processes running, it was 32 connections made by my user
> name. This morning this has come down to 8. Therefore I guess, the maximum is
> reached at 32.
>
> Can you please suggest what is to be changed to increase the 32 connection
> limit. (I am just learning as I go along with all these, so please bear with
> me).
>
> Best regards,
>
>
> Siva Kumar
>
>
>
> Ma Siva Kumar,
> BSG LeatherLink,
> Chennai, India.
> Phone: 6215110
> Email: siva@leatherlink.net
> URL: http://www.leatherlink.net/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html



Re: Too many clients to Postgresql server

From
Andrew Sullivan
Date:
On Fri, Jul 19, 2002 at 09:08:33AM -0400, Ericson Smith wrote:
> Your hosting provider needs to change their "postgresql.conf" and
> increase the number of connections there. My system is set to:
>
> max_connections = 512

That is not the only thing to change in the default config file!  The
default shared_buffers setting will make the system crawl under
anything above near-zero loads.  It'd probably be wise to make up a
list of changes one wants, and ship it in one request.

It strikes me that, since most people don't have root on their
virtual hosts, installing postgres without root is helpful.  Install
it instead in an account to which you have access.  There are some
major annoyances in doing this, but it makes your life a lot easier
than doing the BOHF's Own Root Dance every time you want to make a
config change.  (Alternatively, you can get them to install it, but
allow you sudo access to the postgres account.  That, of course,
assumes they are competent at writing sudoer files.)

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110