[Perfparse-devel] Suggested change to the data structure

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

A revision to suggested alternate structure for storage of binary data.

This adds another four bytes to the binary file, and a foreign key. 
This correctly respects SQL conversions.  This is still drastically less 
than we currently have, and is therefore still faster and smaller.


1.  Table to hold binary data:

CREATE TABLE perfdata_binary (
     mid INTEGER NOT NULL,
     ctime DATETIME NOT NULL,
     PRIMARY KEY (mid, ctime),
     value FLOAT NOT NULL,
     extra_data_id INT,
     INDEX extra_id_idx (extra_data_id),
     FOREIGN KEY (extra_data_id)
         REFERENCES perfdata_binary_extra (extra_data_id)
         ON DELETE SET NULL
)


2. Table to hold extra data:

CREATE TABLE perfdata_binary_extra (
     extra_data_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     mid INTEGER NOT NULL,
     INDEX mid_idx (mid),
     FOREIGN KEY (mid)
         REFERENCES perfdata_service_metric (mid)
         ON DELETE CASCADE
     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,
     raw_data_hash UNSIGNED LONG
)

To get all fields fields, the following can be used:

SELECT * FROM perfdata_binary JOIN perfdata_binary_extra
     ON perfdata_binary.extra_data_id =
         perfdata_binary_extra.extra_data_id

This can be added as a VIEW when MySQL supports these.


3. The current (last added) record for each metric.

The current record pointed to in the perfdata_service_metric table will 
hold the 'ctime' pointing to the perfdata_binary table.  This table also 
holds the Metric ID (mid) so that a correct start on the primary key is 
possible:

SELECT * FROM perfdata_binary
     WHERE mid = perfdata_service_metric.mid
     AND ctime = perfdata_service_metric.last_perfdata_bin



4. The procedure for adding new binary data:

i) Locate the extra data record from the current exiting binary record.

ii) If changes required, a new record to be created.

Because of problems comparing FLOAT and DOUBLE values using '==', 
compare a hash of raw ascii performance data to hash value in table.

Eg: raw_data_hash = hash("102:103;@1232:2343.244;0;3000");

iii) Add new extra data record if appropriate.

iv) Add the binary data record.



Apart from this, the same notes apply as previous posting.


Ben