Обсуждение: Too many clients to Postgresql server
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/
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
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.
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, >
> 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 ...
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/
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
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