Hello,
we are using H2 version 1.4.192. Database URL is jdbc:h2:file:<file
path>;CIPHER=AES;MV_STORE=false.
The size of the database file is growing dramatically in some environments
and we do not understand why. We checked our code for errors that prevented
committing of Hibernate transactions and the closing of Hibernate sessions.
Indeed we did find some issues and corrected them. However, the database
file size is still growing dramatically.
We can reduce the file size by using the SCRIPT and RUNSCRIPT commands. The
reduced database works fine except that it starts growing again.
We run the recovery tool on a database file that has grown big and on a
database file that was reduced. Comparing the recovery tool output of the
two database files we see that the output is a lot bigger for the grown
database.
95% of the lines in the output are of the form
INSERT INTO O_6 VALUES(5154, 0, 0, 441076005, 1865);
..
INSERT INTO O_2 VALUES(5416, 821, 79);
..
Both outputs do contain the following lines
..
DELETE FROM INFORMATION_SCHEMA.LOBS;
INSERT INTO INFORMATION_SCHEMA.LOBS SELECT * FROM O_2;
UPDATE INFORMATION_SCHEMA.LOBS SET TABLE = -2;
DELETE FROM INFORMATION_SCHEMA.LOB_MAP;
INSERT INTO INFORMATION_SCHEMA.LOB_MAP SELECT * FROM O_6;
..
So it seems that entries in the INFORMATION_SCHEMA.LOBS and
INFORMATION_SCHEMA.LOB_MAP cause the growing of the database. What data is
stored in this tables?
I attached excerpts of the output of the recovery tool.
Regards,
Reinhold
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
Lot's of entries of the following kind:
..
INSERT INTO O_2 VALUES(151187297, 821, 79);
INSERT INTO O_2 VALUES(151187300, 821, 79);
INSERT INTO O_2 VALUES(151187309, 821, 79);
INSERT INTO O_2 VALUES(151187312, 821, 79);
INSERT INTO O_2 VALUES(151187321, 821, 79);
INSERT INTO O_2 VALUES(151187324, 821, 79);
INSERT INTO O_2 VALUES(151187333, 821, 79);
INSERT INTO O_2 VALUES(151187336, 821, 79);
INSERT INTO O_2 VALUES(151187345, 821, 79);
INSERT INTO O_2 VALUES(151187348, 821, 79);
INSERT INTO O_2 VALUES(151187357, 821, 79);
INSERT INTO O_2 VALUES(151187360, 821, 79);
INSERT INTO O_2 VALUES(151187369, 821, 79);
INSERT INTO O_2 VALUES(151187372, 821, 79);
INSERT INTO O_2 VALUES(151187381, 821, 79);
-- page 354325: b-tree leaf (last) parent: 344185 index: 9 entries: 19
-- page 354326: data leaf (last) parent: 354356 table: 6 entries: 20 columns: 5
INSERT INTO O_6 VALUES(151187321, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187324, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187333, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187336, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187345, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187348, 0, 0, -1683029071, 5997);
INSERT INTO O_6 VALUES(151187357, 0, 0, -1683029071, 5997);
INSERT INTO O_6 VALUES(151187360, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187369, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187372, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187381, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187384, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187393, 0, 0, -1596552293, 5995);
INSERT INTO O_6 VALUES(151187396, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187405, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187408, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187417, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187420, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187429, 0, 0, -1411958646, 5996);
INSERT INTO O_6 VALUES(151187432, 0, 0, -1596552293, 5995);
-- page 354327: b-tree leaf (last) parent: 346064 index: 9 entries: 6
-- page 354328: data leaf (last) parent: 353976 table: 2 entries: 4 columns: 3
INSERT INTO O_2 VALUES(151053475, 821, 79);
INSERT INTO O_2 VALUES(151053484, 821, 79);
INSERT INTO O_2 VALUES(151053487, 821, 79);
INSERT INTO O_2 VALUES(151053495, 821, 79);
-- page 354329: data leaf (last) parent: 354356 table: 6 entries: 4 columns: 5
INSERT INTO O_6 VALUES(151053475, 0, 0, 289245169, 5983);
INSERT INTO O_6 VALUES(151053484, 0, 0, 289245169, 5983);
INSERT INTO O_6 VALUES(151053487, 0, 0, 923649667, 5984);
INSERT INTO O_6 VALUES(151053495, 0, 0, 923649667, 5984);
-- page 354330: b-tree leaf (last) parent: 356289 index: 7 entries: 25
-- page 354331: b-tree leaf (last) parent: 356289 index: 7 entries: 33
-- page 354332: data leaf (last) parent: 355336 table: 2 entries: 20 columns: 3
INSERT INTO O_2 VALUES(151187384, 821, 79);
INSERT INTO O_2 VALUES(151187393, 821, 79);
..
DELETE FROM INFORMATION_SCHEMA.LOBS;
INSERT INTO INFORMATION_SCHEMA.LOBS SELECT * FROM O_2;
UPDATE INFORMATION_SCHEMA.LOBS SET TABLE = -2;
DELETE FROM INFORMATION_SCHEMA.LOB_MAP;
INSERT INTO INFORMATION_SCHEMA.LOB_MAP SELECT * FROM O_6;
...
---- Statistics ----
-- page count: 400942, free: 0
-- page data bytes: head 23948277, empty 817658364, rows 55015951 (9% full)
-- data leaf 54%, 218902 page(s)
-- data node 0%, 876 page(s)
-- data overflow 0%, 185 page(s)
-- btree leaf 44%, 179100 page(s)
-- btree node 0%, 1464 page(s)
-- free list 0%, 13 page(s)
-- stream trunk 0%, 1 page(s)
-- stream data 0%, 398 page(s)
Some of entries of the following kind:
..
INSERT INTO O_2 VALUES(5022, 821, 79);
INSERT INTO O_2 VALUES(5024, 821, 79);
INSERT INTO O_2 VALUES(5026, 821, 79);
INSERT INTO O_2 VALUES(5028, 821, 79);
INSERT INTO O_2 VALUES(5030, 821, 79);
INSERT INTO O_2 VALUES(5032, 821, 79);
..
INSERT INTO O_6 VALUES(5204, 0, 0, 441076005, 1865);
INSERT INTO O_6 VALUES(5206, 0, 0, 441076005, 1865);
INSERT INTO O_6 VALUES(5208, 0, 0, 129191857, 1866);
INSERT INTO O_6 VALUES(5210, 0, 0, -1596552293, 1859);
INSERT INTO O_6 VALUES(5212, 0, 0, -1596552293, 1859);
INSERT INTO O_6 VALUES(5214, 0, 0, 441076005, 1865);
INSERT INTO O_6 VALUES(5216, 0, 0, 441076005, 1865);
..
DELETE FROM INFORMATION_SCHEMA.LOBS;
INSERT INTO INFORMATION_SCHEMA.LOBS SELECT * FROM O_2;
UPDATE INFORMATION_SCHEMA.LOBS SET TABLE = -2;
DELETE FROM INFORMATION_SCHEMA.LOB_MAP;
INSERT INTO INFORMATION_SCHEMA.LOB_MAP SELECT * FROM O_6;
...
---- Statistics ----
-- page count: 2897, free: 494
-- page data bytes: head 89675, empty 5169158, rows 4161967 (46% full)
-- free 0%, 14 page(s)
-- data leaf 79%, 2300 page(s)
-- data node 0%, 17 page(s)
-- data overflow 0%, 22 page(s)
-- btree leaf 1%, 55 page(s)
-- btree node 0%, 5 page(s)
-- free list 0%, 1 page(s)
-- stream data 16%, 477 page(s)
