I checked the INFOMATION_SCHEMA.SYSTEM TABLE.COLUMNS, there are actually 134,000+ columns, and there are actually many duplications. Those tables and views are actually copied structure from around 40 table/view templates, so they should have the same column name and type. As you can see that 1 million column expressions doesn't take that much memory, and columns shouldn't neither. Is it possible to analyze what's in that 600MB of strings? To me, it's more like the whole database is loaded as a in-memory database?! or more than that? And if it's the cache, it should be controllable and not prevent the database loading when the heap memory is limited. What do you think?
On Thursday, November 21, 2013 10:12:47 AM UTC-5, Thomas Mueller wrote: > Hi, > > Yes, it looks like a lot of memory is used by database metadata: about > 300'000 > columns and about 1 million column expressions (probably from views, not > sure). About 600 MB are used for Strings (probably column names). > > > I don't think we can change how the application work for now. > > I see, but I don't think it's easy to change the way H2 deals with > metadata. And even it it's possible, I don't think I want to change it, as > that would hurt other applications. > > The easiest way to save memory would be if the column names (of the tables > and views) are the same. I mean, let's assume you have tables A, B, C, with > columns A(X, Y, Z), B(X, Y, Z), and C(X, Y, Z), then the column names X, Y, > Z wouldn't be allocated individually for each table. Is that the case? > > Regards, > Thomas > > > > > > On Thu, Nov 21, 2013 at 3:07 PM, Min Huang <[email protected]<javascript:> > > wrote: > >> Thanks, Thomas. Here I attach 4 histogram of the state of H2 server >> start initial, use squirrel sql client to connect and load 250m db, >> shutdown compact, and after GC. Please have a look. Thanks. >> >> The tables in the 250m db are created by the application. I underestimate >> the number. There are 3500+ tables and 2500+ views in the table. >> >> I don't think we can change how the application work for now. Is there >> any way to make H2 load less metadata to the memory, and only load them >> when necessary? >> >> On Thursday, November 21, 2013 6:30:38 AM UTC-5, Thomas Mueller wrote: >> >>> Hi, >>> >>> Could you get a heap histogram and post it ("jstack -l" to get the >>> process id, then "jmap -histo <pid>" to get the heap histogram)? >>> >>> I guess it's because of the many tables and views; H2 currently keeps >>> the whole metadata info in memory (unlike other databases such as Oracle or >>> PostgreSQL). I suggest to use a lower number of tables and views, for >>> example 100 instead of 2000. I wonder, did you manually create so many >>> tables and views? Or is those generated automatically? >>> >>> Regards, >>> Thomas >>> >>> >>> >>> On Thu, Nov 21, 2013 at 2:32 AM, Min Huang <[email protected]> wrote: >>> >>>> I got several report from application users of out of memory >>>> exception when connecting to a H2 server. When trying to reproduce, I used >>>> jconsole to connect to the H2 server and check what's going on. >>>> >>>> I found when I connect to the H2 server and load a database the first >>>> time, H2 server start consuming a lot of memory. Before loading any >>>> database, the H2 server only consume 3M memory. After using Squirrel SQL >>>> client connecting to the H2 and load the 250M file database, H2 server >>>> consume about 1G memory. What could be the reason this happen? >>>> >>>> H2 server start command: java -Xmx2g -Xms16m -XX:PermSize=16m >>>> -XX:MaxPermSize=256m -cp "h2-1.3.173.jar;%H2DRIVERS%;%CLASSPATH%" >>>> org.h2.tools.Server -tcp -tcpPort 9097 -tcpAllowOthers -baseDir >>>> C:\temp\h2db >>>> >>>> My connection string: jdbc:h2:tcp://localhost:9097/ >>>> db1;DB_CLOSE_DELAY=-1 >>>> ------------------------------ >>>> Additional information: We have quite some tables(1200+) in the >>>> database, and 800+ views which union or join on these tables. Will this be >>>> the cause of this problem? Could there be any solutions that reduce the >>>> memory consumption when loading such database? >>>> >>>> >>>> The stackoverflow thread at: >>>> http://stackoverflow.com/questions/20107314/h2-server- >>>> consume-1g-memory-to-load-a-250m-file-based-database >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "H2 Database" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To post to this group, send email to [email protected]. >>>> >>>> Visit this group at http://groups.google.com/group/h2-database. >>>> For more options, visit https://groups.google.com/groups/opt_out. >>>> >>> >>> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> To post to this group, send email to [email protected]<javascript:> >> . >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/groups/opt_out. >> > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
