It really was an eye-opener for me. Glad to help. On Tue, Apr 26, 2011 at 6:53 AM, Anthony <[email protected]> wrote:
> 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. > > -- 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.
