Resurrecting a thread from the dead is always fun... We use H2 in our product as a small JDBC backend suitable for development before deployment over larger, enterprise servers.
In times past, H2 was always *lots* faster than Postgres, for example, at least on smaller (<1million row) datasets. With the advent of the page store, the performance has gone steadily down, although the disk consumption is much better. I'd been experimenting with other Java embedded databases with an eye toward replacing H2. (One of the needs for us is concurrency, not much I can do about that.) But the ingest/query speed was another issue. We'd held onto the 1.1x series for as long as possible, but there are enough new fixes/features etc. that I really wanted to move us along. Experimenting this weekend, I went through all the settings. Eventually, one caught my eye. Our databases aren't particularly large, maybe a gig altogether. Even so, they default page size of 2k seemed ... small. On a whim, I changed it to 4k. Woot! My ingest test (ingest of first-order-logic assertions) went from 3500/sec, with no indexes, to 15000/sec, fully indexed (around 50 indexes involved). That was startling. I upped it to a page size of 8k, and both my query times and my insertion speeds improved dramatically. In the past, I've always told folks that you need to tweak H2's cache size out of the box for good performance (we allocate 1/5 of all available heap for H2), but now I have another rule of thumb -- bump up that page size. I know the old file system and the page store are very different, but it really felt like 2K was below some thrashing level for critical data. Is it possible that the page store's 2K page size is effectively MUCH lower than the old page size? Hope this is useful to folks, Chris On Sun, Nov 14, 2010 at 7:32 AM, ahsuck <[email protected]> wrote: > Hi Thomas, > > What I wanted to point out is, that there seems to be a major penalty > when using the "new" page-store in a "normal" (=2) log-mode. > > When I run my little import test this can seen quite easily. > > The pattern between 118 and 144 changes in some way dramatically. > I monitored my import with jconsole and the "cpu" pattern is a clear > indication on that. > I don't know how to attach "images", otherwise I'd post some screen > shots for the different import-tests. > > I have not looked at the source-code (less the changes from 118to > 144), but from the way the differences look like, I'm afraid, that > this combination (Data/Indexes) and bulky inserts seem to have a > negative effect on the "sync". My guess is, that (probably due to some > balancing) many more pages are actually "dirty" and need to be > flushed than in the 118-version. > > The new 144 is overall somewhat faster than 144 for log-modes<2 or for > "in-mem" databases. > > I did not use the "csvread" from h2 because in a "real" scenario the > data would not come from a csv-file in the first place. But as you > pointed out, the file-reading has no real influence on the tests. > > Just to make it clear, I don't need to "optimize" this particular > import, this can be achieved as you pointed out in changing > the log level. > I accidentally found this "difference" between 118 and 114. As the > difference is quite noticeable, I wanted to make you aware of this > because it *might* indicate a problem in the page store. If the page > store is a B(whatever)-Tree, this may not even be (at least not > easily) avoidable.... > > So if there is anything else I can do to help to understand what > actually causes the difference, I'd be happy to help (as far as I can). > > -- > 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. > > -- C. Schanck -- 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.
