[h2] DB-File growth: internal LOB tables are not cleaned up

[dresa_]

Hello,

we have issues with a steadily growing database file. I've already used the
recovery tool and tracked the problem down to the internal LOB management.

here some basic information:
- Database size after shutdown compact is only about 1 MB and it keeps
growing over time ( it reached 1GB a few days ago )
- INFORMATION_SCHEMA.IN_DOUBT without entries
- H2 version 1.3.171
- USED DB URL PARAMETER: MODE=Oracle;AUTO_SERVER=TRUE
- h2.lobInDatabase is set true

Cause of the problem is a table that is used for messaging between multiple
systems and has one field with the type long raw ( which is stored in the
lob table ).
After an entry of this messaging table gets deleted the corresponding lob
remains in the table INFORMATION_SCHEMA.LOB_DATA.

Is there a way to get rid of this no longer required LOBs without executing
and shutdown compact?

Thank you very much in advance,
dresa



--
View this message in context: http://h2-database.66688.n3.nabble.com/h2-DB-File-growth-internal-LOB-tables-are-not-cleaned-up-tp4027264.html
Sent from the H2 Database mailing list archive at Nabble.com.

[Noel Grandin]

On 2013-08-26 18:24, dresa_ wrote:
> we have issues with a steadily growing database file. I've already used the
> recovery tool and tracked the problem down to the internal LOB management.
>

Hi

We're working on a solution for this.
Shouldn't be more than a couple of weeks.
At the moment, unfortunately, the only work-around is to shutdown
periodically.

-- Noel

[Noel Grandin]

On 2013-08-26 18:24, dresa_ wrote:

> we have issues with a steadily growing database file. I've already used the
> recovery tool and tracked the problem down to the internal LOB management.
>

I found some time today, so this is fixed in SVN now.

[dresa_]

Awesome, that was fast fix. Thanks a lot! :)



--
View this message in context: http://h2-database.66688.n3.nabble.com/h2-DB-File-growth-internal-LOB-tables-are-not-cleaned-up-tp4027264p4027268.html

[dresa_]

Hi Guys,

it took me some time to start testing this change, but it doesn't seem to
work as i expected. After deploying our System with the H2 Database Version
1.3.174 (2013-10-19) the same problem occurs.

Our System works complete transactional, although commits do not clean the
internal h2 LOB Tables after their corresponding data record is deleted. The
only way to stop the Database Files from growing is the use a shutdown
compact regularly.

I hope someone finds the time to take a look at this again or give me a hint
what we are doing wrong.

Thank you very much in advance,
dresa

dresa_ wrote

View this message in context: http://h2-database.66688.n3.nabble.com/h2-DB-File-growth-internal-LOB-tables-are-not-cleaned-up-tp4027264p4028398.html

[Noel Grandin]

On 2014-01-30 08:21, dresa_ wrote:
>
>
>
> Our System works complete transactional, although commits do not clean the
> internal h2 LOB Tables after their corresponding data record is deleted. The
> only way to stop the Database Files from growing is the use a shutdown
> compact regularly.
>

That sounds like a dangling commit, since some of our LOB cleanup logic runs at commit()/rollback() time.

i.e. you might have some piece of code somewhere that opens a transaction and does not close it.

[Thomas Mueller]

Hi,

Yes, it could be an open transaction.

The statement "shutdown compact" doesn't remove any LOB entries. If the statement shrinks your database file, then the entries were already removed before that.

Please note the database file doesn't shrink if you delete data (but keep the database open). However, empty space within the file is automatically re-used. The database file only ever shrinks if you close the database (close all connections or run "shutdown").

How large is your database before and after you run "shutdown compact"?

Regards,

Thomas

--
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 h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[dresa_]

Hello,

so far i haven't got the time to analyse the application for open
transactions, probably I'll do this next week.

As far as i recall after a full compact the file starts with a couple of
megabytes, about 10 or 20. It only contains some data structure and a bunch
of initial values. After that, data records get inserted and deleted
frequently and the files grows up to 2-3 GB steadily, which takes a bit more
than a month. At this point we are shutting down the application and the
database and using a compact to get the file back to a couple of MB.

I am aware that the DB-File has to grow to a certain amount until it will be
able to live off the free space in the file.


Thomas Mueller-6 wrote

> The statement "shutdown compact" doesn't remove any LOB entries. If the
> statement shrinks your database file, then the entries were already
> removed before that.

Yes, thats the problem. There is LOB data existing in the internal data
schemes which corresponding data record in our tables do not exist anymore
and those are the records that cause our DB-file to grow over time.

After the compact those entries in INFORMATION_SCHEMA.LOB_DATA and the other
two lob-schema tables are deleted. If I understand it correctly these
records should be deleted at every commit or rollback. Is that statement
correct?

Assuming so - I will try to analyse the application and search for open
transactions next week.

Regards,
dresa


Thomas Mueller-6 wrote

> Hi,
>
> Yes, it could be an open transaction.
>
> The statement "shutdown compact" doesn't remove any LOB entries. If the
> statement shrinks your database file, then the entries were already
> removed
> before that.
>
> Please note the database file doesn't shrink if you delete data (but keep
> the database open). However, empty space within the file is automatically
> re-used. The database file only ever shrinks if you close the database
> (close all connections or run "shutdown").
>
> How large is your database before and after you run "shutdown compact"?
>
> Regards,
> Thomas
>
>
>

> On Thu, Jan 30, 2014 at 7:21 AM, dresa_ <

> florian-boeck@

> For more options, visit https://groups.google.com/groups/opt_out.

--
View this message in context: http://h2-database.66688.n3.nabble.com/DB-File-growth-internal-LOB-tables-are-not-cleaned-up-tp4027264p4028421.html

[dresa_]

Hello,

i was able to reproduce the problem and found two scenarios where the
records in the table information_schema.lob_data remains.

*First case*:
open database connection -> insert data records -> rollback -> close
database connection

*Second case*:
open database connection -> insert data records -> close database connection

at least the first case should have cleaned up the lob tables!

I've created a new database with a table test with an id (int) and
data(CLOB). Below is some quick java code i came up with to reproduce the
problem:

I got the same results by connecting directly to the database and testing
manually.

*Expected Result:*
select count(*) from test
0
select count(*) from information_schema.lob_data
0
*Actual Result:*
select count(*) from test
0
select count(*) from information_schema.lob_data
10000



Testcase wrote
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.SQLException;
> import java.sql.Statement;
>
>
> public class Testcase1 {
>
> public static void main(String[] args) {
> final String dbUser = "sa";
> final String dbPassword = "sa";
> final String h2ConnectionURL =
> "jdbc:h2:D:\\workspaces\\h2testing_workspace\\h2testing\\database\\DBtest";
> final String h2Driver = "org.h2.Driver";
> final String testTable = "test";
>
> Connection con = null;
> Statement stmt = null;
>
> /*
> * create table test
> * (
> * id int,
> * data CLOB
> * )
> *
> */
>
> //open database connection
> try {
> Class.forName(h2Driver);
> con = DriverManager.getConnection(h2ConnectionURL,
> dbUser, dbPassword);
> con.setAutoCommit(false);
> }
> catch (SQLException e) {
> System.out.println("Failed to open Database connection");
> }
> catch (ClassNotFoundException e){
> System.out.println("Failed to open Database connection");
> }
>
> //create database statement
> try {
> stmt = con.createStatement();
> } catch (SQLException e) {
> System.out.println("Failed to create Statment");
> }
>
> //insert test data, 10000 data records
> int testDataRecords = 10000;
> int Id = 0;
> for (int i = 0; i < testDataRecords; i++){
>
> //generate test data
> String rdmString = "";
> for(int j = 0; j < 20; j++){
> rdmString += Long.toHexString(Double.doubleToLongBits(Math.random()));
> }
>
> //insert
> try {
> stmt.executeUpdate("insert into " + testTable + " values ('" + Id++ +
> "','" + rdmString + "')");
> } catch (SQLException e) {
> System.out.println("Failed to insert test data");
> }
>
> }
>
> //rollback
> try {
> con.rollback();
> } catch (SQLException e) {
> System.out.println("Failed to rollback the database connection");
> }
>
>
> //close connection
> try {
> stmt.close();
> con.close();
> } catch (SQLException e) {
> System.out.println("Failed to close the database connection");
> }
>
> }
>
> }

Regards,
dresa



dresa_ wrote

--
View this message in context: http://h2-database.66688.n3.nabble.com/DB-File-growth-internal-LOB-tables-are-not-cleaned-up-tp4027264p4028456.html

[Noel Grandin]

I reduced the test-case, but I haven't found the problem yet.

(Add this method to the TestLob unit test class)

private void testCleaningUpLobsOnRollback() throws Exception {
deleteDb("lob");
Connection conn = getConnection("lob");
Statement stat = conn.createStatement();
stat.execute("CREATE TABLE test(id int, data CLOB)");
conn.setAutoCommit(false);
stat.executeUpdate("insert into test values (1, '" + MORE_THAN_128_CHARS + "')");
conn.rollback();
ResultSet rs = stat.executeQuery("select count(*) from test");
rs.next();
assertEquals(0, rs.getInt(1));
rs = stat.executeQuery("select count(*) from information_schema.lob_data");
rs.next();
assertEquals(0, rs.getInt(1));
conn.close();
}

[Steven Hall]

Shouldn't the test be asserting that information_schema.lobs be one row not information_schema.lob_data?  Couldn't a large lob have multiple information_schema.lob_data rows?

[Thomas Mueller]

Hi,

I think the test is correct. I think I found the problem (a rollback doesn't currently remove transient LOB entries), I will need to run some more tests however.

Thanks a lot for the great test case!

Regards,

Thomas

--
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 h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.

[Steven Hall]

Is this behavior also a problem for auto commit Connections?

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

I think not (at least I couldn't reproduce it with autocommit enabled).

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.