MySQL Limited Connection Pool Causing Responder Shutdown
search cancel

MySQL Limited Connection Pool Causing Responder Shutdown

book

Article ID: 72937

calendar_today

Updated On:

Products

CA Application Test CA Continuous Application Insight (PathFinder) Service Virtualization

Issue/Introduction

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 releases.

Cause

DB connections are depending on the third party jdbc driver jars we use and can't be controlled with DevTest. 

 

Resolution

Recommendation is to choose 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. 

Should be able to use the connection pool option in the JDBC step, but if running into any issue, what is recommended is for to reach out to your DBA, to find out if the database has run out of connections and to allow the VSE to create more connections to support connection pooling. 

Also want to check with the 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 

Powered by Wolken Software