How to store a vector of time/value data in a database

Question

I'm gathering a vector of time/value pairs, in this case I/O latency versus time for a server. I'd like to store them in a MySQL database that is queryable by date, server and other metadata.

Examples:

• I want be able to query the I/O latencies from 1:00 PM to 3:00 PM for the server Atriedes on August 19th, 2007.
• I want to also be able to query the times that the I/O latency on server Harkonnen where the I/O latencies are above 40 ms.
• I want to find all the servers that had latencies above 100 ms on August 1st, 2007.

How should I structure my database to easily allow this?

Answer 1

serverName, IOLatency, transactionStart

where serverName is the name of the server IOLatency is the time the operation took transactionStart is the start time of the transaction.

You may also want to have the transactionEnd time instead of IOlatency, or to have both, depending on what kind of queries you want to optimize and storage considerations.

Answer 2

CREATE TABLE t_latency (
       id INT NOT NULL PRIMARY KEY,
       server_id INT NOT NULL,
       ts DATETIME NOT NULL,
       latency FLOAT NOT NULL,
       KEY ix_latency_server_time_latency (server_id, ts, latency),
       KEY ix_latency_server_latency (server_id, latency),
       KEY ix_latency_time (ts)
       )

I want be able to query the I/O latencies from 1:00 PM to 3:00 PM for the server Atriedes on August 19th, 2007

SELECT  *
FROM    t_latency
WHERE   server_id = @id_of_atriedes
        AND ts BETWEEN '2007-08-19 01:00' AND '2007-08-19 03:00'
--  will use ix_latency_server_time_latency

I want to also be able to query the times that the I/O latency on server Harkonnen where the I/O latencies are above 40 ms.

SELECT  *
FROM    t_latency
WHERE   server_id = @id_of_harkonnen
        AND latency > .04
--  will use ix_latency_server_latency

I want to find all the servers that had latencies above 100 ms on August 1st, 2007.

SELECT  DISTINCT server
FROM    t_latency
WHERE   ts >= '2007-08-01 00:00'
        AND ts < '2007-08-02 00:00'
        AND latency > 0.1
--  will use ix_latency_time

Answer 3

While I understand you're specifying MySQL, if this is a new development project and there's flexibility on this requirement you might consider RRDTool. This is a simple to use database (with many packages that provide integration with it) for time series data, specifically for sysadmin tasks like you describe.

Answer 4

One row per measurement that has something like these columns:

create table measurements (
  id int primary key,
  servername varchar(64) not null,
  tstamp timestamp not null,
  latency int not null
);