Re: [Perfparse-users] Help Needed

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

Sand Philipp wrote:

> Sounds good, how will the tow tables look like?

As well as some incremental changes, the main change is based around two 
observations:

1. With the current table most data is duplicated.  Warn, Crit, Min, 
Max. are the same for thousands of rows at a time.  When the Warn and 
Crit become a range, they will increase size from 4 to 10 bytes. 
Therefore 28 bytes of duplicate information in every row.

2. Secondly, the key structure contains the metric.  Defined by 
VARCHAR(75), VARCHAR(75), VARCHAR(75).  So there is also a maximum of 
225 more bytes of duplicate data in every row.

These two problems are countered using two changes.

First a new table for all the peripheral duplicate information is 
defined.  Warn, Max etc.  There will only be a new record when there is 
a change to these values.  Linked back to the main table by an integer 
foreign key.  Reducing the field size by 24 bytes.  Quite a lot if you 
have 10e+8 rows.  If you like Entity Relationship Diagrams:

+---------------+     +---------------+
|  bin values   |>----| bin extras    |
+---------------+     +---------------+

The second change will be to assign every Metric a unique ID.  A MID. 
These will auto-increment from 1.  Each value will be indexed by this 
MID and not the massive index above.  Therefore saving another load of 
space.

The schema is not yet defined.  (Discussion should really be in the 
devel news group, but just for interested...)  But the value table will 
be something like:

TABLE perfdata_bin (
	mid UNSIGNED INT NOT NULL,
	ctime DATETIME NOT NULL,
	PRIMARY KEY (mid, ctime),
	value DOUBLE,
	extra_data UNSIGNED INT NOT NULL
) type=innodb;

You can see that the 'value' is now DOUBLE and not FLOAT.  This allows 
date/time data to be safely stored, as well as other data to which an 
extremely accurate comparison is needed.  NULL as the value can also be 
used.  This will imply a missing data sample and reflected by a gap in 
the graph.  Although not yet supported by the plugin performance output 
format.  You can also see that duplicate data is now, thankfully, 
impossible!

The extra table being:

TABLE perfdata_bin_extra (
	mid UNSIGNED INT NOT NULL,
	extra_data UNSIGNED INT AUTO_INCREMENT NOR NULL,
	PRIMARY KEY (mid, extra_data),
	...

When I get a moment, a more complete specification will be posted to the 
perfparse-devel news group.

I hope this gives you some idea,

Regards, Ben.