Shrinking H2 database files

Our code review system Gerrit has several caches, the largest ones being backed up on disk. The disk caches offload memory usage and persist the data between restarts. As a Java application, the caches are stored in H2 database files and I recently had to find how to connect to them in order to inspect their content and reduce their size.

In short: java -Dh2.maxCompactTime=15000 ... would cause the H2 driver to compact the database upon disconnection.


During an upgrade, the Gerrit installation filed up the system root partition entirely (incident report for Gerrit 3.5 upgrade). The reason is two caches occupying 9G and 11G out of a the 40G system partition. Those caches hold differences to files made by patchsets and are stored in two files:

/var/lib/gerrit2/review_site/cache/Size (MB)

An easy fix would have been to stop the service, delete all caches, restart the service and let the application refile the cold caches. It is a short term solution, long term what if it is an issue in the application and we have to do the same all over again in the next few weeks? The large discrepancy also triggered my curiosity and I had to know the exact root cause to find a definitive fix to it. There started my journey of debugging.

They are all empty?

When looking at the cache through the application shows caches are way smaller at around 150MBytes:

ssh -p 29418 gerrit.wikimedia.org gerrit show-caches
  Name                          |Entries              |  AvgGet |Hit Ratio|
                                |   Mem   Disk   Space|         |Mem  Disk|
D gerrit_file_diff              | 24562 150654 157.36m|  14.9ms | 72%  44%|
D git_file_diff                 | 12998 143329 158.06m|  14.8ms |  3%  14%|

One could assume some overhead but there is no reason for metadata to occupy hundred times more space than the actual data they are describing. Specially given each cached item is a file diff which is more than a few bytes. To retrieve the files locally I compressed them with gzip and they shrunk to a mere 32 MBytes! It is a strong indication those files are filled mostly with empty data which suggests the database layer never reclaims no more used blocks. Reclaiming is known as compacting in H2 database or vacuuming in Sqlite.


Once I retrieved the files, I have tried to connect to them using the H2 database jar and kept doing mistakes after mistakes due to my completely lack of knowledge on that front:

Version matters

At first I tried with the latest version h2-2.1.214.jar and it did not find any data. I eventually found out the underlying storage system has been changed compared to version 1.3.176 used by Gerrit.I thus had to use an older version which can be retrieved from the Gerrit.war package.

File parameter which is not a file

I then wanted to a SQL dump of the database to inspect it using the Script java class: java -cp h2-1.3.176.jar org.h2.tools.Script, it requires a -url option which is a jdbc URI containing the database name. Intuitively I gave the full file name:

java -cp h2-1.3.176.jar org.h2.tools.Script -url jdbc:h2:git_file_diff.h2.db'

It returns instantly and generate the dump:


Essentially an empty file. Looking at file on disk it created a git_file_diff.h2.db.h2.db file which is 24kbytes. Lesson learned, the h2.db suffix must be removed from the URI. I was then able to create the dump using:

java -cp h2-1.3.176.jar org.h2.tools.Script -url jdbc:h2:git_file_diff'

Which resulted in a properly sized backup.sql.

Web based admin

I have altered the SQL to make it fit Sqlite in order to load it in SqliteBrowser (a graphical interface which is very convenient to inspect those databases). Then I found invoking the jar directly starts a background process attached to the database and open my web browser to a web UI: java -jar h2-1.3.176.jar -url jdbc:h2:git_file_diff:

h2_web_ui.png (470×810 px, 92 KB)

That is very convenient to inspect the file. The caches are are key value storages with a column keeping track of the size of each record. Summing them is how gerrit show-caches finds out the size of the caches (roughly 150Mbytes for the two diff caches).

Compacting solutions

The H2 Database feature page mentions empty space is to be re-used which is not the case as seen above. The document states when the database connection is closed, it compact it for up to 200 milliseconds. Gerrit establish the connection on start up and keep it up until it is shutdown at which point the compaction occurs. It is not frequent enough, and the small delay is apparently not sufficient to compact our huge databases. To run a full compaction several methods are possible:

SHUTDOWN COMPACT: this request an explicit compaction and terminates the connection. The documentation implies it is not subject to the time limit. That would have required a change in the Gerrit Java code to issue the command.

org.h2.samples.Compact script: H2 has a org.h2.samples.Compact to manually compact a given database, it would need some instrumentation to trigger it against each file after Gerrit is shutdown, possibly as a systemd.service ExecStopPost and iterating through each files.

jdbc URL parameter MAX_COMPACT_TIME: the 200 milliseconds can be bumped by adding the parameter to the JDBC connection URL (separated by a semi column ;). Again it would require a change in Gerrit Java code to modify the way it connects.

The beauty of open source is I could access the database source code. It is hosted in https://github.com/h2database/h2database in the version-1.3 tag which holds a subdirectory for each sub version. When looking at a setting, the database driver uses the following piece of code (code licensed under Mozilla Public License Version 2.0 or Eclipse Public License 1.0):

60     /**
61      * Get the setting for the given key.
62      *
63      * @param key the key
64      * @param defaultValue the default value
65      * @return the setting
66      */
67     protected String get(String key, String defaultValue) {
68         StringBuilder buff = new StringBuilder("h2.");
69         boolean nextUpper = false;
70         for (char c : key.toCharArray()) {
71             if (c == '_') {
72                 nextUpper = true;
73             } else {
74                 // Character.toUpperCase / toLowerCase ignores the locale
75                 buff.append(nextUpper ? Character.toUpperCase(c) : Character.toLowerCase(c));
76                 nextUpper = false;
77             }
78         }
79         String sysProperty = buff.toString();
80         String v = settings.get(key);
81         if (v == null) {
82             v = Utils.getProperty(sysProperty, defaultValue);
83             settings.put(key, v);
84         }
85         return v;
86     }

When retrieving the setting MAX_COMPACT_TIME it forges a camel case version of the setting name prefixed by h2. which gives h2.maxCompactTime then look it up in the JVM properties an if set pick its value.

Raising the compact time limit to 15 seconds is thus all about passing to java: -Dh2.maxCompactTime=15000.

Applying and resolution

7f6215e039 in our Puppet applies the fix and summarize the above. Once I applied, I restart Gerrit once to have the setting taken in account and restarted it a second time to have it disconnect from the databases with the setting applied. The results are without appeal. Here are the largest gains:


The gerrit_file_diff and git_file_diff went from respectively 12GB and 8.2G to 0.5G which addresses the issue.


Setting the Java property -Dh2.maxCompactTime=15000 was a straightforward fix which does not require any change to the application code. It also guarantee the database will keep being compacted each time Gerrit is restarted and the issue that has lead to a longer maintenance window than expect would not reappear.

Happy end of year 2022!


Written by hashar on Dec 16 2022, 3:38 PM.
"Love" token, awarded by Remagoxer."Barnstar" token, awarded by thcipriani.

Event Timeline

It's funny, I would have probably fallocate --dig-holes <database> to reclaim the actual space from h2 behind their back.

(ok, you probably can't run that while gerrit is running without risking a race condition with it, that would require wrapping the command on a lock compatible to what it uses)