Datasource Timeouts and Support for CFQUERY Timeout Attribute

When considering Timeout settings and sources of problematic bottlenecks in ColdFusion applications, a widely held misconception is that all active database connections do not obey timeouts. Discussions surrounding slowness or unresponsiveness in ColdFusion applications often boil down to isolating unusual database activity as the culprit. The explanation provided is often that while the general Timeout in the ColdFusion Administrator, as well as the page level CFSETTING timeout attribute, will be enforced for all page requests except those actively waiting on a response from the database server after a SQL statement has been sent. While this is true, its sometines overlooked that the CFQUERY tag itself has a timeout attribute that is worth considering in some cases. In fact, this particular timeout is something that I've often forgotten, too.

One reason for the confusion over the CFQUERY tag timeout is that not all drivers support it. Of the databases drivers that ship with ColdFusion MX, only the Oracle and SQL Server drivers will enforce the CFQUERY attribute for timeout if it is supplied.

From the ColdFusion documentation for CFQUERY, the timeout attribute is described as:

CFQUERY timeout attribute

Maximum number of seconds that each action of a query is permitted to execute before returning an error. The cumulative time may exceed this value.

For JDBC statements, ColdFusion sets this attribute. For other drivers, check driver documentation.

If you've taken thread dumps to examine what the ColdFusion server was doing at the time of the unresponsiveness or hang, you might find some stacks in the thread dumps that appear to be in a JDBC driver in a socketRead state. These stacks correspond to web requests where the JDBC driver is waiting to receive information back from the database. Its quite normal for web requests to enter this state, so a snapshot taken by a single thread dump is almost meaningless, but if the same thread id appears in the same socketRead state over multiple consecutive thread dumps then you have reason to suspect a slow query or database problem.

In these cases where bottlenecks are present and can be attributed to database queries, then you may want to consider using the timeout attribute of CFQUERY to reduce the risk of completely backing up or hanging the server due to an accumulation of such page requests. The next step would be to consider alternatives to improve query performance such as building faster queries, using views, using cached queries via cachedwithin or a shared scope, or CFQUERYPARAM to cache statements.

So far this blog entry has referred primarily to the CFQUERY tag timeout, which will timeout prolonged, active queries after a database connection has been made. For control over connection timeouts see the CF Admin datasource detail page for the Login Timeout:

Datasource Login Timeout

The number of seconds before ColdFusion MX times out the data source connection login attempt.

The other timeout setting in the CF Admin datasource detail page is just labeled as Timeout, with a paired setting for Interval. This timeout value is how long a connection should be idle before it is removed. So if a connection goes unused for this period of time, then the next time ColdFusion checks that connection it will be removed from the pool. The frequency that ColdFusion checks the connection idle time is set by the Interval setting. The default for the Timeout setting is 20 minutes and the Interval is 7 minutes, so every 7 minutes ColdFusion will check connections for that datasource and remove connections that have been idle for 20 minutes or more. The number of connections idle connections removed from the pool at each interval is hardcoded in ColdFusion to just five. Say you have 20 idle connections hanging around in a pool, this would require at least 28 minutes to be cleaned up.

Timeout (min)

The number of minutes that ColdFusion MX maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Getting back to the CFQUERY Timeout attribute, here are some examples of some stacks that you might see in a ColdFusion thread dump where the stack shows a web request waiting on a response from the database:

Oracle (supports timeout)

SQL Server (supports timeout)

Sybase (does not support timeout)

SequeLink ODBC (does not support timeout)

Comments