Ygthkb

Let's say that I need to implement a widget on the site that allows the user to view the processor load stats via a chart. The user will be allowed to choose how detailed the statistics are: with minute precision, day, month and year precision. The data for processor load is supplied each second.

How should I design the table that holds the processor load data so that I could efficiently query the database for the statistics (or should I, for example, use separate tables for each statistic)?

The target DBMS is MySQL.

One idea would be to create one table and partition it by day of the week ( or some other criteria).
The data older than a week gets moved to a history table, ensuring that the "live" table doesn't get too big.

I think that having different tables for each statistic might not be a good thing since you would need to join them each time.

When partitioning, keep in mind that the maximum number of partitions is 1024
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html#partitioning-limitations-max-partitions

The data should be partitioned into separate tables the time frames that you mentioned, minute, day, month, year, etc. The goal is to reduce processing on either server (mysql or web) or client (in js for example). You want to grab the data quickly and display quickly, right?

This requires either to handle the insertion of the metric for each table at the time the metric is observed or more likely to insert into the minute table only and then aggregate the data into the other tables on a schedule. MapReduce tools can help with the aggregation of data.

The problem in doing this in a relational persistence model (mysql) is that this data is very key/value oriented (http://en.wikipedia.org/wiki/NoSQL#Key-value_stores). You can of course do what you're talking about in mysql and I'm assuming you're going to have a lookup table or a host_id column to relate the metrics to the servers you're monitoring. One huge table with metrics (load) and then a table for the hosts you're monitoring with a foreign key (host_id) to constrain and join the two.

The alternative solution if you feel that maybe this is geared towards a key/value persistence model is to utilize something like redis or dynamodb (in AWS) where you create a set of tables (min, hour, day, etc) for each monitored host and use the timestamp for the key (the value being the load). You can also simply prepend the host_id to the key if you don't want to create sets of tables for each unique host. Btw there are downsides of each. The first thing I can think of is that unless you use a hashing technique you're going to be pounding the same server for a specific host. In AWS land for example your key retrieval method is a hash (of your key) which spreads the load across your table instances (actually the disks the data is stored on): http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/DataModel.html.

There are plenty of other key/value storage systems as well and mongodb is something you might research as well: http://en.wikipedia.org/wiki/MongoDB

MySQL is absolutely excessive for that purpose. There is another very useful software that intended exactly for storing various stats - RRDtool

RRDtool is not a RDBMS, it hasn't indexes, queries and so on. There is only the circular table (only one per DB) of predefined capacity that overwritten from the head row by row when filled to the end.
RRDtool store not exact readings but rather averaged values with some granularity. For each table you can define different granularities and incoming data will be processed automatically. Then you can refer to the data in terms of timestamps [begin-end] and RRDtool automatically returns you the dataset with appropriate granularity.

RRDtool have various interfaces that returns data as CSV, XML and even pretty cool plots.

If you want to store loads, RRDtool is the tool of choice.