存储时间序列数据，关系还是非？ [英] Storing time-series data, relational or non?
问题描述
我创建了一个系统，它可以使用SNMP（可能）以5分钟为间隔，轮询设备的不同指标（如CPU利用率，磁盘利用率，温度等）的数据。最终目标是以时间序列图的形式向系统用户提供可视化。
I am creating a system which polls devices for data on varying metrics such as CPU utilisation, disk utilisation, temperature etc. at (probably) 5 minute intervals using SNMP. The ultimate goal is to provide visualisations to a user of the system in the form of time-series graphs.
我过去使用RRDTool查看过，但拒绝了因为无限期地存储捕获的数据对我的项目很重要，我想要更高级别和更灵活地访问捕获的数据。所以我的问题是：
I have looked at using RRDTool in the past, but rejected it as storing the captured data indefinitely is important to my project, and I want higher level and more flexible access to the captured data. So my question is really:
更好的是，关系数据库（如MySQL或PostgreSQL）或非关系数据库或NoSQL数据库
What is better, a relational database (such as MySQL or PostgreSQL) or a non-relational or NoSQL database (such as MongoDB or Redis) with regard to performance when querying data for graphing.
给定一个关系数据库，我将使用一个 data_instances 表，其中将存储为所有设备测量的每个度量捕获的每个数据实例，其中包含以下字段：
Given a relational database, I would use a data_instances table, in which would be stored every instance of data captured for every metric being measured for all devices, with the following fields:
字段： id fk_to_device fk_to_metric metric_value timestamp
我想为特定设备上的特定指标绘制图表，我必须查询此单一表格，过滤掉其他设备，以及正在为此设备分析的其他指标：
When I want to draw a graph for a particular metric on a particular device, I must query this singular table filtering out the other devices, and the other metrics being analysed for this device:
SELECT metric_value, timestamp FROM data_instances
WHERE fk_to_device=1 AND fk_to_metric=2
此表中的行数为：
d * m_d * f * t
其中 d 是设备数， m_d 是所有设备记录的累计指标数， f 是轮询数据的频率， t 是时间
where d is the number of devices, m_d is the accumulative number of metrics being recorded for all devices, f is the frequency at which data is polled for and t is the total amount of time the system has been collecting data.
对于一年内每5分钟为3台设备记录10个指标的用户，我们就会在 5百万条记录。
For a user recording 10 metrics for 3 devices every 5 minutes for a year, we would have just under 5 million records.
没有索引 fk_to_device code> fk_to_metric 扫描此不断扩展的表会花费太多时间。因此，索引上述字段以及 timestamp （用于创建具有本地化时段的图）是必需的。
Without indexes on fk_to_device and fk_to_metric scanning this continuously expanding table would take too much time. So indexing the aforementioned fields and also timestamp (for creating graphs with localised periods) is a requirement.
MongoDB有一个集合的概念，与表格不同，有了这些，我可以分配每个设备的数据存储，或甚至每个设备记录的每个指标。
MongoDB has the concept of a collection, unlike tables these can be created programmatically without setup. With these I could partition the storage of data for each device, or even each metric recorded for each device.
我没有NoSQL的经验，不知道他们提供任何查询性能增强功能（例如索引），然而上一段建议在NoSQL下将数据存储在结构中的大多数传统关系查询工作。
I have no experience with NoSQL and do not know if they provide any query performance enhancing features such as indexing, however the previous paragraph proposes doing most of the traditional relational query work in the structure by which the data is stored under NoSQL.
具有正确索引的关系解决方案会在一年内是否会被抓取？或者NoSQL的基于集合的结构（符合我的存储数据的心理模型）是否提供了显着的效益？
Would a relational solution with correct indexing reduce to a crawl within the year? Or does the collection based structure of NoSQL approaches (which matches my mental model of the stored data) provide a noticeable benefit?
推荐答案
绝对关系。无限的灵活性和扩展。
Definitely Relational. Unlimited flexibility and expansion.
在概念和应用方面有两个更正，后跟一个标高。
Two corrections, both in concept and application, followed by an elevation.
-
它不是过滤掉不需要的数据; 只选择所需的数据。是的，当然，如果你有一个索引来支持在WHERE子句中识别的列，它是非常快的，并且查询不依赖于表的大小（从160亿行表中抓取1000行是即时的）
It is not "filtering out the un-needed data"; it is selecting only the needed data. Yes, of course, if you have an Index to support the columns identified in the WHERE clause, it is very fast, and the query does not depend on the size of the table (grabbing 1,000 rows from a 16 billion row table is instantaneous).
你的桌子有一个严重的障碍。给定您的描述，实际的PK是（设备，度量，日期时间）。 （请不要称为TimeStamp，这意味着其他，但这是一个小问题。） Id 列是完全和完全冗余的。 行的唯一性标识为：
Your table has one serious impediment. Given your description, the actual PK is (Device, Metric, DateTime). (Please don't call it TimeStamp, that means something else, but that is a minor issue.) The Id column is totally and completely redundant. The uniqueness of the row is identified by:
`(Device, Metric, DateTime)`
Id 列什么也不做， （不冗余）。支持 Id 列的附加索引明显阻碍了INSERT的速度，并增加了使用的磁盘空间，可以摆脱它。
The Id column does nothing, it is superfluous (not redundant). The additional Index to support the Id column obviously impedes the speed of INSERTs, and adds to the disk space used, you can get rid of it.
现在你已经移除障碍物，你可能没有认出它，但你的表格是第六普通形式。非常高的速度，只有一个索引PK。要了解相关信息，请阅读 此答案 。
Now that you have removed the impediment, you may not have recognised it, but your table is in Sixth Normal Form. Very high speed, with just one Index on the PK. For understanding, read this answer from the What is Sixth Normal Form ? heading onwards.
- 一个索引，而不是三个;在非SQL上，你可能需要三个索引）。
我有完全相同的表格（没有 Id ）。我有一个额外的列服务器。我支持多个客户远程。
I have the exact same table (without the Id key, of course). I have an additional column Server. I support multiple customers remotely.
`(Server, Device, Metric, DateTime)`
该表可以用于透视数据（即 Devices 在顶部和 Metrics ），使用完全相同的SQL代码（是，切换单元格）。我使用表来架设无限多种图表和图表，为客户提供他们的服务器性能。
The table can be used to Pivot the data (ie. Devices across the top and Metrics down the side, or pivoted) using exactly the same SQL code (yes, switch the cells). I use the table to erect an unlimited variety of graphs and charts for customers re their server performance.
-
监控统计数据模型 。
（对于内联过大，某些浏览器无法加载内嵌链接;点击链接。 ）
Monitor Statistics Data Model.
(Too large for inline; some browsers cannot load inline; click the link. Also that is the obsolete demo version, for obvious reasons, I cannot show you commercial product DM.)
它允许我生成 Charts Like This ，之后的六个键击使用单个SELECT命令从客户接收原始监视统计文件。注意混合搭配;操作系统和服务器在同一图表上;各种各样的透视。当然，stats矩阵的数量没有限制，因此图表。
It allows me to produce Charts Like This, six keystrokes after receiving a raw monitoring stats file from the customer, using a single SELECT command. Notice the mix-and-match; OS and server on the same chart; a variety of Pivots. Of course, there is no limit to the number of stats matrices, and thus the charts. (Used with the customer's kind permission.)
不熟悉关系数据库建模标准的读者可能会发现 IDEF1X记法 。
Readers who are unfamiliar with the Standard for Modelling Relational Databases may find the IDEF1X Notation helpful.
最后但并非最不重要的是，SQL是一个IEC / ISO / ANSI标准。免费软件实际上是非SQL;如果它们不提供标准，则使用术语SQL是欺骗性的。他们可能提供额外，但他们缺乏基础。
Last but not least, SQL is a IEC/ISO/ANSI Standard. The freeware is actually Non-SQL; it is fraudulent to use the term SQL if they do not provide the Standard. They may provide "extras", but they are absent the basics.
这篇关于存储时间序列数据，关系还是非？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
