Ever increasing number of database connections with JDBC and ODBC drivers - MSSQL

[tribule]

Hi,

We have a legacy CF application that has a number of Java classes as part of its API (we do not have easy access to these). A long-running issue has been that database connections keep rising and are never released. We have tried the JDBC and ODBC drivers for MSSQL with the same overall result. Changing lots of settings in the DSN settings has also yielded no different result. We have a lot of "IF @@TRANCOUNT > 0 COMMIT TRAN" commands when we look in the MSSQL sys.dm_exec_connections table. We wondered if autoCommit was an issue, but have not found a way to effectively use it in the DSN/driver settings. We use the latest MS JSBC driver, version 8.4.1.

We wondered if the Java classes were not properly closing the connections - this seems to be the concensus by many developers, however not even CF can close these "stale" connections and eventually the server crashes (web requests pile up and JDBC connections go to zero).

Any advice appreciated.

[Charlie Arehart]

Just a couple clarifications on BKBK's helpful suggestions. First, it would be fr's requests>activity page (rather than  history) that would show "which requests are running". As for the cpu graph, well, it doesn't show use above perhaps 15% total so I'd not think that to be significant. 

But yep, the fact that the jdbc graph shows no running queries indicates on the one hand that there are no currently running queries.

But on the other hand, the running requests may well have been TRYING to run a query, and I'd not be surprised if stack tracing of the requests running showed them attempting a jdbc connection pool checkin or checkout, meaning that cf was TRYING to talk to the db but was getting no response. 

And that would fit tribules contention that the db "will not serve" the cf requests. And he's said he's seen a limit of 400 connections (which I assume he's tracking in the db itself) .

There could be still more to check there in the db, as well as more to confirm on the stack trace I just mentioned. It's just hard to do all this via back and forth here, and all the more in a holiday week. 

Mark (trubule) and I have been trying to arrange for me to get direct access. Mark, I know you said (privately) that it could be next week before you can arrange the firewall hole for me to rdp in. Given the ongoing enthusiasm even today, Christmas day, to continue among us all here, I will remind you that I'd proposed originally that we could do a screenshare instead, where YOU would remote in and I'd "look over your shoulder" and guide you through diagnosis. And I'd said (privately) that I could work this evening or this weekend, if you may be interested (perhaps you'd rather not or can't). Finally, again I'd offered that at no charge, to try to bring this interesting challenge to a quicker resolution. Consider it a Christmas gift. 🙂

If you want to arrange that, drop me an email. Until then, I realize conversation here may carry on. 🙂 

[BKBK]

The WebRequest Time graph shows that a number of requests finished 30 seconds or more after the database connection limit was reached (the dark blue bars). That gives an indication of how long such requests take. As we know, there was no database activity in those 30+ sconds.

This tells me that a constantly increasing number of requests are coming to the application. A significant number of them last 30 seconds or more. Which means that, before they're done, many more requests come in. The requests also seem to be creating and holding database connections that don't go stale.

This leads me to ask the following questions:

1. What is the nature of the queries involved? Do any use locks?
2. Have you ruled out database deadlock?
3. When the problem occurs, what errors appear in the ColdFusion logs?

[tribule]

The logged errors were "query timeout" mostly. Also there were some strange "null null" errors at the same time. Sorry forgot to mention that. There were no deadlock errors but from time to time we do see some deadlock errors, but they are rare.

[BKBK]

Right, "query timeout" confirms what we know. Could you please share the full text of the "null null" error (if necessary use *** to obfuscate sensitive information) 

[tribule]

Unfortunately I don't have the full text of the error avaible. However, we do reset our DSN on a timer to clear sessions, and it could be related to that. Will look out for more. Btw, "null null" errors are all over the ColdFusion forums like a rash. We are not alone it seems.

[BKBK]

Ah, funny you should mention "null null" errors reported in the forum - have you updated CF2018 to the latest version, namely, Update 10? If you haven't, then do. What you're experiencing might be a bug that has been resolved. 

[tribule]

We are on version 2018.0.10.320417. All updates applied.

[BKBK]

OK

[BKBK]

Idea: in the ColdFusion Administrator, navigate to Server Settings > Caching. What is the value of the setting Maximum number of cached queries?

Experiment by increasing the value exponentially, say, by a factor of 10. Does that help?

The reasoning is, the more queries that are cached, the less the need for database connections.

[BKBK]

An extension of my last suggestion:

1) On the database server, change the setting for connection-limit back to the default. That is, set the value for "Maximum number of concurrent connections" to 0. Effectively, allowing an unlimited number of connections.

2) On the caching page in the ColdFusion Administrator, increase the value of "Maximum number of cached queries" to 10 times its current value. In any case set it to  at least 3000. (How I arrived 3000: the Fusionreactor picture shows a queue of 50 requests/second. I then made an estimate of 1 query per request for a duration of 1 minute).

3) In the datasource settings in the ColdFusion Administrator:

    Connection String:          Max Pool Size=3000
    Max Pooled Statements: 3000
    Maintain Connections:    (check the checkbox)
    Limit Connections:         (uncheck the checkbox)
    Restrict connections to:  (leave empty)

[tribule]

Cached template limit is set to 40000 btw.

[tribule]

Maximum number of cached queries also 40000. This forum is losing posts 😞

[BKBK]

test

[tribule]

Hi, 

Sorry, this forum would not accept my posts for ages for some reason.

The same issue is still present. We used three different drivers, including jTDS, and the connections keep building up. We also tried a JNDI datasource to use a different connection pool and the server still eventually became unresponsive. Our legacy app was around when ODBC drivers were being used. We moved to JDBC when ColdFusion underwent changes to TomCat (when we updated versions). Perhaps the issue is that our code is not designed for use with a connection pool, or has an incompatibility? I can send you code if you want to see some of the classes? The code is all in Java classes. I was wondering if we could drop a new class in to the system and write or amend the code for the database opening/closing?

Regards,

Mark

[tribule]

Where is the private message function?

[tribule]

There is no envelope icon 😞

[BKBK]

Odd, @tribule . I attempted to send you a private message, but it couldn't find you in the directory. In any case, you can reach me at a.bakig=at=chello.nl

[tribule]

Hi, sent you an email. Did you get?

[BKBK]

Idea: in the ColdFusion Administrator, navigate to Server Settings > Caching. What is the value of the setting Maximum number of cached queries?

Experiment by increasing the value exponentially, say, by a factor of 10. Does that help?

The reasoning is, the more queries that are cached, the less the need for database connections.

[BKBK]

tribule: Cached template limit is set to 40000 btw

That's fine. The setting we're more interested in though is "Maximum number of cached queries".

I said so in my posts to this thread some 3 hours ago. However, since then, my replies to this thread no longer appear.

[BKBK]

Hi tribule

Have your most recent replies to this thread been frozen out? Mine have. 

I receive the e-mail conformation though.

[BKBK]

Apparently, it's just this particular thread that is dropping posts. My recent posts in other threads appeared without any problems.

Nevertheless, I can read your reaction in the e-mail conformation.

[BKBK]

A worthwhile test:

1) On the database server, change the setting for connection-limit back to the default. That is, set the value for "Maximum number of concurrent connections" to 0. Effectively, allowing an unlimited number of connections.

2) On the caching page in the ColdFusion Administrator, increase the value of "Maximum number of cached queries" by a factor of 10, from 40000 to 400000.

3) In the datasource settings in the ColdFusion Administrator:

Connection String:                           Max Pool Size=10000
Max Pooled Statements:                  10000
Maintain Connections:                    (check the checkbox)
Limit Connections:                          (uncheck the checkbox)
Restrict connections to:                   (leave empty)

[BKBK]

Test post

[BKBK]

@tribule @Dave Watts @Charlie Arehart 

Sandip (@ Adobe ColdFuson Team) has informed me we may resume posting on this thread.

The problem - which prevented posts from being published - has presumably been fixed. @tribule, did you make any progress on this? Any additional findings?