>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