Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Perf Tests failing badly on mysql

 
Raja Nagendra Kumar
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Tim Holloway
Saloon Keeper
Pie
Posts: 18164
53
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Raja Nagendra Kumar
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic