Low Disk Space, Purge settings adjusted with no effect on H2 database size

---

I don't have any proof as I have not collected any data but I can say the following:

Many months ago, out disk was getting full with a Purge data setting of 4-5 weeks.

Then the disk was starting to get full again, and I reduced that further to a 10 days.

We have not added any additional data points during this time, and the disk is getting full again.

I just realized that the purge settings probably only affect the NoSQL database, but it's the H2 database that is causing us issues.

Can we reset this database cleanly and start again, which I imagine will hold up well for another number of years without trouble. I have instructions for how to delete and restore the database, but that also removes the configuration settings. Is is possible to remove only the data history while keeping the configuration?

What is the difference between core-database and configuration backups anyway?

What configuration details do you find to be missing? What version are you on? The backup / restore is an effective way to shrink the database. No configuration should go missing.

Oh sorry we are running core core 3.3.4 - MangoES.

Which backup should I restore, NoSQL or SQL. I am guessing SQL, but I know restoring NoSQL could also work, or am I wrong?

I have not tried to restore yet, so there aren't any other problems.

There is no reason to backup, delete and then restore your NoSQL database as with the SQL database. It is only housing point values, and any shrinkage would be due to data loss. You want the SQL backup.

I started an SQL restore and I am getting BWB Task Failures.

at java.net.SocketOutputStream.write(SocketOutputStream.java:143) ~[?:1.8.0_151]
        at com.serotonin.modbus4j.sero.messaging.StreamTransport.write(StreamTransport.java:53) ~[modbus4j-3.0.3.jar:?]
        at com.serotonin.modbus4j.sero.messaging.MessageControl.write(MessageControl.java:213) ~[modbus4j-3.0.3.jar:?]
        at com.serotonin.modbus4j.sero.messaging.MessageControl.send(MessageControl.java:149) ~[modbus4j-3.0.3.jar:?]
        at com.serotonin.modbus4j.sero.messaging.MessageControl.data(MessageControl.java:193) [modbus4j-3.0.3.jar:?]
        at com.serotonin.modbus4j.sero.messaging.InputStreamListener.run(InputStreamListener.java:76) [modbus4j-3.0.3.jar:?]
        at com.serotonin.modbus4j.sero.messaging.StreamTransport.run(StreamTransport.java:40) [modbus4j-3.0.3.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_151]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_151]
        at java.lang.Thread.run(Thread.java:748) [?:1.8.0_151]
ERROR 2018-04-17T12:39:33,583 (com.infiniteautomation.nosql.MangoNoSqlBatchWriteBehindManager$StatusProvider.scheduleTimeout:738) - 4 BWB Task Failures, first is: Task Queue Full
ERROR 2018-04-17T12:43:05,714 (com.infiniteautomation.nosql.MangoNoSqlBatchWriteBehindManager$StatusProvider.scheduleTimeout:738) - 4 BWB Task Failures, first is: Task Queue Full
ERROR 2018-04-17T12:43:05,747 (com.infiniteautomation.nosql.MangoNoSqlBatchWriteBehindManager$StatusProvider.scheduleTimeout:738) - 16 BWB Task Failures, first is: Task Queue Full
ERROR 2018-04-17T12:43:05,750 (com.infiniteautomation.nosql.MangoNoSqlBatchWriteBehindManager$StatusProvider.scheduleTimeout:738) - 30 BWB Task Failures, first is: Task Queue Full
ERROR 2018-04-17T12:44:26,352 (com.infiniteautomation.nosql.MangoNoSqlBatchWriteBehindManager$StatusProvider.scheduleTimeout:738) - 5 BWB Task Failures, first is: Task Queue Full
ERROR 2018-04-17T12:44:27,962 (com.infiniteautomation.nosql.MangoNoSqlBatchWriteBehindManager$StatusProvider.scheduleTimeout:738) - 5 BWB Task Failures, first is: Task Queue Full
ERROR 2018-04-17T12:45:28,770 (com.infiniteautomation.nosql.MangoNoSqlBatchWrite                                                                                                                                                             BehindManager$StatusProvider.scheduleTimeout:738) - 8 BWB Task Failures, first i                                                                                                                                                             s: Task Queue Full
ERROR 2018-04-17T12:45:28,783 (com.infiniteautomation.nosql.MangoNoSqlBatchWrite                                                                                                                                                             BehindManager$StatusProvider.scheduleTimeout:738) - 11 BWB Task Failures, first                                                                                                                                                              is: Task Queue Full
ERROR 2018-04-17T12:45:28,786 (com.infiniteautomation.nosql.MangoNoSqlBatchWrite                                                                                                                                                             BehindManager$StatusProvider.scheduleTimeout:738) - 5 BWB Task Failures, first i                                                                                                                                                             s: Task Queue Full
ERROR 2018-04-17T12:46:32,391 (com.infiniteautomation.nosql.MangoNoSqlBatchWrite                                                                                                                                                             BehindManager$StatusProvider.scheduleTimeout:738) - 6 BWB Task Failures, first i                                                                                                                                                             s: Task Queue Full
WARN  2018-04-17T12:46:35,001 (com.serotonin.m2m2.rt.dataSource.PollingDataSourc                                                                                                                                                             e.incrementUnsuccessfulPolls:157) - Data Source 22nd Modbus Network aborted 1750                                                                                                                                                             37 polls since it started.
ERROR 2018-04-17T12:47:48,710 (com.infiniteautomation.nosql.MangoNoSqlBatchWrite                                                                                                                                                             BehindManager$StatusProvider.scheduleTimeout:738) - 6 BWB Task Failures, first is: Task Queue Full
ERROR 2018-04-17T12:47:48,746 (com.infiniteautomation.nosql.MangoNoSqlBatchWrite                                                                                                                                                             BehindManager$StatusProvider.scheduleTimeout:738) - 3 BWB Task Failures, first is: Task Queue Full
ERROR 2018-04-17T12:47:48,796 (com.infiniteautomation.nosql.MangoNoSqlBatchWrite                                                                                                                                                             BehindManager$StatusProvider.scheduleTimeout:738) - 8 BWB Task Failures, first is: Task Queue Full
ERROR 2018-04-17T12:47:48,797 (com.infiniteautomation.nosql.MangoNoSqlBatchWrite                                                                                                                                                             BehindManager$StatusProvider.scheduleTimeout:738) - 9 BWB Task Failures, first is: Task Queue Full
ERROR 2018-04-17T12:47:48,798 (com.infiniteautomation.nosql.MangoNoSqlBatchWrite                                                                                                                                                             BehindManager$StatusProvider.scheduleTimeout:738) - 3 BWB Task Failures, first is: Task Queue Full

Should I just hold out for some hours?

edit: I should mention that the GUI is inaccessible and Mango is down as well.

It could certainly take a moment. I wouldn't expect it to take hours, unless you didn't purge your events, userEvents, and audit tables - in which case it may restore back to the same size! Seems like a critical line has been omitted from that stack trace.

Thankfully it came back up, but you are right, it's right back to what it was. I still need to find a way to reduce the size of this database.

Unfortunately for me, I don't know how to delete those tables. Could you please recommend me some steps? I would save them to our wiki and perform them at a later time if necessary.

How come the purge settings don't reduce those tables?

So I checked the following tables from the SQL console:

Audit: 1026 entires
Events: 6 entries
UserEvents: About 20ish entries

This is unless I don't know how to read the output. I tool each row as an entry.

edit:

Figured it out:

SELECT COUNT() FROM EVENTS = 6
SELECT COUNT() FROM USEREVENTS = 24
SELECT COUNT() FROM AUDIT = 913
SELECT COUNT() FROM DATAPOINTHIERARCHY = 13
SELECT COUNT() FROM REPORTINSTANCEEVENTS = 0
SELECT COUNT() FROM TANK_DATA = 0
SELECT COUNT() FROM BATCH_DATA = 0
SELECT COUNT() FROM GLOBALSCRIPTS = 0
SELECT COUNT() FROM WATCHLISTS = 14
SELECT COUNT() FROM GRAPHICALVIEWS = 0
SELECT COUNT() FROM REPORTS = 0
SELECT COUNT() FROM POINTVALUES = 0
SELECT COUNT() FROM RECIPE = 0
SELECT COUNT() FROM EVENTHANDLERS = 3
SELECT COUNT() FROM DATAPOINTTAGS = 384
SELECT COUNT() FROM DATASOURCES = 6
SELECT COUNT() FROM MAILINGLISTMEMBERS = 2
SELECT COUNT() FROM SELECTEDWATCHLIST = 4
SELECT COUNT() FROM REPORTINSTANCEDATAANNOTATIONS = 0
SELECT COUNT() FROM EXCELREPORTS = 0
SELECT COUNT() FROM JSONDATA = 3
SELECT COUNT() FROM RECIPE_STAGE = 0
SELECT COUNT() FROM MAINTENANCEEVENTS = 3
SELECT COUNT() FROM EVENTDETECTORS = 4
SELECT COUNT() FROM EVENTHANDLERSMAPPING = 3
SELECT COUNT() FROM USERCOMMENTS = 0
SELECT COUNT() FROM REPORTINSTANCEPOINTS = 0
SELECT COUNT() FROM REPORTINSTANCEUSERCOMMENTS = 0
SELECT COUNT() FROM REPORTINSTANCES = 0
SELECT COUNT() FROM MAILINGLISTINACTIVE = 0
SELECT COUNT() FROM BATCH_STAGE = 0
SELECT COUNT() FROM TEMPLATES = 25
SELECT COUNT() FROM PUBLISHERS = 2
SELECT COUNT() FROM SYSTEMSETTINGS = 130
SELECT COUNT() FROM DATAPOINTS = 192
SELECT COUNT() FROM POINTVALUEANNOTATIONS = 0
SELECT COUNT() FROM REPORTINSTANCEDATA = 0
SELECT COUNT() FROM EXCELREPORTTEMPLATES = 0
SELECT COUNT() FROM SCHEDULEDEVENTS = 0
SELECT COUNT() FROM BATCH_TRACKING = 0
SELECT COUNT() FROM USEREVENTS = 24
SELECT COUNT() FROM USERS = 4
SELECT COUNT() FROM POINTLINKS = 0
SELECT COUNT() FROM MAILINGLISTS = 2
SELECT COUNT(*) FROM WATCHLISTPOINTS = 194

What else could be using all this space?

@mihairosu said in Low Disk Space, Purge settings adjusted with no effect on H2 database size:

nd me some steps? I would save them to o

The purge does clear the events table, but it doesn't shrink the H2 database file. The Audit table does have purge settings, but there the 'purge all events' button in the purge settings section of the system settings doesn't clear the audit table, so use the 'purge all events' button along with running in the SQL console, delete from audit; to purge that table.

If something else were taking up the space, I wouldn't wonder if you maybe booted with a NoSQL module at some point? Perhaps if you select count(*) from pointValues; you get a nonzero result? What's the output of du -sh Mango/databases/* ?

select count(*) from pointValues; = 0

mrosu@22historian:~$ du -sh /opt/mango/databases/*
3.2G /opt/mango/databases/mah2.h2.db
4.0K /opt/mango/databases/mah2.lock.db
12K /opt/mango/databases/mah2.trace.db
47M /opt/mango/databases/mangoTSDB
1.2M /opt/mango/databases/mangoTSDBAux
12K /opt/mango/databases/reports

Hmm. Nothing there looks like it should amount to a 3.2GB database. Can you try the

1. SQL Database backup now
2. Stop Mango
3. Move Mango/databases/mah2.h2.db as a backup
4. Start Mango, login is back to default admin/admin
5. Restore the backup from step 1

Process again? Is it possible you restored into your existing database?

Thanks Phil, we're back to a very reasonable database size.