EclipseLink very slow on inserting data

Question

I'm using the latest EclipseLink version with MySQL 5.5 (table type InnoDB). I'm inserting about 30900 records (which could be also more) at a time. The problem is, that the insert performance is pretty poor: it takes about 22 seconds to insert all records (compared with JDBC: 7 seconds). I've read that using batch writing should help - but doesn't!?

@Entity
public class TestRecord {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long id;

    public int test;
}

The code to insert the records:

factory = Persistence.createEntityManagerFactory("xx_test");
EntityManager em = factory.createEntityManager();

em.getTransaction().begin();

for(int i = 0; i < 30900; i++) {
    TestRecord record = new TestRecord();
    record.test = 21;
    em.persist(record);
}

em.getTransaction().commit();
em.close();

And finally my EclipseLink configuration:

<persistence-unit name="xx_test" transaction-type="RESOURCE_LOCAL">
    <class>com.test.TestRecord</class>

    <properties>
        <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
        <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/xx_test" />
        <property name="javax.persistence.jdbc.user" value="root" />
        <property name="javax.persistence.jdbc.password" value="test" />

        <property name="eclipselink.jdbc.batch-writing" value="JDBC" />
        <property name="eclipselink.jdbc.cache-statements" value="true"/>   

        <property name="eclipselink.ddl-generation.output-mode" value="both" />
        <property name="eclipselink.ddl-generation" value="drop-and-create-tables" />

        <property name="eclipselink.logging.level" value="INFO" />
    </properties>
</persistence-unit>

What I'm doing wrong? I've tried several setting, but nothing seems the help. Thanks in advance for helping me! :)

-Stefan

Another thing is to add ?rewriteBatchedStatements=true to the data URL used by the connector.

This caused executing about 120300 inserts down to about 30s which was about 60s before.

score 8 · Answer 1 · edited Jun 20 '12 at 11:02

8

JDBC Batch writing improves performance drastically; please try it

Eg: property name="eclipselink.jdbc.batch-writing" value="JDBC"

edited Jun 20 '12 at 11:02

Chuck Norris

15,207
15
92
123

answered Jun 20 '12 at 07:37

Hilal

81
1
1

score 5 · Accepted Answer · answered Jun 23 '11 at 13:03

5

@GeneratedValue(strategy = GenerationType.IDENTITY)

Switch to TABLE sequencing, IDENTITY is never recommended and a major performance issue.

See, http://java-persistence-performance.blogspot.com/2011/06/how-to-improve-jpa-performance-by-1825.html

I seem to remember that MySQL may not support batch writing without some database config as well, there was another post on this, I forget the url but you could probably search for it.

answered Jun 23 '11 at 13:03

James

17,965
11
91
146

Thanks a lot! Now it takes about 9 seconds, which should be fine, hopefully. – Stefan Jun 27 '11 at 19:07

score 0 · Answer 3 · answered Jun 21 '11 at 19:57

Probably the biggest difference besides the mapping conversion is the caching. By default EclipseLink is placing each of the entities into the persistence context (EntityManager) and then during the finalization of the commit it needs to add them all to the cache.

One thing to try for now is:

measure how long an em.flush() call takes after the loop but before the commit. Then if you want you could call em.clear() after the flush so that the newly inserted entities are not merged into the cache.

Doug

New test: without `em.flush()` the insert takes about 18s. With calling `em.flush()` (takes 17s) right before commit the insert takes also about 18s. Adding `em.clear()` doesn't help either. — Stefan, Jun 22 '11 at 11:31

EclipseLink very slow on inserting data

3 Answers3