Database for moodss and moomps

Contents:

1. About this document

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.

2. Introduction

Important: if you have created a database using a moomps daemon prior to version 2.3, 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 (note: feature soon to be implemented in moodss).

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.

At this time, the MySQL, PostgreSQL and DB2 (yet untested) databases are supported, while other databases that support ODBC should work (also see installation).
Note: please report any experience with other databases access via ODBC, either to jfontain@free.fr or comp.lang.tcl.

3. Tables

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 history tool. 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:

relations diagram

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.

3.1. Instances

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:
numberstartmoduleidentifiermajorminor
72002-09-14 21:52:37randomrandom147
132002-09-14 22:33:26snmpsnmp(1.2.3.4)212

3.2. Options

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:
instancenamevalue
7-a 
13-a1.2.3.4
13--trimsys
13-isysUpTime,sysDescr,sysObjectID,sysUpTime,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 with all the characters replaced by a * character, as seen in the table extract above.

3.3. Entries

This is a copy of the internal module data table description. 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:
instancenumberindexedlabeltypemessageanchor
701nameasciiuser name 
710cpurealcpu usage in percent 
720diskintegerdisk usage in megabytes 
730memoryintegermemory usage in kilobytes 
741commanddictionarycommand nameleft

3.4. History

The table with the data cells values over time, the actual history table.

Columns:

SQL creation statement:
  CREATE TABLE history (
    instant DATETIME NOT NULL,
    instance INTEGER NOT NULL REFERENCES instances,
    row INTEGER NOT NULL,
    entry INTEGER NOT NULL,
    value VARCHAR(255)
  )

Sample extract:
instantinstancerowentryvalue
2002-09-14 21:52:39732593
2002-09-14 21:52:3979125.0
2002-09-14 22:33:28732545
2002-09-14 22:33:2879124.8
2002-09-14 22:33:36732512
2002-09-14 22:33:3679114.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)

3.5. Data

This is where one can determine which data cells were or are monitored by the moomps daemon. Also contains some extra miscellaneous information.

Columns:

SQL creation statement:
  CREATE TABLE data (
    instance INTEGER NOT NULL REFERENCES instances,
    row INTEGER NOT NULL,
    entry INTEGER NOT NULL,
    label VARCHAR(255) NOT NULL,
    comment VARCHAR(255),
    UNIQUE(instance, row, entry)
  )

Sample extract:
instancerowentrylabelcomment
732random: Robert top disklab workstation
791random: Laura cc cpuin cluster
1301snmp: UpTime 

4. Installation

4.1. Configuration

If you want storage of data cell values in a history database, set the following options in the moodss preferences dialog box, database section:

4.2. Choice

The moomps daemon does 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 is not that important (also moomps does not use transactions since all write operations are atomic). Reading from the database only happens when for example, creating graphs of data cell values over time, from a spreadsheet, but 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.
Note: in a future version, an aggregation feature will be implemented.

4.3. Initialization

At this time, the database initialization is only done by the moomps daemon.

Before starting moomps, the only thing to do is to manually create a database named moodss, no matter what the database type that you have chosen. Here are a few examples of the commands required to initialize the database for moomps usage:

Moomps, when started, will then automatically create the moodss 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 Redhat 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 www.mysql.com.

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).

4.4. Upgrade

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 TIMESTAMP DATETIME NOT NULL,
    module VARCHAR(255) NOT NULL,
    identifier VARCHAR(255),
    major INTEGER NOT NULL,
    minor INTEGER NOT NULL
  )

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,
    module VARCHAR(255) NOT NULL,
    major INTEGER NOT NULL,
    minor INTEGER NOT NULL,
    number INTEGER NOT NULL,
    indexed INTEGER NOT NULL,
    label VARCHAR(255) NOT NULL,
    type VARCHAR(16) NOT NULL,
    message TEXT NOT NULL,
    anchor VARCHAR(255 16),
    UNIQUE(module, major, minor instance, number)
  )

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 TIMESTAMP DATETIME NOT NULL,
    instance INTEGER NOT NULL REFERENCES instances,
    row INTEGER NOT NULL,
    entry INTEGER NOT NULL,
    value VARCHAR(255)
  )

Use the following statement (example for MySQL) to alter the history table:

  ALTER TABLE history MODIFY instant DATETIME NOT NULL;