[Perfparse-devel] Using indexes with mysql

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

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