2

I was evaluating the speed of H2 earlier today, and I noticed a significant slowdown when making many subsequent queries. I did a quick CPU profile with JMX and I noticed that the vast majority of CPU time was spent in the FileLock.sleep() method. I debugged the code while several hundred INSERT statements were made, and these calls almost entirely stem from this line in the FileLock.lockFile() method:

save();
sleep(SLEEP_GAP);

FileLock.SLEEP_GAP is a static final int set to 25, so it can't be tuned at all (please don't suggest that I use reflection, if you think that will work I'd encourage you to read this answer). This method is invoked and causes the main thread to sleep for 25ms on every single INSERT statement made. If you have tens of thousands of them to execute, it really adds up to a lot of wasted time. Why is this value set this way? Is there any way around having to use this class?

Source code, if you don't feel like getting it out of SVN.

Community
  • 1
  • 1
monitorjbl
  • 4,280
  • 3
  • 36
  • 45
  • Do you open and close the connection a lot, and there is only one connection? In that case, the database file is opened and closed a lot, which is slow (no matter how low the sleep gap is). In that case, I would consider keeping the connection open, or use a connection pool. Or, simpler, append `";DB_CLOSE_DELAY=1"` to the database URL. – Thomas Mueller Mar 31 '14 at 07:08
  • I'm using Spring to manage the datasource, so it's possible that it's opening/closing the connection a lot. Not sure if there's a way to make it be more conservative with closing it, but I'll try that parameter when I get home tonight. – monitorjbl Mar 31 '14 at 14:17
  • Oh my god, that made a *massive* difference; 20 seconds to load 400 records down to ~1 second. Please make that an answer so I can accept it! – monitorjbl Apr 01 '14 at 03:34

1 Answers1

2

FileLock.sleep() is used while opening the database file, to ensure no other process can open the same database file at the same time (similar to file locking). Such a mechanism is used by most database engines. If you see this in the profiling, then that means the database is opened and closed many times in a row. Opening and closing a database is quite slow and should be avoided.

If possible, databases should be kept open, by keeping the connection open or by using a connection pool.

If that's not an option, then append ;DB_CLOSE_DELAY=1 to the database URL. This will keep the database file open for one second after closing the last connection.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • I'd just like to add for posterity that Spring **will not** keep the connection open on its own using the default configuration. – monitorjbl Apr 01 '14 at 13:39