mysql_connect v/s mysql_pconnect

  • Avinash
  • 0
  • Jun 10, 2013
  • Database

When you tried to connect to the MySQL database using PHP, you will find two types of functions for connection.

Both are as below:

  1. mysql_connect()
  2. mysql_pconnect()

Mysql_connect v/s Mysql_pconnect

mysql_connect

This function will create a new connection to the database once your script start executing and closes the connection to the database once script execution ends. This will make a connection to database every time our script start the execution.

mysql_pconnect

When you tries to connect your database using this function, then it will search for existing connection made to the database using same username and password, if the connection found then it will return the resource ID, otherwise it will make the connection and return the resource ID.

After one time of connection it will just return the resource ID (if found) and it will not try to make the connection every time the script calls, it will also not close the connection once the script ends. This is called persistent connection.

But mysql_pconnect does require some tuning of the servers and you may need to limit the numbers of connections / children and configure timeouts and how to deal with idle children.

When to use which function?

You should use mysql_pconnect() if your site have high traffic.

If PHP and MySQL are on the same server or local network, the connection time may be negligible, in which case there is no advantage to persistent connections.

Things to keep in mind while using mysql_pconnect

When you lock a table, normally it is unlocked when the connection closes, but since persistent connections do not close, any tables you accidentally leave locked will remain locked, and the only way to unlock them is to wait for the connection to timeout or kill the process. The same locking problem occurs with transactions.

Normally temporary tables are dropped when the connection closes, but since persistent connections do not close, temporary tables aren’t so temporary. If you do not explicitly drop temporary tables when you are done, that table will already exist for a new client reusing the same connection. The same problem occurs with setting session variables.

Apache does not work well with persistent connections. When it receives a request from a new client, instead of using one of the available children which already has a persistent connection open, it tends to spawn a new child, which must then open a new database connection. This causes excess processes which are just sleeping, wasting resources, and causing errors when you reach your maximum connections, plus it defeats any benefit of persistent connections.

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by: