All geek questions in one place

EclipseLink insert is very slow

I am trying to optimize a JPA implementation using EclipseLink. I added batch operations to it. But it still takes a long time to make 50,000 inserts. It takes more than 10 times the time it takes to perform the same insert using raw SQL with JDBC.

To make sure that batch operations actually work, I used Wireshark to check my packages and did not use batch inserts.

Here is one of the insert packages:

enter image description here

It does not:

INSERT INTO ENTITYCLASSTEST (LASTNAME, NAME) VALUES ('sfirosijfhgdoi 0', 'dsufius0'), ('sfirosijfhgdoi 0', 'dsufius0'), ('sfirosijfhgdoi 0', 'dsufius0'), ('sfirosijfhgdoi 0', 'dsufius0')... and so on

I expected it to do the same as above, but it inserts one line into a package, not multiple lines per package.

Here is my Entity class:

 @Entity public class EntityClassTest implements Serializable { private static final long serialVersionUID = 1L; @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String name; private String lastname; public EntityClassTest() { } public EntityClassTest(Long id, String name, String lastname) { this.id = id; this.name = name; this.lastname = lastname; } public EntityClassTest(String name, String lastname) { this.name = name; this.lastname = lastname; } public Long getId() { return id; } public String getName() { return name; } public String getLastName() { return lastname; } public void setId(Long id) { this.id = id; } public void setName(String name) { this.name = name; } public void setLastName(String lastname) { this.lastname = lastname; } @Override public int hashCode() { int hash = 0; hash += (id != null ? id.hashCode() : 0); return hash; } @Override public boolean equals(Object object) { // TODO: Warning - this method won't work in the case the id fields are not set if (!(object instanceof EntityClassTest)) { return false; } EntityClassTest other = (EntityClassTest) object; if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) { return false; } return true; } @Override public String toString() { return "database.EntityClassTest [id=" + id + " ]"; } }

And here is my persist method, which gets a List and stores all the objects inside.

 public void insertListToTable(final String persistenceUnit, final List list) throws SQLException { final EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory(persistenceUnit); final EntityManager entityManager = entityManagerFactory.createEntityManager(); final EntityTransaction transaction = entityManager.getTransaction(); try { final int listSize = list.size(); transaction.begin(); for (int i = 0; i<listSize; i++) { //Object object : list) { final Object object = list.get(i); entityManager.persist(object); if ( i % 500 == 0 ) { //500, same as the JDBC batch size defined in the persistence.xml //flush a batch of inserts and release memory: entityManager.flush(); entityManager.clear(); } } transaction.commit(); } catch(Exception e) { if (transaction != null) { transaction.rollback(); } throw new SQLException(e.getMessage()); } finally { entityManager.close(); } }

And my persistence.xml , where I set 500 as the batch value, file:

 <?xml version="1.0" encoding="UTF-8"?> <persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"> <persistence-unit name="ExternalServer" transaction-type="RESOURCE_LOCAL"> <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> <!-- List of Entity classes --> <class>model.EntityClassTest</class> <properties> <property name="javax.persistence.jdbc.url" value="jdbc:mysql://myServer:3306/testdb?zeroDateTimeBehavior=convertToNull"/> <property name="javax.persistence.jdbc.user" value="testdbuser"/> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/> <property name="javax.persistence.jdbc.password" value="myPassword"/> <property name="javax.persistence.schema-generation.database.action" value="create"/> <!-- Weaving --> <property name="eclipselink.weaving" value="static"/> <!-- SQL dialect / Database type --> <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/> <property name="eclipselink.target-database" value="MySQL"/> <!-- Tell the JPA provider to, by default, create the table if it does not exist. --> <property name="javax.persistence.schema-generation.database.action" value="create"/> <!-- No logging (For development change the value to "FINE") --> <property name="eclipselink.logging.level" value="OFF"/> <!-- Enable batch writing --> <property name="eclipselink.jdbc.batch-writing" value="JDBC"/> <!-- Batch size --> <property name="eclipselink.jdbc.batch-writing.size" value="500"/> </properties> </persistence-unit> </persistence>

So my question is: why is this not a batch insert? I believe that EclipseLink is configured to do this from what I read on the EclipseLink website, and here too.

//////////////////////////// EDIT /////////////////// /////

As suggested by Chris, I changed this @GeneratedValue(strategy = GenerationType.IDENTITY) to @GeneratedValue(strategy = GenerationType.SEQUENCE) in my EntityClassTest and re-run the test and the packets are sent as before (for example, the image I posted above ) Therefore, I did not fix my problem, I'm afraid.

//////////////////////////// EDIT 2 //////////////////// ////

I changed the logging level in persistence.xml to FINEST , as shown below.

 <property name="eclipselink.logging.level" value="FINEST"/>

And so the magazine is created. I put it in pastebin because it is quite long.

http://pastebin.com/rKihCKMW

You seem to be calling Execute query InsertObjectQuery quite a few times.

////////////////////////// EDIT 3 ////////////////// ////

Here is the version for each component that I use.

 +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.6.12 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.12-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Win64 | +-------------------------+------------------------------+ Netbeans 8.0 EclipseLink (JPA 2.1) mysql-connector-java-5.1.24.jar

////////////////////////// EDIT 4 //////////////////// ////

After CuriousMind's answer, I edited the EntityClassTest id EntityClassTest for:

  @Id @GeneratedValue(strategy = GenerationType.AUTO, generator="id-seq-gen") @SequenceGenerator( name="id-seq-gen", sequenceName="ID_SEQ_GEN", allocationSize=500 ) private Long id;

But this did not solve my problem, I still get one single insert for each package (as described above) and in the EclipseLink log, which I get:

 [EL Fine]: sql: 2014-10-19 06:44:02.608--ClientSession(824177287)--Connection(1674390738)--Thread(Thread[main,5,main])--SELECT LAST_INSERT_ID() [EL Finest]: sequencing: 2014-10-19 06:44:02.608--UnitOfWork(1985011414)--Thread(Thread[main,5,main])--assign sequence to the object (1.251 -> database.EntityClassTest [id=null ]) [EL Finest]: query: 2014-10-19 06:44:02.608--UnitOfWork(1985011414)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(database.EntityClassTest [id=null ]) [EL Finest]: query: 2014-10-19 06:44:02.608--ClientSession(824177287)--Thread(Thread[main,5,main])--Execute query ValueReadQuery(name="ID_SEQ_GEN" sql="SELECT LAST_INSERT_ID()") [EL Fine]: sql: 2014-10-19 06:44:02.608--ClientSession(824177287)--Connection(1674390738)--Thread(Thread[main,5,main])--INSERT INTO ENTITYCLASSTEST (LASTNAME, NAME) VALUES (?, ?) bind => [sfirosijfhgdoi 2068, dsufius1034] [EL Fine]: sql: 2014-10-19 06:44:02.608--ClientSession(824177287)--Connection(1674390738)--Thread(Thread[main,5,main])--SELECT LAST_INSERT_ID() [EL Finest]: sequencing: 2014-10-19 06:44:02.608--UnitOfWork(1985011414)--Thread(Thread[main,5,main])--assign sequence to the object (1.252 -> database.EntityClassTest [id=null ]) [EL Finest]: query: 2014-10-19 06:44:02.608--UnitOfWork(1985011414)--Thread(Thread[main,5,main])--Execute query InsertObjectQuery(database.EntityClassTest [id=null ]) [EL Finest]: query: 2014-10-19 06:44:02.608--ClientSession(824177287)--Thread(Thread[main,5,main])--Execute query ValueReadQuery(name="ID_SEQ_GEN" sql="SELECT LAST_INSERT_ID()") [EL Fine]: sql: 2014-10-19 06:44:02.608--ClientSession(824177287)--Connection(1674390738)--Thread(Thread[main,5,main])--INSERT INTO ENTITYCLASSTEST (LASTNAME, NAME) VALUES (?, ?) bind => [sfirosijfhgdoi 2244, dsufius1122]

Etc...

+5
source share
4 answers

You use GenerationType.IDENTITY for sequencing, which requires getting identifiers from each insert statement one by one. Try the sequence diagram, which allows you to pre-distribute packages over 500, and you will see improvements: http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#Identity_sequencing

+3
source

Sequence generation seems to be causing the problem, you are linking to this post . It provides an approach to generating a pre-distributed sequence.

0
source

It's already one year, probably too late to answer. In my case, I found that flush () is causing the problem. I call flush () after persist () for each entry. This prevents batch writing for optimization and leads to poor insertion performance. After removing flush (), everything went well.

0
source

This may be due to sequence / indentation (you need to use preallocation), but also for mysql. It seems to me that for full-size insertion you need a connection string property:

rewriteBatchedStatements = true

 jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
0
source

Source: https://habr.com/ru/post/1204884/

More articles:


All Articles