I have a fairly large, multi-threaded application which uses hibernate to interface with a database. Lots of transactions, foreign keys, constraints and a fairly high thru-put requirement. For my current testing, I have about 20 tables, most are small, but a few have 10,000 rows.
I currently support PostgreSQL and MS SQL Server - and I'm exploring adding support for an embedded database. <off-topic> First I tested Derby, and was impressed that it mostly just worked, when plugged into our app as a backend. However, the performance was dreadful. Then I tried out HSQL - but it appears that either Hibernate or HSQL has completely broken their support for each other - I thought things were going to work, until I started using the app and getting random failures all over. Poked around for a bit, thinking something simple was wrong, but that doesn't seem to be the case. </off-topic> So, now I tried out H2 - having never used it before - and I must say, I'm quite impressed with your documentation. It is the best, by far, of any of the offerings I tried. I had 0 issues what-so-ever trying to bring my app up, load in my schema, and make it run with H2. The performance of H2 is at least 4 or 5 times faster that Derby was in my use case. However, it still leaves quite a bit to be desired compared to PostgreSQL on the same hardware (something on the order of 15 times slower) - so I'm looking for tuning feedback. I'm currently using the following as my connection URL: jdbc:h2:file:/work/h2/test;CACHE_SIZE=131072; I have a connection pool which opens connections to the DB. In PostgreSQL, I typically run 26 processing threads, each using connections from the pool. For H2, I backed it down to 2 processing threads, which seemed to help the performance a little. I've tried the TQ and SOFT_LRU cache algorithms, neither seems to impact my performance. When I tried to look into the horrible performance of Derby, I discovered that it was doing table scans for a couple of my (more complex) queries, while PostgreSQL was able to use it's indexes for said queries. I suspect that the same is happening with H2 - PostgreSQL and SQLServer are able to plan a way to perform my ugly queries using indexes, while H2 must be reverting to a table scan at some point. Any suggestions of other things to try to speed it up? One other bit of info about my use case - I don't care if an option makes it more likely to lose a few seconds worth of "committed" transactions - so long as the DB doesn't corrupt itself during a power outage. Is there anyway to have H2 give me something similar to the "Explain" output on Postgres, so I can figure out which queries it is having issues with? Or even better yet, an option to log "slow" queries (where slow is a user defined variable)? Two other quick questions - First - I like your web-gui-console tool - however one feature I found missing was the ability to run a sql script on a command line. Something like: java -jar h2.jar 'jdbc:h2:~/test' superSQLScript.sql Maybe it exists, but I just didn't see how to run it? And Second, in the documentation where you describe the CACHE_SIZE option, you don't specify the units for the number that is to be passed in. I'm assuming that it treats the number as MB? But I'm just guessing.... Thanks for all the work on H2, Dan --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
