Menu
▾
▴
Re: [Perfparse-devel] Key Structure for new database tables.
|
From: Ben C. <bcl...@pe...> - 2004-12-10 10:20:47
|
Tim Wuyts wrote: > 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. Tim, These are my feeling as well. However the complex key structure was requested by a member of this group, in order that data for an entire service or host could be selected more easily. Not a use-case which is currently used, but may be needed by some user :) I'll wait a while to see if anybody else has strong feelings on the subject. > PS: Nice ERD ;) Thanks! Ben > > > 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 >> > > > > ------------------------------------------------------- > 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 |
