Thread: What is wrong with pg_pconnect() ?
Hi, I read a few sites (including php.net, article about db persistent connections) speaking about the adventage of pg_pconnect() php function... I got a "big" problem with pconnections made with the pg_pconnect function. The problem is the following : I'm using a stable debian with: apache 1.3.26 php 4.1.2 postgres 7.2 (I know some guys thinks those are old versions... no, it's just debian stable versions ;)) So , here is the pb : - A client connects for the first time to my httpd and load a script which contain a pg_pconnect() call, what create a pgsql process... so far so good ... - As my httpd is in keepalive mode (wiuth a keepalive_timeout of 15 secs), if the user loads another page with an other pg_pconnect() call (with the same parameters as the first pconnect()), it uses the same pgsql process created before. Cool ! pconnect is great ! - BUT, when the keepalive times out, the dedicated apache process dies (what is normal), BUT the pgsql thread is still alive ! with "idle" state ! Of course, since the related apache process has died, this idling pgsql process will stay in memory for ever ! won't be reused ever ! I don't know how to deal with this ! If the pgsql forked-process where killed when apache forked-process die, all should be OK ! but it does not :( Tell me if there are any solutions... Thanks in advance. Sylvain
Your apache process shouldn't be dying after the keep alive timeout and probably isn't. Which is why the pg process isn't dying, it is still connected to the apache child that made the connection. What you want to look at in httpd.conf is MaxRequestsPerChild. That is the number of non-keep alive requests the child will handle before dying. If "0" then it won't die due to number of requests. So, in your scenario, that child only handled one request (subsequent requests via keep-alive don't count). That connection will remain open until the apache child dies or pg times out the connection. The various *_pconnect functions in PHP are powerful but not suited to all purposes. If your web server handles a lot of static pages, images and even dynamic pages that don't connect to the DB then persistent connections aren't necessarily a good solution. The other place they REALLY suck is on a shared server. Imagine 50 different clients using persistent connections to the DB. If persistent connections are used, it is entirely possible that each apache child has 50 different connections to the DB due to the fact that each client is probably using different parameters to connect (different PG users). Now multiply 50 connections by the number of apache children! Your server will come to it's knees rather quickly. I've told a number of hosting providers to turn off the persistent connection functions. > -----Original Message----- > From: pgsql-php-owner@postgresql.org > [mailto:pgsql-php-owner@postgresql.org]On Behalf Of Sylvain Ross > Sent: Monday, December 15, 2003 3:10 PM > To: pgsql-php@postgresql.org > Subject: [PHP] What is wrong with pg_pconnect() ? > > > Hi, > > I read a few sites (including php.net, article about db persistent > connections) speaking about the adventage of pg_pconnect() php function... > > I got a "big" problem with pconnections made with the pg_pconnect > function. > The problem is the following : > > > I'm using a stable debian with: > apache 1.3.26 > php 4.1.2 > postgres 7.2 > > (I know some guys thinks those are old versions... no, it's just debian > stable versions ;)) > > > So , here is the pb : > > - A client connects for the first time to my httpd and load a script which > contain a pg_pconnect() call, what create a pgsql process... so > far so good > ... > > - As my httpd is in keepalive mode (wiuth a keepalive_timeout of 15 secs), > if the user loads another page with an other pg_pconnect() call (with the > same parameters as the first pconnect()), it uses the same pgsql process > created before. Cool ! pconnect is great ! > > - BUT, when the keepalive times out, the dedicated apache process > dies (what > is normal), BUT the pgsql thread is still alive ! with "idle" state ! > > Of course, since the related apache process has died, this idling pgsql > process will stay in memory for ever ! won't be reused ever ! > > I don't know how to deal with this ! If the pgsql forked-process where > killed when apache forked-process die, all should be OK ! but it > does not :( > > > Tell me if there are any solutions... > > Thanks in advance. > > Sylvain > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >
As Rod pointed out, you likely aren't having dead apache children, just too many. IF you must have pconnects in your code, then you need to configure your apache server and postgresql server so that the postgresql server can spawn more children than the apache server. Set MaxClients in httpd.conf to something MUCH lower than the default of 150. Usually 20 to 50 will be plenty. Restart apache. Then, set postgresql to handle at least 2x as many backends as with the max_connections setting and restart it. Now, you should be able to handle pconnects. My personal testing has shown my dual PIII-750 (being retired at the end of the month) can handle about 1,000 to 10,000 pg_connects a second, and about 100,000 to 1,000,000 pg_pconnects a second. Since the average page build / delivery time on our intranet server is around 0.5 to 1.5 seconds, the speed of connections is pretty much noise. We have templating, database accesses, and httpd authentication via LDAP on each page, and the biggest eater of CPU time is the httpd->LDAP authentication, followed by the templating, followed by the database layer. I.e. Postgresql and its connections are the least of our worries. :-0
Oh, sorry pal, I were convinced that the child process were destroyed after
the keepalived connection has died.
In fact what happens with apache is (if I have understand correctly) :
- The apache child process (pid 8888) is created with "Client A" connected
on it.
- if "Client A" don't query the server for 15 secondes (keepalive timeout),
"Client A" is disconnected from the apache child process, pid 8888. BUT the
apache child process 8888 is still alive waiting for connections ?
- If "client A" had launched a script using a pconnect() call, a connection
has been created beetween a pgsql process and process 8888.
- a new client "Client B" now connects itself to apache process 8888 (why
this process and not another living idling apache process ? :)), I guess any
similar pconnect will use the already existing pgsql connection.
I wish I'm right !
Thx in advance.
Sylvain.
-----Message d'origine-----
De : pgsql-php-owner@postgresql.org
[mailto:pgsql-php-owner@postgresql.org]De la part de scott.marlowe
Envoye : lundi 15 decembre 2003 22:55
A : Sylvain Ross
Cc : pgsql-php@postgresql.org
Objet : Re: [PHP] What is wrong with pg_pconnect() ?
As Rod pointed out, you likely aren't having dead apache children, just
too many.
IF you must have pconnects in your code, then you need to configure your
apache server and postgresql server so that the postgresql server can
spawn more children than the apache server.
Set MaxClients in httpd.conf to something MUCH lower than the default
of 150. Usually 20 to 50 will be plenty. Restart apache.
Then, set postgresql to handle at least 2x as many backends as with the
max_connections setting and restart it.
Now, you should be able to handle pconnects.
My personal testing has shown my dual PIII-750 (being retired at the end
of the month) can handle about 1,000 to 10,000 pg_connects a second, and
about 100,000 to 1,000,000 pg_pconnects a second. Since the average page
build / delivery time on our intranet server is around 0.5 to 1.5 seconds,
the speed of connections is pretty much noise. We have templating,
database accesses, and httpd authentication via LDAP on each page, and the
biggest eater of CPU time is the httpd->LDAP authentication, followed by
the templating, followed by the database layer. I.e. Postgresql and its
connections are the least of our worries. :-0
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
> -----Original Message----- > From: pgsql-php-owner@postgresql.org > [mailto:pgsql-php-owner@postgresql.org]On Behalf Of Sylvain Ross > Sent: Monday, December 15, 2003 5:54 PM > To: scott.marlowe > Cc: pgsql-php@postgresql.org > Subject: Re: [PHP] What is wrong with pg_pconnect() ? > > > Oh, sorry pal, I were convinced that the child process were > destroyed after > the keepalived connection has died. > > In fact what happens with apache is (if I have understand correctly) : > > - The apache child process (pid 8888) is created with "Client A" connected > on it. > > - if "Client A" don't query the server for 15 secondes (keepalive > timeout), > "Client A" is disconnected from the apache child process, pid > 8888. BUT the > apache child process 8888 is still alive waiting for connections ? Yes > > - If "client A" had launched a script using a pconnect() call, a > connection > has been created beetween a pgsql process and process 8888. Yes > > - a new client "Client B" now connects itself to apache process 8888 (why > this process and not another living idling apache process ? :)), That's controlled by the root apache process. It may or may not be a different process. > I guess any > similar pconnect will use the already existing pgsql connection. Only in the same apache child. > > > I wish I'm right ! > > Thx in advance. > > Sylvain. > > -----Message d'origine----- > De : pgsql-php-owner@postgresql.org > [mailto:pgsql-php-owner@postgresql.org]De la part de scott.marlowe > Envoye : lundi 15 decembre 2003 22:55 > A : Sylvain Ross > Cc : pgsql-php@postgresql.org > Objet : Re: [PHP] What is wrong with pg_pconnect() ? > > > > As Rod pointed out, you likely aren't having dead apache children, just > too many. > > IF you must have pconnects in your code, then you need to configure your > apache server and postgresql server so that the postgresql server can > spawn more children than the apache server. > > Set MaxClients in httpd.conf to something MUCH lower than the default > of 150. Usually 20 to 50 will be plenty. Restart apache. > > Then, set postgresql to handle at least 2x as many backends as with the > max_connections setting and restart it. > > Now, you should be able to handle pconnects. > > My personal testing has shown my dual PIII-750 (being retired at the end > of the month) can handle about 1,000 to 10,000 pg_connects a second, and > about 100,000 to 1,000,000 pg_pconnects a second. Since the average page > build / delivery time on our intranet server is around 0.5 to 1.5 seconds, > the speed of connections is pretty much noise. We have templating, > database accesses, and httpd authentication via LDAP on each page, and the > biggest eater of CPU time is the httpd->LDAP authentication, followed by > the templating, followed by the database layer. I.e. Postgresql and its > connections are the least of our worries. :-0 > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >