Logo Questions Linux Laravel Mysql Ubuntu Git Menu

Open and close connection to db or keep it




I have seen a lot of answers regarding .NET and C# applications, but I didn't find what I was looking for about Web ones (or else I didn't search well and I apologize for it).

My question is about connections to database during queries : Should I open and close a connection each query I am doing or should I create one and keep it all long ?
I guess it's the first one, but I want some informations about the differences between both regarding Performance and Security.

Moreover, is this enough to close a connection :

$dbh = new PDO('mysql:host=localhost;dbname=foo', '', '')
// queries
$dbh = null;
like image 226
tektiv Avatar asked Mar 15 '23 08:03


1 Answers

No. You should not close the connection if you want performance. You will have 0 effect on security by closing connection manually. Closing the connection will make things slower.

I'm going to write the rest of the answer based on your criteria of performance and I will assume you are serving php via php-fpm (if not, you should).

Closing and opening a connection carries overhead of handshaking with MySQL and opening file descriptors. If you have a lot of requests going on, this process will impact performance for no gain. Using a persistent connection however, will boost the performance.

Here's why: once php script ends, php will keep the connection open. The next request coming in will use already established connection - you avoid the handshake, tcp overhead and what not.

Excerpt from php manual:

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

Source -> scroll down to example 4 to see how to implement this.

like image 186
N.B. Avatar answered Mar 18 '23 03:03