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

[Tim W. <tim...@gm...>]

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
>