H2 in-memory DB performance

[Lalith Suresh]

Hi all,

I'm using H2 as an in-memory cache in a long-running server application. Are there any guidelines on configuring H2 for such use, with the primary goal being low latency for queries? I'd like to bring performance as close as possible to say, scanning some in-memory collections using an SQL interface.

One key latency-sensitive step here is where I need to query 10-30 tables (select * from table), serially or in parallel, whichever is faster. Here is a benchmark I'm using to evaluate, and the code I'm using to setup a HikariCP connection pool that is backed by H2. The benchmark below is what I've arrived at by iteratively stripping away code from the original system's code path of concern.

The workloads are running on OSX, with OpenJDK - 12.0.1. I've also done a sweep with different numbers of tables/rows etc, and can share more data if required.

Benchmark                                          (rowsPerTable)  Mode  Cnt        Score        Error  Units
ModelUpdateDataBenchmark.withPreparedPool                    1000  avgt   10  4973866.388 ± 734500.031  ns/op
ModelUpdateDataBenchmark.withPreparedPoolParallel            1000  avgt   10  1313731.990 ± 186437.956  ns/op

The parts that I'd like feedback on:

 - is 1.3ms what I should expect to query 10 tables, each with 1000 rows of 2 columns, from an in-memory H2 db? It still feels high, but I'm likely missing something.

 - Any JDBC options I should be using? E.g., I added the options to disable tracing after I spotted JdbcResultSet.getString() spending a lot of time in debugCodeCall(). I feel like there might be more ways to tweak this further. :)

Thanks in advance!

--

Lalith

@Warmup(iterations = 5, time = 1, timeUnit = TimeUnit.SECONDS)
@Measurement(iterations = 10, time = 1, timeUnit = TimeUnit.SECONDS)
@Fork(1)
@BenchmarkMode(Mode.AverageTime)
@OutputTimeUnit(TimeUnit.NANOSECONDS)
@State(Scope.Benchmark)
public class ModelUpdateDataBenchmark {
    private static final int NUM_TABLES = 10;

    @Param({"1000"})
    static int rowsPerTable;

    @State(Scope.Benchmark)
    @SuppressWarnings("all")
    public static class BenchmarkState {

        final DBConnectionPool dbConnectionPool = setupDbConnectionPool();
        final AtomicInteger integer = new AtomicInteger(0);
        @Nullable
        Model model = null;

        @Setup(Level.Invocation)
        public void setUpDb() {
            final DSLContext conn = dbConnectionPool.getConnectionToDb();
            for (int t = 0; t < NUM_TABLES; t++) {
                conn.execute(String.format("delete from t%s where c2 >= 0", t));
                for (int i = 0; i < rowsPerTable; i++) {
                    final int val = integer.incrementAndGet();
                    conn.execute(String.format("insert into t%s values ('%s', %s)", t, val, val));
                }
            }
        }

        private DBConnectionPool setupDbConnectionPool() {
            final DBConnectionPool dbConnectionPool = new DBConnectionPool();
            for (int t = 0; t < NUM_TABLES; t++) {
                dbConnectionPool.getConnectionToDb()
                        .execute(String.format("create table t%s (c1 varchar(36) primary key, c2 integer)", t));
            }
            return dbConnectionPool;
        }
    }

    @Benchmark
    public void withPreparedPoolParallel(final ModelUpdateDataBenchmark.BenchmarkState state,
                                         final Blackhole blackhole) {
        IntStream.range(0, NUM_TABLES).parallel().forEach(
                t -> {
                    try (final ResultQuery<?> rq =
                             state.dbConnectionPool.getConnectionToDb().selectFrom("t" + t).keepStatement(true)) {
                        blackhole.consume(rq.fetch());
                    }
                }
        );
    }

    @Benchmark
    public void withPreparedPool(final ModelUpdateDataBenchmark.BenchmarkState state,
                                         final Blackhole blackhole) {
        IntStream.range(0, NUM_TABLES).forEach(
                t -> {
                    try (final ResultQuery<?> rq =
                                 state.dbConnectionPool.getConnectionToDb().selectFrom("t" + t).keepStatement(true)) {
                        blackhole.consume(rq.fetch());
                    }
                }
        );
    }
}

#DBConnectionPool

class DBConnectionPool {
    private static final Settings JOOQ_SETTING = new Settings().withExecuteLogging(false);
    private final String databaseName;
    private final DataSource ds;

    DBConnectionPool() {
        this.databaseName = UUID.randomUUID().toString();
        setupDb();
        final HikariConfig config = new HikariConfig();
        config.setJdbcUrl(String.format("jdbc:h2:mem:%s;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0;",
                databaseName));
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.addDataSourceProperty("maximumPoolSize", "20");
        this.ds = new HikariDataSource(config);
    }

    @VisibleForTesting
    DSLContext getConnectionToDb() {
        return using(ds, SQLDialect.H2, JOOQ_SETTING);
    }
}