Apache Tomcat 8 + MySQL performance issue

Question

I have a Java (Tomcat) application that uses with MySQL (with Hibernate) as database.

It's a simple Java JSF application and only three users use the system simultaneously. (There's two kind of big tables, with 20k lines - usually is there when it freezes.)

The system keeps freezing and I need to restart Tomcat and MySQL to work again.

In the logs I have:

[WARN] 2017-11-08 16:53:02,438 com.zaxxer.hikari.pool.PoolBase isConnectionAlive - HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@33dc5260

also:

[WARN] 2017-11-08 17:04:32,200 com.zaxxer.hikari.pool.HikariPool run - HikariPool-1 - Thread starvation or clock leap detected (housekeeper delta=1m22s40ms97µs73ns).

And (90x):

[WARN] 2017-11-08 17:04:35,438 com.zaxxer.hikari.pool.PoolBase isConnectionAlive - HikariPool-1 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@2c365a2e (No operations allowed after connection closed.)

Also:

java.lang.OutOfMemoryError: GC overhead limit exceeded

Also later on I got: "java.lang.OutOfMemoryError: Java heap space", but only 760Mb was being used by the system.

(Yes, there are a lot of errors, this is why it's hard for me to figure out how to fix it!)

Any tips?

I have set at setenv.sh:

export CATALINA_OPTS="$CATALINA_OPTS -Xms512m"
export CATALINA_OPTS="$CATALINA_OPTS -Xmx512m"
export CATALINA_OPTS="$CATALINA_OPTS -server"

When I verify the memory in use, shows:

mysqld = 165.3 MiB
java = 662.0 MiB

I'm using Hikari Connection Pool as below:

<property name="hibernate.hikari.maximumPoolSize" value="200" /> 
<property name="hibernate.hikari.idleTimeout" value="30000" /> 
<property name="hibernate.hikari.maxLifetime" value="600000" /> 
<property name="hibernate.hikari.dataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" />
<property name="hibernate.connection.provider_class" value="org.hibernate.hikaricp.internal.HikariCPConnectionProvider" />

MySQL max connections is set to 152.

MySQL my.cnf settings:

wait_timeout = 360
interactive_timeout = 420

The server is running in a DigitalOcean droplet with 1Gb Ram and 1 vCPU. What it's the best Tomcat + MySQL + Hikari configuration for this server?

I removed the Xms512m and Xmx512m parameters and decreased the maximumPoolSize to 20 and after a few hours of the server running OK, I got:

ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - HikariPool-1 - Connection is not available, request timed out after 30004ms. javax.persistence.PersistenceException:
org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

Answer 1

One of the issues that is clearly visible:

java.lang.OutOfMemoryError: GC overhead limit exceeded

Denotes that you have not enough heap memory and JVM spends a lot of time doing garbage collect.

export CATALINA_OPTS="$CATALINA_OPTS -Xms512m"
export CATALINA_OPTS="$CATALINA_OPTS -Xmx512m"

Tells JVM that Tomcat and all applications it is running should initially reserve 512 Mb of memory and they can at maximum allocate 512 Mb of memory.

From the error above, it is clear that 512 Mb is not enough for your app.

The options for you to consider:

• What maxThreads value is defined in your Tomcat Connector. Default is 200. Each thread occupies memory. With just 3 users I expect you may reduce this to 5 to conserve memory.
• hibernate.hikari.maximumPoolSize also seems too large. Again 5 should suffice.
• Each @Entity loaded by Hibernate is stored in first level cache. This slows Hibernate down and consumes memory. Consider using stateless session (if possible) to reduce session size.
• Use VisualVM or some other profiler to take a memory dump to analyze where your memory is going to.

Answer 2

You have way too many connections in the pool, if you don't have a database server with 200 cores, there is no reason to use 200, especially considering that the max connection in the database is 152, that alone is surely causing you some of the issues you are seeing.

Good practice for the max number of connections in the pool is the number of CPU cores of the server that host the database plus 1 or 2 or 3 (to be tested) and that is because there is no real parallelism beyond the number of cores, but we can use a few extra connections because there are CPU waiting times when reading information from disk for instance that would be better used by processing another thread.