Attaching the Big Sister server to a database

As of revision 1.00 Big Sisters supports storing some of the status information in a database like i.e. MySQL for later processing. In 1.00 and 1.01 this is limitted to performance data. Revision 1.02 will introduce a few more graphing capabilities making use of the stored performance data.

Enabling data logging in a database

By default, data storage in a database is disabled. In order to enable it, follow the following procedure:

  • install the DBI perl module, and the DBD (database driver) module for the database you want to attach

  • install the database server (MySQL, PostgreSQL, whatever), create a database, e.g. called "bigsister", and a database user granted permissions to create tables, update and select rows

  • find the db.cfg file in the etc directory and copy it over to the adm directory

  • edit the db.cfg file, comment the entries referring to the "Null" database, uncomment the entries matching your chosen database server or derive entries from the example

  • Re-start the Big Sister server: The database tables should now be automatically created and Big Sister starts logging data

[Tip]Tip

Big Sister is using a very simple data model allowing data to be spread over multiple databases or be stored in very simple, not really relational databases. Therefore, instead of setting up a database server you can even choose to store data in CSV files!

Data logging in MySQL database

The database interface of Big Sister is not limitted to particular database servers. Anyway, since MySQL is very wide-spread we use it as an actual example to show how to set up database access for Big Sister.

Prerequisits

Before you can setup Big Sister data storage, you need to install the MySQL database server, of course. Also, Big Sister uses Perl's DBI (database interface) abstraction layer for accessing databases. You have to install DBI and the MySQL driver for DBI called DBD::MySQL. Both are directly available from CPAN or most probably your operating system or Perl distribution does offer packages for those modules, i.e. on Debian you would install the package libdbd-mysql-perl.

Creating the database and database user

Next, you need to create an (empty) database for Big Sister. You do so by running the mysqladmin command:

# mysqladmin create bigsister
[Note]Note

Your MySQL installation might require you to login with a priviledged user and password in order to create databases and/or create users.

Now, a user must be created, that has read and write access to tables, and can create tables in our newly-created bigsister database. Do this using SQL statements executed via the mysql command:

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.24_Debian-5-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL ON bigsister.* TO bigsister@localhost IDENTIFIED by 'abc123';
Query OK, 0 rows affected (0.15 sec)

mysql> GRANT ALL ON bigsister.* TO bigsister@'%' IDENTIFIED by 'abc123';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

This example creates a user called bigsister with the password abc123 that is granted all permissions in the bigsister database and may access the database from the local machine as well as via the network.

Configure Big Sister

Now, find the etc/db.cfg file and copy it over into the adm directory. Find the (commented out) section where mysql is mentioned. Uncomment this section. Also, find the lines containing Null and comment them out or remove them. Then, replace the username, password, database and database server settings. Your db.cfg should now look something like this:

db=host		driver=DBI table=host datasource=DBI:mysql:database=bigsister \
  user=bigsister password=abc123
db=variable	driver=DBI table=variable datasource=DBI:mysql:database=bigsister \
  user=bigsister password=abc123
db=perf		driver=DBI table=perf datasource=DBI:mysql:database=bigsister \
  user=bigsister password=abc123
db=perfinfo	driver=DBI table=perfinfo datasource=DBI:mysql:database=bigsister \
  user=bigsister password=abc123
...

Re-start the Big Sister server. The database tables will automatically be created as soon as there is data to store in them. Make sure, an agent is reporting performance data to your server. After a moment, have a look at your database:

# mysql       
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 296 to server version: 4.0.24_Debian-5-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use bigsister;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_bigsister |
+---------------------+
| host                |
| perf                |
| perfinfo            |
| variable            |
+---------------------+
4 rows in set (0.01 sec)

Our database is working. If you see no tables, have a look at your syslog. A message should have been logged if Big Sister cannot access the database for some reason. Make sure the database user actually has permission to access the database, i.e. like that:

# mysql -u bigsister -p bigsister
Enter password: 
Reading table information for completion of table and column names
...

Data logging without a database server: the file database

Setting up a database server may be prevented by your IT policy or just too much work for a small installation. Big Sister explicitly supports maintaining its data storage in a file based pseudo-database, mainly consisting of CSV files for non-critical tables and a special binary file storage for performance-critical parts of the database.

In order to enable this file database, please install the DBD::CSV Perl module and follow the general setup above. Find the commented out section in db.cfg with the title File Data Store, uncomment it and replace the file paths if necessary.

Database Structure

Figure 3.3. Database Structure of Big Sister 1.01

Database Structure of Big Sister 1.01

Big Sister 1.01 so far only stores performance data in the database. The main table is the perf table, where each row corresponds with an incoming numerical perf data report. Textual data is stored in the perfinfo table.