MySQL globalproperties table massive growth

I have a test VM with orthanc on it with no active utilization on the machine from any user or dicom activity. It has been in place for a few months now. Last week all of the sudden, the globalproperties table size on disk started to grow rapidly but there are only 3 rows in the table. It was 30gb the other day when I ran “optimize table globalproperties” which shrunk the table down to less than 5mb. In addition, my binlogs are expanding at about the same pace due to this issue. Before I started to write this message it had reached 16gb again so I ran the optimize command which dropped it to 4mb. As soon as I started orthanc, the table started to grow at a rate of a few megabytes a minute. It is up at 50mb right now.

It seems like maybe orthanc is continuously writing to this table and deleting data? I have restarted the server and Orthanc multiple times with no change. There is nothing that seems out of place in the logs. The table size does not grow past 4mb unless orthanc is started.

Hello,

I am definitely not an expert in MySQL administration, so I’ll let other people provide a definitive answer.

Every 10 seconds, if some job has made progress, Orthanc creates a backup of all the jobs that are scheduled/running/finished. The goal is to be able to restore the jobs if Orthanc stops in a hard way (crash). The jobs are serialized in the JSON format, then stored as a global property into the “GlobalProperties” table. This property is deleted then re-inserted. This probably explains the behavior you observe.

As a workaround, you can turn off this behavior by setting the “SaveJobs” configuration option of Orthanc to “false”.

Visibly, your issue is a direct consequence of the fact that MySQL doesn’t reuse deleted space. A quick search on StackOverflow led to many results about this problem, such as in this old thread:
https://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table

I see people talking about the “innodb_file_per_table” global configuration option in that respect. Probably you should give a look at it:
https://dev.mysql.com/doc/refman/8.0/en/innodb-file-per-table-tablespaces.html

Or, as a system administrator, you could run a “cron.d” job that would run “OPTIMIZE TABLE GlobalProperties” each night.

In either case, please share your insight, so that the Orthanc Book can be improved in that respect.

Kind Regards,
Sébastien-