This document contains general and reference information to help the database manager and the user understand the database used as a history storage medium by both the moodss and moomps applications.
Important: if you have created a database using a moomps daemon prior to version 2.3 or version 3.0, please follow the instructions in the upgrade section.
The moodss GUI and moomps daemon have the capability of not only monitoring in real time data cells belonging to loaded modules, but also to keep track of the history over time of any data cells, by storing their values in a database.
Any number of data cells can be monitored over time, using a SQL database as storage mean. The cells values are stored in the database, which can then be used from, for example, any spreadsheet that allows external data sources, such as OpenOffice, ... or from a Web server via PHP, Perl, Tcl, ... or any language capable of database access. Using those tools, it becomes possible to create history graphs, presentations, ... using the moodss database as data source.
It is also possible to browse the database and make graphs, piecharts, ... directly from the moodss graphical user interface.
At this time, the MySQL (native driver and ODBC), SQLite (file based SQL library with no server needed), PostgreSQL (ODBC only), Oracle (ODBC only), DB2 (ODBC only, yet untested) and SQL Server (ODBC only) databases are supported, while other databases that also support ODBC should work (also see installation).
Notes:
Starting with moodss version 18.0 and moomps version 3.0, modules with internal row numbers of 64 bits length are supported, if running under Tcl/Tk version 8.4 or above.
This implies that, depending on the database type, a couple or columns must have their type changed. In the general case:
history.row: INTEGER -> BIGINT data.row: INTEGER -> BIGINT |
Note that the above change is only needed if you archive data from 64 bit modules (see documentation for a detailed list), but is strongly advised if you are running under Tcl/Tk 8.4 or are planning to in the near future (also strongly advised as Tcl/Tk 8.3 support is gradually dropped in modules and finally the core).
Moodss or moomps automatically take care of creating the history and data tables with the right type for the row column, depending on the Tcl/Tk version being used. Also, whenever an incompatibility is encountered (for example, attempting to archive 64 bit module data in a 32 bit database), an error message is generated and the operation aborted, in order to remove any chance of corrupting an existing database.
Since row numbers in modules are unsigned, and most databases do not support unsigned integers, the core (moodss or moomps) transforms huge row numbers into negative numbers when writing data to the database and performs the reverse operation when reading data from the database:
Please refer to the history and data tables sections for specific database information on 64 bits support.
The database structure is quite simple, with all the history data (cells and their values over time) in one table. It was designed to allow the moodss GUI application to be used not only as a real-time monitoring graphical tool, but also as a data history browser. Experience has shown that the database can be very easily used by other applications, such as PHP to make graphical dashboards accessible through a web browser.
The different tables (described in more details later) are:
Note: the chosen database structure and its table definitions may appear minimalist to some experts, but it simply reflects the SQL code that works across all supported and tested databases.
The instances table holds data related to the modules loaded by the application and which contain data cells that the user chose to monitor over a long period of time. For example, when such a module is first loaded by the core, a new entry is created in the instances table.
Columns:
A module instance is uniquely defined from the module name, its major version and its options (also see options table).
Important note: module programmers must insure that the module internal data table structure and static content does not change between minor versions (i.e. between 1.2 and 1.3, but such changes between 1.3 and 2.0 are allowed, in which case a new entry in the instances table is created) (this has changed from moomps version 2.3, since it has been found that new instances, thus new data histories were created too often when modules code was being improved, which often changed the minor number).
SQL creation statement:
CREATE TABLE instances (
number INTEGER NOT NULL PRIMARY KEY,
start DATETIME NOT NULL,
module VARCHAR(255) NOT NULL,
identifier VARCHAR(255),
major INTEGER NOT NULL,
minor INTEGER NOT NULL
)
|
Sample extract:
| number | start | module | identifier | major | minor |
|---|---|---|---|---|---|
| 7 | 2002-09-14 21:52:37 | random | random | 1 | 47 |
| 13 | 2002-09-14 22:33:26 | snmp | snmp(1.2.3.4) | 2 | 12 |
Notes:
The options table holds the options of the modules instances.
Columns:
SQL creation statement:
CREATE TABLE options (
instance INTEGER NOT NULL REFERENCES instances,
name VARCHAR(255) NOT NULL,
value VARCHAR(255)
)
|
Sample extract:
| instance | name | value |
|---|---|---|
| 7 | -a | |
| 13 | -a | 1.2.3.4 |
| 13 | --trim | sys |
| 13 | -i | sysUpTime,sysDescr,sysObjectID,sysContact,sysName,sysLocation |
| 15 | --password | * |
Note: password options values (determined from their option name: see moodss module development section for what defines a password option name) are not stored as readable values, but as a * character, as seen in the table extract above.
Note: in the Oracle database case, columns are named: cinstance, cname, cvalue.
Note: starting with moodss version 20.0 and moomps version 5.0, the following index is created when the options table is created, to improve performance. If the database tables were created before the above versions were used, use the following SQL statement:
SQL creation statement:
CREATE INDEX instance ON options (instance) |
This is a copy of the internal module data table description. It is updated every time data recording is started for the module instance. The term entry is used in place of column, which is a reserved word in SQL.
Note: this table is mostly of use by the moodss database feature, which is meant to display history data in a similar fashion as the regular, real-time modules.
Columns:
SQL creation statement:
CREATE TABLE entries (
instance INTEGER NOT NULL REFERENCES instances,
number INTEGER NOT NULL,
indexed INTEGER NOT NULL,
label VARCHAR(255) NOT NULL,
type VARCHAR(16) NOT NULL,
message TEXT NOT NULL,
anchor VARCHAR(16),
UNIQUE(instance, number)
)
|
Sample extract:
| instance | number | indexed | label | type | message | anchor |
|---|---|---|---|---|---|---|
| 7 | 0 | 1 | name | ascii | user name | |
| 7 | 1 | 0 | cpu | real | cpu usage in percent | |
| 7 | 2 | 0 | disk | integer | disk usage in megabytes | |
| 7 | 3 | 0 | memory | integer | memory usage in kilobytes | |
| 7 | 4 | 1 | command | dictionary | command name | left |
Note: in the Oracle database case, columns are named: cinstance, cnumber, cindexed, clabel, ctype, cmessage, canchor.
The table with the data cells values over time, the actual history table. Can grow quite large.
Columns:
SQL creation statement:
CREATE TABLE history (
instant DATETIME NOT NULL,
instance INTEGER NOT NULL REFERENCES instances,
row BIGINT NOT NULL,
entry INTEGER NOT NULL,
value VARCHAR(255)
)
|
Sample extract:
| instant | instance | row | entry | value |
|---|---|---|---|---|
| 2002-09-14 21:52:39 | 7 | 3 | 2 | 593 |
| 2002-09-14 21:52:39 | 7 | 9 | 1 | 25.0 |
| 2002-09-14 22:33:28 | 7 | 3 | 2 | 545 |
| 2002-09-14 22:33:28 | 7 | 9 | 1 | 24.8 |
| 2002-09-14 22:33:36 | 7 | 3 | 2 | 512 |
| 2002-09-14 22:33:36 | 7 | 9 | 1 | 14.6 |
Note: starting with moomps version 2.3, the following index is created when the history table is created, to significantly improve performance. If the database tables were created by a moomps prior to version 2.3, use the following SQL statement:
SQL creation statement:
CREATE INDEX cell ON history (instance, row, entry) |
Notes:
This is where one can determine which data cells were or are monitored from the moomps daemon or the moodss GUI. Also contains some extra miscellaneous information.
Columns:
SQL creation statement:
CREATE TABLE data (
instance INTEGER NOT NULL REFERENCES instances,
row BIGINT NOT NULL,
entry INTEGER NOT NULL,
label VARCHAR(255) NOT NULL,
comment VARCHAR(255),
UNIQUE(instance, row, entry)
)
|
Sample extract:
| instance | row | entry | label | comment |
|---|---|---|---|---|
| 7 | 3 | 2 | random: Robert top disk | lab workstation |
| 7 | 9 | 1 | random: Laura cc cpu | in cluster |
| 13 | 0 | 1 | snmp: UpTime |
Note: in the Oracle database case, columns are named: cinstance, crow, centry, clabel, ccomment, and the row column type is INTEGER (as it supports 64 bits values).
If you want storage of data cell values in a history database, set the following options in the moodss preferences dialog box, database section:
The moomps daemon or moodss GUI do not permanently write to the database (but obviously it depends on the number of the modules loaded and on the poll times), so the performance on writes may not seem that important (transactions are not used since all write operations are atomic), but in some cases, it is important that the database achieves write operations in the shortest possible time.
For instance, problems can occur for example, when the moomps daemon has a lot of modules loaded, such as snmp, which may time out due to delays incurred by database insertions in the history table. That is why the INSERT DELAYED statement is internally used when MySQL is the chosen database, in order to have write operations as fast as possible.
Reading from the database happens when for example, creating graphs of data cell values over time, from a spreadsheet, or browsing history data from the moodss GUI, and since the history table can grow quite large (tens of millions of recorded samples have been observed on production systems), database performance on reads is not to be neglected. For the same reason, storage space is to be taken into consideration.
As a concrete example, a bi-processor PC with pentium IIIs at 1.25 GHz, 1 gigabyte of memory and 7200 rpm SCSI disks has been adequate for a MySQL database with more than 10 million records.
Before starting moomps or moodss, the only thing to do is to manually create a database named moodss (or possibly some other name defined either in the ODBC configuration, or in the moodss preferences for the native MySQL driver), no matter what the database type that you have chosen (except SQLite, of course, which works with a single data file). Here are a few examples of the commands required to initialize the database:
$ mysqladmin create moodss
|
$ mysql
mysql> create database moodss;
|
$ createdb moodss
|
Moomps or moodss, when started and if any data cells history is to be recorded, will then automatically create the database tables if they do not yet exist (make sure the database user has enough privileges to create tables). An informational message is logged when that operation has succeeded.
Example of a MySQL database server with ODBC installation on a local Red Hat Linux system:
Install the mysql and mysql-server rpms (you may also use instead the latest MySQL-server, MySQL-client and MySQL-shared rpms from www.mysql.com) and the unixODBC rpm.
Install the MyODBC rpm from your Red Hat distribution.
In /etc/odbcinst.ini, insert:
[MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc.so FileUsage = 1 |
and in /etc/odbc.ini:
[moodss] Driver = MySQL Database = moodss |
finally in moodss preferences, thresholds and database sections:
| From address: | jfontain |
| Outgoing mail SMTP servers: | 127.0.0.1 |
| ODBC DSN: | moodss |
| user: | jdoe |
| password: | xxxxxx |
(assuming jdoe MySQL user exists, has proper privileges and xxxxxx as password).
Important: if you have created a database using a moomps daemon prior to version 2.3, please follow the following simple instructions to convert your existing database so that it can be used by the latest moomps and moodss applications. If you have never created a moodss database or used moomps prior to its version 2.3, you can completely ignore this section.
There has been a change in the tables structure at the moodss 17.0 and moomps 2.3 release. The entries table was affected the most, but backward compatibility has been maintained, provided you alter some tables by following the instructions below.
Important: make sure to backup your existing database before making the following alterations, just in case.
In the instances table, the start column type has been changed from TIMESTAMP to DATETIME, since in MySQL, manually updating any row would result in the start cell being set to the current time. It is advised to manually change the type before being caught one day by this behavior (note: can be done while the database is being used without problems).
The identifier column was also added, to record the name set by the module code (which appears as the title of the module tables in the moodss GUI).
instances table modification:
CREATE TABLE instances (
number INTEGER NOT NULL PRIMARY KEY,
start |
To convert an instances table created by a moomps daemon prior to version 2.3, it suffices to insert an identifier column and change the start column TIMESTAMP type, as the following statements (examples for MySQL) show:
ALTER TABLE instances MODIFY start DATETIME NOT NULL; ALTER TABLE instances ADD COLUMN identifier VARCHAR(255) AFTER module; |
Note: with some databases (such as PostgreSQL), it may be necessary to insert the following row to enforce consistency:
INSERT INTO instances VALUES (0, NOW(), '', NULL, 0, 0); |
In the entries table, the module, major and minor columns were replaced by the instance column, since it was wrongly assumed that the internal data columns of a module depended solely on its version, assumption put to pieces by modules (such as snmp) which can dynamically generate completely different internal data columns depending on the module options.
entries table modifications:
CREATE TABLE entries (
instance INTEGER NOT NULL REFERENCES instances,
|
Use the following statements (examples for MySQL) to alter the entries table (note: do not remove the module, major and minor columns, but simply allow them to accept NULL values, so that the database remains consistent, the moodss and moomps applications being able to handle both old and new instance entries):
ALTER TABLE entries ADD COLUMN instance INTEGER NOT NULL REFERENCES instances FIRST; ALTER TABLE entries MODIFY module VARCHAR(255); ALTER TABLE entries MODIFY major INTEGER; ALTER TABLE entries MODIFY minor INTEGER; |
Finally consistency needs to be enforced (note: the second statement may not work in which case it is necessary to set the instances numbers in the instance column manually by matching with the instances table number column):
ALTER TABLE entries DROP PRIMARY KEY; ALTER TABLE entries ADD UNIQUE (instance, number); |
In the history table, the instant column type has been changed from TIMESTAMP to DATETIME, since in MySQL, manually updating any row would result in the instant cell being set to the current time. It is advised to manually change the type before being caught one day by this behavior (note: can be done while the database is being used without problems).
history table modification:
CREATE TABLE history (
instant |
Use the following statement (example for MySQL) to alter the history table:
ALTER TABLE history MODIFY instant DATETIME NOT NULL; |
Starting with moodss version 18.0 and moomps version 3.0, modules with internal row numbers of 64 bits length are supported, if running under Tcl/Tk version 8.4 or above.
If you were using Tcl/Tk version 8.3 and upgrading to 8.4, and you will be archiving data from 64 bits capable modules (see documentation), then you need to change the type of 2 columns in the database. If you are not sure that you will be using 64 bits modules, then just go ahead and upgrade the database, as this is a safe operation.
If you are using:
The history and data tables need to be altered (make a backup before executing the following command just in case), as the following example for MySQL shows:
ALTER TABLE history MODIFY row BIGINT; ALTER TABLE data MODIFY row BIGINT; |
A: Here is a summary of the situation using an example:
The major version change has resulted in a new instance (say number 21) being created for the foo module in the database (assuming the module parameters have not changed), so the old history data (created using foo version 1.3.4 and saved under database instance 6) is still valid but not accessed using the same instance number: is it possible to integrate the old and the new data?
What needs to be done is to change the old data instance number (6) to the new instance number (21) for all recordings in the history table, then remove all references to the instance number 6 in the other tables (although that step is not strictly necessary: that would just leave a database instance (6) with no data, which moodss can handle).
Here is an example of the necessary steps:
