This wiki page explains and also documents the ncsMessage database structure, in a technical way.

For more info about accessing the database, see DatabaseForUsers.

Database Architecture

The function of the Message Database is to keep a persistant copy of the Telescope Messages that the different systems are exchanging all the time.

These messages are centralized via an Elvin server. More information about NCS messages in NcsMessages wikipage.

A program called ncsMessagesToDB.py is used in order to forward the ncsMessages get via Elvin to a MySQL database, currently in mrt-lx1 host (see Feeding the Database below). Painless access to the MySQL server is possible via PhpMyAdmin

Database Structure

Mainly, we can consider two different kinds of Telescope ncsMessages: the Info messages, that are sent each second with status information mostly; and the rest of the messages.

Due to database overload, only the last instance of each Info message is stored in the database. The other types of messages are stored without this constraint, along the time. One set of tables in the database should be enough to stores all messages, but due to the high number of Info messages received (one per second) it is inefficient. When this structure is big enough, around 200 thousand records, MySQL server proc overloads too much the system.

Because of that, these two kinds of ncsMessages have to be stored separated, in our case in two different sets of tables.

DB Structure in depth

As commented in NcsMessages, each ncsMessage has a set of attributes with it:

For a better database design, the set of data values and the rest of attributes of the message should be stored in different tables, with a one-to-many relationship between them. The one-to-many relationship comes because one message has several data values (one-to-many).

So, in order to store ncsMessages we need two tables as follow:

"Messages" table structure

ID

EntryType

TimeStamp

LogId

Value

int(10) autoinc. Primary key.

varchar(255)

datetime

varchar(255)

varchar(255)

"DataValues" table structure

ID

Name

Value

id_message

int(10) autoinc. Primary key.

varchar(255)

varchar(255)

int(10). Foreign key to Messages table (ID).

Please be aware of the id_message field in DataValues table, as the translation of the one-to-many relationship to MySQL level. The id_message field is called a foreign key. A foreign key must always point to a primary key somewhere else, because of referential integrity, among other constraints. Due to that our foreign key is pointing to the ID field in Messages table.

Efficiency in DB handling

As said before, the Info messages have to be stored separated from the rest of messages. Because of that, we implement another pair of tables called SnapshotMessages and SnapshotDataValues with exactly the same structure as explained before. The only difference is that these two tables only store the last instance of any message (please note not only Info message! also the rest of types) to keep the amount of records in these tables as small as possible.

Feeding the Database

A program called ncsMessagesToDB.py is used in order to forward the ncsMessages get via Elvin to a MySQL database, currently in mrt-lx1 host.

The tool is in stable version and could be find in mrt-lx1:/ncsServer/mrt/ncs/tools/ncsMessagesToDB/

Right now, only two parameters are needed, the Elvin Host to get messages from (option --elvin | -e), and the MySQL database server to store messages in (option --mysqlhost | -m) (see ./ncsMessagesToDB.py --help ).

/!\ Please, be aware that this program must be running the whole time, in order to maintain updated the database.

DatabaseStructure (last edited 2010-09-27 09:00:45 by localhost)