mysql "communications link failure" under heavy load - hibernate

Question

I try to test a program (based on hibernate) with many threads which access the mysql database to create workload. the threads have connection to the database and are able to do CRUD operations. It works fine with around 100 threads but with more than 100 threads I get a bunch of exceptions.
I don’t know if it is a hibernate issue, a Mysql issue or just too much workload (but I think it should be possible to handle more than 100 threads).

Hope someone can tell me how I can run more threads without getting the exceptions.

    Exception in thread "Thread-1" org.hibernate.exception.JDBCConnectionException: Could not open connection
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:221)
at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157)
at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67)
at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160)
at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1426)
at com.sedicoloadtest2.UpdateRandomThread2.run(UpdateRandomThread2.java:24)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:673)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1084)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2483)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2516)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2301)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor10.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
at org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl.getConnection(DriverManagerConnectionProviderImpl.java:204)
at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292)
at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214)
... 6 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3052)
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:597)
... 21 more

I already did some research. The common problems that causes the problem are described here:
https://stackoverflow.com/questions/2983248/com-mysql-jdbc-exceptions-jdbc4-communicationsexception-communications-link-fai

But I think the answers from this link don’t make sense in my case because I got a connection to the database. I only got the problem with many threads operating at the same time.

I changed the following MySQL variables with no effect:

• max_connections = 100000
• max_connect_errors = 9999
• connect_timeout = 1000
• wait_timeout = 99999
• interactive_timeout = 99999
• net_write_timeout = 1000

This is the class which creates and starts the threads:

    public class ReadUpdateTest {

public static void main(String[] args) throws InterruptedException {

    int totalDBRows;
    int totalNumberOfClients = 100;
    int numberOfUpdateClients = 250;

    List<Thread> threadlist = new ArrayList<Thread>();
    Deque customers = new ArrayDeque();

    totalDBRows = (int) MainStatisch.setNumberOfTuplesInDB();


    for (int i = 0; i < numberOfUpdateClients; i++) {
        int randomId = MainStatisch.getRandomInt(1, totalDBRows);
        customers.add(MainStatisch.createRandomCustomer(randomId));
    }


    for (int i = 1; i <= numberOfUpdateClients; i++) {
        threadlist.add(new Thread(new UpdateRandomThread2("UpdateThread - " + i, (Customer) customers.pollFirst())));
    }


    Collections.shuffle(threadlist);


    // ################## start threads ##################

    System.out.println("start: \n");
    long queryStart;
    long queryEnd;
    queryStart = System.currentTimeMillis();

    for (Thread thread : threadlist) {
        thread.start();
    }

    for (Thread thread : threadlist) {
        thread.join();
    }

    queryEnd = System.currentTimeMillis();
    long queryTime = queryEnd - queryStart;


    System.out.println("Time " + queryTime);
}

}

This is my thread:

   class UpdateRandomThread2 implements Runnable {
String threadname;
Customer updateCustomer;

UpdateRandomThread2(String threadname, Customer customer) {
    this.threadname = threadname;
    this.updateCustomer = customer;
}

public void run() {


    Session session = HibernateUtil.getSessionFactory().openSession();
    session.beginTransaction();

    com.sedicoloadtest2.MainStatisch.updateRandomCustomer(session, updateCustomer);

    session.getTransaction().commit();
    session.close();

}
}

This is the sessionFactory:

    public class HibernateUtil {

private static final SessionFactory sessionFactory = buildSessionFactory();

private static SessionFactory buildSessionFactory() {
    Configuration configuration = configure();
    ServiceRegistry serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties()).buildServiceRegistry();
    SessionFactory factory = configuration.buildSessionFactory(serviceRegistry);
    return factory;
}

private static Configuration configure() {
    Configuration configuration = SedicoBootstrapper.getHibernateConfiguration()

        .setProperty("format.sql", "true")
        .setProperty("hibernate.current_session_context_class", "thread")
        .setProperty("hibernate.c3p0.min_size", "1")
        .setProperty("hibernate.c3p0.max_size", "1000")
        .addAnnotatedClass(Customer.class);

    return configuration;
}

public static SessionFactory getSessionFactory() {
    return sessionFactory;
}

}