• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
1

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

Enthusiast ,
Jan 20, 2021 Jan 20, 2021

Copy link to clipboard

Copied

Hi,

 

This is a continuation of the thread: https://community.adobe.com/t5/coldfusion/ever-increasing-number-of-database-connections-with-jdbc-a...

 

For some reason I cannot post to that thread any longer, so am creating this one to carry on.

 

Views

902

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 20, 2021 Jan 20, 2021

Copy link to clipboard

Copied

@tribule, I am glad to see that you can now resume the thread. You and I have corresponded privately on it. You shared with me the Java code that is causing the problem.

 

The code enables the application to create connections to the database. My hypothesis is that:

 

1) the code may be creating connections indefinitely;

and/or

2) there may be inadequate or no connection pooling;

and/or

3) connections are not being closed.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 20, 2021 Jan 20, 2021

Copy link to clipboard

Copied

Hi. To give context to others, from what you saw, the code in the Java database class was properly opening and closing connections, however the cfscript code that used that class may have been incorrectly coded in so far as it was not apparant if the calls to the database (to execute SQL) were properly opening and closing just one connection at a time. @BKBK  if you want to explain that better, please do 🙂


I have now made modifications to a lot of templates that had database calls, and am testing to see if the system behaves any better.  Just to reiterate, we are using this MSSQL query to check how many connections to the server are occuring. This is the query which previously has yielded more and more results - it never frees up any rows:

SELECT *
FROM master.dbo.sysprocesses(NOLOCK) 
WHERE (spid > 50) AND (status = 'sleeping') AND (program_name LIKE '%jdbc%')

 

I am not sure if this query should yield a larger and larger result set, or if it should get smaller (automatic data purge by MSSQL) at some point? If someone knows that, I'd appreciate your input.

I will update again soon after watching the server. I do hope, as @BKBK says, that the code may be the problem, but so far this remains unknown

Regards,

Mark

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 20, 2021 Jan 20, 2021

Copy link to clipboard

Copied

Quick update, the master.dbo.sysprocesses table now shows nearly 7000 connections, and that is 2 hours 20 minutes from restarting the server. I think we have a maximum of 32K in MSSQL? Not sure what will happen, but MSSQL does not seem to be housekeeping all of these sleeping connections, so far.

Untitled.png

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 20, 2021 Jan 20, 2021

Copy link to clipboard

Copied

Mark, that's encouraging, sure. It would be interesting also to hear if FR's tracking of connections (in Metrics>CF Metrics) is also showing the conneciton count staying down, compared to before.

 

And if this continues, even with your back-end office staff having come on line, the question then will be what change you did that made the difference. Your last comment said, "I have now made modifications to a lot of templates that had database calls, and am testing to see if the system behaves any better." Any chance you may be able to identify readily which change seemed to do the trick.

 

This has been a knotty problem. I'd not updated the post here (or the last one) with any of the many diagnostic and configuration approaches we'd attempted. I was always leary to propose any code changes, not knowing anything about your app--and also fearful that "just trying things" might not be fruitful. I was really hopeful that some diagnostic may have pointed out WHERE the problem was, or that perhaps some configuration change may have "covered it".

 

Since neither was fruitful, and since the problem continued, I understand why you'd resort to just trying various code changes until something seemed to do the trick. Looking forward to hear what that was. We have sensed from the beginning that it was some transaction that was somehow being left "open", but it's very hard to find that needle in a haystack of thousands of lines of code. (And to be clear to anyone interested, I did not charge for the time, which was many dozens of hours the past few weeks. Besides my initial offer in Dec to help for free, I also have a satisfaction guarantee whereby I don't expect to get paid for my time if it proves of no value.)

 

Looking forward to hearing how things turn out, or if the mystery continues, in which case we'll keep digging.


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 20, 2021 Jan 20, 2021

Copy link to clipboard

Copied

Charlie, I have emailed you code. I cannot release code publically here on the forum.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 20, 2021 Jan 20, 2021

Copy link to clipboard

Copied

OK, but to be clear I wasn't asking to see the code. 🙂 I was just commenting on your recent observations here, as well as updating others following along about the nature of my involvement so far. 

 

And I will add that in his note to me with the code just now, he said the thread count (in SQL Server) continues to rise, and the FR metrics showing open JDBC connections from the CF side are also as high as before, so sadly his code changes did not help.


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 20, 2021 Jan 20, 2021

Copy link to clipboard

Copied

I took up a kind offer of help from Charlie and we tried a new JNDI data source. JNDI can be configured to use TomCat's own connection pooling (so as to "bypass" JDBCs own pooling system). This worked for a while, but then as the server got busier (as our back-end office staff came on line) the same problem was experienced. Connections rose and JDBC connections went to zero. The server could not serve any pages and had to be restarted. Changing various settings on the server did not help. No solution has been found so far.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 20, 2021 Jan 20, 2021

Copy link to clipboard

Copied

Recap:

@tribule , In our e-mail correspondence, I suggested the following, as a possible fix: replace multiple instantiations of the database object with just one. For example, instead of

 

    x1=objToken.getcnnSG().dbFunction1();
    x2=objToken.getcnnSG().dbFunction2();
    objToken.getcnnSG().dbFunction3("some sql string arg");

 

instantiate the database object just once, using

 

// the single instantiation
dbObject=objToken.getcnnSG();

 

This guarantees that every function will be called on the same object. Thus, following dbObject.query(), the call dbOject.ReleaseConnection() will release the connection that dbObject used.

 

// use the instance in subsequent calls

x1=dbObject.dbFunction1();
x2=dbObject.dbFunction2();
dbObject.dbFunction3("some sql string arg");

 

// also use the instance to interact with the connection
dbObject.reset()
/* Alternatively: dbObject.ReleaseConnection(); */
dbObject.setConnectString("max pool size=100");

 

Follow-up:

1) Use var for every variable defined in your custom functions. This ensures that resources will be released, closed or garbage-collected.

2) When the database object has finished its job (querying, interacting, releasing the connection, etc.), then kill the object itself. That is, set it to null. To do so, use, for example:

 

dbObject= javacast("null", 0);

 

The association between the database object and its constituent Statements, Connections, DSN, etc is one of composition. Therefore, when the database object becomes null, so, too, will each of its constituents.

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 21, 2021 Jan 21, 2021

Copy link to clipboard

Copied

Thanks. I have added var's and releaseconnection() and the nullification of the variable.

 

At this point in time there are 21000 connections being used in the master.dbo.sysprocesses table. Will be interesting to see what happens when it reaches 32K! (MSSQL limit).

 

I noticed that MS say that master.dbo.sysprocessess is now deprecated and that we should use 

sys.dm_exec_connections instead. If I use the latter I still see the same number of connections. I also see that the "packet size" is 8000 bytes. Is that ok - I think it's the JDBC default?

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 21, 2021 Jan 21, 2021

Copy link to clipboard

Copied

The last changes I suggested require that you restart ColdFusion.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 21, 2021 Jan 21, 2021

Copy link to clipboard

Copied

I will do that when we reach 32K connections, as I want to see what happens for my own curiosity. The server is at 28K connections now.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 21, 2021 Jan 21, 2021

Copy link to clipboard

Copied

Ok, reached close to 32,400 connections and the server became unresponsive. FusionReactor looks like this:

test.png

 

Errors in the logs are hundreds of these:

SQLException while attempting to connect: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server returned an incomplete response. The connection has been closed. ClientConnectionId:4cf4831f-bc97-4652-a13a-d8f996a97d68.
SQL Server returned an incomplete response. The connection has been closed. ClientConnectionId:4cf4831f-bc97-4652-a13a-d8f996a97d68

 

Update: CF App Server restarted and connections rising again.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 22, 2021 Jan 22, 2021

Copy link to clipboard

Copied

@tribule : Ok, reached close to 32,400 connections and the server became unresponsive. FusionReactor looks like this:...

 

Informative. Come to think of it, FusionReactor could be instrumental in our search for a solution.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 22, 2021 Jan 22, 2021

Copy link to clipboard

Copied

Interested to hear how.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 23, 2021 Jan 23, 2021

Copy link to clipboard

Copied

FusionReactor's JDBC menu-items can give us a lot of information about database connections. Examples follow.

 

Click on JDBC History.

Is the frequency of "Finished" JDBC requests what you expect?

What kinds of threads are being used? What is the proportion of ajp-nio threads?

Note down the name of each of the Data Sources being used. What are they? 

 

Click on Databases.

Are the statistics what you expect? Click on "Hits/Errors" to sort the column. Are the hits what you expect, given the number of user requests? Do this check for each database. (If there is more than one database, FusionReactor will provide a dropdown list as the first option in the menu at top-right of the page)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 23, 2021 Jan 23, 2021

Copy link to clipboard

Copied

There is one datasource and one database. Every single query in the JDBC history is "finished".

 

I notice two different ways in which the datasource is reported in FR. We have CFML queries with look like this:

 

<cfquery datasource="#application.dsn#" ...>

 

where application.dsn is set to "ourdatasource".

 

and these are reported as "ourdatasource" in FR. These queries are used on the front-end of the website e.g. category and product pages (ecommerce site).

 

Then, I see another datasource which is reported by our back-end ecommerce system, these look like this:

sqlserver@listener:1433/OURDB


listener points to our primary SQL server. It's latency is very low. We have a fail-over system where "listener" can change to the IP of our secondary SQL server. This has never been needed yet (we are with our current provider for only 12 months).

I recognise the  listener:1433/OURDB form of the datasource from our datasource setup within ColdFusion, where the setup string for our datasource "ourdatasource" is set to:

jdbc:sqlserver://listener:1433;databaseName=OURDB;SelectMethod=direct;sendStringParametersAsUnicode=false;querytimeout=90

 

where OURDB is our database name. It seems that FR is reporting two forms of thedatasource, even though we only have one datasource set up in the CF Admin. Not sure why that is.

In our back-end system which has code written by the ecommerce 3rd party, the queries are run through CFSCRIPT using their Java API. These are where we see the "sqlserver@listener:1433/OURDB" reported as the datasource. 

 

Sorry, I'm not sure what answer to give you regarding the AJP-nio threads. I do not know what hits we should be expecting. What am I looking for, specifically?

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 23, 2021 Jan 23, 2021

Copy link to clipboard

Copied

Something else. You mentioned that you had once tested by using ColdFusion's MS SQL Server datasource setting. How did you achieve that, given that your custom code requires you to explicitly integrate the driver details into the code?

 

I ask because I am beginning to wonder whether a part of your code may be generating database connections that aren't used. Could you please share the code that integrates the JDBC driver. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 23, 2021 Jan 23, 2021

Copy link to clipboard

Copied

Yeah, this really sounds like a "poorly-written Java" issue more than a JDBC or CF issue.

 

Dave Watts, Eidolon LLC

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 23, 2021 Jan 23, 2021

Copy link to clipboard

Copied

@BKBKI sent you code previously where objToken.getcnnSG() was used to initiate connections. These are for the third party Java API that we use.

 

We are only using the JDBC driver, both for the third party API and for our own CFML queries against the database. I can see see two datasources reported in FusionReactor and it's easy to see that one is for our own queries in CFML and the other is for the back-end using the Java API. However in ColdFusion we have just one datasource set up (MS JDBC 8.4.1 driver) but FusionReactor seems to report its name differently.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 23, 2021 Jan 23, 2021

Copy link to clipboard

Copied

@tribule : BKBK I sent you code previously where objToken.getcnnSG() was used to initiate connections.

 

You did, and I asked a number of times what you observed after replacing that code with a dbOject instance and using that instance throughout.

 

Did the code continue to work as expected? Was there any change in behaviour/result? What was the effect, if any, on the number of DB connections?

 

I also asked what the result is of tests like:

 

 

storeGroupAcbDatabaseObject.setConnectString("max pool size=100");

 

 

 

 

// Test: Is there a DB connection
writeoutput("There is a DB connection: " & storeGroupAcbDatabaseObject.isConnected());

storeGroupAcbDatabaseObject.reset(); 
/* Alternatively: storeGroupAcbDatabaseObject.ReleaseConnection();*/

// Test: Is there a DB connection after the connection is released
writeoutput("There is a DB connection: " & storeGroupAcbDatabaseObject.isConnected());

 

 

 

 

dbObject.setConnectString("max pool size=100");

 

 

I have yet to hear the answers. 

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 23, 2021 Jan 23, 2021

Copy link to clipboard

Copied

@BKBKwe have hundreds of templates to change and I am gradually working my way through them to add the new code.

 

I am using one dbObject now (for objToken.getcnnSG()) and use that to open the connection and then I use it again to close it afterwards. Every open/close uses the same dbObject variable, and it also nullifed after being released. So far, connections still rise, but I have not got to 32K connections yet. That takes 2-3 days and we're on a weekend too so the site is quieter and there are no staff using the system. So far I can see no change in behaviour as connections are rising but it may stop at a lower number this time - takes a bit of time, I will update you.

 

As for:

 

max pool size=100

 

 

This is not doing anything, probably because the "max pool size" is not a recognisable part of the connection string for the MS JDBC 8.4.1 driver. I had a look at the documentation for it (at https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-...) and that option ("max pool size") does not exist.

 

Regarding:

 

 

writeoutput("There is a DB connection: " & AcbDatabaseObject.isConnected());

 

 

I logged this and the result was "NO" after a ReleaseConnection() was used. When I use it after a objToken.getcnnSG() I get "YES".

 

The code works with the new changes, but I can't yet see any difference in outcome.

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 24, 2021 Jan 24, 2021

Copy link to clipboard

Copied

@tribule , Thanks for the update. You've given me hope that we can solve this.

 

The most important news is that the code works with the new changes. That enables us to continue searching for a solution.

 

> Regarding:

writeoutput("There is a DB connection: " & AcbDatabaseObject.isConnected());

> I logged this and the result was "NO" after a ReleaseConnection() was used.
> When I use it after a objToken.getcnnSG() I get "YES".

 

That tells us we're on the right track. The "NO" result is what we want.

 

max pool size=100

> This is not doing anything, probably because the "max pool size" is not a recognisable
> part of the connection string for the MS JDBC 8.4.1 driver.

 

You're right. Although "max pool size" is an SQL Server connection string property, it is apparently not an Microsoft JDBC connection string property. I see only now in the MS JDBC documentation that "The Microsoft JDBC Driver for SQL Server takes the server default values for connection properties". We can leave that setting aside for the time being. I shall say why next.

 

Might it just be that database connections are not pooled in your application - or in any Java application that implements a JDBC driver for that matter? The FAQs on the Microsoft JDBC Driver tell us: "The driver does not provide its own pooling implementation, but rather it relies on third-party Java application servers". I am looking into how to implement that in ColdFusion.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 24, 2021 Jan 24, 2021

Copy link to clipboard

Copied

I always thought that  the latest JDBC handles connection pooling, but perhaps not. I had assumed that since CF had a maximum connections and 'maintain connections' option, that is was doing some kind of connection cache/pool?

 

I tried JNDI earlier, which uses TomCat's DBCP connection pooling, but I have not tried JNDI with the latest changes to the open/close dbObject. Perhaps time to go back and try that? When I used JNDI (Charlie help me set it up btw) the master.dbo.sysprocesses table did not show the ever-increasing connections, so perhaps it was managing connections totally differently?  However our server did still ultimately suffer the same issue e.g. not serving pages. It's often very difficult to dig in to the connection pool to see what it is doing and what its "state" is in.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 26, 2021 Jan 26, 2021

Copy link to clipboard

Copied

@tribule , thanks for the update.

(By the way, shouldn't that simply be master.dbo.sysprocesses rather than master.dbo.sysprocessesmaster.dbo.sysprocesses?)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation