Persistent MySQL connection

It was not that long ago I figured out what this ment, even thou persistent is kind of self-explanatory. I read that this was a must on a sql connection, but I never learnt it at school or did find out how to do it. For me it was a fancy word for a more complex connection type, but is it really more complex?

What I usually do, is that I use the singleton pattern on my database connection so that on each load the connection is created only once and then reused for every new use. A database connection is expensive, so it is a good thing not to create it a lot, and this is what singleton does. A persistent connection is like a singleton to the server – and does the thing that I hoped singleton would do when I first heard about it.

A persistent connection does not close, but it reused by the different connections to the server. This means that the same connection is reused by every user that connects to the server, and there is not created a new one for each time. Sounds simple? It is simple.

The real use of it is probably only when you have a bigger site with many users and traffic is big, so it is not really necessary for small websites that barely use a connections and have under 50 visitors a day. The singleton helps a lot on each user page load, when the connection is preserved and reuses for the page load, but still, using a persistent connection could slightly improve speed even thou it is probably not noticeably. The real problem solved with persistence is that you can have many more simultaneous connections to the database – getting rid of the connection overhead problem.

How to do it?
I use the mysqli extension for PHP, and the only thing needed is to add a ‘p:’ to the host address, like this:

$db = new mysqli('p:localhost', 'username', 'password', 'databasename');

This only works with PHP 5.3.0 or higher, so be sure you have it both locally and on the server. I you don’t have it or don’t use mysqli, then there is an alternative, to switch form mysql_connect to mysql_pconnect.

That simple, really – and no other change to code.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: