communication link failure

[Sagar2323]

jdbcUrl=jdbc:mysql://127.0.0.1:3307/gps?autoReconnect=true&useSSL=false&failOverReadOnly=false&maxReconnects=10
maximumPoolSize=1000
minimumIdle=200
idleTimeout=120000
connectionTimeout=1200000
dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.prepStmtCacheSqlLimit=2048

Hikari version: HikariCP-2.6.3

Server Configuration:
Memory: 32 GB
CPU Sockets: 6
Virtual Processors: 24

MYSQL Configuration:
interactive_timeout: 129600
wait_timeout: 129600
innodb_buffer_pool_size: 16G

With this configuration i am getting below error so what cause below problem.

Problem 1:
Communications link failure

The last packet successfully received from the server was 3,510 milliseconds ago. The last packet sent successfully to the server was 3,509 milliseconds ago

Problem 2:
com.sun.proxy.$Proxy3[createStatement(-1)]
com.zaxxer.hikari.pool.ProxyConnection[createStatement(270)]
com.zaxxer.hikari.pool.HikariProxyConnection[createStatement(-1)]
com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection[lambda$getClosedConnection$0(475)]

Environment

HikariCP version: x.x.x
JDK version     : 1.8.0_111
Database        : PostgreSQL|MySQL|...
Driver version  : x.x.x

^{⚠️ Please verify that your issue still occurs on the latest version of HikariCP before reporting.}

---

Have you searched the CLOSED issues already? How about checking stackoverflow?

[brettwooldridge]

@Sagar2323 Do not use autoReconnect=true and maxReconnects=10, they inherently cause issues with pools.

The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly.

[Sagar2323]

HikariCP version: HikariCP-2.6.3
JDK version : 1.8.0_121
Database : MySQL
Driver version : mysql-connector-java-5.1.40-bin

I have made change according to your suggestion.

[Sagar2323]

Still communication link failure is coming on server.
SO what to do next?

[brettwooldridge]

A pool size of 1000 is insane, unless you have 512 processors. For 24 cores your maximum pool size should be between 50-75 connections.

https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

With 1000 connections, which likely far exceeds MySQLs max_connections, you are causing MySQL to continuously evict connections. It is likely that 90% of the connections in the pool are actually dead.

[Sagar2323]

My application is on gps and 6000 devices are trying to connect at same time .
Every device is sending data at the interval of 10 seconds .
So i need frequent connection for it.
So that is why i have set it to high.
So in the case of frequent connection need what is the best configuration of HIKARI?

[brettwooldridge]

@Sagar2323 This is ultimately an architecture/design question, and typically beyond the scope of answers provided here, but I'll give you my recommendations.

6000 devices inserting data every 10 seconds comes out to 600 inserts per second. MySQL is capable of far beyond that, even on a single connection.

See this answer here, where I provided sample code that demonstrates inserting 10,000 rows into MySQL on a single connection in ~200ms.

I would have each device enqueue its sent data into a MPSC queue (Multiple Producer Single Consumer). Then, a single separate processing thread runs every few seconds, dequeues data from the queue and performs batch inserts.

A single processing thread, using batched inserts, should easily be able to keep up with the devices.

👉 This pattern also nearly eliminates lock contention on the table being inserted into. If you try to insert data from hundreds of threads, you are going to spend 90% of your time blocked waiting for locks, and slow everything down.

👉 You probably want to catch and log SQLException inside the main processing loop.

[Sagar2323]

@brettwooldridge

Environment

HikariCP version: HikariCP-2.7.8
JDK version : 1.8.0_121
Database : MySQL
Driver version : mysql-connector-java-5.1.40-bin

Server Environment

Server Configuration:
Memory: 36 GB
CPU Sockets: 6
Virtual Processors: 24

MYSQL Configuration:
interactive_timeout: 129600
wait_timeout: 129600
innodb_buffer_pool_size: 16G

We have used two pools.

pool1 is used where frequent connection required.
pool1 configuration is as below:
jdbcUrl=jdbc:mysql://127.0.0.1:3307/gps?&useSSL=false&failOverReadOnly=false
maximumPoolSize=30
minimumIdle=20
idleTimeout=120000
connectionTimeout=120000
dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.prepStmtCacheSqlLimit=2048

pool2 is used where queries takes more time to execute
pool2 configuration is as below:
jdbcUrl=jdbc:mysql://127.0.0.1:3307/gps?useSSL=false
maximumPoolSize=200
minimumIdle=5
idleTimeout=60000
connectionTimeout=300000
dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.prepStmtCacheSqlLimit=2048

For optimum performance of server we have used maximumPoolSize as 30 for pool 1.
Server run properly for 12 hours with below kind of Thread Awaiting Connection debug.

We have printed debug every 10 seconds for thread awaiting connection that is as below.

Thread Awaiting Connection - 178
Thread Awaiting Connection - 96
Thread Awaiting Connection - 78
Thread Awaiting Connection - 78
Thread Awaiting Connection - 0
Thread Awaiting Connection - 0
Thread Awaiting Connection - 200
Thread Awaiting Connection - 0

But after 12 hours thread awaiting connection debug looks as below.

Thread Awaiting Connection - 2168
Thread Awaiting Connection - 2127
Thread Awaiting Connection - 1945
Thread Awaiting Connection - 1889
Thread Awaiting Connection - 1901

Thread Awaiting situation was happened for more than 4 minutes continuously.
After that i am geting createTimeOutException when i ask for connection.

So i want to know is there still a problem in HIKARI configuration?
What wrong i have done here?

[brettwooldridge]

@Sagar2323 This indicates that queries are taking a long time.

I would recommend setting up monitoring using DropWizard Metrics or Prometheus (both supported by HikariCP). The metric you should be looking at is "connection usage", which will tell you how long (average) connections are out of the pool (in-use).

You can investigate logging slow queries with p6spy, or even the MySQL driver itself.

You need to find the slow queries and possibly add appropriate indexes to speed them up, or even rewrite the queries.

My company has target query times of 50ms or less, but it took a lot of work to get there.

[brettwooldridge]

@Sagar2323 Looking at my previous suggestion, I have to wonder whether you have changed anything with respect to your architecture?

If you are still trying to insert in the same table from many threads at the same time, there is nothing I can do to help you. Such an approach is fundamentally unscalable due to lock contention.