actual one hereModule of subsystem "Archives" <DBArch>
| Module: | DBArch |
| Name: | Arhivator on the DB |
| Type: | Archive |
| Source: | arh_DBArch.so |
| Version: | 2.4 |
| Author: | Roman Savochenko, Maxim Lysenko (2009) |
| Description: | The archiver module. Provides functions for messages and values archiving to DB. |
| License: | GPL |
Introduction
The module designed for archiving messages and values of OpenSCADA to a database maintained by OpenSCADA.Any SCADA system provides the ability to archive the collected data, i.e. formation of history of the changes (dynamics) of processes. Archives conditionally can be divided into two types: archives of messages and archives of values.
A feature of the archives of messages is that so-called events are archived. The characteristic feature of the events is its time of occurrence. The archives of messages are usually used for archiving messages in the system, that is logging and reports. Depending on the source the messages can be classified according to different criteria. For example, this may be the reports of emergency situations, the reports of actions of the operators, reports of the connection lost and others.
A feature of the archives of values is their frequency, measured in the time lag between two adjacent values. Archives of values are used for archiving the history of continuous processes. As the process is continuous, it can only be archived by introducing the notion of quantization of time gathering, because otherwise we get the archives of infinite dimensions in view of continuity of the nature of the process. In addition, practically, we can get value from the time limited by the data sources. For example, a fairly high-quality data sources in the industry, are rarely allowed to receive data at a frequency of more than 1kHz. And this is without taking into account of the sensors themselves, which have even less qualitative characteristics.
For archiving in the system OpenSCADA the subsystem "Archives" is provided. This subsystem, according to the types of archives, consists two parts: an archives of messages and archives of values. The subsystem, in general, is a module that allows you to create archives based on the different nature and methods of storing of data. This module provides a mechanism for the archiving on the data base for both: for the flow of messages, and for the flow of values.
1. Message Archiver
Archives of messages are formed by an archiver. There can be the set of archivers, with individual settings, allowing to share archiving of different classes of messages.The archiver of messages of this module stores data in a database table, which is named "DBAMsg_{ArchID}", where:
- ArchID archiver identifier.
Module provides additional settings for the archiving process (Fig. 1).
Fig.1. Additional settings of the archiving process of messages.
Those additional parameters represents by:
- Archive size (days) determines the size of the archive over time. After exceeding the limit the old records will be deleted! Set to 0 for the limit disable and some performance rise.
- Force time as string store messages time in the readable form. Only for DBs it supports by a specific data type like to "datetime" into MySQL.
The option isn't compatible then you will lost present archives after its change.
The table of the database archiver has structure {MIN, TM, TMU, CATEG, MESS, LEV}, where:
- MIN UTC time, in minutes, uses on reading by minutes, to rapid.
- TM UTC time of the message, seconds from (01.01.1970). In the DB, containing a specialized type of storage date and time, can be used this specialized type, for the option "Force time as string".
- TMU microseconds of time.
- CATEG message category.
- MESS text of the message.
- LEV level of the message.
2. Values Archiver
Archives of values, by default, are formed particularly by archivers of the values for each registered archive. There can be a lot of archivers with individual settings that allow to divide the archives by various parameters, such as the accuracy and depth. The parameter's archives of one archivator can be grouped, a single table, for pointed limit of the parameters quantity. The grouping allows significant increase the archiving productivity by sending to DB single request which values of parameters into the group.Archive of values is an independent component, which includes buffer processed by archivers. The main parameter of archive of value is a source of data. As a source of data may make the attributes of the parameters of subsystem "Data acquisition", as well as other external data sources (passive mode). Other sources of data could be: network archivers of remote OpenSCADA systems, the environment of programming of systems OpenSCADA etc. No less important parameters are the parameters of the archive buffer. From the parameters of the buffer the opportunity of working of archivers depends on.
Thus, the frequency of values in the buffer should be no more than the frequency of the fastest archiver, a buffer size not less than double the amount for the slowest archiver. Otherwise, the possible loss of data.The overall scheme of archival of values clearly shown in Fig. 2.
Fig.2. The overall scheme of the process of archiving.
Archive of this module stores data in a database table, which is called in way "DBAVl_{ArchivatorID}_{ArchiveID}", for the single mode, and "DBAVl_{ArchivatorID}_<GRP>{N}", for the group mode, where:
- ArchivatorID identifier of the archiver of values.
- ArchiveID identifier of the archive.
- N group number, passed for first one.
The module provides additional settings for the archiving process (Fig. 3).
Fig.3. Additional settings of the archiving process of values.
Those additional parameters represents by:
- Archive size (days) determines the size of the archive over time. After exceeding the limit the old records will be deleted! Set to 0 for the limit disable and some performance rise.
- Force time as string store values time in the readable form. Only for DBs it supports by a specific data type like to "datetime" into MySQL. The option isn't compatible then you will lost present archives after its change.
- Grouping parameters limit nonzero value here enables the grouped archivation and determines of the limit to parameters number per group/table.
The table of database archiver of values has structure: {MARK, TM, VAL), for the single mode, and {MARK, TM, {PRM1}, {PRM2}, {PRMN}}, for the grouping mode, where:
- MARK fast accessing/reading mark of the archive, {TM}/(10*{period}).
- TM UTC time of the value, the second from (01.01.1970). In the DB, containing a specialized type of storage date and time, can be used this specialized type, for the option "Force time as string".
- VAL The parameter value into the single mode, type of the value determines to type of the column (Integer, Real, String).
- PRM1...PRMN Value of parameter with the identifier into the column name into the grouping mode, type of the value determines to type of the column (Integer, Real, String).
3. Informational table of the archival tables
To store the beginning, end and other service information of archives in archival tables the informational table with the name of the module is created: "DBArch". This table has the structure {TBL, BEGIN, END, PRM1, PRM2, PRM3), where:- TBL Name of the table of the archive.
- BEGIN Beginning of data in the archive. Seconds for messages and microseconds for values UNIX epoch (01.01.1970).
- END End of data in the archive. Seconds for messages and microseconds for values UNIX epoch (01.01.1970).
- PRM1 Optional parameter 1: values periodicity, in microseconds.
- PRM2 Optional parameter 2: type of values of the parameter into the single mode or list of parameters into the group {Type}:{ArchiveId} for the group mode.
- PRM3 Optional parameter 3.
According to information into the table for the value archivators the archive objects restoring and creation supported.
4. Efficiency
In the design and implementation of the module it wasn't included any mechanisms of improving the process of archiving but presenting of objective limits into the same data bases and its accessing interfaces. Then the efficiency of archiving to DB mostly related to the DB and its accessing interface. From the most effective interfaces and approaches of the performance increasing there need to mark the following ones:- Reading from DB of several records not by a single/concrete command SELECT, but the grouping SELECT requests, that is for DB minimum in ten times faster. For that behavior using the layer of accessing to DB into OpenSCADA, in the request "dataSeek()", is expanded to support preloading to full of all records of respond to the request. The behavior also now is used by the module and allowed to get data often faster then it is processed further, although giving way to the archiving to file system.
- Writing to DB of a different column also significant faster then a different record. The behavior is used by the module in a part of values archiving and the grouping mode, that is values of each signal write to a different column.
Results of measurement of the performance of archiving placed to the following table:
| Test / Environment and DB | Intel Core3 1.3GHz, Local PostgreSQL 9.3, SSD | AMD A8 3.5GHz, Local PostgreSQL 9.3, HDD |
| Values archiving, 60 records, 1 signal (seconds) | 53...63 | 13...14 |
| Values archiving, 60 records, 10 signal (seconds) | 65...67 | 16...19 |
| Values archiving, 60 records, 100 signal (seconds) | 154...163 | 52...60 |
| Result: average time of writing 60 values of signal (millisecond), maximum number of the archiving signals in periodicity 1 second | 1, 60000 | 0.4, 150000 |