Subsonic database bloat/corruption

Update 04/08/2019:

I've had trouble with this method on newer versions of Subsonic and since the original technique was kind of bad - it more or less triggers crash recovery - I decided to take another look. I ended up finding the hsqldb-utils package, which includes some command-line utilities to manage HSQLDB databases.

Unfortunately, this package doesn't use the same version of HSQLDB as Subsonic - my Subsonic instance is using 1.8.0.5 (it lists this in the database's 'subsonic.properties' file), whereas the tools included in hsqldb-utils (on Ubuntu Bionic, at least) are well past 2.0. But, this is at least a step in the right direction. We can download an older version of the tools.

I found the following jars by scouring the internet, downloading them into /tools/ on my system.

  • hsqldb-sqltool-jdk1.5-2.0.jar
  • hsqldb-1.8.0.5.jar

Without further ado, here's the happy-path way to de-bloat your database:

  1. Install Java. You probably already have this since you're running Subsonic, right?
  2. Stop Subsonic. Subsonic must NOT be running while is operation is performed or database corruption is likely.
  3. Change directories into the database dir. The path below could be different for you:
    • cd /var/subsonic/db/
  4. Open the database using hsqldb-sqltool. Take note of the classpath (-cp), this needs to be pointed at the two jars above:
    • java -cp :/tools/hsqldb-sqltool-jdk1.5-2.0.jar:/tools/hsqldb-1.8.0.5.jar org.hsqldb.cmdline.SqlTool --inlineRc=url=jdbc:hsqldb:file:subsonic --driver org.hsqldb.jdbcDriver
  5. Run the compact function:
    • sql> SHUTDOWN COMPACT;
  6. Exit hsqldb-sqltool by pressing control-d.

At this point, Subsonic's database should be drastically smaller. In my case:

Before: 

# du -sh /srv/subsonic
3.2G    /srv/subsonic

After:

# du -sh /srv/subsonic
14M     /srv/subsonic

As insert/update/deletes queries are performed data moves around inside of HSQLDB's data files. Over time, and similar to a hard drive, data becomes fragmented and the database file size grows despite much of the space within it being "not in use". What we've done is rewrite the database file in an optimized way such that there is no "unused" space left within the file; all the data is contiguous. It will bloat again over time, but that's a concern for later. Do take a look at HSQLDB's docs on the subject.

I maintain a Dockerfile for Subsonic and will eventually add this database compression method as an automated task within the image. This should solve Subsonic database bloat once and for all! Edit: This is complete! As start of the startup procedure, my Docker image will compress the database at every startup.

 

Original article below. I highly recommend NOT doing this; it is here for posterity:

I use a Subsonic media streamer install to stream my music library on my mobile phone, media center, laptop etc. Recently, my web UI and other client apps stopped working and I noticed the subsonic server was spewing database errors.

Ugly things like:

87431 [btpool0-6] WARN org.mortbay.log - Nested in org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [ <snip> ]:
java.sql.SQLException: java.io.IOException: S1000 Data file size limit is reached in statement [update media_file set <snip>]
    at org.hsqldb.jdbc.Util.throwError(Unknown Source)
    at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)

...With hundreds and hundreds of lines in each stack trace. Subsonic is written in Java and uses a database called HSQLDB which has some sort of size-limiting upper limit that's being hit. Since it is similar to SQLite, we can check the size of the database files on disk. Inspecting these revealed it had grown to 2 gigabytes! Considering it holds only a list of media files, a few playlists and some settings, there's no reason it should be that large.

Buried in HSQLDB's docs I found a description of it's startup procedure, here. It turns out by simply changing the "modified" setting to "yes" in the database's property file, the engine triggers a database recovery routine (which actually restores an internal backup). On Ubuntu linux, the database property file is /var/subsonic/db/subsonic.properties. After starting subsonic and letting it grind for a few minutes, I just needed to subsonic re-scan my media files and it was back in business! Not one setting or play count was lost. After the cleanup, the database is all of 44 MB. Nice!

I thought this was a pretty easy fix, but tricky to find. If anyone finds this helpful, shoot me a comment below!

Tags: