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

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

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