[Perfparse-devel] Re: Suggested change to the data structure

[Yves M. <yme...@li...>]

I read the previous mail very quick. I may say things that were already s=
aid.
Only one comment at then end...

> 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 les=
s
> 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 =3D
>          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 als=
o
> holds the Metric ID (mid) so that a correct start on the primary key is
> possible:
>
> SELECT * FROM perfdata_binary
>      WHERE mid =3D perfdata_service_metric.mid
>      AND ctime =3D 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.

Yes, I agree with that. However, won't the check take too much time ?

> Because of problems comparing FLOAT and DOUBLE values using '=3D=3D',
> compare a hash of raw ascii performance data to hash value in table.
>
> Eg: raw_data_hash =3D hash("102:103;@1232:2343.244;0;3000");

Besides fixing problems of comparaison, this also speeds up the search: y=
ou test on only
one key, not on all.

I have a question: is hash() injective ? I mean : how many arguments can =
you give to
hash() to have the same value ? If the answer is 1, it will work well (is=
 injective, and
maybe even bijective). If the answer is "can be more than 1", you also ha=
ve to test the
other values to check if the record already exist, and if yes, which one.

Well, that's all for now. I'm looking forward 0.99.09 (or 0.99.10 if 0.99=
.09 is my
latest 0.99.08ymX) with the new tables !
I prefer not doing it because you have a better idea than me on how this =
can be
implemented, and I prefer that only one work on it.
If you want, I can work on the perfparse binary, on the line scanner. But=
 if I do it, I
want that we freeze the code and work only on that. Otherwise, it will be=
 a mess like we
have never had :)

Yves

>
> 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
>
>
>
>
>


--=20
- Homepage    - http://ymettier.free.fr - http://www.logicacmg.com -
- GPG key     - http://ymettier.free.fr/gpg.txt                    -
- Maitretarot - http://www.nongnu.org/maitretarot/                 -
- GTKtalog    - http://www.nongnu.org/gtktalog/                    -