Slow Hibernate flush

Question

I try to save my data to MySql(local DB) throw HibernateTemplate -

getHibernateTemplate().execute(new HibernateCallback<Void>() {
            @Override
            public Void doInHibernate(Session session) throws HibernateException, SQLException {
                for (TimeInvocationStatistics stat : statistics) {
                    session.persist(stat);
                }
                session.persist(workloadProcessDescriptiveStatistics);
                session.flush();
                return null;
            }
        });

The size of data is not so big, but this operation takes over 60 sec.

I tried to profile it -

(good picture resolution - picture )

As I can see session.flush() (second row in stacktrace) works slow, how I can improve it ? Could it be MySql server problems ?

UPD: find interesting thread - hibernate forum, try to do in this way

Answer 1

Without knowing how many TimeInvocationStatistics you have, nor how your tables are setup or how much data is in those tables., my best guess would be that you are inserting individual rows where you should be inserting as a batch. We recently had an app that tried to insert 18k rows of some fairly simple data (I didn't write it originally), and just by moving to batch inserts we cut the time down from about 18min to about 2sec.

The reason for such a drastic improvement was because of indexing and table size. Each insert causes the DB to recalculate the index. The larger the data set, the longer it takes to recalculate the indexes. This is the trade-off to indexing a DB table: Speed of look-up vs. slowness of insert. By batching the inserts, the DB was only required to calculate the indexes once for the whole table. Much more efficient.

Answer 2

The problem was, that batching processing didn't work, because I used bad generation strategy. Read about this problem here -

1. JPA/Hibernate bulk(batch) insert
2. Hibernate batch size confusion
3. Hibernate forum