Difference between revisions of "History System"

From MidasWiki
Jump to: navigation, search
Line 25: Line 25:
 
The MIDAS distribution contains these drivers:
 
The MIDAS distribution contains these drivers:
  
; MIDAS history
+
; MIDAS-History
: the traditional MIDAS history, data is stored in the .hst binary files. (format is documented here [[mhformat]], see also [[mhist]] and [[mhdump]])
+
: this is the traditional history driver, with data stored in the .hst binary files. (format is documented here [[mhformat]], see also [[mhist]] and [[mhdump]])
: By default ODB subtree '''[[Keys in the ODB /Logger/History subtree#/Logger/History/0/ subtree |history channel 0]]''' is set up to write '''MIDAS''' history.  
+
: By default ODB subtree '''[[Keys in the ODB /Logger/History subtree#/Logger/History/0/ subtree |history channel 0]]''' is set up to write MIDAS-history.  
  
; ODBC history  
+
; ODBC-History  
 
: stores history data in SQL database using the ODBC abstraction layer. See [[#MIDAS ODBC API SQL History system|ODBC SQL History system]].
 
: stores history data in SQL database using the ODBC abstraction layer. See [[#MIDAS ODBC API SQL History system|ODBC SQL History system]].
  
: By default ODB subtree '''[[Keys in the ODB /Logger/History subtree#/Logger/History/1/ subtree |history channel 1]]'''  is set up to write '''ODBC''' history.
+
: By default ODB subtree '''[[Keys in the ODB /Logger/History subtree#/Logger/History/1/ subtree |history channel 1]]'''  is set up to write ODBC-history.
  
; SQLITE history
+
; SQLITE-History
: stores data in SQL database as SQLITE3 .sqlite3 files. The SQL table format is similar to ODBC history, performance is similar, no database server required.
+
: stores data in SQL database as SQLITE3 .sqlite3 files. The SQL table format is similar to ODBC-history, performance is similar, no database server required.
: By default '''[[Keys in the ODB /Logger/History subtree#/Logger/History/0/ subtree |history channel 2]]'''  is set up to write '''SQLITE''' history.
+
: By default '''[[Keys in the ODB /Logger/History subtree#/Logger/History/0/ subtree |history channel 2]]'''  is set up to write SQLITE-history.
  
  
Line 43: Line 43:
 
= Location of History Files =
 
= Location of History Files =
  
By default, the traditional MIDAS history is enabled, writing history files into the MIDAS data directory path given by the ODB key [[Keys in the ODB /Logger tree#Data dir|Data dir]].
+
By default, the traditional MIDAS-history is enabled, writing history files into the MIDAS data directory path given by the ODB key [[Keys in the ODB /Logger tree#Data dir|Data dir]].
 
If history data is important, it is recommended to write it to a directory that is backed up or archived. (The MIDAS data directory is usually located on a scratch data disk without backups).
 
If history data is important, it is recommended to write it to a directory that is backed up or archived. (The MIDAS data directory is usually located on a scratch data disk without backups).
 
   
 
   
The location of the MIDAS history data files can be changed by use of the ODB Key
+
The location of the MIDAS-history data files can be changed by use of the ODB Key
 
[[Keys in the ODB /Logger tree#History dir|History dir]].  
 
[[Keys in the ODB /Logger tree#History dir|History dir]].  
  
Line 71: Line 71:
 
* Set the [[/Equipment ODB tree#Log history|Log history]] key of each equipment to be logged non-zero (see [[Equipment List Parameters#Log History|Log History parameter]]. You may need to restart the frontend. This causes the event contents to be copied to the  ODB [[/Equipment ODB tree#Variables subtree|/Equipment/''<equipment-name>''/Variables subtree]] for the use of the History system.
 
* Set the [[/Equipment ODB tree#Log history|Log history]] key of each equipment to be logged non-zero (see [[Equipment List Parameters#Log History|Log History parameter]]. You may need to restart the frontend. This causes the event contents to be copied to the  ODB [[/Equipment ODB tree#Variables subtree|/Equipment/''<equipment-name>''/Variables subtree]] for the use of the History system.
  
* Select the  '''[[#History drivers|history driver]]''' and therefore '''history channel'''  to use to write the MIDAS history files.  
+
* Select the  '''[[#History drivers|history driver]]''' and therefore '''history channel'''  to use to write the MIDAS-history files.  
 
* set [[Keys in the ODB /Logger/History subtree|Active]] ODB key in the selected [[#History drivers|History channel]] to "y"
 
* set [[Keys in the ODB /Logger/History subtree|Active]] ODB key in the selected [[#History drivers|History channel]] to "y"
 
* set [[/History ODB tree#LoggerHistoryChannel|LoggerHistoryChannel]] to the selected [[#History drivers|History channel]]
 
* set [[/History ODB tree#LoggerHistoryChannel|LoggerHistoryChannel]] to the selected [[#History drivers|History channel]]
  
=== To write MIDAS Frontend History   ===
+
=== To write MIDAS-History Frontend events   ===
  
 
* Perform steps in [[#To write a Frontend History Event]] above
 
* Perform steps in [[#To write a Frontend History Event]] above
* The  '''[[#History drivers|history driver]]''' is MIDAS and therefore '''history channel''' (default is "0") to use to write the MIDAS history files.  
+
* The  '''[[#History drivers|history driver]]''' is MIDAS and therefore '''history channel''' (default is "0") to use to write the MIDAS-history files.  
 
* set [[Keys in the ODB /Logger/History subtree#/Logger/History/0/Active|Active]] ODB key in the selected to "y"
 
* set [[Keys in the ODB /Logger/History subtree#/Logger/History/0/Active|Active]] ODB key in the selected to "y"
 
* set [[/History ODB tree#LoggerHistoryChannel|LoggerHistoryChannel]] to the selected [[#History drivers|History channel]] (default 0)
 
* set [[/History ODB tree#LoggerHistoryChannel|LoggerHistoryChannel]] to the selected [[#History drivers|History channel]] (default 0)
 
* restart [[mhttpd]] and [[mlogger]], start the run.
 
* restart [[mhttpd]] and [[mlogger]], start the run.
  
MIDAS History files will be saved into the  [[#Location of History Files|history data directory]].  
+
MIDAS-history files will be saved into the  [[#Location of History Files|history data directory]].  
  
=== To write ODBC Frontend History ===
+
=== To write ODBC-History Frontend events ===
  
 
See [[#MIDAS ODBC SQL History system]]
 
See [[#MIDAS ODBC SQL History system]]
 
* If necessary, [[Set up a MySQL database#Set up a MySQL database]].
 
* If necessary, [[Set up a MySQL database#Set up a MySQL database]].
 
* Perform steps in [[#To write a Frontend History Event]] above
 
* Perform steps in [[#To write a Frontend History Event]] above
* The  '''[[#History drivers|history driver]]''' is MIDAS and therefore '''history channel''' (default is "1") to use to write the ODBC history files.  
+
* The  '''[[#History drivers|history driver]]''' is ODBC and therefore '''history channel''' (default is "1") to use to write the ODBC-history files.  
 
* set [[Keys in the ODB /Logger/History subtree#/Logger/History/1/Active|Active]] ODB key to "y"
 
* set [[Keys in the ODB /Logger/History subtree#/Logger/History/1/Active|Active]] ODB key to "y"
 
* set ODB key [[/History ODB tree#LoggerHistoryChannel|LoggerHistoryChannel]] to the selected [[#History drivers|History channel]] (default 1)
 
* set ODB key [[/History ODB tree#LoggerHistoryChannel|LoggerHistoryChannel]] to the selected [[#History drivers|History channel]] (default 1)
Line 97: Line 97:
 
  is set to "history reader" (the default values)
 
  is set to "history reader" (the default values)
 
* set [[Keys in the ODB /Logger/History subtree#/Logger/History/1/Debug|Debug]] to 1 if desired
 
* set [[Keys in the ODB /Logger/History subtree#/Logger/History/1/Debug|Debug]] to 1 if desired
* restart [[mlogger]] in verbose mode (i.e. mlogger -v), and observe how it issues SQL commands to create the tables and columns corresponding to MIDAS history events and tags.
+
* restart [[mlogger]] in verbose mode (i.e. mlogger -v), and observe how it issues SQL commands to create the tables and columns corresponding to MIDAS-history events and tags.
** [[mlogger]] is programmed to raise alarms if connection to SQL database is interrupted or if some events cannot be written into the database (i.e. data type mismatch, SQL syntax errors, etc). The traditional MIDAS history never raised alarms because it "never failed" - other than from "disk full" errors, which are immediately obvious.
+
** [[mlogger]] is programmed to raise alarms if connection to SQL database is interrupted or if some events cannot be written into the database (i.e. data type mismatch, SQL syntax errors, etc). The traditional MIDAS-history never raised alarms because it "never failed" - other than from "disk full" errors, which are immediately obvious.
  
 
* restart [[mhttpd]]
 
* restart [[mhttpd]]
Line 108: Line 108:
  
  
=== To write  SQLITE Frontend history ===
+
=== To write  SQLITE-history Frontend events ===
 
   
 
   
 
No special configuration required.  
 
No special configuration required.  
  
 
* Perform steps in [[#To write a Frontend History Event]] above
 
* Perform steps in [[#To write a Frontend History Event]] above
* The  '''[[#History drivers|history driver]]''' is SQLITE and therefore '''history channel''' (default is "2") to use to write the SQLITE history files.  
+
* The  '''[[#History drivers|history driver]]''' is SQLITE and therefore '''history channel''' (default is "2") to use to write the SQLITE-history files.  
 
* set [[Keys in the ODB /Logger/History subtree#/Logger/History/1/Active|Active]] ODB key to "y"
 
* set [[Keys in the ODB /Logger/History subtree#/Logger/History/1/Active|Active]] ODB key to "y"
 
* set ODB key [[/History ODB tree#LoggerHistoryChannel|LoggerHistoryChannel]] to the selected [[#History drivers|History channel]] (default 2)
 
* set ODB key [[/History ODB tree#LoggerHistoryChannel|LoggerHistoryChannel]] to the selected [[#History drivers|History channel]] (default 2)
Line 135: Line 135:
  
 
== History Tag names ==
 
== History Tag names ==
Tags are only used for MIDAS history events.  
+
Tags are only used for MIDAS-history events.  
  
 
The mlogger/mhttpd implementation of <span style="color:purple; font-style:italic">/History/Tags</span> has proved troublesome and we are moving away from it.
 
The mlogger/mhttpd implementation of <span style="color:purple; font-style:italic">/History/Tags</span> has proved troublesome and we are moving away from it.
Line 179: Line 179:
 
The SQL history system implements the MIDAS history using a SQL database for data storage instead of flat binary files.
 
The SQL history system implements the MIDAS history using a SQL database for data storage instead of flat binary files.
  
The SQL history code lives in a separate files history.h and history_sql.cxx. The present implentation uses the ODBC API for accessing SQL databases.  
+
The SQL history code lives in a separate files history.h and history_sql.cxx. The present implementation uses the ODBC API for accessing SQL databases.  
 
MySQL is fully supported, Pgsql support is partially implemented, support for arbitrary ODBC drivers should be easy to add in history_sql and most SQL operations are done using an interface class.
 
MySQL is fully supported, Pgsql support is partially implemented, support for arbitrary ODBC drivers should be easy to add in history_sql and most SQL operations are done using an interface class.
  
 
Preliminary testing with MySQL and PgSQL indicates that disk space requirements are the same for both storage mechanisms using flat binary files or either of the 2 SQL databases. All 3 tested storage mechanisms store the data uncompressed. (Observed gzip-1 compression ratios from CERN-ALPHA history files are 50% or better).
 
Preliminary testing with MySQL and PgSQL indicates that disk space requirements are the same for both storage mechanisms using flat binary files or either of the 2 SQL databases. All 3 tested storage mechanisms store the data uncompressed. (Observed gzip-1 compression ratios from CERN-ALPHA history files are 50% or better).
  
Preliminary testing also indicates that MySQL database is "not too slow" for use by T2K/ND280 slow control system. Using MySQL, performance seems to improve somewhat compared to traditional history because each SQL table is stored into a separate file, compared to everything-in-one-file in traditional MIDAS history.
+
Preliminary testing also indicates that MySQL database is "not too slow" for use by T2K/ND280 slow control system. Using MySQL, performance seems to improve somewhat compared to traditional MIDAS-history because each SQL table is stored into a separate file, compared to everything-in-one-file in traditional MIDAS-history.
  
 
== Set up a MySQL database ==
 
== Set up a MySQL database ==

Revision as of 22:58, 18 June 2014

Links

Introduction

The history system is an add-on capability built into the MIDAS data logger mlogger to record useful information in parallel to the data logging. This information is recorded with a time stamp and saved into a history file (see History drivers) for later retrieval. One set of files is created per day containing all the requested history events. The history data may be displayed in graphical form using the mhttpd MIDAS webserver utility, giving the user an easy way of seeing how experimental variables have changed with time. The history logging will be in action only if the MIDAS data logger is running, but it is not necessary to have any data logging channel enabled.

MidasHistoryInterface

New history configuration defines history channels similar to logger channels. Each history channel creates a history interface class (class MidasHistoryInterface). The mlogger can use this interface to write data into the history, mhttpd, mhist & co can use this interface to read data from the history. The interface is defined in history.h

History drivers

The MIDAS distribution contains these drivers:

MIDAS-History
this is the traditional history driver, with data stored in the .hst binary files. (format is documented here mhformat, see also mhist and mhdump)
By default ODB subtree history channel 0 is set up to write MIDAS-history.
ODBC-History
stores history data in SQL database using the ODBC abstraction layer. See ODBC SQL History system.
By default ODB subtree history channel 1 is set up to write ODBC-history.
SQLITE-History
stores data in SQL database as SQLITE3 .sqlite3 files. The SQL table format is similar to ODBC-history, performance is similar, no database server required.
By default history channel 2 is set up to write SQLITE-history.


More drivers can be easily added by the user (create history_xxx.cxx, add constructor to hs_get_history(), create the ODB subtree /Logger/History/x/channel).

Location of History Files

By default, the traditional MIDAS-history is enabled, writing history files into the MIDAS data directory path given by the ODB key Data dir. If history data is important, it is recommended to write it to a directory that is backed up or archived. (The MIDAS data directory is usually located on a scratch data disk without backups).

The location of the MIDAS-history data files can be changed by use of the ODB Key History dir.

Types of History Events

There are two basic types of history events, which are defined in different ways:


Both these history event definitions take effect when the data logger gets a start run transition. Any modifications made during the run are not applied until the start of the next run.


Frontend History Event

Each Equipment has the capability to generate "history data".

To enable Frontend History Events

To write MIDAS-History Frontend events

MIDAS-history files will be saved into the history data directory.

To write ODBC-History Frontend events

See #MIDAS ODBC SQL History system

Reader_ODBC_DSN

is set to "history reader" (the default values)
  • set Debug to 1 if desired
  • restart mlogger in verbose mode (i.e. mlogger -v), and observe how it issues SQL commands to create the tables and columns corresponding to MIDAS-history events and tags.
    • mlogger is programmed to raise alarms if connection to SQL database is interrupted or if some events cannot be written into the database (i.e. data type mismatch, SQL syntax errors, etc). The traditional MIDAS-history never raised alarms because it "never failed" - other than from "disk full" errors, which are immediately obvious.
  • restart mhttpd
    • With ODBC History enabled, mhttpd will only use history information from the SQL database to make history plots and to extract history variable names for the history plot editor. The ODB /History Tags and

Events subtrees are not used.

** variable names presented to the user may change from MIDAS names to SQL names (use the history panel "Label" text fields to create permanent plot labels).


To write SQLITE-history Frontend events

No special configuration required.

In the history data directory (in Sqlite dir) you should see files with name "mh_xxx.sqlite3" corresponding to your equipment and variable names.


Data path for frontend History Event

The main data path for the frontend History Event is as follows:

  • The user code in the frontend equipment Event Readout routines reads the data, placing it into a MIDAS data bank
  • In mfe.c, if the key Log history > 0 (i.e. the history system is enabled for this equipment), this data bank is written into ODB (by update_odb()).
  • This ODB write triggers an ODB hot-link into mlogger
  • The hot-link calls mlogger.c::log_history(), which calls hs_write() to write the data into the history file. The frequency of the history writes is specified by the number of seconds between writes stored in the ODB key Log history.
  • history.c::hs_write() or history_odbc::hs_write_odbc() writes the data into a history file or into an SQL database.


History Tag names

Tags are only used for MIDAS-history events.

The mlogger/mhttpd implementation of /History/Tags has proved troublesome and we are moving away from it.


During the transition period
Implementations starting with Version 4435 of mlogger and mhttpd (i.e. "new" versions) will now work without /History/Tags. The history tags are read directly from the history files themselves. To run in this mode, set ODB key /History/DisableTags to "y". Existing /History/Tags subtree will be automatically deleted.

Two downsides to running like this are

  • it is slower and tags become non-persistent
  • if some frontends have not been running for a while, their variables may vanish from the history panel editor.


New-style History Tags

For the above two reasons, using /History/Tags is still recommended. However, the format of the tags is now changed to simplify management and reduce ODB size.

For NEW implementations
the key CreateOldTags will not be present. "New-style" history tags will be created automatically in the Tags subtree.


If you have old-style tags
mlogger will automatically convert the old tags to this new format (this is why you should make a backup of your ODB).
    • using "old" (i.e. pre Version 4435) mlogger with "new" mhttpd is OK: new mhttpd understands both formats of /History/Tags.
    • using "old" mhttpd with "new" mlogger is OK provided the ODB key /History/CreateOldTags is set to "y" before starting mlogger.


Old-style History Tags

Prior to versions 4435 of mlogger and mhttpd, old-style history tags were generated. See

for details.



MIDAS ODBC API access SQL History system

This section describes the internal workings of the MIDAS SQL history system.

The SQL history system implements the MIDAS history using a SQL database for data storage instead of flat binary files.

The SQL history code lives in a separate files history.h and history_sql.cxx. The present implementation uses the ODBC API for accessing SQL databases. MySQL is fully supported, Pgsql support is partially implemented, support for arbitrary ODBC drivers should be easy to add in history_sql and most SQL operations are done using an interface class.

Preliminary testing with MySQL and PgSQL indicates that disk space requirements are the same for both storage mechanisms using flat binary files or either of the 2 SQL databases. All 3 tested storage mechanisms store the data uncompressed. (Observed gzip-1 compression ratios from CERN-ALPHA history files are 50% or better).

Preliminary testing also indicates that MySQL database is "not too slow" for use by T2K/ND280 slow control system. Using MySQL, performance seems to improve somewhat compared to traditional MIDAS-history because each SQL table is stored into a separate file, compared to everything-in-one-file in traditional MIDAS-history.

Set up a MySQL database

In order to setup a MySQL database, the following is needed:

  • the server hostname and port number
  • database name
  • user name and password.

It is recommended to create 3 different users:

  • a "root" user with full priveleges,
  • a "reader" user with read-only priveleges
  • and a "writer" user with "insert, create table, and add column" priveleges (no permission to drop table, remove columns, etc)

Example

  1. Setup a MySQL database (RHEL/SL 5)
ssh root@localhost
service mysqld start
/usr/bin/mysql_secure_installation (enter current password for root: press <enter>, then accept  default answer "Y" to all questions)
mysqladmin -p create history
using mysql-administrator, create 2 users:
history_writer, with database "history" schema privileges: select, insert, create, alter
history_reader, with database "history" schema privileges: select
  1. Setup $HOME/.odbc.ini file:
cat $HOME/.odbc.ini
[history_writer]
Description  = history_writer
Driver       = /usr/lib64/libmyodbc3.so
SERVER       = ladd05.triumf.ca
PORT         =
Database     = history
OPTION       = 3
SOCKET       =
User         = history_writer
Password     = ******
[history_reader]
Description  = history_reader
Driver       = /usr/lib64/libmyodbc3.so
SERVER       = ladd05.triumf.ca
PORT         =
Database     = history
OPTION       = 3
SOCKET       =
User         = history_reader
Password     = 

Layout of SQL tables

  • One table is created for each history event. Table name is the same as the history event name (as reported by mhdump).
  • one column is created for each history tag (history variable). Column name is the same as the tag name (as reported by mhdump). For array tags, one column is created for each array element: array a[3] will produce columns a_0, a_1 and a_2.
  • two special columns are always created: _i_time and _t_time are the timestamps in the UNIX-time integer format and in the SQL "time" format. (A priori, it is not clear which timestamp format is more useful for end users. mhttpd uses the _i_time integer time format). Note that this timestamp is the time when mlogger receives the hot-link signal (see above) and it can be a few seconds behind the time when the user placed the data into the MIDAS bank. For high precision (and sub-second time resolution) time stamps, users should generate their own timestamps and include them as part of the data itself.
  • a special table called _history_index is created to remember the mapping between SQL names and MIDAS names for event names, variable name and tag names.

Schema Evolution

  • when new data fields are added, new SQL columns are created automatically
  • existing SQL columns are never deleted
  • arrays are expanded automatically, and arrays never shrink.
  • if the type of a data field changes, a conflict may occur with existing SQL columns, for example if previous type was "FLOAT" and new type is "DOUBLE", the existing SQL columns would have the SQL data type "float", too narrow to store new values. mlogger will complain about this. Such conflicts may be resolved by changing the type of the SQL column using SQL tools. In general, SQL names are created from MIDAS names by replacing all non-alphanumerical characters to an underscore "_". If this procedure creates duplicate column names, the column names and made unique by appending them with a random number, i.e. MIDAS names "A+B" and "A-B" will be translated to "A_B" and "A_B_12345" (random integer). True MIDAS names are saved into the _history_index.