[Perfparse-devel] Problems with data structure

[Ben C. <be...@cl...>]

Garry, Yves,

To address the two problems Yves found:

1.	Range values for Warn and Crit not supported.
2.	Only store the last found value for Max and Min.

I propose a new data structure for the Binary data which will also 
increase the speed of the queries and lower the size of the data files.

This will require a heavy conversion, but may make a lot of difference.

This will be done through two ideas:

1.	Key the binary data on a Metric ID (MID) and not on (Host, Service, 
Metric).  This will give a far smaller and faster key structure.

2.	Remove all recurring data from the binary file to a second file.  A 
process I call anti-normalization.


This can be done as follows:


1. Two new tables will be used to replace the one binary table we 
currently use.  The first if which is:

CREATE TABLE perfdata_binary (
	mid INTEGER NOT NULL,
	ctime DATETIME NOT NULL,
	PRIMARY KEY (mid, ctime),
	value FLOAT NOT NULL
)

Each record in now only 12 bytes long, plus the external key.  Therefore 
a months data at a frequency of one minute will be half a meg, plus key 
data.  This is considerably less than the 73 bytes currently used.  (If 
new fields for the range were to be added, then this would add another 
12 bytes, making 85 bytes per record!)


2. The second table will contain the extra information, stuff which only 
occasionally changes:

CREATE TABLE perfdata_binary_extra (
	mid INTEGER NOT NULL,
	ctime DATETIME NOT NULL,
	PRIMARY KEY (mid, ctime),
	warn_type ENUM ('none','inside','outside') NOT NULL,
	warn_low FLOAT,
	warn_high FLOAT,
	crit_type ENUM ('none','inside','outside') NOT NULL,
	crit_low FLOAT,
	crit_high FLOAT,
	max FLOAT,
	min FLOAT
)

The frequency of this second table may be as low as once a month. 
Therefore a months samples can be JOINed to one record of the second 
table.  This will result in faster queries from less data, using:

SELECT * FROM perfdata_binary, perfdata_binary_extra
	WHERE perfdata_binary.mid = perfdata_binary_extra.mid
	AND perfdata_binary.ctime >= perfdata_binary_extra.ctime

The 'state' information currently stored will be calculated.  This will 
be done in code-space today, and later with an embedded function when we 
get to MySQL version 5.  This is not the same as the State stored 
against the raw data.


3. The field 'mid' needs adding to perfdata_service_bin:

AMEND TABLE perfdata_service_bin
	ADD mid INTEGER DEFAULT NULL;
AMEND TABLE perfdata_service_bin
	ADD UNIQUE INDEX mid_idx (mid);

This needs populating from zero to the number of metrics so that no 
record is NULL.


4.	A foreign key needs to be added from the new tables to this.


5.	The 'last_perfdata_bin' field on perfdata_service_bin needs to be 
pointed at the correct tables.  This will require a careful conversion!


6.	The delete policies need changing to delete from these tables.

7.	The CGI needs to be altered to read these new files.

8.	The old binary tables can be dropped.

---------------------------------------------------

Alternatively, we can hack the existing binary table.  But it's a mess 
already.  Biting the bullet one more time before version 1.0.0 might 
make all the difference....

Regards, Ben.