[h2] Database Grows Rapidly

[bryan]

Hi,

I am exploring H2 as a candidate for embedded data storage in our
application. I like a lot of the features it offers. However, the database
seems to grow very large, very quick. I have read a lot of forum threads
and such about how this is typically caused by uncommitted transactions. I
just don't see how that is the case with my simple test.

Notes:

- Using version 1.4.187 (2015-04-10)
- The database grows even if I'm doing reads (SELECTs)
- Setting "WRITE_DELAY" to 0 significantly increases the size of the
database; but we will want it set to zero to improve durability
- Calling "SHUTDOWN COMPACT" and "SHUTDOWN DEFRAG" shrinks the database
to a reasonable size
- I'm only using 1 connection , when its closed the database is still
large (I even set "MAX_COMPACT_TIME=10000"). It does not shrink the
database when the connection is closed (I thought it did this automatically
if the "last" connection closes)
- I'm leaving autocommit to true
- INFORMATION_SCHEMA.SESSIONS had no other open sessions after finishing
my inserts
- INFORMATION_SCHEMA.IN_DOUBT had no entries (so I'm assuming no
uncommitted transactions)

Questions:

1. Any ideas of why my database is growing so fast (why does it grow
when reading/selecting)?
2. Why does it seem like it does not compact the database when the last
connection is closed?
3. Our application cannot stop running for very long periods of time. Is
there a way to automatically "vacuum" the database without shutting down? I
saw other threads about this but they are fairly old, maybe something new
has been added...

Any help is appreciated, thanks!
Below is my code...

The test just creates some parent tables "primary_table" and child tables
"data_table".

*How I create the connection:*
private static Connection getMyConnection(String dbPath)
throws SQLException, ClassNotFoundException
{
String connFmtStr = "jdbc:h2:file:%s";

connFmtStr += ";TRACE_LEVEL_FILE=3";
connFmtStr += ";DB_CLOSE_DELAY=0";
connFmtStr += ";ACCESS_MODE_DATA=rws";
connFmtStr += ";MAX_COMPACT_TIME=10000";

Class.forName("org.h2.Driver");
String connectionString = String.format(connFmtStr, dbPath);
Connection connection = DriverManager.getConnection(connectionString);

Statement statement = connection.createStatement();

statement.execute("SET WRITE_DELAY 2000");
statement.execute("SET LOCK_MODE 3");
statement.execute("SET MAX_LOG_SIZE 2");

statement.close();

return connection;
}

*How I insert the data:*
public static void createDb(String dbPath, final int numberOfLogs, final
int numberOfEntries, final int sizeOfEntry)
{
try
{
Connection connection = getMyConnection(dbPath);
Statement statement = connection.createStatement();

// Create primary table.
statement.execute("CREATE TABLE primary_table (id INT, name
VARCHAR(255))");

// Create data tables.
statement.execute("CREATE TABLE data_table(id INT, primary_table_id
INT, data_entry TEXT)");

for (int i = 0; i < numberOfLogs; ++i)
{
String sql = String.format("INSERT INTO primary_table(id, name)
VALUES (%d, 'Log %d')", i, i);
statement.executeUpdate(sql);
}

statement.close();
statement = connection.createStatement();

// Create the raw data to go into the table entries...
char[] rawData = new char[sizeOfEntry];
for (int x = 0; x < rawData.length; ++x)
{
rawData[x] = 'A';
}
String data = new String(rawData);

String sqlFmt = "INSERT INTO data_table (id, primary_table_id,
data_entry) VALUES (%d, %d, '%s')";
String sql;
int count = 0;

for (int i = 0; i < numberOfLogs; ++i)
{
for (int j = 0; j < numberOfEntries; ++j)
{
sql = String.format(sqlFmt, count, i, data);
statement.executeUpdate(sql);
++count;
}
}

// UNCOMMENT the "SHUTDOWN" commands to cleanup the database.
statement.close();
connection.commit();
//connection.createStatement().execute("SHUTDOWN COMPACT");
connection.close();
/*
connection = getMyConnection(dbPath);
connection.createStatement().execute("SHUTDOWN DEFRAG");
connection.close();
*/
}
catch (Exception ex)
{
ex.printStackTrace();
}
}

Please let me know if you need more information.

[Noel Grandin]

We have some weaknesses around BLOB columns, which are probably showing up in your context.

For now either
(a) use the older storage engine by appending MV_STORE=false to your URL
(b) split off your TEXT column into a separate table so you only access it when absolutely necessary
(c) just use a VARCHAR column if your data is not going to be particularly large
(d) append LOB_TIMEOUT=0 to your URL, but this means that you can only access BLOB columns from inside a transaction.

[bryan]

Hi, thanks for the reply.

I'm not sure it is related to the TEXT/CLOB column. In my tests I have used
TEXT,CLOB, and VARCHAR with small sizes (64 bytes each) and larger sizes
(1MB each). All tests have shown the same increase in size. MV_STORE=false
did not seem to change this. Although, I only ran one test so far.

Some things I'm seeing:
- The smaller the WRITE_DELAY is, the larger the database grows which makes
me think its related to transactions (although I don't think I have any
uncommitted ones).
- Calling SHUTDOWN COMPACT shrinks the database to an expected size. But
why do I have to call it manually? It does not seem to compact when the
last connection is closed. Am I closing it wrong?
What is the proper way to maintain the database (COMPACT, DEFRAG etc.) in a
long running application?

[Noel Grandin]

Post by bryan
(1MB each). All tests have shown the same increase in size. MV_STORE=false
did not seem to change this. Although, I only ran one test so far.

If using the old engine also resulted in an increase in size then you
have a bug in your tests, since that engine is extremely reliable.

The most common problem is that somewhere you are opening a
transaction and not committing it, which results in the transaction
log continually growing.

[bryan]

Yes, that was my conclusion in the original post. However, I could not find
any uncommitted transactions. My database creation test is right up above.
If my test has a bug, then perhaps someone can spot it.
Thanks!

Post by Noel Grandin

Post by bryan
(1MB each). All tests have shown the same increase in size.

MV_STORE=false

Post by bryan
did not seem to change this. Although, I only ran one test so far.

If using the old engine also resulted in an increase in size then you
have a bug in your tests, since that engine is extremely reliable.
The most common problem is that somewhere you are opening a
transaction and not committing it, which results in the transaction
log continually growing.