Programming Forums - MySQL connection bottleneck?

Programming Forums (http://www.programmingforums.org/forumindex.php)

- Other Web Development Languages (http://www.programmingforums.org/forum40.html)

- - MySQL connection bottleneck? (http://www.programmingforums.org/showthread.php?t=10550)

MySQL connection bottleneck?

I'm writing a site in PHP+MySQL, and I've been using mysql_pconnect for everything... logically this would result in the entire system using a single persistent connection to the database (I'm trying to avoid overhead from opening and closing connections all the time). The site is intended to have multiple (and hopefully lots of) users doing stuff simultaneously, so I got to thinking about it and I was wondering if having the single connection would end up as a bottleneck if the server had any sort of load on it. Might it become necessary to implement a server pool if the traffic becomes high enough?

I've never done a site expecting any sort of load before, so I'm learning these things as I go, and I figured it's easiest to get it right the first time... :o

Have you seen this, Jim?

According to the PHP documentation, a persistent DB connection is not shared between child processes. If you are using Apache, more child processes will spawn as more connections are simultaneously served, according to the settings for the MPM (Multi-Processing Module). I believe that that module is in common use, so chances are, lots of requests will end up being split among a relatively small pool of DB connections. I don't know how much of a bottleneck a persistent connection can be, but I doubt it will be a problem. I would anticipate the load on the DB server to be the issue if you reach that point.

If you are using persistant connections and expect any number of users at the same time, make sure you have your allowed mysql connections set a lot higher than normal. The majority of the time I've seen scripts use them, they rapidly fill up the allowed user connections and often the total connections allowed, which then breaks the site for anyone else. Also be sure to close them upon exiting. Leftover connections will also trash the number of connection slots you have, even though they are no longer in use. Really I cannot see very many uses of persisten connections, probably mostly for a smaller amount of users performing a heavy amount of mysql calls in a single visit (which only helps if you have php run as a module and not as cgi).

I had seen that DaWei, but thanks for pointing it out anyways. I guess I kinda scanned over the part about the child processes a little fast, but reading though it again helped clear things up a bit.

I'm still slightly confused about how it works with the child processes. According to Dameon, the child processes can't share a persistent connection between them; however the link DaWei posted says:

Quote:

When a request comes in from a client, it is handed off to one of the children that is not already serving another client. This means that when the same client makes a second request to the server, it may be served by a different child process than the first time.

So does this mean that each child process will have it's own connection to the database, and then in the eventuality that UserRequest(N+1) is on a different child process than UserRequest(N) the session will actually end up using different DB connections, just without the overhead of closing and opening in between?

And Mocker brought up closing the connections. Is that necessary if the different child processes will be using the same database? If so, how do you close a persistent connection? Or does it automagically close if the child process opens a persistent (or regular) connection to the database with different parameters?

Quote:

Originally Posted by php.net

First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()).

Quote:

Originally Posted by Apache Docs

A single control process (the parent) is responsible for launching child processes. Each child process creates a fixed number of server threads as specified in the ThreadsPerChild directive, as well as a listener thread which listens for connections and passes them to a server thread for processing when they arrive.

Each child process is going to serve a number of requests in its lifetime. The first time a script executed under that process calls pconnect, that child has a DB connection. You can't assume (since HTTP is fairly stateless) that subsequent requests from the same client will be handled by the same thread or process, but it doesn't matter;if another client requests a script that uses pconnect on the same database with the same credentials, then the connection is already open, saving a bit of time but giving the same result.

As load increases, so do the benefits of pconnect. If you have 10 child processes with 10 server threads each and all threads are serving requests (high load indeed), then you end up with 10 long-lived database connections instead of 100 very short ones. Short being in the range of a fraction of a second, long being the child process's lifetime.

Ok, I think I've got that part down now. Thanks for the explanation.

What about closing them? Should I be worried about that?

pconnect acts as a drop-in replacement for a normal connect. Normal connections terminate when the script does (or when explicitly told to do so), but not persistent ones. Attempts to close them with the <database>_close function result in no action. Instead, cleanup is up to the child process, perhaps when it terminates, perhaps after a timeout.