How bad is repeated select * on an empty table in h2 database (v1.4.195)?

Question

So I have a h2 database event table, that I am monitoring for events. There is a thread that fires every 2 secs and checks with select * from eventTable limit 10 offset 0.

I was wondering what is the performance impact of this hammering in an h2 database table. It is B-tree based but the db itself is a file. Does the h2db go to the file and has to read blocks and so to determine if table is empty. Think Oracle Db and High Water Mark problem for querying tables with large rows that get deleted later on without truncate and this causes unnecessary read of blocks to get select * done and is bad for performance.

If at all this is bad, would swapping out the thread part be recommended with the Trigger approach for Insert operations described in this qt here.

Regards

Answer 1

Here are some numbers from io monitoring:

I started my application that has the monitoring thread with the select statement. Then I started "sudo fsusage MYPID". The pattern with 4 reads and 2 writes repeats itself:

16:18:10.809774  pread F=44   B=0x400      O=0x00037000 0.000020  java.11010
16:18:10.809809  pread F=44   B=0x400      O=0x00037000 0.000005   java.11010
16:18:10.809825  pread  F=44   B=0x400      O=0x00037000 0.000003   java.11010
16:18:10.809839  pread F=44   B=0x400      O=0x00037000 0.000004   java.11010
16:18:10.810044  pwrite F=44   B=0x1000     O=0x00031000 0.000034   java.11010
16:18:10.810087  pwrite F=44   B=0x2000     O=0x00000000 0.000010   java.11010

FD is file desciptor and is through lsof -p PID confirmed as the database file. B= no of bytes count read or written. O is offset in file.

I see the above pattern of read and writes constantly. The reads are than most likely the selects. There is no other activity on DB. So even for empty tables I am reading something like 1600 bytes consistently and making 2 writes in the range of 3000 to 4000 bytes.

But than I went into more detail, since I am on macosx, strace is not an option but Dtruss works nicely. So just gave dtruss -a -p PID and following is the relevant output for the reads and writes:

632/0x653a:   2229289      37     24 pread(0x2C, "chunk:3157,block:31,len:2,map:9,max:1540,next:4d,pages:6,root:c55c0000027cf,time:18ffdc4,version:3157                                                         \n\0", 0x400, 0x31000)   = 1024 0
632/0x652c:    773689      86      2 gettimeofday(0x70000B107C68, 0x0, 0x0)      = 0 0
632/0x653a:   2229327      13      5 pread(0x2C, "chunk:3157,block:31,len:2,map:9,max:1540,next:4d,pages:6,root:c55c0000027cf,time:18ffdc4,version:3157                                                         \n\0", 0x400, 0x31000)   = 1024 0
632/0x653a:   2229347      10      4 pread(0x2C, "chunk:3157,block:31,len:2,map:9,max:1540,next:4d,pages:6,root:c55c0000027cf,time:18ffdc4,version:3157                                                         \n\0", 0x400, 0x31000)   = 1024 0
632/0x653a:   2229373      11      4 pread(0x2C, "chunk:3157,block:31,len:2,map:9,max:1540,next:4d,pages:6,root:c55c0000027cf,time:18ffdc4,version:3157                                                         \n\0", 0x400, 0x31000)   = 1024 0
632/0x653a:   2229621      45     34 pwrite(0x2C, "chunk:3159,block:24,len:1,map:9,max:b80,next:35,pages:4,root:c5640000027cf,time:19001ef,version:3159                                                          \n\0", 0x1000, 0x24000)     = 4096 0
632/0x653a:   2229686      32     24 pwrite(0x2C, "H:2,block:24,blockSize:1000,chunk:3159,created:1610362b746,format:1,version:3159,fletcher:1d05a51e\n\0", 0x2000, 0x0)         = 8192 0

So adding above the return values of pread and pwrite I can see actual read is 1024 x 4 Bytes and writes is 4096 + 8192 bytes. And also one can see what is read and written. The last write sometime appears and sometimes doesn't. The 1st param to fread and fwrite is the file descriptor 0x2c which matches that of the database file. And the 2nd param is the buffer being written. I wonder why we need to write here anything though. But that got explained when I read the following architecture explanation in h2 project page:

The above writes and reads can be explained by h2database.com/html/mvstore.html#fileFormat

Browsing the source code I find that the BackgroundWriterThread class, which i noticed in the profiler as well churning bytes up as time goes by ( but no memory leaks, it cleans up properly), is waking up every second and just blindly commiting the store. That gives the location of the writes and reads above in code.

More googling revelead the problem was discussed here in the google group, though no resolution occured except someone later on posting that the parameter WRITE_DELAY does the trick for him. groups.google.com/forum/#!searchin/h2-database/… Then I wondered if he did not try setting autoCommit on connection to false. I tried and the above pattern of read and writes stopped for me.

So adding ;AUTOCOMMIT=OFF to the connection parameter does the trick and the query is in memory, so overhead of select * from an empty table is quite minimum. This ends this investigation for me. The data is in memory as I am using the Version 1.4.195 and that has the MVStore database as default. So querying an empty table in memory should be a relatively inexpensive operation.

Regards