Consistent 5 minute interval dramatic performance degradation on simple select/insert/update

[Liam Maher]

I have a very basic set of select/insert/update queries that run at 1 minute intervals.

The job runs on extremely lightweight table and every minute processes approx the same amount of data. 

It is the only service that interacts with the tables in question.

Within a 5 minute period the job runs in ~0.5 second on the first 4 iterations ( minutes 1,2,3,4,6,7,8,9,11,12,13 etc). However on the 5th iteration so at 05,10,15,20,25,30 etc the job goes from 0.5 second to 8-10seconds.

Every 5 minutes it appears to get caught in some sort of lock that I can't locate. 

The code is exactly the same for each 1 minute iteration and the volume of data to be processed doesn't really change which suggests to me it is db side.

I have attached the log file where you can see the job at 09:59:00 in less than a second, 10:00:00 in 8 seconds and then  10:01:00 in less than a second again.

Any guidance or advice would be greatly appreciated.

Regards

Liam

[Noel Grandin]

which version of H2, and what does your database URL look like?

[Liam Maher]

Hi Noel,

Thanks for your reply.

I am still using 1.4.196

db.connection=jdbc:h2:///B:/LIVE/MASTER/CandleCollection;AUTO_SERVER=TRUE

[Noel Grandin]

probably you are triggering the periodic auto-compact/GC then.

You can either dial it back to run less (at the expensive of using more disk space), or try the latest .200 version,
where Andrei Tokar improved the GC runtime.

[Liam Maher]

thanks Noel will give it a go