This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes Object Relational Mapping and the fly likes Perf Tests failing badly on mysql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Perf Tests failing badly on mysql" Watch "Perf Tests failing badly on mysql" New topic
Author

Perf Tests failing badly on mysql

Raja Nagendra Kumar
Greenhorn

Joined: Jun 14, 2008
Posts: 27
I am using Hibernate for our java product. We have number of unit tests which all run perfectly as a single user mode. However when we run these tests using junitperf with n number of users and m iterations of each user, many tests fail.

The failure rate is more than 50% on mysql on windows and is < 5% on postgre on windows.

Most prevailing Errors on mysql are

[java] at java.lang.Thread.run(Unknown Source)
[java] Caused by: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
[java] at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:647)
[java] at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
[java] at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
[java] at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
[java] ... 31 more
[java] org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
[java] at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
[java] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
[java] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
[java] at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
[java] at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
[java] at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:143)
[java] at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)


and on Postgre is

org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
[java] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
[java] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
[java] at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
[java] at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
[java] at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
[java] at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:218)
[java] at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2159)
[java] at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2595)
[java] at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)
[java] at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
[java] at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
[java] at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
[java] at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
[java] at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
[java] at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
[java] at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
[java] at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
[java] at com.tejasoft.persist.jpa.hibernate.HibernateUtil.persist(HibernateUtil.java:109)
[java] at com.tejasoft.persist.jpa.CRUDOperation.persist(CRUDOperation.java:49)
[java] at com.tejasoft.persist.jpa.CRUDOperation.save(CRUDOperation.java:21)
[java] at com.tejasoft.dialer.model.bdo.test.TestUser.testRetriveAndInsert(TestUser.java:524)
[java] at sun.reflect.GeneratedMethodAccessor80.invoke(Unknown Source)
[java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
[java] at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
[java] at junit.extensions.TestDecorator.run(TestDecorator.java:32)
[java] at junit.extensions.RepeatedTest.run(RepeatedTest.java:30)
[java] at com.clarkware.junitperf.ThreadedTest$TestRunner.run(Unknown Source)
[java] at java.lang.Thread.run(Unknown Source)
[java] Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into
TBL_USERS (name, loginID, password, email, zip, pk_user) values (, nagkumar15885
, abc123, e@e.com, , 177068) was aborted. Call getNextException to see the cause.
[java] at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2537)
[java] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1328)
[java] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:351)

Another observation is that postgre is completing the perf tests in 20 min and where as mysql is not able to complete the same tests ever after 3 hours..

Every where I read mysql on windows is more stable and more performing than postgre.. However my results are totally contrary.

Could any one point me why such errors and possible issues.


Regards,
Nagendra


Raja Nagendra Kumar,
C.T.O, www.tejasoft.com
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

Unfortunately, the results you are seeing can be caused by many issue, not even related to the specific database that you are using. And in some cases can be directly related to the database dialect you are using.

1. It is possible the code is written in such a way that there are long Sessions, or non thread safe code. Without seeing the specific code where things hang, it would be impossible for us to know.

2. Personally I know of one type of insert/update query with I think subqueries in the Where or From clause that works in Postgres but not in MySQL. We had that on the www.rhq-project.org open source project that I work on.

I am sure there are other possible reasons that I can't think of, but it is not necessarily pointing out that MySQL is not as stable as Postgres. I actually feel both are great stable products on Windows.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16019
    
  20


Lock wait timeout exceeded; try restarting transaction


That sounds like you're stacking up requests faster than they can be serviced. Not unexpected in a stress test, but if it kicks in too early, some tuning is in order - possibly a larger connection pool size.

I've seen some really horrible performance on MySQL databases with lots of foreign key interrelationships, but since they were expecting the network administrator to sideline as a DBA, I'm not sure the server was optimized for it.

I'd love to see what PostgreSQL had to say by way of comparison, but unfortunately, the details are in a chained exception that wasn't displayed.


Customer surveys are for companies who didn't pay proper attention to begin with.
Raja Nagendra Kumar
Greenhorn

Joined: Jun 14, 2008
Posts: 27
Hi

Thank you for giving me enought hints, I shall try do tests based on this..

I have used same parameters for both MySql and Postgre and they are..

<property name="hibernate.connection.autoReconnect">true</property>
<property name="hibernate.connection.pool_size">4</property>
<property name="hibernate.c3p0.max_statement">50</property>
<property name="hibernate.c3p0.testConnectionOnCheckout">false
</property>
<property name="hibernate.c3p0.initial_pool_size">3</property>
<property name="hibernate.c3p0.min_size">3</property>
<property name="hibernate.c3p0.max_size">80</property>
<property name="hibernate.c3p0.min_pool_size">3</property>
<property name="hibernate.c3p0.max_pool_size">20</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.maxStatementsPerConnection">100
</property>

Here I have both hibernate.connection.pool_size and hibernate.c3p0.max_size, which one would it take... 80 for hibernate.c3p0.max_size seems to be a good number..

I shall rerun the tests on postgre and provide more info.

Regards,
Nagendra
Raja Nagendra Kumar
Greenhorn

Joined: Jun 14, 2008
Posts: 27
Hi Mark,

>Unfortunately, the results you are seeing can be caused by many issue, not even related to the specific database that you are using. And in some cases can be directly related to the database dialect you are using.

I am using custom dialect

package com.tejasoft.persist.jpa.hibernate;

import org.hibernate.dialect.MySQLDialect;

import java.sql.Types;

public final class TejaSQLDialect extends MySQLDialect
{
public TejaSQLDialect()
{
super();
registerColumnType(Types.BIT, "tinyint(1)");
}
}


>1. It is possible the code is written in such a way that there are long Sessions, or non thread safe code. Without seeing the specific code where things hang, it would be impossible for us to know.

If such is the case of bad thread safe code, I should see a similar behaviours for both db tests.


>2. Personally I know of one type of insert/update query with I think subqueries in the Where or From clause that works in Postgres but not in MySQL. We had that on the www.rhq-project.org open source project that I work on.

o.k, Is there a way to avoid such clauses being generated by hibernate by way using standandard jap annotations.

Regards,
Nagendra
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Perf Tests failing badly on mysql