Pushing to cloud foundry works for a couple requests

[jancel]

With default on all of the configurations for the prod instance of a cloudfoundry install, I've attempted to run my project. It works great on login and maybe the first couple requests, but then I start having issues getting anything to save into the database.

Caused by: org.hibernate.exception.GenericJDBCException: unable to obtain isolated JDBC connection
2015-01-29T13:42:24.95-0600 [App/0] OUT [ERROR] org.hibernate.engine.jdbc.spi.SqlExceptionHelper - [jibberish] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:4; busy:4; idle:0; lastwait:30000].

Is 4 generally enough? Is that something we're setting in the project somewhere? I can't find it in the production yml

How have others gotten by this pushing a prod build of jhipster to production?

[jdubois]

• 4 connections should be enough for your app to work, but of course it depends of what you do
• I am personally using a paid plan (well, ElephantSQL gives it for free to the project, that's why we like them!), and of course this helps a lot -> for anything serious, you probably need more than 4 connections

Which database are you using? And are you deploying on the Pivotal Cloud, or somewhere else?

[jancel]

I'm deploying to an internal Pivotal cloud; and the scope of the application should not at this point need more than 4. But after one update to the database, I can't update other models because of this error. I'm the only user as well. We're using a mysql database right now. Default config (100mb). Java Version 1.8 (from the generator, so completely 1.8). We're using the cloudfoundry java buildpack, which is also 1.8 (github).

[jdubois]

Last time I checked this work working: it might depend on your authentication mechanism, what are you using?
Otherwise, are you using the MySQL "auto increment" option for your primary keys? This is the JHipster default, as if you use a table (another Hibernate option), that eats a couple of connections and you will get stuck if you only have 4 connections.
You should be able to monitor your MySQL database : can you check which requests are taking up the connections?

[jancel]

The authentication mechanism is the default user/admin logins. I did not use the OAuth mechanism or any other SSO options yet. I'm just deploying the prototype before I integrate our internals.

The id on every model/domain is:

            <column name="id" type="bigint" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>

In the domain/model, the typical setting is:

@Entity
@Table(name = "T_PROJECT")
[...]
    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    private Long id;

I think we're using the TABLE option. Which should I change this to? I don't mind eating connections, but at this level, I would like to maintain somewhat reasonable level of performance for just a few users. i.e. I don't mind scaling the connections when we have a lot of people hitting the app. But for just me, hitting the second or third model on a fresh deploy, it shouldn't have an issue for a default type setup.

I'm going to set it back to AUTO and see how it does. (maybe this issue was specific to me using h2 for dev?)

[jancel]

On further inspection, the model I pasted above is the only "TABLE" strategy.

[jdubois]

Yes, but the "table" strategy eats the connections, that's your issue -> use the "autoincrement" option, like the rest of JHipster, you will see the difference!

[jancel]

By Auto increment, this is initially created in the Liquibase generated file. I have updated the model to GenerationType.AUTO. Is there something I'm missing? A re-deploy did not settle the issue.

Thanks.

[jdubois]

If you are in "AUTO" it should be OK. In my experience that's what was leaking connections... Can you check the currently opened connections? You should be able to see the requests, so you can understand what's taking the connections. Or maybe a thread dump? You could see the locked threads a dump them in the "metrics" page.

[jancel]

Okay. I'll look at this on Monday. I can't get to CF from home.

[jancel]

I think this is coming from a tomcat container. I was watching the logs and here's a log message that I think might be related:

2015-02-02T13:46:58.58-0600 [App/0] OUT [WARN] org.apache.tomcat.jdbc.pool.ConnectionPool - maxIdle is larger than maxActive, setting maxIdle to: 4

This is coming from this spot in the startup phase.

2015-02-02T13:46:56.59-0600 [App/0]   OUT [INFO] com.example.app.config.CloudDatabaseConfiguration - Configuring JDBC datasource from a cloud provider
2015-02-02T13:46:58.52-0600 [App/0]   OUT [DEBUG] com.example.app.config.DatabaseConfiguration - Configuring Liquibase
2015-02-02T13:46:58.58-0600 [App/0]   OUT [WARN] org.apache.tomcat.jdbc.pool.ConnectionPool - maxIdle is larger than maxActive, setting maxIdle to: 4

[jdubois]

This is because when you deploy on CloudFoundry, there is a "Spring Cloud" configuration that is applied: it auto-reconfigure the datasource, and as it "knows" that you only have 4 connections available, it gives the connection pool a max of 4.
So, this is normal.
The real issue is why 4 connections isn't enough for you: it's pretty small, but still it's enough for a simple project. Can't you check what those connections are doing? You should be able to see the threads waiting on the JHipster side, or the requests pending on the database side.

[jancel]

I did some digging at the connection level and couldn't find anything in particular. When a resource is requested, it hangs for 30 seconds and then produces that error. Seems to me that it shouldn't take 4 connections to run either. I'm not a mysq dba, if there's any pointers on providing the information you're asking about. (I totally wanted this all to just work).

+----+------------------+--------------------+-----------------------------------------+---------+------+-------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------------------+--------------------+-----------------------------------------+---------+------+-------+-----------------------+----------+
| 26 | user | ip.address.of.cloud:xx698 | db | Sleep | 7296 | | NULL | 0.000 |
| 27 | user | ip.address.of.cloud:xx699 | db | Sleep | 7293 | | NULL | 0.000 |
| 28 | user | ip.address.of.cloud:xx700 | db | Sleep | 7293 | | NULL | 0.000 |
| 29 | user | ip.address.of.my.machine:PORT | db | Query | 0 | init | SHOW FULL PROCESSLIST | 0.000 |
+----+------------------+--------------------+-----------------------------------------+---------+------+-------+-----------------------+----------+

Consequent hits add another... It sits for about 65 seconds then goes away. The logs are below. (This is after app has been idle for several hours)

| 30 | user | ip.address.of.cloud:xx097 | db | Sleep | 6 | | NULL | 0.000 |

Application DID re-authenticate the user based on session successfully. But I couldn't execute a create on any of the generated models. I do have Atmosphere configured, is this potentially using some of the connections? I've shut it off client side.

[jancel]

That makes sense and is too my concern. I've updated all generation types
to auto. I am going to try it on a public cloud with the same build pack
(provided by cloud foundry) and see what happens. Any metrics I can gather
or that you suggest, just let me know how you would like me to get them.

On Monday, February 2, 2015, Julien Dubois notifications@github.com wrote:

This is because when you deploy on CloudFoundry, there is a "Spring Cloud"
configuration that is applied: it auto-reconfigure the datasource, and as
it "knows" that you only have 4 connections available, it gives the
connection pool a max of 4.
So, this is normal.
The real issue is why 4 connections isn't enough for you: it's pretty
small, but still it's enough for a simple project. Can't you check what
those connections are doing? You should be able to see the threads waiting
on the JHipster side, or the requests pending on the database side.

—
Reply to this email directly or view it on GitHub
#1054 (comment)
.

Jeff Ancel

(314) 703-8829 - Main

www.jeffancel.com

[jdubois]

I used to have this issue before switching to "auto" : it's very hard to test, as it's done behind-the-scenes by Hibernate, but using a separate table for sequences is eating several connections. I didn't check, but I guess it's locking the table in order to guarantee to have a unique number.

• yes you should try on the Pivotal cloud, to be sure it's not another issue on your side
• with cleardb I'm not sure of what you can do, but with ElephantSQL we can see the current requests, which can be a good help in your case

[jancel]

Cleared. This is a good discussion and the GenerationType.TABLE is the culprit, which isn't used anymore by the JHipster generator (maybe it was as some of my early models had this effect and I wasn't expecting it). Additionally, the Spring Auto Reconfiguration restricts you to limits imposed by Cloud Foundry. Digging into this documentation lead me down a path which my Cloud Foundry configuration had to be updated to more reasonable defaults (maxActive, maxIdle) which my organization had set a little low.

When changing generation type, you must take into consideration ALL tables using it, as if there is ONE, it could mess up the entire pool of configurations.

[jiwhiz]

Very good discussion. I had similar issue just a couple of weeks ago. The old jHipster was using GenerationType.TABLE, and caused big headache for me when I deployed the app to CloudFoundry with ClearDB :) Then after I changed to GenerationType.AUTO and add autoIncrement="true" to all entity schema tables, the app can run with 4 connections.