I have processes to retrieve CPU, memory, disk usage from a couple of machines and store data into MySQL database. I would like to draw them in line charts, like CPU usage vs. date or memory usage vs. date.
After a couple of months, my database has millions of data. When selecting data for a periods of time for charting, it spent plenty of time. (DB normalization has been applied.)
Are there any known statistic methods to consolidate data to minute or hour before saving to database?
Or what keywords should I search in Internet for consolidating these kinds of monitoring data?
Thanks.
It is a better to have full data for storage and consolidated data for building graphs and etc. You can use triggers, cron jobs, materialized views and other kinds of scripts to create a separate set of consolidated data.
Another kind of solution is to create temporary set of unconsolidated data and then to compress it to what you need in you main data table.
About statistical methods - you should apply common sense. Sometimes you need just AVG value, sometimes MAX or MIN (I think that you'd like to see MAX CPU usage, MIN disk space left, MAX RAM usage and so on).