is there a limit of how many records in one table?

[kais haddadin]

Hello!

I am trying to load big table: 250 columns and 6 million lines in H2. It loads (or I think at least it is because the db is now 11.5GB), but It gives me always out of memory as soon as I try to make a query on it. I stopped my program andI tried to access the H2 database outside my own program and through the commandline tool and after 10-15minutes of waiting, I get this error:

SQL Exception: Allgemeiner Fehler: "java.lang.RuntimeException: old!=record pos:

516 old:page[516] data leaf table:0 SYS entries:1 parent:514 keys:[5] offsets:[1

947] new:page[516] data leaf table:8 LOBS entries:0 parent:0 keys:null offsets:n

ull"

General error: "java.lang.RuntimeException: old!=record pos:516 old:page[516] da

ta leaf table:0 SYS entries:1 parent:514 keys:[5] offsets:[1947] new:page[516] d

ata leaf table:8 LOBS entries:0 parent:0 keys:null offsets:null"; SQL statement:

CREATE CACHED TABLE IF NOT EXISTS INFORMATION_SCHEMA.LOBS(ID BIGINT PRIMARY KEY,

 BYTE_COUNT BIGINT, TABLE INT) HIDDEN [50000-169]

[Enter]   jdbc:h2:...

I am using version 1.3.168. I can't attach the database since it is very big. 

Is this a result of the "out of memory" exception I am getting when doing the big query? how can I make this work?

Thanks in advance,

Kais

[Thomas Mueller]

Hi,

What database URL do you use?

What happens during those 10-15 minutes, could you get a few full thread dumps?

How exactly did you load the database? You wrote "I am trying to load big table" but when I read your mail I'm not sure: did you finish loading the database, and now try to access it (read from it)? 

By the way, according to the error code, you are actually using H2 version 1.3.169 (but that's no a problem).

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/6NYu5xiRtO8J.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Kais Haddadin]

Hi,

sorry about this, but I think there was some confusions about the versions of the H2 used in my test. It seems that there was older versions involved. I redid the test completely with version 169 and I could connect to the database after the load is finished. In my program, am trying to make a big query and am getting out of memory exception,  my query looks like this, on 6 million records:

select "Col1",...,"Col50","Measures",SUM("Measure51") as "Measure51",...,SUM("Measure250") as "Measure250" from "sampleNorm_1"."NormalizedData" group by "Col1",...,"Col50","Measures"

the result should not be a lot, since each column in the group by clause include small number of distinct records.

I redid the test with H2 console, I assigned 10GB as Xmx, I still get out of memory exception,the weird part,the java goes only up to 7.5 G, I have 16G on the machine and there still more RAM free for the JVM. I'm not sure if this is something that H2 can do something about it. But For me I can simply not perform this query regardless how much Memory I assign for the JVM. Do you think H2 team can help me in this? I can provide the database for testing if needed.

Thanks a lot,

Kais

[Kais Haddadin]

when assigning 12GB as Xmx, the JVM went to 9.1G, and then the out of memory.

when assigning 15GB on the 16GB machine, the JVM went to 10.6GB and then the exception came. I am not sure how many GB does such a query need in H2? Is there a possibility to enhance such queries to use 

Cheers,

Kais

[Noel Grandin]

On 2012-11-05 12:15, Kais Haddadin wrote:
> when assigning 12GB as Xmx, the JVM went to 9.1G, and then the out of
> memory.
> when assigning 15GB on the 16GB machine, the JVM went to 10.6GB and
> then the exception came. I am not sure how many GB does such a query
> need in H2? Is there a possibility to enhance such queries to use
>
> Cheers,
> Kais
>

You could try debugging it with -XX:+HeapDumpOnOutOfMemoryError and
Eclipse Memory Analyzer.

[kais haddadin]

Ok I did what you recommended. The dump file was 16GB so it took a while to open it. I am not sure what information should I provide here: The Thread stack for the exception is as follows:

main
  at java.lang.OutOfMemoryError.<init>()V (Unknown Source)
  at org.h2.expression.Aggregate.updateAggregate(Lorg/h2/engine/Session;)V (Aggregate.java:242)
  at org.h2.expression.Alias.updateAggregate(Lorg/h2/engine/Session;)V (Alias.java:76)
  at org.h2.command.dml.Select.queryGroup(ILorg/h2/result/LocalResult;)V (Select.java:339)
  at org.h2.command.dml.Select.queryWithoutCache(ILorg/h2/result/ResultTarget;)Lorg/h2/result/LocalResult; (Select.java:613)
  at org.h2.command.dml.Query.query(ILorg/h2/result/ResultTarget;)Lorg/h2/result/LocalResult; (Query.java:307)
  at org.h2.command.dml.Query.query(I)Lorg/h2/result/LocalResult; (Query.java:277)
  at org.h2.command.dml.Query.query(I)Lorg/h2/result/ResultInterface; (Query.java:36)
  at org.h2.command.CommandContainer.query(I)Lorg/h2/result/ResultInterface; (CommandContainer.java:86)
  at org.h2.command.Command.executeQuery(IZ)Lorg/h2/result/ResultInterface; (Command.java:191)
  at org.h2.jdbc.JdbcStatement.executeInternal(Ljava/lang/String;)Z (JdbcStatement.java:173)
  at org.h2.jdbc.JdbcStatement.execute(Ljava/lang/String;)Z (JdbcStatement.java:152)
  at org.h2.tools.Shell.execute(Ljava/lang/String;)V (Shell.java:429)
  at org.h2.tools.Shell.promptLoop()V (Shell.java:270)
  at org.h2.tools.Shell.runTool([Ljava/lang/String;)V (Shell.java:148)
  at org.h2.tools.Shell.main([Ljava/lang/String;)V (Shell.java:80)

I attached 2 snapshots of the histogram and the dominator_tree.

[Noel Grandin]

Nice work.
I think you have something wrong with your query, because it's trying to return a seriously large number of records.

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/dHoUqfZNlcsJ.

[Kais Haddadin]

well am doing the same art of query with smaller tables and it works as expected.

But I found out what I was thinking wrong:

The query looked like this

select "Col1",...,"Col50",SUM("Measure51") as "Measure51",...,SUM("Measure250") as "Measure250" from "sampleNorm_1"."NormalizedData" group by "Col1",...,"Col50"

The Col(i) colums contain values col(i)_value(j) and the columns Measures(i) contain only numeric values. The number of distinct values in each Col(i) column should be 3, so actually the number of records at the end will not be small as I though but very big, it is (3 distinct values in each column power 50 columns) = 7.1789798762e+23. This is a very big number, which I can understand if it even does not fit in 10G.

Cheers and thanks for the reponses,

Kais