How often should I close database connections?

Question

I am currently opening a database connection in my application initialization. This is a fairly small application, PHP, if relevant.

Should I connect to the database, make calls, and then close and repeat this process for every database function that I write?

For example, I have the following function that grabs the $ db variable from my application initialization.

function get_all_sections() { global $db; $sql = 'select * from sections'; if (!$db->executeSQL($sql, $result)) { throw new Exception($db->getDatabaseError()); exit(); } $sections = array(); for ($i = 0; $i < $db->numberOfRows($result); $i++) { $sections[] = new Section($db->fetchArray($result, MYSQLI_ASSOC)); } return $sections; } 

Would it be better if I opened the connection and then closed it after I took the lines? This is like a lot of connections that open and close.

Answer 1

If you have a connection pool ( http://en.wikipedia.org/wiki/Connection_pool ), then its ok to grab a new connection when you need it. HOWEVER, I would say that I’m in the habit of considering any resource as "limited", and if you open the db handle, keep it as long as possible.

Answer 2

Connecting to the database takes a finite time. This is insignificant when connected via a domain socket or named pipe, but it can be much more if it is through a network connection or, even worse, an open Internet. Leave it connected for at least the duration of the request.

Answer 3

Use mysql_pconnect to pool pools and exit at the end of each operation. The thread will not be closed and the thread will be reused. Thus, it is safe and effective.

Answer 4

Since PHP MySQL connections are fairly lightweight, you probably open and close the connection when necessary. The same does not apply to other database connections, such as connecting to SQL Server, which has fairly heavy connections.

In all cases, however, do what matters most in terms of maintainability / logic / dependencies. Then, if you find that you are experiencing a noticeable slowdown, you can optimize those areas that need acceleration.

If in doubt, follow the golden rule: do not optimize prematurely.

Answer 5

The simplest solution would be to use mysql_pconnect () - see here

Thus, if the connection is already open, it will use it instead of creating a new one. If it will not be connected again