I have a NativeQuery
which copies about 1000 rows (selects and inserts with some columns changed).
The problem is that sometimes (about 1/3 of the runs) executeUpdate
takes 50 ms and sometimes (2/3 of the runs) it takes 1500 ms.
The same query is run many times during a request. Usually all these runs are all slow or all fast.
All the requests were started from the same database state (ie. exact same records are selected and inserted). Neither database nor Tomcat had other users at this time. Sometimes it is slow up to 5 times in a row, sometimes it is fast up to 5 times in a row, sometimes it is slow,fast,slow,fast.
I've tried restarting both Tomcat and Postgres. Sometimes it is slow after the restart, sometimes it is fast. I tried adding System.gc()
to the beginning of the request but the randomness remained.
When I try the same query directly (via dBeaver/JDBC), it is always fast.
My environment:
- Tomcat 8.5.33
- Eclipselink 2.6.4
- PostgreSQL 9.6.11
- Ubuntu 18.04
Any ideas how to debug this situation?