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);
}
}
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/6ed9ca4f-bc55-4da5-970e-ae2bb6abd54do%40googlegroups.com.
