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:
In the chart below the Y-Axis represents result score (maximum 100) in linear scale - higher is better:
The configurations in which MySQL has been tested are listed in the following table:
| Database + JPA Provider | Normalized Score 100 - Best, 0 - Worst | Final Position 1 - Best, 33 - Worst | Failed Tests | |
|---|---|---|---|---|
| 1 | OpenJPA with MySQL server | 2.8 | 26 (out of 33) | 0 |
| 2 | Hibernate with MySQL server | 2.7 | 28 (out of 33) | 0 |
| 3 | EclipseLink with MySQL server | 2.3 | 29 (out of 33) | 0 |
| 4 | DataNucleus with MySQL server | 2.1 | 29 (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
