ES database with many points inflates abnormally

[Phillip Weeks]

We have an ES with 10k points in the configuration. It is polling every 15 minutes and over a period of a couple weeks the system will eat up all the free disk space however the noSQL database is 50 mb where the entire database is 5G and if I delete the databases and restore everything from backup the database shrinks from 5G down to 500mb!! This was a procedure I learned from an earlier post last year however I am trying to understand why the database is inflating like this over time and if there is something I can do to prevent it.

[phildunlap]

It is likely there is a flux of events and audit events. Were any of the events, userEvents, or audit tables very large? You could change some event or audit levels to 'Ignore'. I think this issue affects MySQL to a lesser degree, so you could install MySQL on it and convert the database. We are aware of the issue being an annoyance for many, and we do intend to resolve how to get the H2 database to compact itself more aggressively. https://github.com/infiniteautomation/ma-core-public/issues/925

[Phillip Weeks]

Thanks Phil at least it means that I haven't necessarily done something to cause this. So in rebuilding this am I to infer that I only need to delete the H2 database and I can restore this without doing anything with the noSQL, is this right?

[phildunlap]

To shrink the H2 database? No, you don't need to touch the NoSQL database. The steps of,

1. Purge tables that need purging
2. Run an SQL backup
3. Stop Mango
4. Move you Mango/databases/mah2.h2.db file (to keep a backup)
5. Start Mango
6. Restore the SQL backup from step 2

Will shrink the H2 database, as you have found.