RE: [PHP] Re: Understanding persistent connections with oci8

Hi Richard,

You've asked the $64,000 question. What is a reasonable way for this
timeout to work? Unfortunately we haven't gotten far with the
bug.php.net folks to ask the question. I agree with you that providing
an active thread or process to go out and check for idle persistent
connections is too much overhead.
I think what is needed is a little investigation to see what can be
reasonable done if a customer wants to use the oci8.persistent_timeout
to limit how long these connections hang around.

From our testing of multiple connections we could easily create 50 of
these persistent connections and would have to hit the apache web server
pretty hard with a 1000 requests at 50 at a time to get these to
terminate after the timeout. But that does work, so that is one data
point and limit when setting this idle timeout. What I think should
also happen if the timeout is set, and the persistent connection goes
idle long enough it should be marked as a candidate for shutting down.
PHP itself if it goes to use this stale connection, should kill it and
use another connection, hopefully a non-stale persistent connection.

And actually, Kiran in our lab proposed that as a very easy fix to
implement, and we've tested it and it works fine. In other words we
make 50 persistent connections, with the timeout at 10 seconds, wait a
minute and then make another round of the same php requests. With the
fix we proposed all new php connections are made. So we know this
works. I think if this was implemented and the documentation was clear
that what the persistent timeout provided was for these connections to
be shutdown when every a new connection tried to use them, PHP or not,
that would go along way to satisfying most customers. Especially if it
clearly and cleanly documented that was how it worked.

I can also see environments where the system hosting apache and these
persistent connections for the database is very busy, and can get large
burst of PHP requests from different users accessing the data at a given
time, which creates lots of persistent connections. I can see them
wanting a more active mechanism for terminating any connections after
the peak has ended and thinks have quieted down for awhile. I think
your idea of a cron job might work there, or the same mechanism within
PHP. If there was one, I'd make it optional as it would be overhead and
many customers wouldn't want it.

There seems to me to be several ways to implement this
persistent_timeout that would make it act like how most idle timeouts
work, and I agree if we can come up with some simple, low overhead ways
that are well documented that would do the trick.


Thanks and regards, Jay



-----Original Message-----
From: Richard Lynch [mailto:ceo@l-i-e.com]
Sent: Wednesday, October 11, 2006 3:44 PM
To: Bauer, Jay W
Cc: php-general@lists.php.net; Bauer, Jay W; Mendonce, Kiran (STSD);
Nikiel, Carsten; Rai, Moni (GSE WTEC Cupertino); Rieslund, Mikael
Subject: RE: [php] Re: Understanding persistent connections with oci8

On Wed, October 11, 2006 8:46 am, Bauer, Jay W wrote:
> Yes there have been some trigger-happy, bug-bogus-marking going on,
> but that happens.
>
> And yes, this stays alive without anything going on. And we have
> tested your "other wrinkle" and that is how we have been able to end
> the persistent connections by making connections to a non-oracle-db
> request which will terminate the persistent connections. The only
> other way has been to restart apache. And I can redo the test with
> just one server and one connection and explicitly terminate the
> persistent connection by making a new non-oracle-db request.

Okay, so here's the thing...

At what point in the process of normal web-server operations would you
expect PHP to "notice" that the connection is stale and it should die?

It's clear, to me, and to many others, that the problem here is not that
it doesn't work -- Just that it "works" in a non-intuitive way for
perfectly logical technical reasons.

To clarify this, consider your one-process, one-user, one-hit test.

Time
00:00 User asks for URL that accesses DB
00:01 PHP opens persistent connection
00:02 PHP returns HTML to user
[It's probably not really 2 seconds, but let's just pretend.]

At this juncture, Apache and PHP pretty much just go back to sleep, and
don't do anything at all until something else comes along to wake them
up.

If the next thing to wake them up authenticates against the DB, and
starts using the connections, then they are re-used, because they are
already there, and there is no sense in tearing down a perfectly good
"stale" connection if you're about to build another one.

If the next thing to wake them up does NOT use the DB connection, the
connection is noticed to be stale and killed off.

This is how it works, as far as we can see from your testing.

So there are several possible solutions here.

PHP could set some kind of "timer" to wake itself up, check any
outstanding persistent connections, and kill them off. This would need
to be managed so that there is only one active "timer" and its wake-up
time is extended as various events occur, since you wouldn't want it
doing too much extra work that PHP is already doing.

That would probably involve a great deal of inter-process communication
and some significant overhead.

The other option, cheap and easy, is to behave the way it does now:
If somebody wakes PHP up, and it sees a stale connection, and it's not
gonna use that stale connection, it kills it off.

Now for MOST users, on MOST real-life scenarios, the cheap and easy
solution is best.

For those who have an unusual case, such as your dev server, a cron job
to "heartbeat" a non-DB page every X minutes is a no-brainer, once one
understands what's going on and why.

You are certainly welcome to code and submit a patch to PHP to make it
wake up and kill off stale connections, if you really think this is
absolutely crucial... But if it has the kind of overhead I suspect it
would have, it's not likely to be implemented.

Or you could run a heartbeat cron job to a non-DB connection just a
little bit longer than your connection time-out, and call the problem
solved.

NOTE:
I am NOT an expert on PHP internals, and may be grossly incorrect about
all this. I'm just giving my perception of your test results, based on
how HTTP / Apache/ PHP generally work.

PS I think this is how all the "persistent" connections operate...

--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some starving artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?

# jay.bauer@hp.com / 2006-10-11 16:42:06 -0400:
> And actually, Kiran in our lab proposed that as a very easy fix to
> implement, and we've tested it and it works fine. In other words we
> make 50 persistent connections, with the timeout at 10 seconds, wait a
> minute and then make another round of the same php requests. With the
> fix we proposed all new php connections are made. So we know this
> works. I think if this was implemented and the documentation was clear
> that what the persistent timeout provided was for these connections to
> be shutdown when every a new connection tried to use them, PHP or not,
> that would go along way to satisfying most customers. Especially if it
> clearly and cleanly documented that was how it worked.

That's not so much a fix as a different behavior. FWIW the current
one is perfectly sensible in other circumstances.

Is there a way to query the age of the connection?

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

Hi Roman,

As our customers see this, they think if they set a timeout of 10
seconds, that when these connections aren't used for hours they should
go away. And I can understand that. As I understand the current
workings, I can see an argument for keeping things as they are, but if
that is the case the current behavior needs to documented. In a sense
the documentation is the bug.

And yes whatever is done will be change in behavior, and it may be a
design change or it might be a fix, it really isn't all that important.
What I like to see is the documentation and behavior match. They don't
now.

Regarding there being a way to query the age of the connection, what
was found is that when the timeout occurred there is a easy check that
can be made to see if it has happened. Currently that is never checked
once the connection is opened. The "fix" our lab tried was to check if
the timeout happened whenever the an attempt was made to use the open
connection again. If the timeout had occurred, then the connection was
closed. It is a passive check, and I think reasonable.

Again if someone is using this oci8.persistent_timeout they are using
it because they want a way of controlling how long these connections
stay around. And given this is a timeout for idle connections, it is
our customer's expectation that these connections will terminate.

What I see as needed is clear documentation regarding how this timeout
works and what it does and under what circumstances a persistent
connection will terminate when the timeout is set to a time other than
-1, or infinite.

I don't see this as a major coding issue, simply one of documentation
and possibly implementing a small change which would actually implement
an action once the timeout occurred. Obviously this 10 sec timeout
being used is for testing to see the behavior, I would expect that
normally the time, if not -1 and infinite would be more along the line
of hours in the real world.

Thanks and regards, Jay


-----Original Message-----
From: Roman Neuhauser [mailto:neuhauser@sigpipe.cz]
Sent: Wednesday, October 11, 2006 9:25 PM
To: Bauer, Jay W
Cc: ceo@l-i-e.com; php-general@lists.php.net; Mendonce, Kiran (STSD);
Nikiel, Carsten; Rai, Moni (GSE WTEC Cupertino); Rieslund, Mikael;
Bauer, Jay W
Subject: Re: [php] Re: Understanding persistent connections with oci8

# jay.bauer@hp.com / 2006-10-11 16:42:06 -0400:
> And actually, Kiran in our lab proposed that as a very easy fix to
> implement, and we've tested it and it works fine. In other words we
> make 50 persistent connections, with the timeout at 10 seconds, wait a

> minute and then make another round of the same php requests. With the

> fix we proposed all new php connections are made. So we know this
> works. I think if this was implemented and the documentation was
> clear that what the persistent timeout provided was for these
> connections to be shutdown when every a new connection tried to use
> them, PHP or not, that would go along way to satisfying most
> customers. Especially if it clearly and cleanly documented that was
how it worked.

That's not so much a fix as a different behavior. FWIW the current
one is perfectly sensible in other circumstances.

Is there a way to query the age of the connection?

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

On 12 Oct 2006, at 11:10 , Bauer, Jay W wrote:
> Again if someone is using this oci8.persistent_timeout they are using
> it because they want a way of controlling how long these connections
> stay around.

Well, are they really?

I would think they are using it to free up idle connections for use
with new connections. That seems reasonable. And they are saying,
"Even if a connection appears to be idle right now, I only want it
considered idle if there's been no activity in x seconds".

I can see how it's not what you expected, but it still seems reasonable.

Look at it this way, there's no point checking if an idle connection
needs to be closed unless the idle connection is needed elsewhere.

--
Bishops move diagonally. That's why they often turn up where the
kings don't expect them to be.