Jay Pipes Shows Us: MySQL Connection Management in PHP – How (Not) To Do Things

      Comments Off on Jay Pipes Shows Us: MySQL Connection Management in PHP – How (Not) To Do Things

p. “Jay Pipes”:http://jpipes.com/ posted a great article on his blog entitled “MySQL Connection Management in PHP – How (Not) To Do Things”:http://jpipes.com/index.php?/archives/99-MySQL-Connection-Management-in-PHP-How-Not-To-Do-Things.html. As the title implies, he talks about PHP coding practices with relationship to MySQL connections. Instead of a contrived demo however, he uses the real-world code from WordPress.

p. Jay kick starts us by introducing us to the problem.

bq. Connecting to a MySQL resource, while inexpensive, requires PHP to issue a call to the mysql client API via mysql_connect(). Passed as variables to this function are the connection arguments for the database host, user, password, etc. Once the MySQL database server has received the connection information, it will either return a success value, or a failure. Upon failure, the mysql_error() function can be used to determine what went wrong during the connection attempt (typically user credential problems or the max connections issue).

bq. So, where, you ask, is the problem? Well, the issue that I commonly see is that connections are made to MySQL resources when they do not need to be made. But, you say, almost all web applications serve dynamic content, so therefore doesn’t dynamic content virtually require a connection to a database be made?

p. From that starting point, Jay launches into a very detailed and code laced tutorial on (as he puts it) how NOT to do things. Along the way, Jay introduces concepts like “Lazy Loading” and “Content Caching”. He defines these terms by giving examples from the WordPress code base. Not satisfied to point out the flaws he sees in WordPress’ database connection handling, Jay actually suggests new code that will alleviate the bottlenecks he is seeing. (even though he admits in the opening paragraphs that MySQL connections are relatively inexpensive he just doesn’t see the need to waste them.)

bq. The code in the wpdb class isn’t fundamentally wrong. It just needs some tweaking to ensure that a connection to the database is only made if a query is executed against the database. A technique called lazy loading essentially delays the connection to the database until the last minute, instead of upon creation of the database abstraction object.

p. After showing sample code to implement Lazy Loading, Jay then goes on to tackle Content Caching. In both cases he uses sample code from the “MySQL Forge”:http://forge.mysql.com/projects/ project.

p. This is a great tutorial for anyone dealing with building and maintaining large-scale systems built in PHP and MySQL. (I would say “scalable systems but I’m reading “Theo Schlossnagle’s book”:http://www.amazon.com/Scalable-Internet-Architectures-Theo-Schlossnagle/dp/067232699X/sr=8-1/qid=1157070272/ref=pd_bbs_1/102-7171827-4484961?ie=UTF8&s=books right now and I’m trying to re-learn the meaning of scalable) It is not an easy read. It is full of technical terms and code that may not be obvious at first glance. But it is a good article and I highly recommend it.

p. =C=