Re: [Perfparse-devel] Using indexes with mysql

[Flo G. <fl...@bi...>]

Hi,

i did some thoughts about the schema too:
the mid field is a nice concept, but it makes queries that sort by 
hostname or service description impossible. I think, if indexes in mysql 
(or other db's) are efficient, it's the same if you use a "mid" field or a 
combined index. The extra data table is probably useless, if you assume 
that most performance is gained if the index search is good. So my 
proposal would be:

TABLE perfdata_bin (
    host_name VARCHAR(75) NOT NULL,
    service_description VARCHAR(75) NOT NULL,
    metric VARCHAR(75) NOT NULL,
    ctime INTEGER NOT NULL,
    PRIMARY KEY (host_name,service_description,metric,ctime),
    value DOUBLE,
    state TINY INT,
    warn DOUBLE,
    critical DOUBLE,
    max DOUBLE,
    min DOUBLE
)

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?

Flo Gleixner


On Thu, 28 Oct 2004, Ben Clewett wrote:
...
>
> There will be a new version of the schema out soon.  This only has *one* key. 
> The one key in the new table will be used for the same reason as Key 1 and 
> Key 4 above.
>
> RI.  The new schema will not have RI from the main schema to the data schema. 
> The reason for this is speed and size.  The advantages of RI are far 
> out-weighted by the need for speed and small size.
>
> The new schema will look *something* like:
>
> TABLE perfdata_bin (
>    mid UNSIGNED INT NOT NULL,
>    ctime DATETIME NOT NULL,
>    PRIMARY KEY (mid, ctime),
>    value DOUBLE,
>    state TINY INT,
>    extra_data UNSIGNED INT NOT NULL
> ) TYPE=innodb;
>
> (Field 'extra_data' is a link to a table containing warning, critical, max 
> and min data.  Field 'mid' = Metric ID = (host, service, metric) as a unique 
> INT)
>
> What do you think?  Will this be better?  I note this has no concept of a 
> HOST as-is, will this bother you?
>
> Regards,
>
> Ben.
>
>