我正在尝试实现一个新的数据库模式模板来高效地存储/检索“公园”的时间序列数据。
一个公园有多个设备(和子设备),每个设备都有自己的信号。在公园里可以有2-5公里的设备,在某些情况下甚至更多。通常,信号的时间分辨率对于公园是相同的,通常为5-10-15分钟或1小时。
因为每个公园都可以有不同数量的设备,每个设备都有不同数量的数据信号和不同的时间分辨率,所以我必须创建一个在所有情况下都能正常工作的数据库模板。
在我们的系统中,有一个api,它经常读取最近的数据(最多是上周的数据),而偶尔读取历史数据(当最终用户通过接口请求时)。这同样适用于聚合新数据的后端进程,例如从5分钟到1小时的分辨率等,并且仅在手动请求时才对历史数据执行此操作。历史数据也将被用来做一些离线分析公园专用软件。
重要的是能够迁移数据库/表并在出现问题时快速恢复它们。
我有两个选择:
使用基于日期的mysql分区。
有一个“当前数据”表,其中存储所有信号数据,以便快速访问,然后定期将“旧”数据移到日、月、年表(块)中。这些块可以是自适应的,以便所有表都具有相同的大小(就所使用的磁盘空间而言)。这是因为某些设备或整个园区可能会离线一段时间,并且会出现数据漏洞。
您是否有其他更符合目的的想法,并强调不同方法的优缺点?
以下是有关如何存储设备的一些信息:
CREATE TABLE `Device` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`devicetype_id` smallint(5) unsigned NOT NULL,
`parent_id` smallint(5) unsigned DEFAULT NULL,
`name` varchar(50) NOT NULL,
`displayname` varchar(30) DEFAULT NULL,
`status` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `dev_par` (`name`,`parent_id`)
) ENGINE=InnoDB
以及如何存储数据:
CREATE TABLE `Data_raw` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`device_id` smallint(5) unsigned NOT NULL,
`datetime` datetime NOT NULL COMMENT '[UTC] beginning of timestep',
`value` float NOT NULL,
`signal_id` smallint(5) NOT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
其他信息:
公园数量:150个,很快将达到500个左右。每个公园将有一个模式。
一个公园每年平均有8000万行数据。历史最长可以达到20年,但按平均值我们已经/将达到5年。考虑到rick估计的50b/行,我们将达到每年约5gb,因此历史上约50gb。考虑到aws aurora mysql的容量可以达到64tb,所有的公园都可以放在数据库中。在未来最坏的情况下,我们可以将一个客户机的parks拆分为不同的数据库碎片,这样就不成问题了。
对于数据库,我们将在aws上使用mysql aurora,其中一个主数据库当前将有16gb的ram和4个vcpu(我们还可以增加,针对后台进程/数据插入进行了优化),并且将有一个该数据库的读取副本,具有针对api方面的操作优化的不同硬件规范。
所有历史记录都必须存储并可用(对于不太频繁的操作,如历史分析、数据下载等)。
还有哪些信息可以帮助您了解/确定更好的选择?谢谢你抽出时间。
2条答案
按热度按时间vql8enpb1#
你有没有想过用一个时间序列数据库来存储你的数据?
您建议的模式是通用类型(度量名称存储在
signal_id
列),并且每个time:value
在读写数据时具有相应的i/o负载。相比之下,对于axibase tsd(我的从属关系)这样的现代时间序列数据库,这个值小于2字节。这是压缩测试。请随意发布一些其他人建议的数据子集,以获得更具体的反馈。yjghlzjz2#
只有当您打算删除“旧”数据时,分区才有用。更多讨论:http://mysql.rjweb.org/doc.php/partitionmaint
您可能需要索引到原始数据中。和/或您可能需要摘要表。
如果确实是utc,请考虑使用
TIMESTAMP
; 这样可以避免时区混乱。如果不能在同一秒钟内获得两个读数,请将pk改为
(device_id, datetime)
扔掉无用的东西id
.“当前数据”表,其中存储所有信号数据以供快速访问——上述对pk的更改导致每个设备的“当前数据”被聚集在一起;不需要单独的table;分区带来的好处不足以依赖它来实现这样的功能。
“定期移动”--不值得编程。
“整个公园有一段时间处于离线状态”--很好。不,不会有任何意义的“洞”。
modified
似乎没用,浪费空间。使用innodb。
给我们一些数字。ram大小。行数。保留时间。公园数量。我在这方面有经验;我想“查一下数字”,看看是否还有其他问题需要指出。
更多
PRIMARY KEY(device_id, datetime)
--如果可能有DUP,请考虑使用INSERT ... ON DUPLICATE KEY UPDATE ...
插入新行到达时插入或替换。这只是一步。大表可以有索引。摘要表避免了对大表上大多数索引的需要。
摘要表有一个限制,即您需要确定“时间”的粒度。在业务应用程序中,“天”通常就足够了。对于传感器监测,“小时”可能更合适。目标是将10行或更多行的平均原始数据折叠到汇总表的一行中。
使用多个表作为划分数据的方法通常是错误的。它使代码复杂化,但不一定提供任何好处。
PARTITION BY RANGE(TO_DAYS(...))
更好(尽管仍有些笨拙)(注:TO_DAYS()
可以替换为日期计算,例如TIMESTAMP
进入时间的最高界限——如果你想解决这个小时。类似的10分钟等)调整大小
这是对数据量的正确分析吗?
500个公园
300m行/年/公园--150b行/年--插入5000行/秒
10年以上保留期——1.5t行
17字节/行(假设有开销的话为50)--75tb
7天“热”75/52/10=150gb
有些东西需要给予。
可能需要分片(多个服务器,每个服务器处理公园的一个子集)。
5000
INSERTs
/sec(如果在一台机器上)是可能的,但我们需要讨论如何做到这一点(我使用的经验法则是:“开箱即用,mysql可以每秒处理100次插入;更重要的是需要一些讨论”你需要多少钱(1周到10年)才能“在线”?
在上面的数字中,我已经排除了
id
(在INT
太小了,没有用),和modified
.要提高删除“旧”数据的效率,请执行以下操作:
PARTITION
如果只保持一周在线,则按天计算;如果保存10年(或以上),则按年份。可以使用
BY RANGE
但它在重新排列尺寸时有一个缺点:合并,比如说4个星期组成一个月,把table捆起来,而REORGANIZE
正在完成。