Re: [Perfparse-devel] Key Structure for new database tables.

[Ben C. <bcl...@pe...>]

Hi Tim,

No problem.

The host, service and metric is a unique ID of a real host, service or 
metric entries.  Referenced in host, service and metric tables.

The host table, for example:

CREATE TABLE IF NOT EXISTS perfdata_host (
	host_name VARCHAR(75) NOT NULL PRIMARY KEY,
	host_id INT NOT NULL,
	UNIQUE(host_id),
	....

(host_name is PK because it is already in current tables.  host_id is a 
new field.)


The full table definition of the new binary tables may be one of these:

Option 1:

CREATE TABLE IF NOT EXISTS perfdata_bin (
	host_id INT NOT NULL,
	service_id INT NOT NULL,
	metric_id INT NOT NULL,
	ctime DATETIME NOT NULL,
	PRIMARY KEY (host_id, service_id, metric_id, ctime),
	value DOUBLE,
	state TINYINT NOT NULL,
	bin_extra_id INT
	) TYPE=InnoDB;

Option 2:

CREATE TABLE IF NOT EXISTS perfdata_bin (
	metric_id INT NOT NULL,
	ctime DATETIME NOT NULL,
	PRIMARY KEY (metric_id, ctime),
	value DOUBLE,
	state TINYINT NOT NULL,
	bin_extra_id INT
	) TYPE=InnoDB;


The difference is that the second is:
- Smaller
- Faster
- Easier to construct queries.


A simple ERD:


Option 1:

[host] ---< [service] ---< [metric]
   |            |              |
   | +----------+              |
   | |                         |
   | | +-----------------------+
   | | |
   ^ ^ ^
[bin data]
   v
   |
[extra data]


Option 2:

[host] ---< [service] ---< [metric]
                               |
                               |
                               |
       +-----------------------+
       |
       ^
[bin data]
   v
   |
[extra data]



Ben.




Tim Wuyts wrote:

> Ben,
> For the sake of keeping the discussion clear, could you provide us
> with a complete database schema? For the moment, it is not clear what
> e.g. metric is (it's declared INT, so I suppose it references some
> other table, but this is not defined).
> 
> A graphical representation (ERD) would be nice :)
> 
> Thx,
> Tim
> 
> On Fri, 10 Dec 2004 08:39:43 +0000, Ben Clewett <bcl...@pe...> wrote:
> 
>>Dear PP development community.
>>
>>I am recoding our main database to be smaller and faster.  As discussed
>>in this document:
>>
>>http://wiki.perfparse.org/tiki-index.php?page=DatabaseConversionSpecification
>>
>>I want to look again at the key structure for the main binary table.
>>
>>The two options are:
>>
>>1:
>>        host INT, service INT, metric INT, ctime DATETIME,
>>        PRIMARY KEY (host, service, metric, ctime)
>>
>>2:
>>        metric INT, ctime DATETIME,
>>        PRIMARY KEY (metric, ctime)
>>
>>Originally we were going to use (2).  However several of you commented
>>that (1) would be more useful for extracting all data for, say, a host
>>or a service.
>>
>>I am looking at this again and am moving back towards structure instead
>>(2).  This gives:
>>
>>- Smaller table space.
>>- Faster keyed access.
>>- Easier to construct queries.
>>
>>I am also looking with respect to the only two likely use-cases in the
>>near future:
>>
>>- Extracting data for one metric.  Eg, a graph.
>>- Extracting data for two or more random metrics.  A graph of multiple
>>metrics.
>>
>>Neither of these options require the large complex key.
>>
>>If it was ever needed to get all data for a host, this can be completed
>>simply using a JOIN.  Slightly slower, but the number of times this may
>>be used is small enough that the disadvantaged are not significant.
>>Where as we all want fast graphs.
>>
>>Since it was users on this group who suggested using the longer key
>>structure (1), I would very much like to know how you feel before I decide.
>>
>>Regards,
>>
>>Ben Clewett.
>>
>>--
>>Ben Clewett  bcl...@pe...
>>PerfParse    http://www.perfparse.org
>>PP FAQ       http://wiki.perfparse.org/tiki-list_faqs.php
>>
>>-------------------------------------------------------
>>SF email is sponsored by - The IT Product Guide
>>Read honest & candid reviews on hundreds of IT Products from real users.
>>Discover which products truly live up to the hype. Start reading now.
>>http://productguide.itmanagersjournal.com/
>>_______________________________________________
>>Perfparse-devel mailing list
>>Per...@li...
>>https://lists.sourceforge.net/lists/listinfo/perfparse-devel
>>
> 
> 


-- 
Ben Clewett  bcl...@pe...
PerfParse    http://www.perfparse.org
PP FAQ       http://wiki.perfparse.org/tiki-list_faqs.php