Intermittent Database Connecitivy Problems in CF10.

Report · Oct 01, 2012

We have recently rolled out ColdFusion 10 Standard 64-bit to our production environment. We've installed the mandatory update as well as the two available updates. Our web server is IIS on Microsoft Server 2008 R2 64-bit.

The symptoms we are experiencing are:

Loading ColdFusion pages that use database queries (CFQUERY) start to take a long time to load.
In application.log we see several of the following errors
- "Error Executing Database Query.Timed out trying to establish connection"
- "The request has exceeded the allowable time limit"
If we wait for the request to be served (that is we don't hit stop in the web browser) we eventually get one of the following errors:
- "[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too many client tasks."
- "Error Executing Database Query. Timed out trying to establish connection"
On the most heavily trafficked pages we will see other errors that correspond to the time we experience this problem that look like:
- "Element RECORDCOUNT is undefined in <query name>"
If we load a web page that performs non-database-querying operations (file reads, parsing text, whatever) the pages load fine and quickly even during the period of poor performance on pages with database operations.

---EDIT BEGIN---

We cache queries heavily.

During one period of trouble a query's cache time had expired and was rerun. In subsequent attempts to run this CACHED query the query variable, after the CFQUERY, does NOT exist according to CF. e.g.

...

</cfquery>

<cfif IsDefined( "myQuery" ) is false> BAD QUERY </cfif>

This would output "bad query" even after the period of slowness had subsided. If I rerun the query without the cachedwithin attribute everything is fine and subsequent query operations, with or without cachedwithin set, are fine (because the cached copy has been revised).

This is why we're seeing those "Element <element name> is undefiend in <query name>" errors.

---EDIT END---

If we do nothing the server returns to its normal, responsive self in about 5 minutes.

The time between these five minute blocks of database issues can vary between 15 minutes and several hours.

Note that we use a lot of Access databases. I am aware that Access datasources should have the "maintain connections" left unchecked and they all do.

These symptoms, to me, seem to indicate there is a problem with ODBC resources, as if something clogs up the ODBC "pipe" and everything is stuck waiting for it to clear, but once it clears up everything is back to normal. I'm not sure of that's an ODBC Server/Agent issue or could it be an issue with how ColdFusion allocates resources (threads) to communicate with the ODBC Server/Agent.

Has anyone else experienced this and perhaps found a reason or even a resolution?

--BEGIN EDIT--

Our version of ColdFusion is 64-bit. I believe SequeLink is 32-bit. Could this possibly be related to the problem?

Can anyone offer some advice on how I could further troubleshoot the issue to narrow down the problem?

--END EDIT--

Any help is greatly appreciated.

Thanks

Report · Oct 01, 2012

Hi

Can you please confirm if you are using MS Access database? If yes then can you please do same testing with SQL or Oracle databases? Generally MS Access not recommended for large queries and large base of users accessing that.

Regards

Swaraj

Report · Oct 02, 2012

I can confirm I am using MS Access.

I understand the many reasons why not to use MS Access.

I can not do same testing with SQL or Oracle as that would require us to install such a platform and rewrite our applications to use that platform. Furthermore we are using ColdFusion STANDARD which means Oracle queries too would go through the ODBC Server and if the ODBC Server is not responding then Oracle queries would not work as well.

Report · Oct 02, 2012

I know it would be a real pain to reformat this for Oracle or SQL, but that is best for large amounts of data and/or many users. Access could be part of your problem.

Otherwise, check the datasource settings in CF Admin and make sure the number of connections isn't limited and that the other settings are more in line with your production environment.

^_^

Report · Oct 02, 2012

Does it seem right to suggest that we just drop Access when, under ColdFusion 8, this system was stable?

That this system was stable and that SequeLink hasn't been updated in forever tells me that it could be an issue with ColdFusion 10.

Or could it be an issue with allowing ColdFusion to run too many threads. In server.xml the connector, by default, only runs 10 threads. We had to up that to the Tomcat default of 200 to get better performance. That means there's at least 200 connections hitting SequeueLink when it was originally expecting just 10. Could that be the problem? Could it be that either A) SequeueLink just can't handle the load or B) SequeueLink needs to be configured to handle the higher load?

I'm digging through the SequeueLink manual that was hidden inside ColdFusion hoping to find an answer.

Report · Oct 02, 2012

I have found the SequeLink manual and I have modified MaxThreads for the ODBC Agent and ODBC Server services to match the max number of threads set on the ColdFusion connector (200). It had been previously set at 64.

cd <ColdFusion10 directory>\cfusion\db\slserver54\admin\

swcla.exe -l

swcla> sar "ColdFusion 10 ODBC Agent" ServiceMaxThreads 200

swcla> sar "ColdFusion 10 ODBC Server" ServiceMaxThreads 200

Then restart the ODBC services.

We typically see several periods of slow performance a day, clearly outlined in application.log so I should know within a day whether or not this works.

-- EDIT --

Nope! Didn't work.

Report · Dec 11, 2013

Did you ever find a solution this this problem? I think I am having the same issue with CF10 Ent on Win2008/IIS7 connecting to Oracle. Almost every day all the sites with a database connection (i.e., CFQUERY) timeout and what looks like loses connection for about 3-9 minutes. I have been tearing my hair out trying to find why.

It also sounds EXACTLY like this Stackoverflow thread where one user said he traced it back to an actual hardware issue.

I am beginning to think it is somethign like that because I have tried EVERYTHING...stress testing, app pool changes in IIS, Max pooled statements changes in CF, running every diagnostic and log you can think of (Jprofiler, Jconsole, Server Monitor, logging all database call, NETSTAT, etc) and have found nothing. In the Failed Request Tracking I see a suspicious error like this during the 500 event (probably when CFQUERY times out): The specified network name is no longer available. (0x80070040). I have seen similar messages in other Failed Request tracking logs such as: An operation was attempted on a nonexistent network connection. (0x800704cd).

What does all this mean? By the way we are on VMware if it matters. Everything was rock solid for years and years until we moved onto either a) VMware, or b) Win2008 / IIS 7 or a combination of the two.

Update: Finally resolved my issue here: http://forums.adobe.com/message/6060877#6060877