In my case, since it is embedded, the cache size has to fit in heap space of the java process; I wrote some code somewhere to calculate and then set the cache size to 1/5 available memory (the app needs most). Page size has an enormous impact; my performance went up 20x when I jumped up the page size to 16k pages. Given 100 million rows, I'd think about setting it even higher.
On Thu, Jun 23, 2011 at 10:31 AM, Stephane Geney <[email protected]> wrote: > Hi and thanks for your answer, > > I do agree that it is a large volume of data, but as H2 is great in > every other aspect, I was just trying to make sure that I didn't miss > anything. > I tried playing with the cache_size, and it seems that if you specify > a cache_size (I tried 4G for instance), as the index creation process > doesn't have enough memory, it exists with an "out of memory" error > message. > > On a sidenote, as the indexes will only be used to access the data > directly (where id2= , and not where id>...), I'l trying to create a > hash index to check if the performance is any better. > > Steph. > > On Jun 23, 4:14 pm, Chris Schanck <[email protected]> wrote: >> An embedded database storage engine with 100 million rows? That's ... >> bigger than what I would think is the sweet spot for an embedded db. >> >> Either way, I would look into changing the page size and the cache >> size; both can be specified in the connection url, I think, and the >> page size can't be changed after the db is created. >> >> Chris >> >> On Thu, Jun 23, 2011 at 2:53 AM, Stephane Geney >> >> >> >> >> >> >> >> >> >> <[email protected]> wrote: >> > Hi, >> >> > I've just started using H2 for my application as an embedded database >> > storage engine and it's really working great except for one part : >> >> > I have a table with 3 columns : id1 char(8), id2 char(8), value float. >> > I need indexes on the first two columns separately as I'll do a lot of >> > (select..where id1=...) and (select... where id2=...). >> >> > The table as more than 100 millions rows, and the index creation is >> > just too long. Creation of index on column id2 took more than 10 >> > hours. >> >> > Is there a workaround ? should I try to create the indexes first and >> > then only import data in the table by splitting in in smaller chunks ? >> >> > Thank you for your answers. >> >> > -- >> > 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 >> > athttp://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.
