Precompute h2 persisted cache space used per entry
The problem is h2 cannot execute concurrent queries on the same
database. The query to get the disk stats(count and space) is a long
lasting query which blocks any other queries to the same cache. The disk
stats are needed when pruning the cache (every day at 01:00) and when
caches are listed, either using the REST API or the show-caches ssh
command.
Here is an example of the problem: diff_intraline of 1GB limit with 1.6
million entries can take up to 16 seconds to compute disk stats. During
that time, any request that needs to get/insert data into the diff
intraline will be blocked until disk stats are done. Every time an admin
executes the show-caches command, a lot of requests can be blocked for a
long time; (number of big persisted caches) x (time to compute stats)
The problem is even worse during pruning because right after computing
the space, another long lasting query is executed; select every row and
associated space used.
Precomputing the space used improves the situation. In the previous
example, it reduces the query time from 16 to 4 seconds.
The real fix would be to also precompute the sum of space used by all
entries in a separate table but this is more complex since it would
involve triggers to keep the sum up to date when a row is inserted,
updated or deleted. Even with this, it would still not be perfect, in the
previous example, it reduces the query time from 4 to 2.5 seconds. The
number of rows also needs to be precomputed, but precomputing this would
be wrong since counting number of rows should be quick in a database; it
should not be implemented in the schema.
This change improves the situation by cutting the query time by 4 with
minimal change. A real fix will be done in another change.
Bug: Issue 5876
Change-Id: I38805688289a474aa719c52ab03079cbf11d1c5c
diff --git a/gerrit-cache-h2/src/main/java/com/google/gerrit/server/cache/h2/H2CacheImpl.java b/gerrit-cache-h2/src/main/java/com/google/gerrit/server/cache/h2/H2CacheImpl.java
index b0c7764..97c540e 100644
--- a/gerrit-cache-h2/src/main/java/com/google/gerrit/server/cache/h2/H2CacheImpl.java
+++ b/gerrit-cache-h2/src/main/java/com/google/gerrit/server/cache/h2/H2CacheImpl.java
@@ -504,7 +504,8 @@
try {
c = acquire();
if (c.put == null) {
- c.put = c.conn.prepareStatement("MERGE INTO data VALUES(?,?,?,?)");
+ c.put = c.conn.prepareStatement(
+ "MERGE INTO data (k, v, created, accessed) VALUES(?,?,?,?)");
}
try {
keyType.set(c.put, 1, key);
@@ -572,7 +573,7 @@
try (Statement s = c.conn.createStatement()) {
long used = 0;
try (ResultSet r =
- s.executeQuery("SELECT SUM(OCTET_LENGTH(k) + OCTET_LENGTH(v)) FROM data")) {
+ s.executeQuery("SELECT SUM(space) FROM data")) {
used = r.next() ? r.getLong(1) : 0;
}
if (used <= maxSize) {
@@ -583,7 +584,7 @@
s.executeQuery(
"SELECT"
+ " k"
- + ",OCTET_LENGTH(k) + OCTET_LENGTH(v)"
+ + ",space"
+ ",created"
+ " FROM data"
+ " ORDER BY accessed")) {
@@ -618,7 +619,7 @@
s.executeQuery(
"SELECT"
+ " COUNT(*)"
- + ",SUM(OCTET_LENGTH(k) + OCTET_LENGTH(v))"
+ + ",SUM(space)"
+ " FROM data")) {
if (r.next()) {
size = r.getLong(1);
@@ -670,7 +671,7 @@
this.url = url;
this.conn = org.h2.Driver.load().connect(url, null);
try (Statement stmt = conn.createStatement()) {
- stmt.execute(
+ stmt.addBatch(
"CREATE TABLE IF NOT EXISTS data"
+ "(k "
+ type.columnType()
@@ -679,6 +680,10 @@
+ ",created TIMESTAMP NOT NULL"
+ ",accessed TIMESTAMP NOT NULL"
+ ")");
+ stmt.addBatch(
+ "ALTER TABLE data ADD COLUMN IF NOT EXISTS "
+ + "space BIGINT AS OCTET_LENGTH(k) + OCTET_LENGTH(v)");
+ stmt.executeBatch();
}
}
