Connecting to the DB once per session

Can't figure out how to stop connecting, disconnecting.

[raist007]

Ok, we came across a huge problem today when trying to troubleshoot why the upgrade from Ubuntu 6.04 to 6.10 (Dapper to Edgy) caused our website to all of a sudden take 18+ seconds to load instead of 1 second (apparently the upgrade to Edgy introduced MySQL ability to have GRANT permission for name-based domains instead of just IPs and was causing the DB to call the nameserver to do DNS lookups).

On our site, which uses PHP, MySQL is called all the time for database transactions on pageloads, AJAX calls, and other regular server-side stuff. So, if you go to the homepage or another page, each time a page is loaded, no matter how many times you have requested that page, it (PHP) always opens a new MySQL connection and then [I assume] severs it once it has received the appropriate data from the database.

This seems very inefficient, and logically (unless I'm a dolt!) a more efficient way of doing it would be to see if the user's PHP session is the same as it was when they loaded the last-visited [or first] page and if they have already once connected to MySQL (using the PHP function mysql_connect), keep the DB connection alive until the user's session ends so that a "reconnect" [connect] isn't necessary each time, but that simply queries can be performed on an already-open (persistent?) connection.

Is this true, that it must be the way I am doing it (DB connect each page load) or can it be done a more efficient way (such as keeping the connection open until session destroy), and if there is a "more efficient way" would the efficiency be negated by using more memory and processor time while the connection stays open for users that are simply going to look at a page for say 30 seconds or more?

[edited by: jatar_k at 11:32 pm (utc) on Dec. 7, 2006]
[edit reason] no urls thanks [/edit]

[FalseDawn]

It's not actually inefficient to open and close mysql connections for every script - I believe each apache child process holds its own mysql connection anyway, so the overhead is pretty miniscule.

Trying to link connections with sessions sounds like a recipe for disaster, and is really not needed.

[phranque]

i think you might want to look at mod_php here...

[justageek]

Have you tried mysql_pconnect()? That will keep a connection open.

JAG

[FalseDawn]

I would not recommend using persistent connections unless you fully understand how they work and in what situations they may be useful.

[php.net...]

[edited by: FalseDawn at 5:56 pm (utc) on Dec. 7, 2006]

[coopster]

Straight from the source ...

Web Applications

In web-based applications an SQL transaction cannot span multiple web requests, i.e., you can't start a transaction, issue some queries, then lead the customer through a web form using the retrieved information, and then process the submitted form and complete the transaction. This does not work because web requests (HTTP) are "state-less".

There simply is no continuous connection from the customer's web browser to a server. The next request may in fact arrive at a different web server, and/or at a different database server. A persistent connection in PHP does not help this aspect, as there is no way to guarantee that this user's next request will a) arrive at the same PHP instance and then b) use that connection. The customer could also open an extra browser window and issue other (or duplicate) requests.

[dev.mysql.com...]