Peformance Data in a Database

Druzhinin Eugene druzhinin at rusmedia.ru
Mon Apr 19 17:38:10 CEST 2004


Hello.

I suggest you to consider such an algoritm
 where analytical plugins (or PgSQL user's functions)
 take in arguments some expressions describing lists
 of triples of host/service/perfdata_variable.
In this schema "perfdata_variable" is a meaningful
 name, which define it's sense and measured units.
My opinion is that "perfdata_variable" must be
 combination of management base name (or MIB) and
 variable (or object) name in base scope.
The structure you described omits the variable meaning.
So you can not list entities with similar or
 related characteristics which could be derived
 from variables names (in SELECT queries).
Otherwise we have to operate on basis of
 service_description only and map them on variables
 types manually. In case MIB involved it could be
 entirely or partially automated. I think in specific
 cases it allow analysis code to be reused.
Also you have omitted types of objects other
 then numeric. I like to keep them able to be
 analysed too. Perhaps I will not persist in doing so...


-------------------------------------
On Fri, 16 Apr 2004 10:37:04 +0100
Ben Clewett <Ben at clewett.org.uk> wrote:

> Hi.
> 
> Yes, I think I understood.
> 
> I am concentrating on representing the entities (Hosts, services etc) in 
> a cohesive logical structure.  Designed to store the performance data in 
> such a way to enable meaningful fast queries from the system, in order 
> to analyze the numeric data from the plugins.
> 
> This is hopefully best for data analysis.  It does not provide any 
> information about how the whole network is arranged.  It does not 
> provide any information about how the plugins work, or any meaning to 
> their data other than the unit.  Neither does it store the original 
> text.  This is a strength, as new plugins can be designed which do not 
> require changes to the structure, and data can be extracted with optimum 
> efficiency.
> 
> As far as I can see, having the structure:
> 
> host ----< service ----< metric ---< perfdata
> 
> Is the best way to correctly hold this data for easy analysis.  I would 
> not store the data as CHAR, but as FLOAT to allow a format which can 
> store any number type.
> 
> Eg, to get the max value for a time range for a service, you can easily:
> 
> SELECT MAX(value), metric.metric, metric.unit
> 	FROM perfdata JOIN metric ON metric.metric = perfdata.metric
> 	WHERE time >= $1 AND time < $2 AND host = $3 AND service = $4
> 	GROUP BY metric.metric AND metric.unit
> 
> MAX(value) | metric.metric         | metric.unit
> -----------+-----------------------|------------
>      120232 | /usr                  | B
>    11342300 | /home                 | B
>   145325443 | /usr/local/nagios/var | B
> 
> As this is 100% binary data, correctly indexed and needing no parsing, 
> it would be expected that the results would be produces with great 
> efficiency.  This would be absolutely essential if any great number of 
> services were used over any large length of time.
> 
> This does not store the original textual description.  This is not 
> really needed for data analysis of the type I want.  I think this is 
> what worries you?  Maybe the raw data could be stored in a separate 
> table, something like:
> 
> host ----< service ----< metric ---< binary_perfdata
>                |
>                +--------< raw_perfdata
> 
> Does this help ?????
> 
> Ben.
> 
> Note, I could use the following schema:
> 
> CREATE TABLE metric (
> 	host VARCHAR, service VARCHAR, metric VARCHAR,
> 	PRIMARY KEY(host, service, metric),
> 	unit VARCHAR,
> 	max FLOAT, min FLOAT, warn FLOAT, crit FLOAT)
> CREATE TABLE perfdata (
> 	no INTEGER AUTO_INCREMENT PRIMARY KEY,
> 	host VARCHAR, service VARCHAR, metric VARCHAR,
> 	INDEX id0 (host, service, metric),
> 	value FLOAT)
> 
....................

Best regards.
Druzhinin Eugene.



-------------------------------------------------------
This SF.Net email is sponsored by: IBM Linux Tutorials
Free Linux tutorial presented by Daniel Robbins, President and CEO of
GenToo technologies. Learn everything from fundamentals to system
administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click




More information about the Developers mailing list