实时数据仓库用于Web访问日志

Question

我们正在考虑建立一个数据仓库系统，用于存储我们Web服务器生成的网络访问日志。我们想要实时加载数据。

我们希望向用户呈现数据的折线图，并允许用户按维度进行钻取。

问题是如何平衡和设计系统，以便：

（1）数据可以在实时（＜2秒）内提取并呈现给用户，

（2）数据可以按小时和天聚合，并且

（3）大量数据仍然可以存储在仓库中，

我们当前的数据速率大约为每秒约10次访问，这使我们每天得到大约800K行的信息。我的MySQL和简单星型模式的简单测试表明，当我们拥有超过8百万行时，我的查询开始花费超过2秒钟的时间。

是否可能从这样一个“简单”的数据仓库中获得实时查询性能，同时仍然存储大量数据（最好不要丢弃任何数据）？

有没有办法将数据聚合到更高分辨率的表格中？

我感觉这不是一个新问题（尽管我已经谷歌搜索了很多）。也许有人可以指出像Splunk这样的数据仓库解决方案？

也许我期望太高了。

更新：

我的架构看起来像这样：

- 维度：

- 客户端（IP地址） - 服务器 - URL

- 事实：

- 时间戳（以秒为单位） - 传输的字节数

- jrydberg

---

非常有趣的问题。这是一种高雅的说法，意思是我不知道，但我也想学习一下... - Roland Bouman

Answer 1

Seth的回答非常合理，我相信如果你投资适当的知识和硬件，它有很高的成功几率。

Mozilla进行了大量的Web服务分析。我们按小时跟踪细节，并使用商业DB产品Vertica。它对这种方法非常有效，但由于它是一种专有商业产品，因此具有不同的相关成本。

您可能还想调查的另一种技术是MongoDB。它是一个文档存储数据库，具有一些功能，使其可能非常适合此用例。

即，capped collections（搜索mongodb capped collections以获取更多信息）

以及快速递增操作，用于跟踪页面浏览量、点击量等。 http://blog.mongodb.org/post/171353301/using-mongodb-for-real-time-analytics

- user241295

---

谢谢，我已经看了一下MongoDB来存储我的非关系数据。也许它也适合其他东西。每个事实都有一个唯一的维度，比如客户端IP地址，这是不是一个不好的做法？在我看来，这使得将数据聚合到较低分辨率表中变得不可能。或者我错过了什么？ - jrydberg

如果您只想存储客户端IP地址，那么您可以将其存储为退化维度。由于高基数，它仍然会很丑陋，但是这是可行的。如果可能的话，您应该避免将其作为单独的维度，因为连接两个极高基数的表对性能非常不利。 - user241295

Answer 2

听起来不像是个问题。MySQL非常快。

对于存储日志数据，使用MyISAM表格--它们更快，非常适合Web服务器日志。(我认为InnoDB是新安装的默认值 - 外键和InnoDB的所有其他功能对于日志表格并不必要)。您还可以考虑使用merge表格 - 您可以将单个表格保持在可管理的大小，同时仍然能够将它们全部作为一个大表格访问。

如果您仍然无法跟上，则按以下顺序获取更多内存，更快的磁盘，RAID或更快的系统。

另外：永远不要丢弃数据可能是个坏主意。如果每行大约200字节长，则仅用于原始日志数据的最小值为50 GB每年。如果有索引，请乘以至少2。备份再次乘以（至少）2。

如果你想的话，可以保留所有数据，但我认为你应该考虑将原始数据存储几周，聚合数据存储几年。对于任何更早的数据，只需存储报告即可。（也就是说，除非你有法律要求保留数据。即使如此，也可能不会超过3-4年）。

- Seth

---

谢谢回答。我会再深入研究一下MySQL。我们的想法是使用星型模式，其中日志行的时间戳被放置在事实表中。这样可以保持每个日志条目数据的低延迟，但如何聚合这种数据呢？客户端很可能永远不会再次请求相同的实体，因此表中永远不会存在相同的（客户端、资产、服务器）行。 - jrydberg

为了收集数据，我会创建一个带有许多列的单个表格 - 如果你做任何比这更复杂的事情，你就必须花时间打开其他表格并在服务器负载下进行计算。由于您已经表示您正在遇到麻烦，难以跟上，您可能希望尽可能简化。如果您设置从服务器来进行规范化和聚合（将报告与日志记录分离），那么您甚至可以进一步减少主服务器的负载。 - Seth

Answer 3

此外，要考虑分区，特别是如果您的查询大多数访问最新数据；例如，您可以设置每周约5.5M行的分区。

如果按天和小时聚合，请考虑使用日期和时间维度--您没有列出它们，所以我假设您没有使用它们。这样做的想法是不在查询中使用任何函数，如HOUR(myTimestamp)或DATE(myTimestamp)。日期维度应该与事实表以相同的方式进行分区。

有了这个设置，查询优化器可以使用分区修剪，因此表的总大小不再像以前那样影响查询响应。

- Damir Sudarevic

---

我理解你的意思是查询中不应该使用任何函数？它们会对性能产生如此大的影响吗？与时间维度进行连接是否更快？ - jrydberg

是的，没错——请记住，每行数据都必须评估函数。当正确设置时，WHERE子句仅包含维度表字段、常量和= < > <= >= AND；如果您有一个函数，请在维度表中预先计算它。 - Damir Sudarevic

此外，为了让查询优化器使用分区剪枝，只有 = < > <= >= BETWEEN 才是允许的。当优化器使用分区剪枝时，只会扫描包含 WHERE 数据的分区，其他分区会被忽略 - 处理速度更快。 - Damir Sudarevic

Answer 4

这是一个相当常见的数据仓库应用程序。我已经运行了一个多年，支持每天2000-1亿行数据，数据库响应时间为0.1秒，网页服务器响应时间超过1秒。这甚至不是在一台巨大的服务器上。

你的数据量并不太大，因此我认为你不需要非常昂贵的硬件。但我仍然建议选择多核、64位和大量内存。

但你会想要主要汇总数据而不是详细数据——特别是对于跨越几天、几个月等时间范围的时间序列图形。聚合数据可以通过异步进程定期创建到你的数据库中，或者在这种情况下，最好是你的ETL过程，也就是转换你的数据，创建聚合数据。请注意，聚合通常只是你的事实表的分组。

正如其他人所说，当访问详细数据时，分区是一个好主意。但是对于聚合数据，这个问题就不那么关键了。另外，依赖预先创建的维度值比依赖函数或存储过程更好。这两种策略都是典型的数据仓库策略。

关于数据库-如果是我，我会尝试Postgresql而不是MySQL。原因主要是优化器的成熟度：postgresql可以更好地处理您可能运行的查询类型。MySQL更有可能在五向连接时混淆，运行子选择时从下往上走等。如果这个应用程序非常重要，那么我会考虑商业数据库，如db2、oracle、sql server。然后你会获得额外的功能，如查询并行处理、自动查询重写对聚合表、附加的优化器复杂程度等。

- KenFar