Thursday, August 23, 2012

Mirth: recover space when mirthdb grows out of control

I was recently asked to recover a mirth instance whose embedded database had grown to fill all available space so this is just a note-to-self kind of post.
Btw: the recovery, depending on db size and disk speed, is going to take long.

The problem

A 1.8 Mirth Connect instance was started, then forgotten (well neglected, actually). The user also forgot to setup pruning so the messages filled the embedded Derby database until it grew to fill all the available space on the disk.
The SO is linux.

The solution

First of all: free some disk space so that the database can be started in embedded mode from the cli. You can also copy the whole mirth install to another server if you cannot free space. Depending on db size you will need a corresponding amount of space: in my case a 5GB db required around 2GB to start, process logs and then store the temp files during shrinking.

Then open a shell as the user that mirth runs as (you're not running it as root, are you?) and cd into the mirth home.
Start the Derby cli as follows:

java -cp lib/derbytools.jar:lib/derby.jar org.apache.derby.tools.ij

All the following commands were given on the Derby console:

ij> connect 'jdbc:derby:mirthdb';
ij> set schema app;
ij> delete from message where DATE_CREATED < TIMESTAMP('2012-01-01 00:00:00');
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'MESSAGE', 1);
ij> exit;

(Basically these commands delete messages older than this year and then compact the stable) At this point the database whould have shrunk enough to restart Mirth and enable the pruner to keep at the desired size.

Last, a word to the wise: embedded db is not recommended for production use.

No comments: