Re: MySQL close connection, what's the purpose?

On Mon, April 3, 2006 1:33 pm, tedd wrote: >>Just out of interest, could you re-run the test using persistent >> connections? >> >>change mysql_connect to mysql_pconnect.. > > In doing so, the overall results dropped from a tenth of a second > difference between both methods to three-one-hundredths of a second > difference. In other words, using mysql_pconnect is three times > faster for multiple open/closes. > > However, this only improved the connect/get-data/close sequence. The > open once and keep open showed no improvement. > > Thanks -- does the "persistent connection" thing hold the server up > until released? How does that work? What "persistent connection" REALLY means is: Ask MySQL to keep the connection laying around handy for re-use. So it's not really "persistent" from PHP's point-of-view -- It's more like "re-usable" from PHP's point-of-view. The actual persistence happens on the MySQL side of things, mostly. Note, however, that for security reasons, the re-use can only be done if the SAME MySQL user is asking for a connection. You wouldn't want to risk the data left over from a 'power' user to leak out to a 'normal' user, if you have multiple MySQL users with multiple levels of access, and the connection to be re-used was previously a 'power' user one, but now a 'normal' user is asking to use it. There are more constraints on this re-use which I forget... What they boil down to, though, is that EACH Apache child process will end up having a connection allocated to itself, for each username that you use in your application. In /etc/httpd.conf (or wherever your httpd.conf file is) you have something like MaxServers to define the maximum number of Apache children that can exist. So you need to edit /etc/my.cnf (or wherever yours is installed, or your MySQL expects it to be installed, based on compile-time settings) and make sure you have that number of connections PLUS A FEW EXTRA ONES. The few extra ones are needed in case of a server melt-down, so that even if every Apache child is running, sucking down a MySQL connection, you can STILL connect from the shell as 'root' and do something useful, like shutdown MySQL safely. If your MySQL # of connections are ALL taken up by Apache children thrashing away, then you CANNOT connect using mysql_admin to shut down MySQL and you're in big trouble. -- Like Music? http://l-i-e.com/artists.htm