Re: [h2] Continuous Increase in H2 db size after dropping and loading same data repeatedly

[Davide Cavestro]

Before giving up definitively, could you please tell me if getting the 
*pageStore: Transaction log could not be truncated; size: 16 MB *
warning in the trace file indicates an error?
I feel very dumb, but I got it in cases were I wouldn't expect it... and it 
it indicates a problem, then it could reveal the cause of the increasing db 
size issue.

So I've attached a simple standalone test case that uses a single 
connection with auto commit to create and populate 20 tables with 7001 rows 
each one.
It always reproduces the warning mentioned above, approximately at row 5150 
of the last table.


On Wednesday, August 21, 2013 9:36:14 AM UTC+2, Noel Grandin wrote:
>
>  
> On 2013-08-21 07:56, Thomas Mueller wrote:
>  
> Hi, 
>
>  I suspect if you read mailing list messages from a mobile device you miss
>> preformatted contents... I posted the head and tail of the .sql file in my
>> previous post
>
>
>  Well, I think you didn't post the content of the transaction log (not 
> before, and not now).
>
>    Davide seems to be using some other interface (other then google 
> groups or straight email) to post to this newsgroup, so quite often his 
> posts are incomplete.
>
> Also, I have modified the SESSIONS metadata table - I replaced the 
> UNDO_LOG_SIZE column with a "CONTAINS_UNCOMMITTED" column, which should be 
> more reliable as an indicator of uncommitted transactions.
>
>  

-- 
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 http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import org.h2.jdbcx.JdbcDataSource;
import org.h2.store.fs.FileUtils;

/**
 * A standalone test case trying to reproduce an issue about <a
 * href="http://h2-database.66688.n3.nabble.com/h2-Continuous-Increase-in-H2-db-size-after-dropping-
 * and-loading-same-data-repeatedly-td4026836.html">continuous increase in H2 db size</a>.
 * 
 * @author Davide Cavestro
 */
public class H2IncreasingSizeTest {

    private static final int ITERATIONS_COUNT = 1;
    // private static final int TABLE_COUNT = 5000;
    // private static final int ROW_COUNT = 101;
    private static final int TABLE_COUNT = 20;
    private static final int ROW_COUNT = 7001;

    /**
     * @param args
     */
    public static void main(final String[] args) {
        try {
            new H2IncreasingSizeTest().run();
        } catch (final IOException e) {
            e.printStackTrace();
        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }

    private void run() throws IOException, SQLException {
        final File dbDir = new File(System.getProperty("java.io.tmpdir"), "h2sizetest");
        // remove previous contents
        FileUtils.deleteRecursive(dbDir.getCanonicalPath(), false);
        dbDir.mkdirs();

        final String dbName = "data";
        final String url = "jdbc:h2:" + dbDir.getCanonicalPath() + "/" + dbName;

        final JdbcDataSource dataSource = new JdbcDataSource();
        dataSource.setURL(url);
        dataSource.setUser("sa");
        dataSource.setPassword("sa");

        for (int i = 0; i < ITERATIONS_COUNT; i++) {
            final Connection connection = dataSource.getConnection();
            connection.setAutoCommit(true);
            try {
                // Drop db contents
                {
                    final Statement stmt = connection.createStatement();
                    try {
                        stmt.executeUpdate("DROP ALL OBJECTS");
                    } finally {
                        stmt.close();
                    }
                }

                // Generate tables and populate them with fake data.
                for (int tableIdx = 0; tableIdx < TABLE_COUNT; tableIdx++) {
                    final Statement stmt = connection.createStatement();
                    try {
                        stmt.executeUpdate("CREATE TABLE mytable" + tableIdx
                                + " (the_pk integer, the_name varchar(4000), the_ts timestamp)");
                    } finally {
                        stmt.close();
                    }

                    final PreparedStatement pstmt =
                            connection.prepareStatement("INSERT INTO mytable" + tableIdx
                                    + " (the_pk, the_name, the_ts) VALUES (?,?,?)");
                    try {
                        for (int rowIdx = 0; rowIdx < ROW_COUNT; rowIdx++) {
                            pstmt.setInt(1, rowIdx);
                            final String name =
                                    "A veeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeery long "
                                            + "string generated only to insert enough data to reproduce "
                                            + "the increasing db size issue (provided the record size somewhat matters) "
                                            + rowIdx;
                            pstmt.setString(2, name);
                            pstmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));

                            pstmt.execute();
                        }
                    } finally {
                        pstmt.close();
                    }
                }
            } finally {
                connection.close();
            }
        }
        /*
         * at the end you will see "pageStore: Transaction log could not be truncated; size: 16 MB" in the trace file
         */
    }
}