While attempting to optimize a prototype application, H2 was identified as a bottleneck. I initially used H2 in server mode and inserts (and other DB operations) start out fast but rapidly become slower as the number of rows (and specifically, the DB file size) increase. Oddly enough, H2 in server mode starts out slower but doesn't suffer from the same performance degradation.
Also the embedded DB file size increases rapidly compared to the server mode DB file. I tried all manner of DB settings and they didn't make much difference. To illustrate the issue, I've created an inclusive Spring Boot application and hosted it on github athttps://github.com/balamuru/h2demo . I created a simple table with a MEDIUMBLOB field populated by the contents of a bitvector (to speed up the process of bulking up the DB) admin.employee ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, address TEXT, data MEDIUMBLOB, PRIMARY KEY (id) ); - Server mode db config spring.datasource.url=jdbc:h2:tcp://localhost/~/h2db;DB_CLOSE_ON_EXIT=FALSE;CACHE_SIZE=1000000; - Embedded mode db config spring.datasource.url=jdbc:h2:./h2db;DB_CLOSE_ON_EXIT=FALSE;CACHE_SIZE=1000000 - Here are the results that illustrate my concern. Any ideas? Thanks in advance -------embedded mode (msecs)------- Run time : 27464 Run time : 7281 Run time : 6363 Run time : 6326 Run time : 5522 Run time : 6070 Run time : 5262 Run time : 6502 Run time : 6016 Run time : 7196 Run time : 15735 ==> spike in run time Run time : 39973 ==> embedded mode slower than server mode from this run onwards Run time : 44079 Run time : 46769 Run time : 50443 Run time : 53640 Run time : 59635 Run time : 61937 Run time : 64344 Run time : 71259 -------server mode (msecs)------- Run time : 20872 Run time : 17319 Run time : 18081 Run time : 18205 Run time : 18596 Run time : 18089 Run time : 18453 Run time : 18386 Run time : 18330 Run time : 17259 Run time : 18826 Run time : 19717 Run time : 19122 Run time : 18152 Run time : 18016 Run time : 18818 Run time : 19414 Run time : 19207 Run time : 19022 Run time : 19446 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
