Hi,

A bit of a "school boy" question I suspect but... when is it most efficient to open and close a MySQL database?

For the site http://www.bmtg.org.uk then I'm generally doing it a number of times per page - each page is a number of included scripts and I generally tend to open and close the same database within each include. It occurs to me that opening the database at the top of each page and then closing it at the bottom is possibly the most efficient way.

Any thoughts, suggestions?

Many thanks,
David.

It depends...

Ha, you didn't thought of that one, right !?
Seriously, in PHP, you have 2 connections mode: Permanent or non-permanent

The non permanent is the traditional mysql_connect(), while the other is mysql_pconnect()

When you do a request with PHP in a NP (non permanent) mode, the engine will reuse the connection resources during your page processing.
As far as I know, a idle resource will be used for the answers of other peoples requests too.
If the number of opened connection is too low, then a new connection is opened to the server, until the server says that the max_connections_allowed is reached.
In that mode, you don't need to close the db connection. PHP does that alone when the engine shut down itself.

In a permanent mode, on the other hand, the only difference is that the PHP engine don't close the connection on it's shutdown. He keeps it opened to server other instances.

You have to find the balance between the open/close time, the limitation of opened connection and the memory consumption of those connections.

Every connection use a bit of memory that is used as a buffer.
So, with numerous NP connections, you could run out of memory if the number of threads goes up.
On the other end, it means that you have a lot of traffic, because you need a lot of concurrent db connections.

So, in my experience, with low to medium traffic, a NP connection is ok. Just remove your mysql_close() calls.

But if your site traffic reaches pick, a switch to P connections can help you to handle more precisely the memory consumption of your web app.

Reading myself back, I wonder if this is not too cryptic...
I hope so.

Sounds good to me.

You say I could simply remove the closes - of course this would leave a number of opens of the database. Although this won't cause errors, won't it be better, performance wise, to remove these extra opens as well, and leave it to a single open and close?

David.

No, the PHP engine recycle the already opened connections that are idle.

Originally Posted by tripy

When you do a request with PHP in a NP (non permanent) mode, the engine will reuse the connection resources during your page processing.
As far as I know, a idle resource will be used for the answers of other peoples requests too.

If the connection string is identical, and there's a free connection in the pool, it should be reused. Bear in mind, tho, plucking a connection from the pool is still an expensive operation. It's far better to "manually reuse" this type of resource by not closing and opening it in the first place, when that's possible.