VARBINARY need much more memory than BLOB?

[rmuller]

Hi,

If i store binary data (actual photo's) into H2 in *one* transaction i
run into memory problems when using VARBINARY. If i use BLOB's
everything goes well. See code sample. Is this a H2 issue or do I miss
a configuration option?

final Properties props = new Properties();
props.setProperty("USER", "admin");
props.setProperty("PASSWORD", "admin");
props.setProperty("MAX_MEMORY_UNDO", "100");

Class.forName("org.h2.Driver");
final Connection c = DriverManager.getConnection("jdbc:h2:./
test", props);
c.setAutoCommit(false);

final Statement stm = c.createStatement();
//stm.executeUpdate("create table TEST (id IDENTITY, data
BLOB)"); // OK
stm.executeUpdate("create table TEST (id IDENTITY, data
VARBINARY)"); // OOM
stm.close();

final PreparedStatement pstm = c.prepareStatement(
"INSERT INTO test (data) VALUES (?)");

// H2 keeps the undo log in memory by default, MAX_MEMORY_UNDO
must be set
// to a low value if memory is limited
// When using VARBINARY and -Xmx128M OoM always occurs
int count = 0;
while (++count < 100) {
pstm.setBytes(1, new byte[1024 * 1024]);
pstm.executeUpdate();
}

c.commit();
c.close();

Regards,

Ronald

[Sam Van Oort]

Hi,

This behavior is normal and expected -- it's one of the main
differences between BINARY and BLOB types.
See the documentation: http://www.h2database.com/html/datatypes.html#binary_type

Cheers,
Sam Van Oort

[rmuller]

Hi Sam,

Thanks for the quick reply!

Sorry, i am not clear enough (now I read the title again): the point
is that processing 100 files of 1MB in one transaction with 128MB
memory is not possible with VARBINARY's even if i set
"MAX_MEMORY_UNDO" to a (very) low value.

Regards,

Ronald

[Sam Van Oort]

Hi again rmuller,

The BINARY data type should not be used for large objects (more than a
few kB) unless you have a lot of memory, and the documentation makes
this clear. H2 doesn't put arbitrary limits on BINARY or VARCHAR
size like MySQL or many other engines, but this is for convenience and
will cause problems if abused.

You need to switch to BLOB if you want to do multi-row updates on MB-
sized objects. OR, you can split big transactions into very small
ones to fit in memory. To migrate, you'll want to add the BLOB column
and then migrate BINARY data to blob in one-row transactions to avoid
memory issues. This can easily be done using JDBC, or by running a
number of one-row (or few-row) UPDATE statements with WHERE conditions
that use the primary key to limit transaction size.

Regards,
Sam

[rmuller]

Ok Sam, I have got the message: I just switch to BLOB's.

Thanks for your help!

Regards,

Ronald

[Thomas Mueller]

Hi,

> Sorry, i am not clear enough (now I read the title again): the point
> is that processing 100 files of 1MB in one transaction with 128MB
> memory is not possible with VARBINARY's even if i set
> "MAX_MEMORY_UNDO" to a (very) low value.

Yes, that's true. This is a known limitation of H2. Currently, the
transaction needs to fit in memory, sorry. I know large transactions
are an important feature, and supporting it has high priority.

Regards,
Thomas