Thread: persistent vs. non-persistent
I'm having headaches with PHP, postgres and persistent connections.
The problem is that my postgres backends stay alive after closing the
connection.
I use pg_pconnect to connect to the database, put after closing the
conection, nothing happens, la backend stays alive, and all I can do is
restart apache to make them close (really close).
Any solution to this?
I'm on Solairs, Apache 1.3.14, PHP 4.0.6, PostgreSQL 7.1.2, I have persistent
connections enabled in PHP, and a max of 3 persistent connections.
Saludos... :-)
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
> I'm having headaches with PHP, postgres and persistent connections. They never have really worked for me, but I haven't tried with the recent PHP->PGSQL improvements.. > The problem is that my postgres backends stay alive after closing the > connection. That's what a persistant connection is, though... You'll have as many minimum PG backends hanging around as you do minimum apache backends (once that many PG backends get started, they hang around). > I use pg_pconnect to connect to the database, put after closing the > conection, nothing happens, la backend stays alive, and all I can do is > restart apache to make them close (really close). > > Any solution to this? Sure, don't use persistant connections..It sounds like they're the opposite of what you want... -Mitch
On Lun 01 Oct 2001 18:46, you wrote:
> > I'm having headaches with PHP, postgres and persistent connections.
>
> They never have really worked for me, but I haven't tried with the recent
> PHP->PGSQL improvements..
>
> > The problem is that my postgres backends stay alive after closing the
> > connection.
>
> That's what a persistant connection is, though... You'll have as many
> minimum PG backends hanging around as you do minimum apache backends (once
> that many PG backends get started, they hang around).
>
> > I use pg_pconnect to connect to the database, put after closing the
> > conection, nothing happens, la backend stays alive, and all I can do is
> > restart apache to make them close (really close).
> >
> > Any solution to this?
>
> Sure, don't use persistant connections..It sounds like they're the opposite
> of what you want...
I've been all day looking for solid info on this case, and what I thought is
this:
Is there a way to seek for open db connections?
I don't understand why apache (or PHP) doesn't see that it has a persistent
database connection open to use.
I'm checking the PHP PGSQL extensions at this moment.
More info later....
Saludos.... ;-)
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
I'm not sure about the internal workings, I see what you mean and will await your finding with great interest! -Mitch ----- Original Message ----- From: "Martín Marqués" <martin@bugs.unl.edu.ar> To: <pgsql-php@postgresql.org> Sent: Monday, October 01, 2001 6:53 PM Subject: Re: [PHP] persistent vs. non-persistent > On Lun 01 Oct 2001 18:46, you wrote: > > > I'm having headaches with PHP, postgres and persistent connections. > > > > They never have really worked for me, but I haven't tried with the recent > > PHP->PGSQL improvements.. > > > > > The problem is that my postgres backends stay alive after closing the > > > connection. > > > > That's what a persistant connection is, though... You'll have as many > > minimum PG backends hanging around as you do minimum apache backends (once > > that many PG backends get started, they hang around). > > > > > I use pg_pconnect to connect to the database, put after closing the > > > conection, nothing happens, la backend stays alive, and all I can do is > > > restart apache to make them close (really close). > > > > > > Any solution to this? > > > > Sure, don't use persistant connections..It sounds like they're the opposite > > of what you want... > > I've been all day looking for solid info on this case, and what I thought is > this: > > Is there a way to seek for open db connections? > > I don't understand why apache (or PHP) doesn't see that it has a persistent > database connection open to use. > > I'm checking the PHP PGSQL extensions at this moment. > More info later.... > > Saludos.... ;-) > > -- > Porqué usar una base de datos relacional cualquiera, > si podés usar PostgreSQL? > ----------------------------------------------------------------- > Martín Marqués | mmarques@unl.edu.ar > Programador, Administrador, DBA | Centro de Telematica > Universidad Nacional > del Litoral > ----------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Mon, Oct 01, 2001 at 06:56:37PM -0400, Mitch Vincent wrote: > I'm not sure about the internal workings, I see what you mean and will await > your finding with great interest! > > -Mitch [ . . . ] > > I don't understand why apache (or PHP) doesn't see that it has a > persistent > > database connection open to use. > > > > I'm checking the PHP PGSQL extensions at this moment. > > More info later.... > > > > Saludos.... ;-) I keep having problems too; I think because I never quite managed to figure out the mechanism either. Sometime last year someone mentioned that the php.ini parameters pgsql.max_links pgsql.max_persistent are meant to be understood as /per Apache child/; so if you have, say, 5 Apache children waiting for a request, and pgsql.max_persistent = 2 you could have up to 10 open connections. If an apache child which does not have an open connection gets a request, it will open a new connection, obviously. So if 4 of the 5 Apache children have open connections, but the 5th gets the next request, a new connection will be opened. I am not sure about what happens when a child which already has an open connection gets a new request. Hypothesis: It depends on the database. Say you have 3 distinct databases on your server and your php.ini parameter pgsql.max_persistent = 2 Now let's say you have 10 open Apache children, each of which has a connection to databases #1 and #2. Now requests to databases #1 and #2 will be answered, but not to #3: You'll get the error message: Warning: PostgreSQL: Too many open persistent links (2) My tests seem to confirm this. It would seem to follow that you only need as many persistent connections per Apache child as you have databases from which you want to serve http requests. Regards, Frank
On Tue, 2001-10-02 at 21:21, Frank Joerdens wrote:
> On Mon, Oct 01, 2001 at 06:56:37PM -0400, Mitch Vincent wrote:
> > I'm not sure about the internal workings, I see what you mean and will await
> > your finding with great interest!
> >
> > -Mitch
>
> [ . . . ]
> > > I don't understand why apache (or PHP) doesn't see that it has a
> > persistent
> > > database connection open to use.
> > >
> > > I'm checking the PHP PGSQL extensions at this moment.
> > > More info later....
> > >
> > > Saludos.... ;-)
>
> I keep having problems too; I think because I never quite managed to
> figure out the mechanism either. Sometime last year someone mentioned
> that the php.ini parameters
>
> pgsql.max_links
> pgsql.max_persistent
>
> are meant to be understood as /per Apache child/; so if you have, say, 5
> Apache children waiting for a request, and
>
> pgsql.max_persistent = 2
>
> you could have up to 10 open connections. If an apache child which does
> not have an open connection gets a request, it will open a new
> connection, obviously. So if 4 of the 5 Apache children have open
> connections, but the 5th gets the next request, a new connection will be
> opened. I am not sure about what happens when a child which already has
> an open connection gets a new request. Hypothesis: It depends on the
> database. Say you have 3 distinct databases on your server and your
> php.ini parameter
Yes, I believe that is how it works. For a more reasonable approach to
this problem you should look into (something like) DBBalancer (see
SourceForge for more info).
I have used persistent connections on one application and achieved a
roughly 10x speed improvement, but you need to take a few things into
consideration:
Don't quit your Apache processes too quickly.
- To get the full benefit of a persistent connection, it needs to hang
around for a good number of requests. Make sure Apache isn't re-
starting it's processes until they've done a thousand or so requests - I
usually set it up to around 30,000 with no problems on Linux.
Make sure you have enough RAM.
- When things have reached some sort of 'steady state' you are likely
to have a lot of processes running. You lose all benefit (and then
some) if you are swapping.
This could also be "Be careful about setting your maximums" because you
can control this within Apache and PostgreSQL configurations to some
extent.
But this is where DBBalancer becomes a better approach. As a pool of
database connections, you can get the benefit of (a), while still
retaining a softer degradation under extreme demands. The downside is
that you have to manage your transactions slightly more carefully, and
you are susceptible to memory leak problems in the database client
connection.
These are not insurmountable problems. The upside is that DBBalancer
also lets you make a fairly smooth transition to the _next_ level, where
you have a load-balanced pool of replicated databases.
Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
--- Mart�n Marqu�s <martin@bugs.unl.edu.ar> wrote: > I'm having headaches with PHP, postgres and persistent > connections. > > The problem is that my postgres backends stay alive after > closing the > connection. The connection is never closed if it is a persistent connection. Even if you use pg_close it remains open. > I use pg_pconnect to connect to the database, put after > closing the > conection, nothing happens, la backend stays alive, and all I > can do is > restart apache to make them close (really close). > > Any solution to this? This is exactly the behavior to expect from a persistent connection. This is not a bug. If you do not want this behavior then simply use pg_connect rather than pg_pconnect. Brent __________________________________________________ Do You Yahoo!? Listen to your Yahoo! Mail messages from any phone. http://phone.yahoo.com
--- Andrew McMillan <andrew@catalyst.net.nz> wrote: > Yes, I believe that is how it works. For a more reasonable > approach to > this problem you should look into (something like) DBBalancer > (see > SourceForge for more info). This DBBalancer is very impressive. What type of performance gains have you gotten by using it? Brent __________________________________________________ Do You Yahoo!? Listen to your Yahoo! Mail messages from any phone. http://phone.yahoo.com
Brent, given, that you keep the connection open indefinitely, how do you use it? Do I go and $db = pg_pconnect(...); when I first start apache and then never do a connect again, unless I re-start apache? How would I query if a connection already exists? Best regards, Chris ----- Original Message ----- From: "Brent R. Matzelle" <bmatzelle@yahoo.com> To: <pgsql-php@postgresql.org> Sent: Tuesday, October 02, 2001 9:25 AM Subject: Re: [PHP] persistent vs. non-persistent > --- Martín Marqués <martin@bugs.unl.edu.ar> wrote: > > I'm having headaches with PHP, postgres and persistent > > connections. > > > > The problem is that my postgres backends stay alive after > > closing the > > connection. > > The connection is never closed if it is a persistent connection. > Even if you use pg_close it remains open. > > > I use pg_pconnect to connect to the database, put after > > closing the > > conection, nothing happens, la backend stays alive, and all I > > can do is > > restart apache to make them close (really close). > > > > Any solution to this? > > This is exactly the behavior to expect from a persistent > connection. This is not a bug. If you do not want this > behavior then simply use pg_connect rather than pg_pconnect. > > Brent > > __________________________________________________ > Do You Yahoo!? > Listen to your Yahoo! Mail messages from any phone. > http://phone.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
--- Chris Ruprecht <chrup999@yahoo.com> wrote: > Brent, > > given, that you keep the connection open indefinitely, how do > you use it? > > Do I go and $db = pg_pconnect(...); when I first start apache > and then never > do a connect again, unless I re-start apache? Not quite. When you run pg_pconnect() apache automatically opens a PG connection and holds on to it until it dies or apache is restarted. However, in order to use that database connection again, for each PHP page you will need to re-run pg_pconnect(). Then apache will look for any httpd processes that have an identical open PG connection. If there is one then it will use that connection instead of creating a new one, thus saving you the connection overhead. You can read more about it here: http://www.php.net/manual/en/features.persistent-connections.php Brent __________________________________________________ Do You Yahoo!? Listen to your Yahoo! Mail messages from any phone. http://phone.yahoo.com
On Wed, 2001-10-03 at 02:28, Brent R. Matzelle wrote:
> --- Andrew McMillan <andrew @ catalyst.net.nz> wrote:
> > Yes, I believe that is how it works. For a more reasonable
> > approach to
> > this problem you should look into (something like) DBBalancer
> > (see
> > SourceForge for more info).
>
> This DBBalancer is very impressive. What type of performance
> gains have you gotten by using it?
Performance improvement very much depends on the sorts of queries you
do. Gains are particularly evident for complex queries which return
only a few records, since query plans and data all end up being cached
in the client session.
DBBalancer seems to give about the same level of performance improvement
as persistent connections do, but without needing one PostgreSQL client
/ database / apache session.
Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
You are correct. It is not max per Apache, but max per child process. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Frank Joerdens" <frank@joerdens.de> To: <pgsql-php@postgresql.org> Sent: Tuesday, October 02, 2001 5:21 AM Subject: Re: [PHP] persistent vs. non-persistent > On Mon, Oct 01, 2001 at 06:56:37PM -0400, Mitch Vincent wrote: > > I'm not sure about the internal workings, I see what you mean and will await > > your finding with great interest! > > > > -Mitch > > [ . . . ] > > > I don't understand why apache (or PHP) doesn't see that it has a > > persistent > > > database connection open to use. > > > > > > I'm checking the PHP PGSQL extensions at this moment. > > > More info later.... > > > > > > Saludos.... ;-) > > I keep having problems too; I think because I never quite managed to > figure out the mechanism either. Sometime last year someone mentioned > that the php.ini parameters > > pgsql.max_links > pgsql.max_persistent > > are meant to be understood as /per Apache child/; so if you have, say, 5 > Apache children waiting for a request, and > > pgsql.max_persistent = 2 > > you could have up to 10 open connections. If an apache child which does > not have an open connection gets a request, it will open a new > connection, obviously. So if 4 of the 5 Apache children have open > connections, but the 5th gets the next request, a new connection will be > opened. I am not sure about what happens when a child which already has > an open connection gets a new request. Hypothesis: It depends on the > database. Say you have 3 distinct databases on your server and your > php.ini parameter > > pgsql.max_persistent = 2 > > Now let's say you have 10 open Apache children, each of which has a > connection to databases #1 and #2. Now requests to databases #1 and #2 > will be answered, but not to #3: You'll get the error message: > > Warning: PostgreSQL: Too many open persistent links (2) > > My tests seem to confirm this. > > It would seem to follow that you only need as many persistent > connections per Apache child as you have databases from which you want > to serve http requests. > > Regards, Frank > > ---------------------------(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 >
In a way, it is a bit obvious because if you have a little amount of traffic that child process for Apache are dieing frequently and not hanging around, odds are you aren't doing enough traffic where persistent connections are going to give you any noticeable speed improvement. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Andrew McMillan" <andrew@catalyst.net.nz> To: "Frank Joerdens" <frank@joerdens.de> Cc: <pgsql-php@postgresql.org> Sent: Tuesday, October 02, 2001 6:27 AM Subject: Re: [PHP] persistent vs. non-persistent > Yes, I believe that is how it works. For a more reasonable approach to > this problem you should look into (something like) DBBalancer (see > SourceForge for more info). > > I have used persistent connections on one application and achieved a > roughly 10x speed improvement, but you need to take a few things into > consideration: > > Don't quit your Apache processes too quickly. > - To get the full benefit of a persistent connection, it needs to hang > around for a good number of requests. Make sure Apache isn't re- > starting it's processes until they've done a thousand or so requests - I > usually set it up to around 30,000 with no problems on Linux. > > Make sure you have enough RAM. > - When things have reached some sort of 'steady state' you are likely > to have a lot of processes running. You lose all benefit (and then > some) if you are swapping. > This could also be "Be careful about setting your maximums" because you > can control this within Apache and PostgreSQL configurations to some > extent. > > But this is where DBBalancer becomes a better approach. As a pool of > database connections, you can get the benefit of (a), while still > retaining a softer degradation under extreme demands. The downside is > that you have to manage your transactions slightly more carefully, and > you are susceptible to memory leak problems in the database client > connection. > > These are not insurmountable problems. The upside is that DBBalancer > also lets you make a fairly smooth transition to the _next_ level, where > you have a load-balanced pool of replicated databases. > > Regards, > Andrew. > > -- > -------------------------------------------------------------------- > Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington > WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St > DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
You don't do anything different. For the most part, persistent connections are a "man behind the curtain" thing. PHP and Apache handle the details of reusing connections. You just tell it to pgconnect and it does the checking for you and decides if it needs to build a new connection or not. I don't like to call it this, but you might understand it better if you think of "pooling" (even though it really isn't pooling). Each Apache child will have its own pool of db connections to reuse. From page to page, there is no guarantee you will get the same connection. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Chris Ruprecht" <chrup999@yahoo.com> To: <pgsql-php@postgresql.org> Sent: Tuesday, October 02, 2001 11:02 AM Subject: Re: [PHP] persistent vs. non-persistent > Brent, > > given, that you keep the connection open indefinitely, how do you use it? > > Do I go and $db = pg_pconnect(...); when I first start apache and then never > do a connect again, unless I re-start apache? How would I query if a > connection already exists? > > Best regards, > Chris