Optimizing Database Connections (Part 1)
The efforts to optimize our website to account for the traffic increases that we have seen have been tireless. I've noticed that it is much harder and time-consuming to figure out what might be causing the problem than it is to actually fix problems. Our problem (or the symptoms) have been that our website is slow to load occasionally, especially when we send out a newsletter or have some other marketing event occur that causes short term spikes in web traffic. It started a few weeks ago and have gotten steadily worse since we first noticed it, to the point that on one or two occasions the site seems to not load at all.
The first thing that we checked was our hosting account. We were concerned that somehow our server was not up to the task or our traffic, as we had been meaning up upgrade our server but had not done so. Seemed like a perfectly good thing to do once our problems started, so that was the first thing we did. If nothing else, it would eliminate that as a source of our problems, since our upgraded server should not have performance issues with our site given the traffic levels that we have. While things are nicer on the new server, it did not help our issues. On the plus side, we upgraded our server. On the minus side, we are back to square one with traffic increasing and the problem getting worse.
After talking with some other developers, we started to suspect that perhaps the database was causing the problem. More specifically, it had been theorized that our PHP scripts were using persistent database connections, which were staying open after the script had executed. This would mean that once the database connections have been maxed out, the server will have to wait until the persistent connections time out to get a new connection. Seemed reasonable, but since I built the site I was pretty confident that I had not used persistent connections. However, the issue fit the visible symptoms that we were seeing, so it seemed reasonable. Of course, after scouring the site and not finding a coding problem, and changing the PHP settings to absolutely dissallow persistent connections, it appeared that was not the problem either.
However, the database still seemed to be the issue, since it is the one part of the PHP scripts that run our site that would cause such a slow down. The rest is just not complex enough to tax our server's hardware, and our traffic (although increasing) is not at a level that would explain the sluggishness that we were seeing. While digging into more complex solutions, I decided that installing the Zend optimizer would be a good thing to do, for no other reason than because it would optimize PHP scripts as they are compiled, which provides at least some help.
Of course, that led to an almost week-long battle with the Zend optimizer not being compatible with our subscription application, which we have heavily modified to our needs. It seemed like everything was a headache, and nothing wanted to just cooperate. In hindsight, these are all issues that we knew would come up eventually, but that didn't make them any easier to deal with. Once the subscription system was in place, and the Zend optimizer was doing it's thing, we were STILL seeing the site get slower and slower. The server processor was being worked hard, and I couldn't figure it out.
It had to be the database. Our site requires a ton of information from the database and that is the only explanation. After analyzing our site for a long time, I figured that there were about 50 or more hits on the database for some of the pages on our site. Considering the amount of traffic we are getting, the performance bottleneck had to be at the point where the script is waiting for queries back from MySQL. I had to look at options for making this run smoother.
As you may be able to tell, our site is running a bit better now. I am in the middle of implementing a solution to our heavy database reliance, which will be outlined in the next post.
This post is filed under Developers' Corner and has the following keyword tags: database, php, mysql.
