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]> 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]. > 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]. 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.
