Database design for monitoring tool

[Ruben Werk]

Dear all,

I'm working on a mysql database to provide data to a monitoring tool. The columns are not so complex, but since it has to contain lots of data, I"m looking for an optimized implementation.

Since I'm not really familiar with database design, I would like some feedback on my design

The data comes from daily logfiles that contain:
1. Server (4 different servers)
2. Function (120 different servers)
3. Date
4. Time (every 5 minutes data is written away)

And the actual data:
5. Function calls
6. Mean access time

This data is then used to create graphs, which mostly contain the meantime or number of calls of few functions during the current day. However, it is also possible that the current day is compared to the average Monday of the last 2 years. Or that the current month is compared to the average of the last 5 February's

Inserts don't have to be very fast. Every 5 minutes new data gets available. Select queries don't have to be very fast either, although it would be nice if the queries finish within a couple of seconds.

One important issue is that the select query contains a function. For example SELECT SUM(calls) where dateTime BETWEEN x AND y AND function = z GROUP BY YEAR(dateTime),MONTH(dateTime), DAY(dateTime), HOUR(dateTime)

Now I'm not sure what direction I should go.

A table for each server_function_year_month combination (=4*120*12*nrYears) tables, which might be way to many?

Or 1 table with server_function_year_month_day primary keys?
Also, I should probably have some tables with aggregation data for the cases where I'd like to plot several months.

Thanks so much for your help!