Optimizing Database Connections (Part 2)
Continuing from last weeks post, it seems that our biggest performance issues have been tackled. The site has not been slow to load in over a week, at least not on my connection. There are some times that the script takes a few seconds to run before the HTML stream is sent back to the browser, but all-in-all I haven't had to wait more than 6 seconds or so for the pages to load in. The answer to the problem, as I alluded to last week, was that there were simply too many calls being made to the database for each page of the website.
Caching database queries was the way that I approached fixing the problem, which seems to have worked. To illustrate, let's look at the home page at Practical eCommerce. The menu on the left side is dynamically generated, since we have the ability to edit categories and such. However, since that is hardly ever changed, there is no real compelling reason for the database (in our case MySQL) to be involved for each page request. That was a perfect candidate for caching, as the left side navigation menu changes so infrequently that the database need only be queried once a day, just to make sure nothing is different.
Going through the home page, I found probably close to 75 different places where the database was being queried. To optimize the PHP scripts to ensure that they are only involving MySQL when they need to meant going through each page of the site, looking at each instance that the script accesses MySQL to determine if that query could be cached and for how long. Let's just say that was not a terribly fun job, but it did give me the chance to abstract out all the database code into what is called a "database abstraction layer" in PHP jarble. In english that means that I removed all database interaction code to it's own class, which then also handles the caching as well. Never hurts to clean up a bit, right?
So that the database connection class will now do is check each database connection request to see if there is a cache file that has been generated. In my case, the cache files store the raw output from the database onto the server, identified by query so that multiple pages can take advantage of the same cache files. If a cache file is found, it is accessed and the information is returned to the PHP script. If a cache file is not found, or has expired, the database is queried and a new cache file is written. The advantage here is that although accessing the disk to get the cache file represents and I/O process to the server, it is a much faster process to execute than the I/O process for the script to query the database. Waiting for the database to execute a query and return the results is the bottleneck that was killing us when a couple hundred people are trying to access the site at the same time.
So a couple days later, as I was finishing up the tweaks, the site began to really work a lot better. There are, of course, some other programming optimizations that need to be done, but we are in good shape. In the end, this was a pain-in-the-butt since it wasn't MySQL causing the problem, it was me throwing too many requests at it. The trade-off for the caching solution is that the site does not update right away for certain changes. This is all calculated into the expiration settings for various cache files. In the case of the left-side navigation menu, if I were to go in and make changes in our content management system (let's say add a few article categories), they would not show up on the live site until the next time the cache is rebuilt, which would be 24 hours from the last time it was built.
This post is filed under Developers' Corner and has the following keyword tags: database, mysql, performance.
