Re: [Perfparse-devel] Using indexes with mysql

[Ben C. <Be...@cl...>]

Viljo,

I can see exactly what you mean, and I understand your point.  There are 
in fact four indexes not three, which is I will be the first to admit, a 
bit of an overkill. :)

The keys are there for a reason, I'll explain why I put them in:

Key 1, (id), Primary Key.

   As well as the PK, used to reference data from 
perfparse_service_metric.last_perfdata_bin.

Key 2, (host, service)

   Referential integrity (RI) to perfdata_service.

Key 3, (host, service, metric)

   RI to perfdata_service_metric.

Key 4, (host, service, metric, time)

   Used when plotting graph.  To get data between a range and in correct 
ordering.

First off, Key 2 does not serve any reason for being.  RI is enforced by 
Key 3.  This is redundant and I'll cut that for new users from next 
version.  Thanks :)

---------------------------------------

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.


Viljo Marrandi wrote:

> Hello,
> 
> Just a question about mysql database schema.
> 
> Here I'm monitoring about 280 services and perfparse if logging them 
> all. And mysql db grows with speed 1GB per week and after two weeks my 
> perfdata_service_bin table had some 6 million rows.
> 
> The problem is, that searches were very-very slow. Not for graph drawing 
> but for other things like finding last 200 results for hosts etc, which 
> required sorting and searching.
> 
> As I see perfdata_service_bin table has 3 indexes and they are in same 
> order, just that one contains 2, second 3 and third 4 fields which are 
> indexed.
> 
> With these 3 indexes mysql data file was 351MB and index file was 757MB. 
>  And doing search puts my server swapping heavily (server has 512MB 
> RAM), because index doesn't fit to RAM.
> 
> After removing 2 indexes with 2 and 3 keys mysql index file was 374MB 
> and same query took half of time with 30k more rows.
> 
> We thought here that either this is something really-really smart that 
> we don't get, like speeds up some specific queries or was this some kind 
> weird assumption about index count equals more speed, because if you 
> index host_name, service_description, metric, ctime in one index then 
> you really don't need to do that again with 3 fields, there's no use.
> 
> Viljo
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Sybase ASE Linux Express Edition - download now for FREE
> LinuxWorld Reader's Choice Award Winner for best database on Linux.
> http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click
> _______________________________________________
> Perfparse-devel mailing list
> Per...@li...
> https://lists.sourceforge.net/lists/listinfo/perfparse-devel
>