OutOfMemoryError: PageStore 159MB PageDataLeaf 106MB

[ecerulm]

Hi,

I'm using H2 1.2.131 with the following schema
c.createStatement().execute("CREATE TABLE NOTES ("
+ "ID IDENTITY, "
+ "ISACTIVE BIT NOT NULL, "
+ "GUID CHAR(36) NOT NULL,"
+ "SERIALIZEDOBJECT OTHER, "
+ "CONTENT VARCHAR(5242880) NOT NULL,"
// + "PRIMARY KEY (ID),
"
+ "CONSTRAINT UNQ_GUID UNIQUE (GUID))");

c.createStatement().execute("CREATE TABLE RESOURCES ("
+ "GUID CHAR(36) NOT NULL,"
+ "SERIALIZEDOBJECT OTHER, "
+ "OWNERGUID CHAR(36), "
+ "HASH CHAR(32), "
+ "DATA BINARY, "
+ "CONSTRAINT UNQ_GUID_RES UNIQUE (GUID))");
c.createStatement().execute("CREATE INDEX
I_RSOURCS_OWNER ON RESOURCES (OWNERGUID)");

I get an OutOfMemoryError while inserting rows.

org.h2.jdbc.JdbcSQLException: Out of memory.; SQL statement:
INSERT INTO RESOURCES VALUES(?,?,?,?,?) [90108-131]
at
org.h2.message.DbException.getJdbcSQLException(DbException.java:316)
at org.h2.message.DbException.get(DbException.java:156)
at org.h2.message.DbException.convert(DbException.java:278)
at org.h2.table.TableData.addRow(TableData.java:137)
at org.h2.command.dml.Insert.insertRows(Insert.java:120)
...

I manage to add 10500 rows in NOTES and aproximately the same in
RESOURCES. The BINARY DATA in resources ranges from 100bytes to 25MB.

I analyzed the heapdump.hprof that I get after the OOME and it seems
that PageStore , PageDataLeaf and ValueJavaObject take a lot of heap
space (159MB , 106MB and 46.5MB respectively). I wonder if this values
are ok, given the fact that the row that I'm adding is maximum 25MB.

All the config parameters are set to their defaults. How can I reduce
the memory comsumption of my db?

Thanks/Ruben

[Ruben Laguna]

I also tried to 

            c.createStatement().execute("SET MAX_LOG_SIZE 0");

            c.createStatement().execute("SET MAX_MEMORY_ROWS 0");

            c.createStatement().execute("SET MAX_MEMORY_UNDO 0");

            c.createStatement().execute("SET MAX_OPERATION_MEMORY 0");

            c.createStatement().execute("SET UNDO_LOG 0");

           

but I get the same result. The root cause is that H2 tries to expand to 151088942 bytes. See below. The insert is 25MB, why does it need 151MB?

org.h2.jdbc.JdbcSQLException: Out of memory.; SQL statement:

INSERT INTO RESOURCES VALUES(?,?,?,?,?) [90108-131]

        at org.h2.message.DbException.getJdbcSQLException(DbException.java:316)

        at org.h2.message.DbException.get(DbException.java:156)

        at org.h2.message.DbException.convert(DbException.java:278)

        at org.h2.table.TableData.addRow(TableData.java:137)

        at org.h2.command.dml.Insert.insertRows(Insert.java:120)

        at org.h2.command.dml.Insert.update(Insert.java:82)

.....

Caused by: java.lang.OutOfMemoryError: Requested memory: 151088942

        at org.h2.util.Utils.newBytes(Utils.java:412)

        at org.h2.store.Data.expand(Data.java:1023)

        at org.h2.store.Data.checkCapacity(Data.java:1018)

        at org.h2.index.PageDataLeaf.writeData(PageDataLeaf.java:477)

        at org.h2.index.PageDataLeaf.addRowTry(PageDataLeaf.java:227)

        at org.h2.index.PageDataNode.addRowTry(PageDataNode.java:143)

        at org.h2.index.PageDataNode.addRowTry(PageDataNode.java:143)

        at org.h2.index.PageDataIndex.addTry(PageDataIndex.java:153)

        at org.h2.index.PageDataIndex.add(PageDataIndex.java:126)

        at org.h2.table.TableData.addRow(TableData.java:119)

--
/Rubén

[Thomas Mueller]

Hi,

> BINARY DATA in resources ranges from 100bytes to 25MB.

Use BLOB instead of BINARY. See also:
http://www.h2database.com/html/datatypes.html#blob_type
http://www.h2database.com/html/datatypes.html#binary_type

Regards,
Thomas