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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Ben,
If I understand correctly, every record in the METRIC table has a
unique id, plus a reference to a SERVICE record (using the
service_id). Every record in the SERVICE table has a unique id and a
reference to a HOST record (using the host_id).

In that case, good _relational_ db design commands that you use option
2, since the other option is again duplication of information.

Tim.

PS: Nice ERD ;)

On Fri, 10 Dec 2004 09:49:16 +0000, Ben Clewett <bcl...@pe...> wrote:
> 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
>