8. Writing data into MySQL

8. Writing data into MySQL
Prev

Sometimes it is desirable to store monitoring data in a permanent and flexible form. This might be for generating reports in an automated fashion, for conducting ad-hoc trend analysis or just to see whether your site's current behaviour compares with something similar seen several months ago.

Ganglia, which uses RRDTool for data storage, allows the user to adjust the time-frame, permitting a historic view. However, the reference point is always the current time. One cannot generate a “last day” set of graphs for a period three months ago. Within the RRDTool file, aging data looses fidelity. This is a useful feature of RRDTool data storage, but it means that a graph showing a 24-hour period six months ago is necessarily less accurate.

To archive monitoring data without compromise an alternative storage mechanism is needed. One such solution is to store the data within a database and MySQL is a popular choice of database.

This section demonstrates how to configure MonAMI so it stores data within a MySQL database.

Configuration file

As before, copy the following configuration file as /etc/monami.d/example.conf.

##
##  MonAMI by Example, Section 8
##

# Our root filesystem
[filesystem]
 name = root-fs
 location = /
 cache = 2

# Our /home filesystem
[filesystem]
 name = home-fs
 location = /home
 cache = 2

# Once a minute, record / and /home stats into MySQL database.
[sample]
 read = root-fs, home-fs
 write = mysql
 interval = 1m

# Store data in MySQL database
[mysql]
 database❶ = monitoring
 user = monami-writer
 password = somethingSecret
 table❷ = filesystem
 field❸ = root-available❹ : root-fs.capacity.available❺
 field = home-available : home-fs.capacity.available

Some points to note:

❶	The database attribute must be specified when writing to a MySQL database and the corresponding database must already exist within MySQL.
❷	You must specify a table attribute but the table doesn't have to exist.
❸	The field attributes define which metrics are written to the columns.
❹	The name of a table column within the MySQL table.
❺	The path to a metric within supplied datatrees.

Setting up MySQL

Before running MonAMI, you must create the MySQL user monami-writer and create the monitoring database. If the user or database you intend to use already exist, then you can just reuse the existing ones. If you are using a user that already exists, make sure it has sufficient privileges to store the data.

The following SQL will create the monitoring database, create the monami-writer user and authorise this user to create tables within the database and to append data to those tables.

CREATE USER 'monami-writer' IDENTIFIED BY 'somethingSecret';
CREATE DATABASE monitoring; 
GRANT CREATE,INSERT ON monitoring.* TO 'monami-writer';

Before appending new data, the MonAMI mysql plugin will check the table exists. If it doesn't the plugin will try to create the table.

The created table will be base on the datatree the mysql target receives: each field attribute will have a corresponding column in the created table and the storage type will be based on the metric. If a field attribute's metric is missing from the first datatree then MonAMI will create a STRING column for that metric.

You can create the table manually; the procedure is described in the MonAMI User Manual. If the table is created manually, then the MonAMI writer account can be granted less privileges; however, the increase in security is limited and manually creating the tables is a hassle, so it is easier to let MonAMI create the tables.

Running the example

Run MonAMI in the usual fashion (/usr/bin/monamid -fv) for at least one minute. During that minute, if the mysql target creates the storage table, it will display a message. So, the first time you run the plugin, you will see an extra line:

Starting up...
mysql> table filesystem doesn't exist, creating it...

As data is added to the database the row count will increase. The following shows a MySQL interactive session where the number of entries in the filesystem table is counted:

mysql> SELECT COUNT(*) from monitoring.filesystem\G
*************************** 1. row ***************************
COUNT(*): 20
1 row in set (0.00 sec)

mysql>

The same SQL can be executed from the command-line. For example:

paul@donachain:~$ mysql --skip-column-names -se \
> 'SELECT COUNT(*) FROM monitoring.filesystem;'
22
paul@donachain:~$

You may need to specify which MySQL user to use and (usually) request that mysql client prompts you for a password:

mysql -u user [-p] --skip-column-names -se SQL-query

Using the stored data

Once the monitoring data is in the MySQL database, it can be used as any other data stored in a database. This greatly increases the opportunity with which you can conduct post-analysis of the monitoring data.

Perhaps the quickest way of analysing the available data is to write SQL queries. However, this requires knowledge of SQL before one can write custom queries and other methods may be easier.

Handling awkward column names

The above example has column names of root-available and home-available. Because these names include the hyphen character (-), they must be quoted by placing these words inside back-ticks (e.g. `root-available`) when used in SQL queries. The same is true if the column name is a MySQL reserved word, such as SELECT and LIKE.

Another option is to build dynamic web pages that display information from the database. The PHP language includes support for querying a MySQL database. It also includes support for building custom graphics through the GD library. Using these tools, it is relatively easy to build custom graphs to that are dynamic, based on the data stored in MySQL.

For the purpose of this tutorial, we shall use OpenOffice to analyse the monitoring data. There are two parts: how to obtain live data from the database within a spreadsheet and how to plot and export graphs using this data. The instructions are written for OpenOffice v2.2; other versions may require a slightly different process.

Although this tutorial uses OpenOffice, a similar procedure should work for other spreadsheet packages.

Creating a spreadsheet with monitoring data

This part of the tutorial shows how to link the data gathered in MySQL into an OpenOffice spreadsheet. This allows for easy post-analysis of the data using normal spreadsheet functions.

Start OpenOffice.
Create database document file. This document will hold OpenOffice's understanding of the MySQL database.
1. Select File → New → Database from the menu or click on the New, Database button. The Database Wizard dialogue box should appear.
2. Select Connect to an existing database, choose the MySQL database type and either click on the Next>> button or type Alt+N.
3. Select Connect using JDBC (Java Database Connectivity) and either click on the Next>> button or type Alt+N.
4. Complete the requested information:
  Name of the database
  monitoring
  Server URL
  localhost (or whatever is the MySQL server hostname). N.B. this field takes a hostname, not a URL.
  Port number
  use the default port.
  MySQL JDBC driver class
  com.mysql.jdbc.Driver
  Then either click on the Finish button or type Alt+F.
5. The Save as dialogue box will appear. Save the resulting OpenDocument Database document somewhere as fs-monitoring.odb
Create new spreadsheet that has data from the filesystems table imported.
1. Select File → New → Spreadsheet from the menu or click on the New Spreadsheet button. This will create a new, empty spreadsheet.
2. Select View → Data Sources from the menu or press F4. The Data Sources section should appear above the spreadsheet. The Data Source Explorer (left pane of the Data Sources section) should be visable and show a tree structure, with fs-monitoring as one of the root elements.
3. Expand the fs-monitoring and Tables branches, and select monitoring.filesystems. You should see data from the filesystems table appear within the right pane of the Data Explorer section.
4. Click on monitoring.filesystems and drag this over to a cell within the spreadsheet. You will see the data appear within the spreadsheet, which will be highlighted.

With the data from database is imported, you have the full spectrum of analysis tools available from the spreadsheet. You can perform statistical or trend analysis.

Updating the imported data

The spreadsheet remembers that the monitoring data was originally taken from a database. You can fresh the imported data by selecting one of the cells within the imported data and selecting Data → Refresh Range from the menu.

Creating graphs from monitoring data

Perhaps the most useful action is to plot a graph using the gathered data. The following procedure describes how to create a stand-alone image of a graph (as PNG-, EPS- or SVG- formatted file).

Start OpenOffice and load the spreadsheet containing the imported data.
Create a chart, using imported monitoring data.
1. Make sure the data is highlighted, then either select Insert → Chart... from the menu or click on the Insert Chart button. Then click and drag out a region on the spreadsheet where you want the graph to appear. After doing this, the AutoFormat Chart dialogue box will appear.
2. Make sure First row as label and First colum as label options are both selected and either click on the Next>> button or press Alt+N.
3. Select the Areas option from Choose a chart type and either click on the Next>> button or press Alt+N.
4. Select the Stacked option from Choose a variant and either click on the Next>> button or press Alt+N.
5. Enter chart information:
  Chart title
  Storage usage
  X axis
  Available space / MB
  Then click on the Create button or press Alt+A.
Export chart as a PNG-, EPS- or SVG- formatted file.
1. With the chart selected, either select Edit → Copy from the menu or press Ctrl+C.
2. Create a new drawing. Either select File → New → Drawing from the menu or click on the New, Drawing button. This will create a new, empty drawing window.
3. Either select Edit → Paste from the menu or press Ctrl+V.
4. With the chart still highlighted, select File → Export... from the menu.
5. Within File format, select the appropriate format (e.g. PNG, EPS or SVG), enter a filename in the File name input and either click on Export... or press Alt+E
6. For some file formats some aditional information is required. If so, a dialogue box will appear requesting information. Complete this information and click on OK.

Watching database disk usage

Over time, the monitoring data stored within the MySQL database will increase. We must take care to keep the table sizes reasonable. MonAMI can be used to make sure sufficient space is available.

If MonAMI is running on the MySQL server is running, then the filesystem plugin can monitor the available space. The nagios plugin can provide an alert if the filesystem space becomes too low.

The MonAMI mysql plugin is also a monitoring plugin. In addition to stores monitoring data, it can query the current status of the MySQL database system and the tables stored within databases. The plugin does this using the MySQL API, so it can monitor the database remotely.

One of the branches (mysql.Database) contains information on each database. This information includes information on each of that database's tables. So the branch mysql.Database.monitoring.Table.filesystems contains information on our filesystems table, part of the monitoring database. The metric ...Table.filesystems.Datafile.current records the current space taken up by the filesystems table in Bytes. Using this, one can monitor the space the table is taking up and trigger an alert once this table reaches a critical level and intervention is needed.

An example configuration is:

[filesystem]
 name = root-fs
 location = /
 cache = 2

[filesystem]
 name = home-fs
 location = /home
 cache = 2

# Once a minute, record / and /home stats into MySQL database and
# update Nagios status.
[sample]
 read = root-fs, home-fs
 write = mysql, nagios-fs
 interval = 1m

# Every ten minutes, update Nagios status of MySQL storage usage.
[sample]
 read = mysql.Database.monitoring.Table.filesystems.Datafile.current
 write = nagios-mysql
 interval = 10m


# Store data in MySQL database
[mysql]
 database = monitoring
 user = monami-writer
 password = somethingSecret
 table = filesystem
 field = root-available : root-fs.capacity.available
 field = home-available : home-fs.capacity.available


# Nagios checks for file-system
[nagios]
 name = nagios-fs
 host = nagios-svr.example.org
 port = 5668
 password = NotSecretEnough

 service = rfs : ROOT_FILESYSTEM
 check = rfs : root-fs.capacity-available, 10, 0.5
 check = rfs : root-fs.files.available, 400, 100

 service = hfs : HOME_FILESYSTEM
 check = rfs : home-fs.capacity-available, 100, 10
 check = rfs : home-fs.files.available, 400, 100


# Nagios checks for MySQL
[nagios]
 name = nagios-mysql
 host = nagios-svr.example.org
 port = 5668
 password = NotSecretEnough

 service = ms : MYSQL_SPACE
 # Warn: 20 MiB, Crit: 1 GiB
 check = ms : mysql.Database.monitoring.Table.filesystems.Datafile.current,\
              20971520, 1073741824