5,742,000 Queries Per Hour = DataBound = Slowdowns
First of all, apologies for the silence here, a lot has happened in the past month, I relocated, temporarily and that took a good deal of my time, plus we are very busy at present. In this posting I want to introduce a mildly un technical but nevertheless critical issue for web applications reliant upon Relational Database Management Systems, RDBMS's, which is still most sites out there. One of the first things we look at, typically either via FusionReactor or SeeFusion, is the number of queries per request; hence my reference to this being mildly non-technical.
I will start at the end and say this, any application running with more than 10 queries per request is a potential red-flag for us. There are good reasons for this and here is an example why; we have a current client who's initial query to request count was 45-50 queries per request. We immediately homed in on this as a potential throttle point to the
site performance, for may reasons. Calls to RDBMS's almost always traverse a network and very often the database is in a different subnet, both those things are probable points
of bottlenecks, then there is the work needing to be done by the RDBMS and in almost all cases there will be something lacking in the installation of the RDBMS, for instance hard drive arrays with everything on one array, data logs TempDB etc, shared SAN's and in a worse case scenario the RDBMS resides in a Cloud.
Here is an actual example which caused a spike in busy threads and a slow-down in performance, as observed via Metrics Logging. In one 20 second period on one web-ColdFusion server 212 requests were handled so with a ratio of 50 queries per request that is 10,600 queries in 20 seconds or 31,800 per minute or 1,908,000 per hour from a single web-CF server and there are 3 web-CF servers in a cluster so if all is being load-balanced equally that is 5.7 million queries per hour hitting the RDBMS. That is a high number of transactions and sure enough every time the system is handling over 120 requests every 20 seconds, everything degrades.
So, we have definitive evidence that a high query to request ratio will degrade performance and it is something to watch out for right from the beginning. As a rule of thumb, do not go beyond 10 queries per request and if possible stay well below that and do not ever have your transaction logs and TempDB and of course the data itself all on the same drive array and never put those in a Cloud. Following these simple procedures will prevent your applications from becoming "data-bound" with inevitable performance problems.