I experience degrading performance when executing
many small database transactions
such as in the following:
Code:
for (int i=0;i<5000;i++) {
Transaction tx = null;
Person p = new Person("Person" + i);
try {
tx = hibernateSession.beginTransaction();
hibernateSession.save(p);
tx.commit();
} catch (Exception e) {
if (tx != null)
tx.rollback();
System.err.println("Insert failed. " + e.toString());
}
if ((i%100)==0) ... // Performance measuring code
}
The first 100 inserts take approx. 50ms, then the time
increases, up to approx. 1300ms for the last 100 inserts.
The bulk of the time is spent in the line "tx.commit()".
>95% of the CPU time is spent by the Java
interpreter (i.e. not by mysqld).
What could be wrong with the above code / with my
configuration? I'd expect that all inserts should take
approximately the same time.
I use Hibernate 3.1.3, MySQL 5.0.18 (same problem
with HSQL). Person is a simple table with only id and
name, there are no other tables. The above example
is an extract of a much larger application - I'm aware
that in the above example a single large transaction
would suffice.
The debug log mentions a number of other Person entities
during each commit (in addition the single one that
actually should be inserted), but I do not know if this might
be related to the problem:
6 Mrz 2006 13:01:29 DEBUG: listing entities:
26 Mrz 2006 13:01:29 DEBUG: Person{name=Person136, id=41640}
26 Mrz 2006 13:03:54 DEBUG: Person{name=Person824, id=42666}
...
26 Mrz 2006 13:01:29 DEBUG: more......
Thanks in advance and best regards,
Guido