tree c73f643a1e161ae22f476096dac777c990a1739b
parent f2d4fac3f35febc56fb6b64b482bfb35a4de9451
author Hugo Arès <hugo.ares@ericsson.com> 1490392113 -0400
committer David Pursehouse <dpursehouse@collab.net> 1490756995 +0000

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