>I'm running windows 2000 and the latest releases of PHP and MySQL. All
>database connections are made with mysql_pconnect(). Connections build
>up, however. The only person connecting to this webserver at the moment
>is myself. It seems that it opens connections and keeps them open, but
>doesn't always reuse them, thus causing it to open new ones. Is this a
>bug in mysql_pconnect()? Should I just use mysql_connect()? Any and all
>help is appreciated.
Some of this answer uses Un*x-specific terminology. MS, of course, would
never use an industry-standard term, so you may need to translate. And,
there actually is a big difference between "process" on Un*x and "thread"
under Windows. However, for the purposes of this discussion, the difference
is *MOOT* so I'm going to pretend they are the same, even when they aren't.
Here's the deal:
When you do mysql_pconnect(), PHP tells MySQL to *KEEP* the connection open.
MySQL keeps the connection open, and, for performance+security reasons,
knows which process opened the connect, and which username/password combo
was used. You see, if MySQL has to change all the stuff related to
username/password, then a lot of the benefit of keeping the old connection
around would be lost. Much of the benefit of the connection is the
construction of some particularly complicated gnarly pieces of data that tie
the connection to a particular username/password.*
* These last two sentences are possibly wildly inaccurate. Maybe even call
them lies. They are particularly useful, though, in that the average user
of PHP/MySQL can believe them and understand them, and cope with the fact
that _pconnect is tied to the username/password, which is just how it is. I
never let facts get in the way of education. YMMV.
When the *SAME* process ('thread' under Win 2K, alright?) with the same
username/password asks the (obviously same) MySQL server to mysql_pconnect()
again, MySQL goes:
"Oh, look, here I already *have* the connection open and ready. I don't
need to make another one."
The reason you see multiple connections "open" is that you have multiple
processes (threads in Win2K), each one holding on to their connection for
their username/password. Actually, MySQL holds on to them *for* the
process, but they are tagged "for that process/username/password only". But
some folks find it easier to think of them as being "held onto" by the HTTP
process. [shrug]
You see, when you asked for your first page, it was "HTTP Process # 1"s turn
to answer.
Then, on the second page, it was "HTTP Process # 2"s turn.
Third, maybe it was #3's turn. Or #4's. Who knows?
Maybe you didn't even do mysql_pconnect on your 4th request.
Maybe by the time you asked for the fifth page, #1 was ready again.
Time went by, and different processes answered different HTTP requests.
*EACH* Process, as it does a _pconnect() gets its own "persistent"
connection left open for it. Indeed, two processes *might* be running
"concurrently" and *need* two connections open, since you don't want one
connection's data getting mixed up with the other's data. They are both
running at the same "time". **
** Purists will point out that the CPU can only do one thing at a time...
Yes, but the *context* for what it is doing, the script that is running, the
data structures/streams required, are all "alive" in some sense
concurrently, even though the actual CPU is only dealing with one at a time.
The Apache/PHP/MySQL/OS software needs to keep all this crap organized and
different processes separated from each other, and it's easiest to think of
them as running "concurrently" even when technically they are all taking
turns with the CPU in time "slices". Unless, of course, you have multiple
CPUs, in which case this paragraph (and entire post) would get hopelessly
complexified to no real benefit for the average reader.
In time *every* process will have its own persistent connection.
If you are running Apache, you can configure the number of "Child" processes
and thus control the maximum number of open connections to MySQL. (One per
process/username/password).
If you are running IIS, you are on your own. :-) While I assume there is
some kind of way to configure IIS wrt number of threads/children, I have no
idea, and don't even want to know, how to do it.
Meanwhile, it is abso[bleep]ing critical that your MySQL settings be such
that *IT* does not have fewer connections limited than the number of
"persistent" connections that your web-server is going to use. In fact, you
want a few extra, so that you can *ALWAYS* use the monitor from the shell to
take down a thrashing, dying, over-loaded MySQL. If you're maxed-out, you
can't even get to MySQL from the shell, and you're in trouble.
Example:
Suppose httpd.conf has a "cap" of 30 HTTPD child processes.
Then my.conf should have, oh, 35 as a limit on the number of open
connections.
Eventually, each HTTP process will have one (1) connection of its own, and
you'll have a few spares for shell access.
*IF* you have multiple username/password combinations, such as on a shared
ISP, and you are using _pconnect, then you need to take this into careful
consideration: *EACH* username/password tied to *EACH* HTTPD using a
_pconnect will "use up" one of your MySQL connections.
While low-traffic virtual sites may not ever even approach the limit, you'd
want every high-traffic virtual site to have a full complement (number of
HTTPD children) of MySQL connections, plus a few spare. You *NEVER* want to
have those high-traffic site "use up" all the connections.
IE, if there are 7 high-traffic sites, and 200 HTTPD children, you'd want at
*least*:
7 * 200 == 1400 MySQL connections as your limit, plus, a few spare... At
least, in theory.
You'd then need to factor in "moderate" traffic sites, and bump this even
higher.
Each persistent connection chews up RAM, though, and while RAM is cheap,
it's not an unlimited resource.
--
Like Music? http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
