MySQL Database Server

MySQL Performance Summary

The following charts show the performance of MySQL (in green) relatively to other JPA/DBMS combinations (in gray).

Position #1 on the X-Axis (the fastest) is about 100 times faster than position #33 (the slowest).
In the chart below the Y-Axis represents result score (maximum 100) in logarithmic scale - higher is better:
JPA performance benchmark comparision Hibernate, EclipseLink, OpenJPA, DataNucleus, ObjectDB
In the chart below the Y-Axis represents result score (maximum 100) in linear scale - higher is better:
JPA performance benchmark comparision Hibernate, EclipseLink, OpenJPA, DataNucleus, ObjectDB


The configurations in which MySQL has been tested are listed in the following table:

 Database + JPA ProviderNormalized Score
100 - Best,  0 - Worst
Final Position
1 - Best,  33 - Worst
Failed Tests
1OpenJPA with MySQL server2.826  (out of 33)0
2Hibernate with MySQL server2.728  (out of 33)0
3EclipseLink with MySQL server2.329  (out of 33)0
4DataNucleus with MySQL server2.129  (out of 33)5

As shown above, MySQL was faster when used with Hibernate or EclipseLink as a JPA provider.

About MySQL

MySQL is probably the most popular open source database today. The MySQL database was developed by MySQL AB and first released in 1995. It has been acquired by Sun Microsystems in 2008. Now MySQL is owned by Oracle Corporation.

MySQL is available under a dual license. It is free under the GPL license for in house and open source projects. A commercial license is required in other cases.

In this benchmark MySQL version 5.6.4 (which was released in December 2011) has been tested.

The InnoDB storage engine was used in all the tests. To avoid flush to the disk on every transaction by InnoDB (which affects the performance of insert/update/delete mainly in small transactions) the innodb_flush_log_at_trx_commit configuration option has been set to 0.

Solved Problems and Issues

The stack traces below demonstrate some exceptions that have been thrown when using MySQL database in previous runs of this benchmark and have been fixed by upgrading software and setting.

org.apache.openjpa.persistence.PersistenceException: Communications link failure

Last packet sent to the server was 0 ms ago.
FailedObject: SELECT o FROM Person o WHERE o.id >= :firstId [java.lang.String]
	at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4827)
	at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4787)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:987)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.getConnection(JDBCStoreManager.java:286)
	at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:385)
	at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:354)
	at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94)
	at org.apache.openjpa.lib.rop.EagerResultList.(EagerResultList.java:34)
	at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1246)
	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1005)
	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:861)
	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:792)
	at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542)
	at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:288)
	at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:302)
	at org.jpab.Test.retireveEntities(Test.java:383)
	at org.jpab.Test.doAction(Test.java:250)
	at org.jpab.Runner$RetrieveAction.run0(Runner.java:540)
	at org.jpab.Runner$TestAction.run(Runner.java:507)
	at java.lang.Thread.run(Thread.java:619)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2120)
	at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:723)
	at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
	at sun.reflect.GeneratedConstructorAccessor15.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
	at org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:96)
	at org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:91)
	at org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:139)
	at org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:112)
	at org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:139)
	at org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:325)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:998)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:983)
	... 17 more
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.

This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable. 

For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required.

For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271).
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
	at com.mysql.jdbc.MysqlIO.(MysqlIO.java:335)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2043)
	... 33 more
Caused by: java.net.BindException: Address already in use: connect
	at java.net.PlainSocketImpl.socketConnect(Native Method)
	at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
	at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
	at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
	at java.net.Socket.connect(Socket.java:519)
	at java.net.Socket.connect(Socket.java:469)
	at java.net.Socket.(Socket.java:366)
	at java.net.Socket.(Socket.java:209)
	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:253)
	at com.mysql.jdbc.MysqlIO.(MysqlIO.java:284)
	... 34 more
org.apache.openjpa.persistence.RollbackException: Communications link failure

Last packet sent to the server was 0 ms ago.
	at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:583)
	at org.jpab.Test.persist(Test.java:216)
	at org.jpab.Test.persist(Test.java:199)
	at org.jpab.Runner$PersistAction.run0(Runner.java:528)
	at org.jpab.Runner$TestAction.run(Runner.java:507)
	at java.lang.Thread.run(Thread.java:619)
Caused by:  org.apache.openjpa.persistence.PersistenceException: Communications link failure

Last packet sent to the server was 0 ms ago.
	at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4827)
	at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4787)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:110)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:62)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:987)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.retainConnection(JDBCStoreManager.java:272)
	at org.apache.openjpa.kernel.DelegatingStoreManager.retainConnection(DelegatingStoreManager.java:164)
	at org.apache.openjpa.kernel.BrokerImpl.retainConnection(BrokerImpl.java:3914)
	at org.apache.openjpa.kernel.BrokerImpl.beginStoreManagerTransaction(BrokerImpl.java:1395)
	at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2097)
	at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2037)
	at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1955)
	at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
	at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1479)
	at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:925)
	at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:559)
	... 5 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2120)
	at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:723)
	at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
	at org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:96)
	at org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:91)
	at org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:139)
	at org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:112)
	at org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:139)
	at org.apache.openjpa.jdbc.schema.DataSourceFactory$DefaultsDataSource.getConnection(DataSourceFactory.java:325)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connectInternal(JDBCStoreManager.java:998)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.connect(JDBCStoreManager.java:983)
	... 16 more
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.

This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable. 

For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required.

For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271).
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
	at com.mysql.jdbc.MysqlIO.(MysqlIO.java:335)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2043)
	... 33 more
Caused by: java.net.BindException: Address already in use: connect
	at java.net.PlainSocketImpl.socketConnect(Native Method)
	at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
	at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
	at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
	at java.net.Socket.connect(Socket.java:519)
	at java.net.Socket.connect(Socket.java:469)
	at java.net.Socket.(Socket.java:366)
	at java.net.Socket.(Socket.java:209)
	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:253)
	at com.mysql.jdbc.MysqlIO.(MysqlIO.java:284)
	... 34 more
javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.1.v20100817-r8050): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`jpab8441120340`.`node`, CONSTRAINT `FK_NODE_CHILD1_ID` FOREIGN KEY (`CHILD1_ID`) REFERENCES `node` (`ID`))
Error Code: 1451
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:102)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:63)
	at org.jpab.Test.doAction(Test.java:272)
	at org.jpab.Runner$RemoveAction.run0(Runner.java:578)
	at org.jpab.Runner$TestAction.run(Runner.java:508)
	at java.lang.Thread.run(Thread.java:619)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.1.v20100817-r8050): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`jpab8441120340`.`node`, CONSTRAINT `FK_NODE_CHILD1_ID` FOREIGN KEY (`CHILD1_ID`) REFERENCES `node` (`ID`))
Error Code: 1451
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeJDK12BatchStatement(DatabaseAccessor.java:830)
	at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatchedStatements(ParameterizedSQLBatchWritingMechanism.java:139)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.writesCompleted(DatabaseAccessor.java:1632)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.commitTransaction(DatabaseAccessor.java:399)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicCommitTransaction(AbstractSession.java:504)
	at org.eclipse.persistence.sessions.server.ClientSession.basicCommitTransaction(ClientSession.java:155)
	at org.eclipse.persistence.internal.sessions.AbstractSession.commitTransaction(AbstractSession.java:686)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitTransaction(UnitOfWorkImpl.java:1581)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitTransaction(RepeatableWriteUnitOfWork.java:559)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitTransactionAfterWriteChanges(UnitOfWorkImpl.java:1604)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:207)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1148)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:84)
	... 5 more
Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`jpab8441120340`.`node`, CONSTRAINT `FK_NODE_CHILD1_ID` FOREIGN KEY (`CHILD1_ID`) REFERENCES `node` (`ID`))
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1693)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1108)
	at org.eclipse.persistence.platform.database.MySQLPlatform.executeBatch(MySQLPlatform.java:198)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeJDK12BatchStatement(DatabaseAccessor.java:817)
	... 17 more