Re: Database design for monitoring tool
Hey Rick,
Thanks so much for your extensive reply.
Rick James Wrote:
-------------------------------------------------------
> How many rows come in every 5 minutes? Sounds
> like 1??
1 for each function/server. There are around 120-140 functions and 4 servers.
>
> Have columns for COUNTs, SUMs, etc. Compute
> average() via SUM(sum)/SUM(ct).
>
> If it adds up to more than a million rows,
> consider PARTITIONing the data on months
If I make a table for each function/year, the table will have around 430k rows, and 120 tables a year.
If I make a table for each month/year only, I will have 4.3 million rows and 12 tables a year.
> (PARTITION by RANGE, use TO_DAYS()). That will
> make purging old data easier. (Don't worry about
> setting up PARTITIONing until you have, say, a
> month's worth of data.)
Currently I have log files from november 2011 until now. I'm not familiar with partitioning, but I'll look into it.
> > (=4*120*12*nrYears) tables
> NO!
would you consider 120 tables a year also too much?
> > the average Monday of the last 2 years
> Do NOT make the mistake of having an index on
> day-of-week -- the query planner won't use it. It
> will be cheap enough just to scan all the data and
> filter out 6/7 of the data.
My first database was just one big table with all the data in it and no indexing. I'm not sure how indexing can help me as they have to be unique and I'll need multiple rows.
I was thinking of concatenating fields like
serverID_functionID_date_time into the primary key. But I'm not sure this will work properly. I do know that, when having many rows, the primary key should be small in size since it is loaded into memory.
> As the log files come in, I assume you have some
> code (Perl?) to process them and do the INSERTs.
> Suggest you save the logs for the first few
> days/weeks -- you are likely to find something you
> forgot, say "ah, s***", then need to start over,
> reprocessing all the data.
>
> You may find that there is no need for purging
> data after 2 years.
>
Yeah, I'm reading the logfiles with java. And i'll keep the logfiles anyway in addition to the database.
Again, thanks so much!