Обсуждение: 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