Many Many Thanks chris that was very useful. Did as you suggest and wow
Thanks for that Menkaura On Apr 25, 10:04 pm, Chris Schanck <[email protected]> wrote: > 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.
