Group Design for Data Cache

Hi

I need some information about working of buffered data cache incase of database failure in FactorySQL. I have version 4.2.12.0 in a redundancy configuration. I am creating sotred procedure groups with OPC items and Action Items. The Action items are all expression based and no SQL queries in them.

Now could you kindly explain if the database goes down for whatever reason, will these groups buffer data in the cache while database is down.

I ran some tests by logging datetime every second via a stroed procedure group and there is a delay of about 30 seconds to data caching. Is there a way to ensure that stored procedure groups are executed in cache before being transfered to database and there is no data loss.

Also could you explain, under what conditions the data will not be buffered like if sql queries are used in groups etc?

I have 3 sets of FactorySQL Servers throughout the plant and replacing the RSSQL configurations with FactorySQL (for obvious reasons) and the group configurations are mostly stored procedure based. Data Buffering is critical in case of conenction loss with database. if there is another way in FactorySQL, kindly mention.

Thanks

One major feature of the Ignition platform is the “Store and Forward” system that provides a local cache prior to SQL database writes. This can lead to performance gains for high speed logging and naturally lends itself to caching in the event of loss of database connectivity. Is probably your cleanest way ahead. I would contact the sales team for a quote to see what kind of upgrade options you have.

Colby or Travis should be able to help you with possible options with the legacy FactorySQL package. I emailed them a heads up.

Hello,

As Nathan mentioned, Ignition has corrected one big flaw in how FactorySQL performed data caching, which was that it was reactionary, only storing data to the cache once the connection was determined to be down. This could lead to a delay in the data, because it can sometimes take a fair amount of time to determine this. With some group types in FactorySQL, “buffered execution” was introduced, in order to minimize this effect. Stored Procedure groups do not support this.

Furthermore, stored procedure groups in Ignition don’t currently pass through the store and forward system. So, in both products, your best bet is to adapt your project to write directly to the database with History or Standard groups.

Using this historical group, which used buffered writing, you shouldn’t see any gap in the data.

Also, the main requirement for groups to use datacaching is that they can’t require anything from the database during execution- so, they can’t have action items that run queries.

Regards,

Thanks Nathan and Coby

I have 2 ignition licenses also, but currently we dont plan to upgrade from FactorySQL to ignition. So am I to udnerstand that my stored procedure groups which do not have any SQL Action items will not be cached even after some time, if the database is down? cause when i tested with a sotred procedure, yes there was dataloss in between but stored procedure executions were cached and wirtten to database later on.

Since stored procedure groups aren’t buffered before execution in either products, I will have to work around it somehow. One method could be to write stored procedure parameter values to a table with a “unexecuted flag” and then read that table and execute procedures with the corresponding parameter values. Anyway, just kindly clarify the behaviour of the Stored Procedure Group in FactorySQL with regards to database downtime.

Thanks

If this is a possibility given your setup, I think it's a great way to go. Should work great with store-and-forward.

Regards,

Thanks Colby

well for groups which are for recording data I will have to come up with some similar scheme as i mentioned before, However, could be please answer my following question?

“am I to understand that my stored procedure groups which do not have any SQL Action items will not be cached even after some time, if the database is down?”

before I start the activity of moving the 1000s of groups from RSSQL to FactorySQL or even ignition, I would like this clarified so I can implement the best method from the beginning

Hello,

Sorry for the confusion. Even I had to double check the behavior of Stored Procedure groups in FactorySQL.

FactorySQL will cache stored procedure groups, if they do not have any SQL based action items, and no items linked to output parameters.

Currently, Ignition does not. This would be a defect in Ignition, because it should behave the same as FactorySQL, and there is no reason not to cache if there is no dependency on data from the database.

In FactorySQL, stored procedure groups do not support buffered execution. This means that the group may be blocked for some time (30 seconds usually in the worst case) before the data is diverted over to the data cache.

You can decide how to proceed based on this information. Going with historical groups in a table will let you use buffered execution and will work as-is today with Ignition. Going with Stored Procedure groups will be more direct, but will not have buffered execution, and will not act the same in Ignition as it is today. I’m sure we can get stored procedure groups to go through the store and forward system within the month or so (for 7.2), so since it seems that you will be using FactorySQL for a while longer, I wouldn’t base my decision completely on that. By the time you move to Ignition it should work correctly.

Hope this clears things up.

Regards,

Thanks Colby

Yes even in my tests FactoryQL was chaching the Stored Procedure Groups, but I wanted an official comment on that. I do hope that Ignition does it by the time, I need to move to it.

I will create scheduled SP groups along with the Trigger Based to verify that the trigger ones have been executed and werent lost for any reason including disconnection to DB.

I would hope that someone will realize the need for Store and Forward for SP groups also in ignition and implement that in a near future release.