portal entry

Interesting, Priyank. Can you offer a sentence or two about what scenarios may lead one to want to do this? Is there some benefit to using a JNDI-defined datasource, where a CF-provided one (or the "other JDBC" option) won't work for folks? I just ask because I'd hate to see some folks think this is something they SHOULD do, when the existing options may suffice. Perhaps you had a user who had a real need for this. I'm just saying it would be helpful to hear the need, as well as this solution to it. :-) Thanks.

Comment by Charlie Arehart

Hi Charlie, We have users who still would like to use JNDI data source who are either migrating from WebSphere environment to Standalone installation of CF. We offered them to use the JDBC option however they want to stick to JNDI data source. I don't see any benefit over "Other JDBC". Maybe the way users have written their application calling the JNDI. I published this blog because the old blog was deleted during the migration.

Comment by Priyank Shrivastava

Great. That explains it reasonably. Thanks.

Comment by Charlie Arehart

Hi Charlie, There is a performance benefits on switching  from CF datasource to jndi datasource. CF implementation to get a connection from pool and returning a connection is synchronized i.e. if one thread either taking the connection from connection pool or returning the connection then all the other thread have to wait and if for any reason establising the new connection is slow then with will slow down all the threads. Using JNDI we can switch to different datasource pool which have non blocking implementation. Thanks        

Comment by mohit1984

Hi Charlie, There is performance gain in switching from CF datasource implemenation. CF datasource implementation to get connection from connection pool and return connection to connection pool is blocking i.e. if one thread is obtaining the connection or return the connection then all other threads have to wait. This implenation slowdown the complete application if the establishing new connection is slow. Using JNDI, we can switch to datasource implementation with are non blocking. Thanks,  

Comment by mohit1984

Thanks, and that's very interesting to hear, Mohit. I think it deserves more discussion, for everyone's sake. First, are you saying that in cases where you saw that problem, it was not resolved by turning off the "maintain connections" option in the CF datasource configuration screen? (I do realize that it doesn't always have the impact people expect. I'm simply asking if you had tried it, in trying to solve the problem you refer to.) Second, had you explored any other possible solutions to that, with Adobe? I just find it odd that the ONLY way to solve that problem would be to switch to a JNDI datasource. (I'm not aware that this was marketed as a feature ever, by Adobe at least.) Finally, do you have evidence of confirmation of that? Either from someone from Adobe confirming it, or from running something that did confirm it (such as viewing stack traces/profiles or requests running, showing they never used any connection pooling). If you are from Adobe (and we just can't tell from your handle here), do let us know. Again, I do find this VERY interesting, and yes I HAVE had situations where people wanted to consider removing the impact of CF's use of db connection pooling.

Comment by Charlie Arehart

Also, Mohit, there are two comments here that are very close to each other (but different). I suppose the problem was that you didn't realize it was being moderated (all comments are moderated), so you re-wrote it, though slightly differently. Note that you could pick one and edit it as you please, and then you could edit the other to say "removed as a duplicate". No worries if you don't want to bother. Just trying to help.

Comment by Charlie Arehart

Hi Charlie, Please find below my response to your queries <ol> <li>If we turn off the maintain connection then the problem become worse if the database connection establishment is slow due to network issues.</li> <li>I haven't connected adobe about this. I am not aware of the process.</li> <li>Yes, I do have thread dump which prove that JDBC pool checkin and checkout methods are blocking in nature. "ajp-nio-8016-exec-88" prio=5 tid=217 BLOCKED at coldfusion.server.j2ee.pool.ObjectPool.checkIn(ObjectPool.java:263) at coldfusion.server.j2ee.sql.pool.JDBCPool.returnConnection(JDBCPool.java:892) at coldfusion.server.j2ee.sql.pool.JDBCPool.connectionClosed(JDBCPool.java:333)</li> </ol> My finding is about the way CF database pool work. CF database pool is using synchronized block which will always degrade the performance of the application if multiple request comes to the server at same time and for any reason connection creation is slow then the problem become worse. There are multiple instances in CF code which have been implemented code using blocking calls. Below are couple if references <ol> <li> <a href="https://mohitratra.blogspot.com/2017/11/coldfusion-use-cfthrow-with-percautions.html" rel="nofollow">https://mohitratra.blogspot.com/2017/11/coldfusion-use-cfthrow-with-percautions.html</a></li> <li>parseDateTime(date) has blocking nature with can impact system performance but on the other hand parseDateTime(date, format) is doesn't have blocking nature.</li> </ol>

Comment by mohit1984

<p>Mohit, you misread my reply, but I see now how you could. <img src="https://s.w.org/images/core/emoji/2.3/72x72/1f642.png" alt="??" class="wp-smiley" style="height: 1em; max-height: 1em;" /> I’ll re-frame my question, because I’m indeed intrigued about what you are sharing.</p><p>First, I wasn’t asking for evidence that normal cf dsn connection pooling could be blocked/is synchronized. (I am well aware of that. And FWIW, it's usually that the checkin's/checkout's are waiting for one thread that is doing a fetchConnection operation on the pool.)</p><p>Instead, I was asking if you had evidence confirming that your use of a jndi dsn would NOT ever block that way. <img src="https://s.w.org/images/core/emoji/2.3/72x72/1f642.png" alt="??" class="wp-smiley" style="height: 1em; max-height: 1em;" /></p><p>But I see now said specifically, “Using JNDI we can switch to different datasource pool which have non blocking implementation. Ah, <strong>so can you clarify more on how you did that? </strong>That sounds very interesting.</p><p>And I wonder (Priyank or anyone else) if we might also be able to configure NON-jndi dsn’s to be non-blocking this way.</p><p>(That said, the hangup in pool processing is usually the RESULT of some other problem that LEADS to the blocking, like how you say “if for any reason connection creation is slow”. So this change to a non-blocking connection may merely MASK a bigger problem, in some situations. But I hear you, in saying it’s only helped in yours.)</p><p>If jndi dsn’s (alone, perhaps) can add this benefit of being able to be configured as non-blocking (without any known negative impact), it could well be an important new weapon in addressing problems of requests hanging in connection pool processing, and would indeed deserve better documenting of that fact. .</p>

Comment by Charlie Arehart

Mohit?

Comment by Charlie Arehart

Charlie, I was not able to respond you early on this as I was not working on performance issue in my project and didn't have any stats. Currently, I have started working on proof of concept to jndi datasource. And after implementing the solution in our dev with low system configration, we are observing the high throughput and less response time. We are using  Hikari for the connection pool implementation.      

Comment by mohit1984

That's great, Mohit. Thanks. Can you share more on how you configured things.. It could benefit many. You could even do a blog post, or I'd be happy to help you do one. This sound like a compelling possibility.

Comment by Charlie Arehart

I tried that but it did not work. Then I saw Charlies' comment, went to his page where he talked about using configuration manager and enabling TCP/IP for your instance of SQL Server. Thank you, Charlie. I will go to your page and comment.

Comment by budhines1