English 
Español
한국어
print pageTroubleshooting Database Performance Issues with ShadoCMS
A client recently created a new server and network enviornment and moved their install of ShadoCMS to the new enviornment.
The new enviornment consisted of three, load-balanced front-end servers. Each of the front-end server was could easily handle 500 simultaneous users.
After looking at the log files, it was discovered that lot of pages were taking a over a minute to be delivered and a many pages were timing out.
JRun was running normally -- using the right amount of memory and CPU. And the database (Oracle) was not under any considerable load. This pointed to some sort of a network issue between the front-end servers and the database server.
I wrote a diangostic script to figure out what was going wrong. The diagnostic script consisted of making repeated database calls (outside Shado API) and calculating the average time taken for the requests.
The script uncovered that a simple query on a table with 50 rows was taking anywhere between 150 to 300 milliseconds to return results. That looked very abnormal. Such a request should take anywhere between 20-60 milliseconds.
It turned out that database server was on a different subnet than the front-end serves, leading to considerable network latency. After the front-end and database servers were moved on the same subnet, the average access times started looking much more normal -- between 40-100 milliseconds.
Because the JRun threads were queueing due to network latency, there were repeated page time outs.
Here is the script so that you can test your enviornment. Make sure that you change the value of the "dsn" variable to point to the datasource name of your site database.
<cfparam name="form.time" default="1000">
<cfparam name="form.repeat" default="20">
<!--- Change the dsn variable to point to the CF datasource for which you want to run tests --->
<cfparam name="form.dsn" default="shadozoomdemo">
<cfoutput>
<html>
<head>
<title> Test to check database connectivity for #form.dsn#</title>
</head>
<body>
<h2>Test to check database connectivity to #form.dsn#</h2>
<cfform name="frmTestDB" action="#cgi.script_name#" method="post">
<table>
<tr>
<td>Datasource Name (DSN): </td>
<td><cfinput type="text" name="dsn"
maxlength="50" size="25"
required="true"
message="Enter a valid datasource name"
value="#form.dsn#"></td>
</tr>
<tr>
<td>Number of successive database requests to make: </td>
<td>
<select name="repeat">
<cfloop from="20" to="200" step="20" index="r">
<option value="#r#" <cfif form.repeat eq r>selected</cfif>>#r#</option>
</cfloop>
</select>
</td>
</tr>
<tr>
<td>Amount of time (in seconds) between each database request: </td>
<td>
<select name="time">
<cfloop from="250" to="10000" step="250" index="t">
<option value="#t#" <cfif form.time eq t>selected</cfif>>#decimalFormat(t/1000)#</option>
</cfloop>
</select>
</td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="btnSubmit" value="Run Test"></td>
</tr>
</table>
</cfform>
<cfif structKeyExists(form,"btnSubmit")>
<cfset runTest(time=form.time,repeat=form.repeat,dsn=form.dsn)>
</cfif>
</body>
</html>
</cfoutput>
<cffunction name="runTest" output="true">
<cfargument name="time" required="true"
hint="Number of milliseconds between each database requests">
<cfargument name="repeat" required="true"
hint="Number of database requests to make">
<cfargument name="dsn" required="true"
hint="Name of the datasource">
<cfargument name="query" required="false"
default="select * from app_globals"
hint="A syntactically correct SQL for the database call">
<cfargument name="flushInterval" required="false"
default="5"
hint="Characters after which to flush">
<cfset var oThread = CreateObject("java", "java.lang.Thread")>
<cfset var aTime = arrayNew(1)>
<cfset var i = "">
<cfset var qTemp = queryNew("")>
<cfflush interval="#arguments.flushInterval#">
<p>Database query: #arguments.query#</p>
<cfloop from="1" to="#arguments.repeat#" index="i">
<cfquery name="qTemp" datasource="#arguments.dsn#">
#arguments.query#
</cfquery>
Execution time for loop #i#: #cfquery.ExecutionTime#<br/>
<cfset aTime[i] = cfquery.ExecutionTime>
<cfset oThread.sleep(arguments.time)>
</cfloop>
<p><strong>Average time for #i-1# database requests:
#arrayAvg(aTime)# milliseconds</strong></p>
</cffunction>
Comments
There are no comments for this page as yet.
Full RSS
