Menu
▾
▴
perfparse-devel
|
From: Ben C. <bcl...@pe...> - 2004-12-10 08:39:51
|
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 |
|
From: Tim W. <tim...@gm...> - 2004-12-10 09:36:29
|
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 > |
|
From: Ben C. <bcl...@pe...> - 2004-12-10 09:49:22
|
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
|
|
From: Tim W. <tim...@gm...> - 2004-12-10 09:59:03
|
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 > |
|
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 |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X