Re: [Perfparse-devel] Using indexes with mysql

[Viljo M. <na...@ma...>]

Hello,

Actually, the first thing I thought about perfparse data schema, was 
that why store hostnames in varchar for every value nagios gives us. 
Therefore I like Flo's idea about putting hosts, metric names and 
service_descriptions to different tables and simply joining them with 
_bin table. And also putting raw data to same table would save lots of 
space and unneeded duplicate values. I guess that would speed up things 
greatly.

I'm not sure about storing times in unix timestamp format. I know mysql 
supports this, don't know about Oracle, but it's not supported by 
PostgreSQL, for which I indend to write module sometime (if somebody 
doesn't beat me on this). By 'supported' I mean that postgres doesn't 
have TO_UNIXTIME and FROM_UNIXTIME functions (or however they were called).

So searching would be really weird, because first I had to convert unix 
timestamp to date and then compare dates to find one week interval or 
whatever. But then again, I might be wrong too :)

Rgds,
Viljo

> What i would do to save storage:
> 
> create table hosts_names (
>    host_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
>    host_name VARCHAR(75) NOT NULL
> )
> 
> and replace host_name column in perfdata_bin with the host_id. Do joins 
> to get the textual host_name in queries. The same could be done for 
> service_description and metric, since there is a limited number of 
> different values.
> 
> Another proposal would be to use a integer to store timestamps. I'd use 
> unix timestamps (seconds since 1.1.1970) to store dates, since - in my 
> experience - the handling of dates in different databases (mysql, 
> oracle, ...) is not very portable.
> 
> Last i'd propose to store the raw data in the bin table, since i really 
> don't know why this is stored in a extra table. But probably there is a 
> reason i didn't realize.
> 
> Finally i get:
> 
> TABLE perfdata_bin (
>    host_id INTEGER NOT NULL,
>    service_id INTEGER NOT NULL,
>    metric_id INTEGER NOT NULL,
>    ctime INTEGER NOT NULL,
>    PRIMARY KEY (host_id,service_id,metric_id,ctime),
>    value DOUBLE,
>    state TINY INT,
>    warn DOUBLE,
>    critical DOUBLE,
>    max DOUBLE,
>    min DOUBLE,
>    txt_data VARCHAR(255)
> )
> 
> And tables for host_name, service_description and metric.
> 
> Note that this table has almost only fixed sized small columns.
> 
> What do you think?