MySQL INSERT gradually slows down on Ubuntu, but not on Windows

Question

I am trying to insert 3.6 million records in MySQL 5.1 using JPA in Glassfish 3.0.1. I do this in one call to the EJB business method, so I believe that this is done in a single SQL transaction. I'm out of my mind?: -)

Since the same EntityManager instance is used, the business method must call em.flush () and em.clear () every 3000 entries, or em will become clogged. (I tried different values, this seems to me optimal).

This works very well on my test platform (WinXP on a quad-core box). Each 100000 em.persist () takes 24-28 s, and the whole operation takes 15 minutes.

But in our production box with Ubuntu 10 in a virtualized x86_64 window, every 100,000 em.persist () is getting slower. The first takes 40 s, then 70, 77, 89, 121, 130, 126, 163, 201, 247 s. The server application ends up completely freezing.

Mysql: 5.1.47-MySQL Community Server (GPL) (Windows), 5.1.41-3ubuntu12.6 (Ubuntu)

I cannot understand why (almost) the same software behaves so fundamentally different. Any ideas?

Answer 1

15 minutes, it is very slow. Do you use transactions? If not, you should be. If you use transactions, how often you commit, you should commit regularly enough, but not too regularly.

Answer 2

3,6 . , JVM, em.flush() em.clear(), , 3,6 , .

, Java? , , Java. . , Java, .

Answer 3

EJB/JPA (afaik) ; EJB -. , EJB , - 10000 ...

(I could do this using JDBC directly, of course, but that would pull the carpet under the JPA).

The strange thing, however, is the difference in behavior on the two machines.