Loading...
 

Database

FAQ that covers Database issues.

Questions

Answers

Question: Do I really need MySQL 4 installed in order to use PerfParse?
Answer  Yes, you do really need MySQL version 4. This version has support for transactions, which PerfParse uses to ensure your data is entered correctly. When problems do occur, these cause the program to break without adding garbage to your tables.
PerfParse also uses a feature that was added to MySQL 4.0.4: updates to multiple tables.
You can either install MySQL 4 binaries or build the raw system by downloading the source from mysql.org.
Building MySQL from source should not be too difficult for most Nagios users. However, if you decide to do this, please uninstall the original MySQL binary files first (but not the 'data').
Question: perfparse-0.104.X : perfparsed no longer parse data to the mysql database
Answer  Check your configuration file (perfparse.cfg) that you have enabled mysql storage.
e.g.
Storage_Modules_Load = "mysql"
Question: In Chapter 4 there is: "Please use InnoDB tables ...". What does InnoDB mean? (MySQL Only)
Answer  MySQL uses many table types. The most advances is InnoDB tables. (Although not quite the fastest). I chose these because they offer significant advantages:

- Referential Integrity.
- Transactions.
- Concurrent Backup.

These protect the database from corruption. For instance if a program crashes. They also bind the data together. You cannot, for instance, delete a 'host' if there is still valid data for this host. Therefore avoiding hundreds of meg of data you cannot see or delete without specialist coding :-)

They also offer concurrent backup. 'Hot Backups'. You can dump the contents of the database without stopping PP. Even if that dump takes many days, you always get a snapshot of how the database was at the start of the backup.

More information on InnoDB tables from http://www.innodb.comQuestion.

Question: How can I configure MySQL to use InnoDB tables?
Answer  You must download the 'max' version of MySQL. The rest is completed by PerfParse.
Question: How do I create my MySQL database and database users?
Answer  You have to create a user and a database yourself.

Start the MySQL shell as the root user and enter:

mysql> CREATE DATABASE perfparse;
mysql> GRANT ALL ON perfparse.* TO 'perfparse'@'%' IDENTIFIED BY 'password';

You can then create the tables from the UNIX shell by entering:

$ cat mysql_create.sql | mysql -u perfparse -p -D perfparse

If you use the Nagios MySQL system, you can use the same database and users for PerfParse.

Question: How to port perfparse to another database engine (Postgresql, Oracle, Sybase...)
Answer  Perfparse was initially developped with MySQL support. PostgreSQL support is being done. Contact the development team if you want to help. And read the following...

In the following, replace mydb with the name of the database engine you are working with

1. Convert the database schema:

scripts/mydb_create.sql
scripts/mydb_delete.sql

Try to keep to ANSI-SQL. Eg, use BOOL, TRUE and FALSE which MySQL does
not support. Rather than copying MySQL including it's short fallings :-)

This stage will give you a good idea of the challange to complete the
work.


2. Two bits of work here, DBMS library.

(i) Copy scripts/storage_mysql.* to scripts/storage_mydb.*

Edit where required. Keep the .h the same in all cases.

(ii) Copy directory libpp_mysql to libpp_mydb.

Edit all parts, starting with dbms.c. Please keep .h files the same.


3. The CGI and db tools

Check the SQL commands and replace where needed using syntax similar to:

#if defined USE_DB_MYSQL
"SELECT * WHERE boolean_field = 1"
#elseif defined USE_DB_MY_NEW_DATABASE
"SELECT * WHERE boolean_field = TRUE"
#endif

Your 'USE_DB_MY_NEW_DATABASE' will be added.

4. Storage modules

Copy an existing module source file, rename it, and update it for mydb. The files to copy are modules/storage_mydb.c and modules/storage_mydb.h.

5. Autotools config files

You should update configure.ac and all the Makefile.am files.
Check what was done for MySQL and PostgreSQL and add something similar.
You do not need to update the other files. For instance, running automake should generate the Makefile.in files. Running configure should generate the Makefile files.


If you have a lot of time for you, think about making a modular system for CGI and db tools, like the existing one for perfparse (storage modules).
Question: Why don't we use RRD to store data?
Answer  I have been asked many times, why not RRD to store data? This is self-maintaining, fast, lots of tools available, great support. Some people have suspected my sanity when I mention using anything as complex and fickle as MySQL for the storage of Nagios data.

This FAQ answer lists some of those reasons. I am willing to accept if any of these are wrong, so this may be the start of a constructive discussion.

There Two kinds of needs for PerfParse.

If you need to see what happened yesterday or few days ago, on a graph, and only a graph. There is a storage module that allows an export of data into a format friendly to gnuplot. This should fit most of your needs already.

If somebody implements a storage module for rrdtool, this should not be too hard to do, and it should also fit that need : fill the RRD database, and use your own tools to ask rrdtool to make the graph.

For any other kind of use, RRD Storage is inappropriate.



1. RRD Storage is not client server.

Using a DBMS (Database Management System, such as MySQL) allows the database, Nagios and CGI to all reside on different servers. Complete with security.

This also allows multiple Nagios servers to a single Database to multiple CGI front ends. Which some users have found useful.

2. RRD Storage is fixed length.

An RRD is a fixed length sample of data. This allows automatic auditing of records, they are deleted automatically. However it becomes hard to change the size of the sample. It is also impossible to store an unbound sample length. The DBMS offers a far more flexible solution, including unlimited sample length. You can store load data for the entire lifetime of a server if you so desire.

3. RRD Storage is fixed frequency.

Nagios is variable frequency. This is because of three factors:
(i) You may freely change the sample period at any time.
(ii) Nagios may miss one or more samples.
(iii) Nagios may delay the sampling of data due to load, by a noticeable error.

The first point probably being more important. Under RRD it becomes very hard to resample data when the frequency changes. Again, the DBMS solution is flexible enough to handle this. You can change the sample length as much as you like without effecting the stored data or it's presentation.

4. RRD Storage is inefficient for Nagios data.

RRD is inefficient, leading to large disk space. Nagios produces seven variables for each metric: Value, Critical Low, Critical High, Warn Low, Warn High, Max, Min. An RRD solution demands all these are stored for each sample. PerfParse (when finished) will only store the peripheral data when it changes. This will end up with anything as much as four times as little disk space consumed. As many systems are speed limited to the speed of disk access, this may also lead to a faster solution than RRD.

5. RRD Storage cannot store Nagios data format.

A minor point rarely used, but the Warn and Critical are not single values. They are in fact either an inside or an outside range. Therefore describing an area and not a line. RRD cannot describe an area.

When PerfParse is finished, this will draw areas for Warn and Critical.

6. RRD Storage cannot store raw data.

PerfParse stores the raw data output from Nagios for as long as the user wished to view it. The ability of the DBMS allows for fast access to massive amounts of saved data in a small time. RRD cannot store this data.

7. RRD Storage cannot link completely to a DBMS.

The DBMS does not just store values. It contained limitless information about the hosts, services and metrics. For instance, it can store a picture of each host. This is linked to the data in such a way that the change of one effects the other. Again, this referential integrity becomes very hard under RRD.

8. RRD cannot easily complete complex analysis.

A DBMS has huge ability to analyse numerical data. Either for reports or extraction of statistical data. This allows the developers of PerfParse to generate very strong applications with ease and speed. Including custom reports for users. Again, this is very hard using RRD.

9. Central Database Theory.

Most companies these days have somewhere a large database. Usually heavily guarded and afforded the best and most expensive servers, backups, staff and IT revenue. The idea of PP is that it uses a part of this resource to store the state and history of all equipment handled by the company. (When PP is ported to all other platforms.) Therefore taking advantage of, and becoming part of the heart of a company. Again RRD cannot in my opinion claim this.



I hope this list helps some users understand my motives. You may please email me if you want to discuss this further.

Ben & Yves.



Question: What databases are supported by PerfParse?
Answer  MySQL

Popular Open Source database from http://www.mysql.com.Question GPL licence, free for non-commercial use. Fast and easy to use. Lots of support and tools.

PostgreSQL

Popular Open Source database from http://www.postgresql.orgQuestion BSD licence, truelly free. About as good as databases get. Now avaiable for Windows.

FileModule

Custom PerfParse database written by Yves Mettier. Client server textual database of Nagios output. Can be used to store and graph metrics only.


Question: What abilities are supported by different databases
Answer  The following abilities are avaiable for the following databases:

FM = FileModule
MQ = MySQL
PQ = PostgreSQL

- FM - - MQ - - PQ -
Storage of Nagios data * * F * F
Graphing of metrics * * *
- Using GnuPlot * P P
- Using PerfChart P * *
Bin Summary Stored * *
Bin Summary Used P P
Storage of Raw data * *
Raw History * *
Raw Summary Storage * P
Raw Summary Reports P P
Stored Graphs * *


P = Planned for Later
F = Also FileModule storage (optional)

24-01-2005, Version 0.105.1

Shoutbox

redflo, 14:25 UTC, Sun 10 of Apr, 2005: No, lneuxyuhje, this shall write to a pipe not to a file and so ">" is o.k.
lneuxyuhje, 15:14 UTC, Thu 07 of Apr, 2005: is ">" meant to be ">>" in /usr/local/perfparse/bin/perfparse_nagios_pipe_command.pl ?
redflo, 08:35 UTC, Tue 05 of Apr, 2005: fhdplbvjipsld, it works now. Permissions were wrong.
fhdplbvjipsld, 14:42 UTC, Wed 30 of Mar, 2005: How to post to Forums->Help?
benclewett, 09:27 UTC, Thu 03 of Feb, 2005: Hi Jon! I hope you like our wiki site.