MySQL Limited Connection Pool Causing Responder Shutdown

Document ID : KB000072937
Last Modified Date : 12/03/2018
Show Technical Document Details
Issue:
I have a custom VSM, which calls to a local MySQL database instance. It inevitably runs into connection errors past 60 transactions per second, which cause the responder to go down. 

STACK TRACE 

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. 
at sun.reflect.GeneratedConstructorAccessor109.newInstance(Unknown Source) 
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) 
at java.lang.reflect.Constructor.newInstance(Unknown Source) 
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) 
at com.mysql.jdbc.Util.getInstance(Util.java:386) 
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014) 
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988) 
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974) 
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) 
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2367) 
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2288) 
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:822) 
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) 
at sun.reflect.GeneratedConstructorAccessor96.newInstance(Unknown Source) 
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) 
at java.lang.reflect.Constructor.newInstance(Unknown Source) 
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) 
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:404) 
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317) 
at com.itko.lisa.test.JDBCDataSet.openConnection(JDBCDataSet.java:203) 
at com.itko.lisa.test.JDBCDataSet.openResultSet(JDBCDataSet.java:278) 
at com.itko.lisa.test.JDBCDataSet.getNextRecord(JDBCDataSet.java:349) 
at com.itko.lisa.test.JDBCDataSet.getRecord(JDBCDataSet.java:314) 
at com.itko.lisa.test.DataSetManager.getDataSetResponse(DataSetManager.java:528) 
at com.itko.lisa.test.DataSetManager.processDataSet(DataSetManager.java:551) 
at com.itko.lisa.test.DataSetManager.processUnsynchedDataSets(DataSetManager.java:409) 
at com.itko.lisa.test.DataSetManager.processDataSetList(DataSetManager.java:172) 
at com.itko.lisa.test.TestNode.modifyVariables(TestNode.java:1535) 
at com.itko.lisa.test.TestNode.executeNode(TestNode.java:939) 
at com.itko.lisa.test.TestCase.execute(TestCase.java:1288) 
at com.itko.lisa.test.TestCase.execute(TestCase.java:1203) 
at com.itko.lisa.test.TestCase.executeNextNode(TestCase.java:1188) 
at com.itko.lisa.test.TestCase.executeTest(TestCase.java:1129) 
at com.itko.lisa.coordinator.Instance.run(Instance.java:204) 
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.

Below 60 TPS, there aren't any issues. I was wondering if there were any other options I could try or if there's anything I could be missing to help mitigate/solve this issue. I've already tried raising the max_connections of the MySQL database to over 1000, and I've also added this property to the local.properties file: lisa.vse.max.hard.errors=-1 so that the responder won't immediately shut down once these errors start occurring. 
 
Environment:
All supported DevTest Environments
Cause:
DB connections are depending on the third party jdbc driver jars we use and can't be controlled with DevTest. 

 
Resolution:
The one recommendation is to try the "Keep connection open" in the JDBC steps. 

* Keep Connection Open: 
---------------------------------- 
If this option is selected on the JDBC Step, the database connection that is opened the first time that the step executes is cached. That database connection is then closed when garbage collection happens for the step. If "Keep Connection Open" is not selected, the connection is closed each time that the step executes 

* Use Connection Pool: 
-------------------------------- 
When the 'Use Connection Pool' option is checked, what will happen is that the VSE that is executing the step will check for the existence of a connection pool based on the following 4 items 

Class Driver 
JDBC Connect String 
Username 
Password 

If a connection pool is not available, it will create a new connection pool using the same four items that were defined in the step. 

You should be able to use the connection pool option in the JDBC step, but if you are running into any issue, what we would recommend is for you to reach out to your DBA, to find out if you have run out of connections in the database, and to allow the VSE to create more connections to support connection pooling. 

You might also want to check with your end users who are sending transactions to make sure they are using a generic SQL account to make better use of the connection pool. If users are using their own accounts for the JDBC step and not a generic account, that will increase the connection count for the database, and most likely cause additional overhead from the database to support the execution of your requests. Your DBA should also be able to help you identify if this is the case