Is it a problem having a big H2 database?

Ava2018

Hi, I am doing some evaluation with mango to see the system behavior under a medium/big amount of pointvalues/events.

I notice that until the SQL database is less than 1.5GiB, my CPU consuming goes around 20%. However after 2 or 3 hours running with the configuration above, the SQL DB pass the 2GiB(not sure if this is the source of the problem) and the CPU consuming start growing until keeps in the 90/100% CPU, making mango unresponsive.

Configuration:
CPU: QuadCore Xeon 2.4GHz
RAM: 8GB

Mango 3.5.6 (set to ext-enabled with 6GB)

Created Datasources: 430
Enabled dataSources: 8 virtual datapoints, 100ms update, with 1 random numeric DP each, 1 change event record by update each.
Created Datapoints: 6500
Enabled Datapoints: 10

Writes per second during database batches: 79
Point values to be written: 32 (Average)

Database (reset) size after proceding a backup, deleting the mah2.hs.db and restore it:
SQL Database size 1.8 GiB
NoSQL point value database size: 100.5MiB

Point values Count: 4918301
Events count: 1652711

JVM free memory (MB) 1113 (After a fresh restart)
JVM free memory (MB) 759 (At the lock time)
JVM maximum available memory (MB) 4960

phildunlap

Hi Ava2018,

Yes a large H2 database can cause degradation in performance. I'm not sure that's what I'd expect in your case, but you can download thread dumps from the /rest/v1/threads?asFile=true&stackDepth=40 endpoint and send them into support@infiniteautomation.com . I'll run a python script that I have shared in our github to digest that thread JSON and see what long running or blocked threads would account for the high cpu usage.

If you would like, you can also send in your database backup if your database is growing that fast. We may be able to mitigate the issue by finding what specifically is making it expand, as we did with the persistent publisher's former database expansion.

The good news on the whole topic is that the underlying cause of the H2 database growth was solved in the 197 revision of H2. Unfortunately they made the upgrade path somewhat needy (you must do a backup with 196, restore with 197 or you will only be able to open the database once), so we haven't migrated everyone yet.

Ava2018

Just adding some information. Maybe the problem is related with the events amount and not directly with the H2 size.
I got the system a little before locks on 100% CPU. The USEREVENTS table count was around 13millions, then I purged all the events and from that moment on Mango returned to run very quickly using around 10%CPU even with the fast datasources enabled and showing SQL Database size in 2.9GiB.

Just to have an idea of my concern about that, on my current SCADA system (SCADABR), it works with around 20000 events and 1500000 userevents a day. In the current purge setting(2 months), it results a 2 million events and 122million userevents total. The db system is Oracle, the question is if Mango (H2,MangoTSDB) can handle that...

phildunlap

Just to have an idea of my concern about that, on my current SCADA system (SCADABR), it works with around 20000 events and 1500000 userevents a day. In the current purge setting(2 months), it results a 2 million events and 122million userevents total. The db system is Oracle, the question is if Mango (H2,MangoTSDB) can handle that...

You can use MySQL on the backend instead of H2. The NoSQL store is quite capable but it not a factor in a question of event table size. Then it should have a similar performance on a similar machine, I would expect.

I am unsure of the event handling options in ScadaBR, but we have the levels of nuance between Do not log and Ignore where Do not log will invoke any event handlers but not store the event information, and Ignore will do as little work as possible.

So, if events are important enough to be kept forever, the solution to the database table issue would be to store what event information you care about into a data point via set point handler. The NoSQL database will not give near the same issues upon getting bigger.

More likely though, most of those events are not being looked at by anyone and could have been ignored altogether.

There has been discussion internally about making events have a similar timeseries aspect to data points based on the type of event, but that isn't manifest yet, but they would likely be stored in a NoSQL store once they were.

Ava2018

Understood Phil.
In your experience, would MySQL handle better a big number of DP, users and event logging with the frequency near 20000 events record/day and 1500000 userevents record/day?
Is there some known performance threshold on H2 (related to H2 size and events record frequency)?
The idea of putting that in a NoSQL db seems very welcomed.

I am testing your suggestion of decreasing the default alarm level logging to check how the system behaviors, I set all the audit and system event alarm levels(/system_settings.shtm) to both Do not Log or ignore, however, in my test, the change detector event keeps recording all the events change, for example.

Table Events
ID TYPENAME SUBTYPENAME TYPEREF1 TYPEREF2 ACTIVETS RTNAPPLICABLE RTNTS RTNCAUSE ALARMLEVEL MESSAGE ACKTS ACKUSERID ALTERNATEACKSOURCE
3028491 DATA_POINT 6595 291 1548332743821 N 0 0 0 event.detector.changeCount|!!!Andre4 - DP1|49,16 |15,45 |

Table UserEvents
EVENTID USERID SILENCED
3028491 1 N
3028491 4 N
3028491 5 N
3028491 12 N
3028491 8 N
3028491 9 N
3028491 10 N

Is there any other setting I missed?

Thanks for your prompt attention.

phildunlap

A data point event detectors alarm level is configured on the same page the event detector is configured, on the edit data point page. You could also do it through export, find/replace, import to change many at the same time.

In your experience, would MySQL handle better a big number of DP, users and event logging with the frequency near 20000 events record/day and 1500000 userevents record/day?

Yes. In my experience MySQL handles bigger databases longer better.

Is there some known performance threshold on H2 (related to H2 size and events record frequency)?

Probably something calculable from the system resources, but it wouldn't be an easy calculation. May also involve periodic shrinkage, currently, but the next version of the DB has solved the growing problem so I can't say. What I can say is that there's almost certainly a great deal of events that don't matter still to be brushing against that threshhold. For instance, if you change the logging type of a point to 'when point value changes' then every recorded value would correspond to a change event if there was also such a detector.

Ava2018

Ok.
So I understood that what triggers the recording events on the EVENTS/USEREVENTS tables is the event detector option in DP and not the logging type.
I just tested here one DP, with "Do not Log" Logging type, but with the event detector (Change type) added. And it keeps bulking the H2.
Is there a way to have the Event detector working without the record in those tables?

Another question is if there any change to use Oracle instead MySQL in case of replacing H2?

Thanks.

phildunlap

It will not Log an event if it is set to Do Not Log. I am certain. Are you sure you saved the point after you changed it?

Another question is if there any change to use Oracle instead MySQL in case of replacing H2?

There's an article about it here: https://help.infiniteautomation.com/database-conversions
This is a useful thread too, perhaps: https://forum.infiniteautomation.com/topic/3559/convert-from-h2-to-mysql-database-guidelines

After the env.properties have been changed over to use MySQL and the MySQL database has been set up, then nothing else needs to be done.

So I understood that what triggers the recording events on the EVENTS/USEREVENTS tables is the event detector option in DP and not the logging type.

There are several things that can trigger events. Event detectors are definitely one of them.

Ava2018

Phil, the links you provided only mention derby, mySQL and H2, then I will assume Mango isn't ready to use Oracle, maybe modifying the queries in the source code since some sintax and colun types are different, so it would demand development effort.

About the events question.
I don't know what I am missing so, because even with the logging at "Do not Log", see my DP code, event screen and db record :

{
   "dataPoints":[
      {
         "xid":"DP_85537b93-353d-4bbc-8e0c-82f7af745148",
         "name":"DP1",
         "enabled":true,
         **"loggingType":"NONE",**
         "intervalLoggingPeriodType":"MINUTES",
         "intervalLoggingType":"AVERAGE",
         "purgeType":"YEARS",
         "pointLocator":{
            "dataType":"NUMERIC",
            "changeType":{
               "type":"RANDOM_ANALOG",
               "max":100.0,
               "min":0.0,
               "startValue":"0"
            },
            "settable":false
         },
         "eventDetectors":[
            {
               "type":"POINT_CHANGE",
               "sourceType":"DATA_POINT",
               "xid":"ED_b18bf2f2-8611-4cf8-be87-db9f2544e117",
               "name":"",
               "alarmLevel":"NONE"
            }
         ],
         "plotType":"SPLINE",
         "rollup":"NONE",
         "unit":"",
         "simplifyType":"NONE",
         "chartColour":"",
         "chartRenderer":{
            "type":"IMAGE",
            "timePeriodType":"DAYS",
            "numberOfPeriods":1
         },
         "dataSourceXid":"DS_2f623aba-672a-4220-be21-501b29ca1eac",
         "defaultCacheSize":1,
         "deviceName":"!!!Andre4",
         "discardExtremeValues":false,
         "discardHighLimit":1.7976931348623157E308,
         "discardLowLimit":-1.7976931348623157E308,
         "intervalLoggingPeriod":1,
         "intervalLoggingSampleWindowSize":0,
         "overrideIntervalLoggingSamples":false,
         "preventSetExtremeValues":false,
         "purgeOverride":false,
         "purgePeriod":1,
         "readPermission":"",
         "setExtremeHighLimit":1.7976931348623157E308,
         "setExtremeLowLimit":-1.7976931348623157E308,
         "setPermission":"",
         "tags":{
         },
         "textRenderer":{
            "type":"ANALOG",
            "useUnitAsSuffix":true,
            "unit":"",
            "renderedUnit":"",
            "format":"0.00"
         },
         "tolerance":0.0
      }
   ]
}

Event screen: Events

DB screen: Screen

What would it be?

Another thing I notice is that each event generated is replicated in the userevents table for each "admin" user I have in Mango. Is there a setting to control/avoid that?

Thanks

phildunlap

Phil, the links you provided only mention derby, mySQL and H2, then I will assume Mango isn't ready to use Oracle, maybe modifying the queries in the source code since some sintax and colun types are different, so it would demand development effort.

Correct, the Oracle database is not supported. I was getting tripped up on that because MySQL is maintained by Oracle Corporation too. H2 and MySQL should be considered the supported types, but MSSQL and Postgres should also work.

What would it be?

That is the loggingType for the point that you had bolded / added asterisks. Notice I didn't say NONE which is itself an alarm level. Try changing the alarmLevel in the eventDetectors array from NONE to DO_NOT_LOG . I would also expect that you probably don't want a logging type of none, and would like to record values.

Jared Wiltshire

@phildunlap said in Is it a problem having a big H2 database?:

but MSSQL and Postgres should also work.

MSSQL and Postgres will not work, yes there is technically some support for them in the code but it has gaps and is entirely untested.

Ava2018

Ohh yes, this is where I got confused! I haven't seen that options on the Alarm level combo. All clarified now!
I found in the forum that I can export data from any table to another external DB, so I can keep the events shorter in H2. I will make an effort to keep the structure with H2 DB because the management through Mango seems simplier.
Thanks a lot for all the info.