JDBC locking under load

[Brad Wood]

I trying to load test a new site and I am seeing alarming performance degradations with high concurrency using both the jTDS 1.3.1 JDBC driver and the Microsoft JDBC 4.0 driver.

Setup:

• Ubuntu 12.04 Server 64-bit
• Java 1.7.0_17 (Oracle Corporation) 64bit
• Apache Tomcat/7.0.37
• Railo 4.1.1.002 final
• Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) 
• Tried both:
• jTDS JDBC driver 1.3.1
• MS JDBC Driver 4.0

My app connects to the database fine and runs with no issues under minimal load. Once the load is increased to about 100 concurrent connections (using JMeter) requests get slower and slower, all waiting for database calls.  I have already posted on both the MSDN and SourceForge forums, but I have started to wonder if the issue does not lie in Railo or if you guys can at least help me troubleshoot this.

Here are the two posts I have done already in the respective driver forums.  Each of these posts has a full thread dump taken during load testing with that driver:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c38fc00b-c799-4094-a954-c6496d1c085c/prepared-statements-blocking-with-jdbc-drivers

https://sourceforge.net/p/jtds/discussion/104389/thread/c1d78794/

Note this apparent Java deadlock I'm seeing when using the jTDS driver.  The two classes blocking each other are internal Railo classes:

"http-bio-8888-exec-29" Id=151 BLOCKED on railo.runtime.db.DCStack@26c9b4c owned by "http-bio-8888-exec-9" Id=110
    at railo.runtime.db.DatasourceConnectionPool.releaseDatasourceConnection(DatasourceConnectionPool.java:86)
    -  blocked on railo.runtime.db.DCStack@26c9b4c
    at railo.runtime.db.DatasourceManagerImpl.releaseConnection(DatasourceManagerImpl.java:111)

and...

"http-bio-8888-exec-9" Id=110 BLOCKED on railo.runtime.db.DCStack@26c9b4c owned by "http-bio-8888-exec-29" Id=151
    at railo.runtime.db.DatasourceConnectionPool.getDatasourceConnection(DatasourceConnectionPool.java:40)
    -  blocked on railo.runtime.db.DCStack@26c9b4c
    at railo.runtime.orm.hibernate.HibernateORMEngine.createSession(HibernateORMEngine.java:128)

See the second link for the full thread dump.  Can anyone help me troubleshoot this?  Unfortunately, it's keeping my app from going live and I can't figure out why I'm seeing similar lockups with two different JDBC drivers.

Thanks!

~Brad

[Brad Wood]

An update on this that might be related.  I'm running my tests again with the jTDS driver and I'm noticing that even though there are 100 threads all trying to get a database connection right now, SQL Server reports that only ONE spid is active on the database that is connected to from the web server.  I have set the "Connection limit (max)" setting to both "inf" as well as "100" and the behavior does not change.

select @@MAX_CONNECTIONS on the SQL Server returns 32,767.  

Is there any reason why Railo is only creating a single database connection to service 100 active threads?  Note, all DB access is via ORM.

Thanks!

~Brad

[Denny]

On 9/5/13 12:28 PM, Brad Wood wrote:
...

> My app connects to the database fine and runs with no issues under minimal
> load. Once the load is increased to about 100 concurrent connections (using
> JMeter) requests get slower and slower, all waiting for database calls. I

DB interaction is a "usual suspect" for stuff like this (generally one
of the big bottlenecks). Are you using 2nd level caching, lazy fetching
where possible, and verified what queries are being generated, etc.?

As for the connection limit... hrm. Are you sure you're editing the
right DSN? Maybe you defined it in web and server or something?

Shooting from the hip here, hopefully someone else has ideas there.

:Denny

--
Railo Technologies: getrailo.com Professional Open Source
Skype: valliantster (505)510.1336 de...@getrailo.com
GnuPG-FP: DDEB 16E1 EF43 DCFD 0AEE 5CD0 964B B7B0 1C22 CB62

[Brad Wood]

Thanks for the reply Denny.

Are you using 2nd level caching

Yes

lazy fetching where possible, 

I believe so

and verified what queries are being generated

Not sure what you mean, but the queries are returning the correct data and the app is working correctly.  If it help, this (almost) exact codebase is currently running a single Adobe CF server without batting an eye.  It gets quite a bit of traffic though-- around 900  concurrent users.  We're trying to "upgrade" it to 3 Railo instances and they're falling over on their face each serving 1/3 the load of the old server.

As for the connection limit... hrm.  Are you sure you're editing the right DSN?

Yep, there's only one :) The default is infinite though, and we had it left at the default before we even tried changing it.  As you can see from my followup, Railo is appearing to only create a single database connection which would explain all the threads blocking each other for DB access.

Maybe you defined it in web and server

Nope, just server.

Thanks!

~Brad

Thanks!

~Brad

ColdBox Platform Evangelist

Ortus Solutions, Corp 

E-mail: br...@coldbox.org

ColdBox Platform: http://www.coldbox.org 

Blog: http://www.codersrevolution.com

--
Did you find this reply useful?  Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to a topic in the Google Groups "Railo" group.

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/5228F0BD.5090007%40getrailo.com.

For more options, visit https://groups.google.com/groups/opt_out.

[Denny]

On 9/5/13 3:23 PM, Brad Wood wrote:
...

> **and verified what queries are being generated*
> *

> Not sure what you mean, but the queries are returning the correct data and
> the app is working correctly. If it help, this (almost) exact codebase is
> currently running a single Adobe CF server without batting an eye. It gets
> quite a bit of traffic though-- around 900 concurrent users. We're trying
> to "upgrade" it to 3 Railo instances and they're falling over on their face
> each serving 1/3 the load of the old server.

I was angling towards a common problem with ORM-- not only is it getting
the data you need, it's fetching lots of data you don't.

Generally you'd watch the queries being generated (on the SQL server
side-- checking query execution plans, etc.) and verify that they are
optimal.

It's not sounding like that's the issue though.

> *As for the connection limit... hrm. Are you sure you're editing the right
> DSN?*

>
> Yep, there's only one :) The default is infinite though, and we had it left
> at the default before we even tried changing it. As you can see from my
> followup, Railo is appearing to only create a single database connection
> which would explain all the threads blocking each other for DB access.

Out of curiosity, how are you defining the connection? Using the
"other" JDBC option or the MSSQL specific settings? (Not sure what
difference it would make, just wondering).

You could always instantiate the connection pool yourself (using java),
and see if you get different results, but that's not a fix.

I can't run a test myself at the moment, but hopefully someone else will
chime in with their results.

...
> Thanks!

No problem! Not that I've been much help, but bouncing ideas around is
rarely a bad thing, neh? :)

[Brad Wood]

Generally you'd watch the queries being generated (on the SQL server side-- checking query execution plans, etc.) and verify that they are optimal.

Honestly, I haven't really looked into that angle yet because the SQL Server is so bored I can almost hear it snoring :) This is what the SQL Server looks like while I'm running my tests and that WITH the live site hitting a different database on the same server:

• CPU is averaging 20%
  
• IOPS for data and log drives are no more than 40
• Disk queues are at 0
• IO latencies are fantastic-- serving all IO ops in 2-10ms

Furthermore, none of the Java stack traces are waiting on the SQL server for processing.  They're all waiting just to get a SQL Connection.

Out of curiosity, how are you defining the connection?

I've used the MS JDBC driver and jTDS with similar results and I've used the Railo "wizard" and entered the JDBC URLs manually as well.

Thanks!

~Brad

Thanks!

~Brad

ColdBox Platform Evangelist

Ortus Solutions, Corp 

E-mail: br...@coldbox.org

ColdBox Platform: http://www.coldbox.org 

Blog: http://www.codersrevolution.com

--
Did you find this reply useful?  Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to a topic in the Google Groups "Railo" group.

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/5228FE59.6090809%40getrailo.com.

[Brad Wood]

Another update in regards to Railo only using a single connection to the database. I've found that if I kill the only spid on the database with the test running, Railo will immediately create around 60-70 new database connections.  However, it appears to only use one of them and the remaining all disappear within a minute or so as they timeout.  It's like Railo ORM is creating a pool of connections, but only using one of them.

Thanks!

~Brad

[Denny]

Ah, that's kind of ringing a bell.

I was doing some JNDI/JTA stuff... yeah, and someone else ran into
something similar. I think it goes like this:

The default hibernate pool uses one connection, so you have to specify*
a custom hibernate config and 1) use C3P0 (add the libs to WEB-INF/lib)
or 2) perhaps just set hibernate.connection.pool_size to the same number
the DSN has.
* this.ormSettings.ormConfig = "hibernate.cfg.xml";

I'd try the latter first (and really Micha could step in here and say
I'm wrong, so take all this with a grain of salt) as I think we pass in
the datasource, which has its own pooling.

If upping the pool size via the hibernate.cfg.xml setting does the
trick, would you file a ticket so we automatically set it to the DSN
pool size? (Assuming once again that I'm not barking up the wrong tree.)

[Brad Wood]

Hmm, that's not what you said in this thread  :)

https://groups.google.com/forum/#!topic/railo/PqHxIMwKuaM

"You might be better off ignoring that message about connection pools, as Railo does its own connection pooling by DSN. "

Plus the docs confirm:

http://railodocs.org/index.cfm/tag/application/version/current

"The connection information in the Hibernate Configuration XML file is however ignored because Railo uses its own connection pool. "

My understanding is that I have no control over the connection pooling other than the settings in the data source.  Interestingly enough, ACF lets me turn off connection pooling, but Railo doesn't.  I'm not sure if that's on purpose, or just an unimplemented feature.

Thanks!

~Brad

[Denny]

On 9/5/13 11:45 PM, Brad Wood wrote:
> Hmm, that's not what you said in this thread :)
> https://groups.google.com/forum/#!topic/railo/PqHxIMwKuaM
> "You might be better off ignoring that message about connection pools, as
> Railo does its own connection pooling by DSN. "

That's the one! Imagine that this is just a progression of that-- the
OP there was using a custom pool (it's not uncommon to "tune" hibernate
to your usage), and anybody doing that wouldn't see this issue, (again,
assuming I'm in the ballpark) so maybe letting hibernate know it can use
X connections has simply been overlooked? This is all percolation on my
part, just sort of ruminating.

> Plus the docs confirm:
> http://railodocs.org/index.cfm/tag/application/version/current
> "The connection information in the Hibernate Configuration XML file is
> however ignored because Railo uses its own connection pool. "

Ah yeah, thus my JNDI fork. Hrm. I guess any properties that are not
overwritten by the hard coded ones would survive:

https://github.com/getrailo/railo/blob/4.1/railo-java/railo-core/src/railo/runtime/orm/hibernate/HibernateSessionFactory.java

So you might try my idea. Micha may say "we set that somewhere else" or
something, but at least it's an idea for the meantime :)

> My understanding is that I have no control over the connection pooling
> other than the settings in the data source. Interestingly enough, ACF lets
> me turn off connection pooling, but Railo doesn't. I'm not sure if that's
> on purpose, or just an unimplemented feature.

I'm not sure which. You'd think setting the number of connections to 1
would achieve the same end without cluttering the UI, no? Or maybe they
use a 3rd party pooler? Wow. "3rd party pooler" really rolls off the
tongue. :)

[Michael Offner]

we are speaking about a application that is using JDBC only via hibernate, no cfquery at all (just to be clear)?

Micha

2013/9/5 Brad Wood <br...@bradwood.com>

--

Did you find this reply useful? Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---

You received this message because you are subscribed to the Google Groups "Railo" group.
To view this discussion on the web visit https://groups.google.com/d/msgid/railo/31b15bcf-0a17-4e03-80aa-e0d593a6cc4b%40googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--

/micha

Michael Offner CTO Railo Technologies GmbH

[Chris Blackwell]

This looks very similar to the issue i experienced recently, posted here

Our app is a mix of ORM and plain SQL, running against MySQL 5.1.

With 4.1.1.000 I can run the app with around 300 request/min and its quite happy, however I only ever see a single active connection to the database.

If i upgrade to 4.1.1.001 (can't test 4.1.1.002 as 2595 breaks our app) with the same load i start to see deadlocked threads just like Brad, and again only a single connection to the database.

We have @@max_user_connections = 0 (unlimited) for the server and the user, and Railo is set to max 100 connections. 

Here's our datasource config from railo-web.xml

<data-source 

allow="511" 

blob="false" 

clob="false" 

class="com.mysql.jdbc.Driver" 

connectionLimit="100" 

connectionTimeout="1" 

custom="" 

database="" 

dsn="jdbc:mysql://xxx.xxx.xxx.xxx:3306/myapp?useOldAliasMetadataBehavior=true&amp;allowMultiQueries=true&amp;characterEncoding=UTF-8&amp;jdbcCompliantTruncation=true&amp;tinyInt1isBit=true&amp;autoReconnect=true&amp;zeroDateTimeBehavior=convertToNull&amp;useUnicode=true&amp;relaxAutoCommit=true&amp;useSSPSCompatibleTimezoneShift=true&amp;useLegacyDatetimeCode=true&amp;useJDBCCompliantTimezoneShift=true&amp;serverTimezone=UTC&amp;useTimezone=false&amp;useGmtMillisForDatetimes=false" 

host="" 

metaCacheTimeout="60000" 

name="myapp" 

password="encrypted:......." 

port="-1" 

storage="true" 

username="myapp" 

validate="false"

/>

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/CAJVB4jd-a5K_spOCZ7Q8TrCe%3D_DWGD5BgsCaXXNfn_NFg%2Bqngw%40mail.gmail.com.

[Michael Offner]

First of all, connection pooling is happening by Railo for 2 reasons.

- we need full control over the connections to make the tag cftransaction work

- we only wanna have one connection pool, not 2

i think i have found and fixed the problem

https://issues.jboss.org/browse/RAILO-2598

Problem was that the orm session has released the connection used twice, so you could end with a pool full of the same connection.

so the pool could provided the same connection to multiple threads.
we have made sure the connection is released only once and we have add a check to the pool to make sure every connection in the pool is unique. Better safe than sorry.

Micha

2013/9/6 Michael Offner <mic...@getrailo.com>

[Chris Blackwell]

Hi Micha,

That fits exactly with the behavior I've seen whilst trying to observe this.  From cold Railo was using a few connections, but gradually all db activity gravitates towards a single connection when ORM is in use. 

As soon as 003 is out i'll test this.

Thanks for the rapid response as always!

Cheers, Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/CAJVB4jdLQqFjncRjNrrb1y6kQKJNBkv0qyjNvbTQUYEkGdz17Q%40mail.gmail.com.

[Brad Wood]

Micha, that is good news.  When will Railo-2598 be available as a patch or an update?  I am very interested in test this fix as it is very possibly what's holding up my production release of this site.  We've tried to roll it out twice with disastrous results and the client is quite eager for us to finally put it live.

Thanks!

~Brad

[Brad Wood]

Chris, the post you made on Aug 27 is exactly the same kind of stack traces we're seeing.  I searched the forums before posting and I'm surprised that thread didn't come up.

Thanks!

~Brad

ColdBox Platform Evangelist

Ortus Solutions, Corp 

E-mail: br...@coldbox.org

ColdBox Platform: http://www.coldbox.org 

Blog: http://www.codersrevolution.com

You received this message because you are subscribed to a topic in the Google Groups "Railo" group.

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/CAB%3DtfTposd40HOY8yvKh812zekubOCQTF3i6D6bW0Ri9-AbcaQ%40mail.gmail.com.

[Michael Offner]

We plan it for today, when there are no surprises with the test cases.

Micha

Von meinem iPhone gesendet

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/062a61fa-5186-402d-8721-a4c66bd8e07a%40googlegroups.com.

[Brad Wood]

Do you mean you will be releasing Railo 4.1.1.003 today?  I stand by ready to test :)

Thanks!

~Brad

ColdBox Platform Evangelist

Ortus Solutions, Corp 

E-mail: br...@coldbox.org

ColdBox Platform: http://www.coldbox.org 

Blog: http://www.codersrevolution.com

You received this message because you are subscribed to a topic in the Google Groups "Railo" group.

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/81C7ACFD-000B-40C1-A4EF-CE9DF5B91807%40getrailo.com.

[Denny]

FWIW, there are automated builds which fire after every commit, so if
you're chomping at the bit:

http://cfmlprojects.org/artifacts/org/getrailo/railo-rc/4.1.1.002

Should contain the patch, as it looks like that commit fired the last
build (the version number won't bump until Micha bumps it-- we're
working on using -SNAPSHOT or some such to better denote status
(versioning is complicated and effects development style, etc.)).

These are unofficial, and I use the jar more than the patch, so YMMV,
but theoretically you could replace your 002.rc with the one from the
link above and see the latest and greatest.

Theoretically. =)

:Denny

On 9/6/13 12:20 PM, Brad Wood wrote:
> Do you mean you will be releasing Railo 4.1.1.003 today? I stand by ready
> to test :)
>
> Thanks!
>
> ~Brad

[Brad Wood]

Thanks Denny!  I had no clue that there were automatic builds of Railo available on the interwebs.  Micha told me earlier today that he was going to try and release 003 soon-- today if possible.

 I use the jar more than the patch

Can you explain what you mean there?  I don't see any jars on that page.

Thanks!

~Brad

ColdBox Platform Evangelist

Ortus Solutions, Corp 

E-mail: br...@coldbox.org

ColdBox Platform: http://www.coldbox.org 

Blog: http://www.codersrevolution.com

--
Did you find this reply useful?  Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to a topic in the Google Groups "Railo" group.

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/522A451F.80308%40getrailo.com.

[Denny]

On 9/6/13 3:15 PM, Brad Wood wrote:
> Thanks Denny! I had no clue that there were automatic builds of Railo
> available on the interwebs. Micha told me earlier today that he was going
> to try and release 003 soon-- today if possible.

Yeah, his are official. The auto-builds are a convenience. I'm a
chompper at the bitter myself, thus... :)

The idea is to provide a potential path for doing this officially.

CFML is one of the hardest languages to fully test, as it is *so*
freakishly powerful, and spans *so* many
technologies/protocols/whathaveyou. I'm always adding harnesses to the
build tool, for automated testing of stuff like LDAP, SSH, S3, blah,
blah, blah, so that setting up CI is a one-click type deal, vs. forcing
people to manually install and configure harnesses "outside" of the build.

> * I use the jar more than the patch*

>
> Can you explain what you mean there? I don't see any jars on that page.

If you go up a level or two you'll see that just about every artifact
one would want to produce is being produced (fully automated):

http://cfmlprojects.org/artifacts/org/getrailo

The jar can be had from here:

http://cfmlprojects.org/artifacts/org/getrailo/railo/4.1.1.002/

I use the jar more often because the build tool leverages maven-style
dependencies, so testing the latest version is a matter of doing this:

cfdistro build.test railo.version=4.1.1.002

That's using ANT, which I'm sticking with (it's better for what I need
to do), though I'm quite handy with Maven these days. I just happened
to set things up in a way that folks who like/want/need to use Maven can
do so as well-- because why have one, when you can have both? :)

:Denny

[Brad Wood]

How would one use the jar to upgrade their install?  Is the jar file any different than the rc file other than the extension?

Thanks!

~Brad

[Brad Wood]

Good news.  I have ran my tests with the new fix that Micha put in for ticket RAILO-2598 and all locks related to JDBC connections are completely gone.  Furthermore, I have confirmed on the database that there are now dozens of connections being continuously used instead of a single connection. As soon as .003 drops, I would recommend anyone with ORM on their site to update as all your traffic is essentially being forced to share the same DB connection without this fix.

Thanks for the very quick turn-around Micha!

~Brad

On Thursday, September 5, 2013 1:28:58 PM UTC-5, Brad Wood wrote:

[Denny]

On 9/6/13 9:22 PM, Brad Wood wrote:
> How would one use the jar to upgrade their install? Is the jar file any
> different than the rc file other than the extension?

The jar contains the rc file, but you install it by replacing the
railo.jar in WEB-INF/lib with the new jar, versus putting it in the
patches directory.

Stuff like this is especially useful for "cattle"*, which you generally
don't patch manually.

* There's an analogy about systems, where you have "pets" or "cattle":
Pets you take care of, cattle you replace. So instead of maintaining
one specially configured Railo instance, which you add patches to and
manually configure DSNs and stuff -- a pet -- you burn everything to the
ground and build it anew on demand-- it's replaceable**.

** Seems cold, I know. But it's a rough world out there. We must stay
fast and hard, lest we end up like The Great Lord Hidetora Ichimonji***.

*** Only, ya know-- more tragic, and less comeuppancey. :)

:Denny

[Brad Wood]

Lol, I've heard the pets and cattle analogy :)

Thanks!

~Brad

ColdBox Platform Evangelist

Ortus Solutions, Corp 

E-mail: br...@coldbox.org

ColdBox Platform: http://www.coldbox.org 

Blog: http://www.codersrevolution.com

--
Did you find this reply useful?  Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to a topic in the Google Groups "Railo" group.

To view this discussion on the web visit https://groups.google.com/d/msgid/railo/522AB8B0.2050500%40getrailo.com.