Optimizing Database Connections (Part 3)
The caching seems to have helped out some, with the server load dropping considerably since the PHP scripts no longer have to access MySQL all the time. Rather, they are now accessing cache files of the results of database queries that are run frequently and don't change much. An example of this, as mentioned in my last post, is the left side navigation of our site, which does to change hardly ever, and therefor is cached daily.
However, the problems are not completely solved. From here, it looks like there are still too many connections to the database for the site to load in as quickly as we would like it to when there is a lot of traffic. It appears that this is caused by my programming, and should be a relatively easy fix. It comes down to the number of times that the database connection object in PHP is being instantiated.
The way I had the site programmed, there was an instance of the database connection object created for the page that loads, let's say the home page (which represents one connection to the database). Then when the ads are called, the functions that were powering them were again creating an instance of the database connection object (another connection), and to make it worse it was happening once for each ad. This means that for the home page there are now 5 connections being created, when only one is needed. If there were 100 people trying to access the site at once, which isn't too uncommon, there will be 500 connections created. Since the server has a limit on connections (which I believe I have set at 250), it can only run 50 requests before the server makes the others wait until the connections have been closed before creating new ones. Even though the connections are closed when the script ends, this still causes a slow down, and makes the server work much harder than it has to.
For me, the solution is to go through and change the programming so that all the functions in the script use a global variable for the database connection object. This will reduce the number of connections to one no matter what page is running and how many ads are on it. Having done this on the home page and article pages, it seems to work pretty well, as they are markedly faster now. In addition, it has been pointed out that there is a better database abstraction layer that I can be using, and I will be possibly looking into changing over to that. However, since the most taxing server processes are still MySQL, there are more optimizations that can be done there as well, obviously.
This was identified by my friends Holger Selover-Stephan and Steve Misenhimer at Glassword.com, two of the most talented and efficient programmers that I have met. They have been rooting out the performance issues that we have been dealing with in a manner that is purely awe inspiring. Having had two other consulting companies take a look, it was time to get these guys involved. The preliminary report that I have received, along with some sample changes that have been made to our home page and article pages, have convinced me that we are going to solve this problem. Many thanks for your help, Steve and Holger!
My next post will highlight some of the database optimizations that have been recommended, and why they are important. Hopefully, by the time I am done posting about our optimization woes our site will be as fast as it can be.
This post is filed under Developers' Corner and has the following keyword tags: database, mysql, performance.
