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
> 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
--
View this message in context:
http://h2-database.66688.n3.nabble.com/DB-File-growth-internal-LOB-tables-are-not-cleaned-up-tp4027264p4028456.html
Sent from the H2 Database mailing list archive at Nabble.com.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
