Database Setup

ID #1084

Why are persistent DB connections bad?

Well ... they're not *bad*.  They're just less than ideal in many circumstances.  For long running programs such as Mongrel or FastCGI apps, or anything you're managing yourself on the shell server - a persistent DB connection is perfectly fine. 

The problems start to come in when you're using persistent DB connections from Apache & PHP.  In this situation, PHP's mysql_pconnect() function doesn't always tear down the DB connection when you're done serving a page - it keeps it open in the background.  Big surprise, since you did ask for a persistent connection.  So what can happen is that you end up with a lot of idle connections to the database server, and eventually hit the limit of connections per user.

The reason for the above problem is pretty simple.  Apache works by forking off child to handle a web request.  If a child handles your web request and connects to the DB, then a different child handles the next request, and a different child your next request, you've suddenly got 3 idle persistent connections.  Since we use a load balancer at GeekISP, you might have several idle connections from each backend webserver.  It starts to add up to a lot of idle connections.

The upside here is that it is a very easy fix - just change all calls of mysql_pconnect to mysql_connect, and your app won't have this problem.  MySQL connections are very inexpensive - especially over a gigabit LAN - so the overhead cost is still pretty small compared to actually running queries.

Tags: -

Related entries:

Last update: 2010-07-27 13:54
Author: Dave Steinberg
Revision: 1.0

Digg it! Print this record Send to a friend Show this as PDF file
Propose a translation for Propose a translation for
Please rate this entry:

Average rating: 0 out of 5 (0 Votes )

completely useless 1 2 3 4 5 most valuable

You cannot comment on this entry