Historical Query Crashes the Data Manager

[emaddocks]

We are currently having issues with large historical queries that crash the data manager when executed. It is difficult to exception handle as the number of records is less than the default config settings of 1,000,000 ([event]maxValueRequestCount) or 100,000 ([valarch]queryMaxValues).

The query we run is as follows:

Code: Select all

timeRange = "TIMERANGE(\"" + (string)tFrom + "\",\"" + (string)tTo + "\",1,0)";
DebugTN("historical count start");
dpQuery("SELECT 'COUNT(_online.._value)'" +
                 "FROM '{*}' REMOTE '"+sSys+":' " + timeRange, dda);
dyn_errClass deErr = getLastError();
DebugTN("historical count end",dda[2][2]);

if (dynlen(deErr) == 0 && dda[2][2] < 100000)
{
   // Create the historical data query
   DebugTN("historical select start");
   dpQuery("SELECT '_online.._value'," +
                  "'_online.._stime'," +
                  "'_original.._user' " +
                  "FROM '{*}' REMOTE '"+sSys+":' " + timeRange, dda);
   deErr = getLastError();
   DebugTN("historical select end", dynlen(dda) - 1);
   DebugTN("Last error", deErr);
}

This outputs the following in the local UIs log viewer and never completes or outputs an error:

Code: Select all

WCCOAui201:2022.03.17 20:32:18.283["historical count start"]
WCCOAui201:2022.03.17 20:32:48.157["historical count end"][44635]
WCCOAui201:2022.03.17 20:32:48.157["historical select start"]

However the following error occurs on in the servers log viewer and the Data Manager state in the console changes to 3 (purple):

Code: Select all

WCCILdata    (0), 2022.03.17 20:32:50.518, SYS,  SEVERE,     39, Connection lost, Send buffer limit exceeded, closing connection to (SYS: 3 Data -num 99 CONN: 1) @ localhost, [PeerItcIOHandler::sendCall]
WCCILpmon    (1), 2022.03.17 20:32:57.683, SYS,  WARNING,    36/pmon, Blocking Manager WCCILdata(0) detected. No heartbeat since 30 seconds.

Does anyone know of a way of determining when a large historical query will crash the Data Manager so you can prevent it from being run?

We are runnning 3.17 P014 on RHEL 8.2.


Cheers
Eric

[dbindernagel]

Maybe this support entry could help:
https://www.winccoa.com/knowledge-base/ ... ction.html

[emaddocks]

Thanks dbindernagel. I'm not sure I want to change these settings though (maxBcmBufferSize and bcmBufferLimitTimeout). I may be able to tweek these settings to get more historical queries to work but there'll always be those quieres that break it. I want to know before it breaks so we can avoid sending the query.

[leoknipp]

If the Data Manager is blocking and gets back to the running state it does not crash.
Which time range do you want to read?

It looks like you are using RDB archiving. Is this correct?
The config entry in the section [valarch] will not have any effect if RDB is used.

Best Regards
Leopold Knipp
Senior Support Specialist

[emaddocks]

Hi Leopold,

Thanks for your response.

Yes we are using an Oracle RDB. We have a large number of DPEs with archiving so the time range isn't that relevant. We need to rationalise our archived points as we had some modbus polled DPEs archiving every second which filled our RDB with lots of data. None the less when using a small time range and recieving a few thousand results it is fine but if we recieve many thousands of results we crash the Data Manager. It doesn't recover and we have to KILL the process which then restarts the whole server project and rebuilds the database on start up.

We currently have three Systems with three separate schemas in the RDB and the other two systems seem to run queries a lot quicker and don't crash their server. I will try rebuilding/reinitialise the problem schema to see if that helps the speed and subsequently the crashing.

Cheers
Eric

[emaddocks]

We were not able to resolve the Data Manager crashing issue however we have been able to avoid it from occuring by using the '[ui] queryRDBdirect = 1' config setting. This has significally improved the query response time. So much so now we have a different issue when populating the Table widget...however I've raised this in a separate thread (https://www.winccoa.com/forum/viewtopic ... 15&t=11646).

Cheers
Eric

[leoknipp]

When you can reproduce the problem with the blocking and/or crashing manager please get in contact with your common WinCC OA support for further analysis.
It is clear that queryRDBDirect will prevent the system from this situation as the UI manager will read directly data from the Oracle database.

Best Regards
Leopold Knipp
Senior Support Specialist