ColdFusion Site Timeouts? Check To See If Your Database Server.
Awhile back I had switched one of my clients websites from CrystalTech to HostMySite. The site on CrystalTech was inaccessible at least 2 times a day, due to either a ColdFusion/JRun restart or some other problem. Talking it over with the client, we decided it would be a good time to update their code and move it on over to HostMySite.
Almost immediately, we noticed some speed gains, but again we started to see that the site would go down once a or twice a day. I was starting to wonder if it was my code, but I had been running it internally on my server with no problems at all. My server is about 7 years old with only 1 gig of ram. Surely, if it could run on this junker, it should run fine on our ISP's shared server.
Looking closer at what pages were causing most of the crashes, I began to suspect some of my cfqueries. Granted, by no means am I a MySQL guru, but on a couple of pages I was performing some robust queries. I made a test page that had only one cfquery on it and began running a simple test. To do this you need to add a result tag to the query and do a cfdump.
<cfquery name="MyTest" datasource="#myDSN#" result="QueryResult">
SELECT *
FROM test
</cfquery>
<cfdump var="QueryResult">...and the resulting output from the cfdump:
Reloading the page, I could see the amount of time it took to perform the query by looking at the ExecuationTime value. For my problematic site, it was across the board. Taking anywhere from 90ms to 40,000ms or I would see that SeeFusion stopped the process all together for request timeout limitations. (SeeFusion is a server monitoring extension for ColdFusion that HostMySite implements. It's will throw an error for anything over 50,000ms). This definitely wasn't my code, it was their database server. I contacted them with this discovery and they put me on a new database server. I haven't had a problem since.
So, if you are having the same problems, do this little test to see if your results stay consistent. If not, then you know it's their server.
2 responses to "Shared Hosting Woes"
What I'm really hoping is with the introduction of Railo (an open-source ColdFusion alternative) we will start to see some more hosting options.
Good luck with your issues.
The problem gets frustrating for users because often tech support are just looking at the SeeFusion or FusionReactor alerts. It's possible to use the jdbc wrapper to get mroe detailed info on the database connection and query performance but alas for shared hosting it's just not feasible time/cost wise.
Anyway great post as I'm sure alot of CF developers have been in the same boat.
Cheers, Mike G.