database - 如何提高 MonetDB 单节点查询性能？

Question

我在 Windows 2012 Server 上安装了最新的(MonetDB 5 服务器 v11v.27.5“Jul2017-SP1”)，我正在尝试在 2-3 秒的合理时间内查询 14 亿行的大表。

MonetDB 甚至可以做到这一点吗？我可以做些什么来提高性能？

到目前为止我所做的详细描述:

1. 创建表:

   CREATE TABLE t939ba ( id INT, xa INT, xb INT, ya INT, yb INT, a1 TINYINT, a2 TINYINT, a3 TINYINT, a4 TINYINT, a5 TINYINT, a6 TINYINT, a7 TINYINT, a8 TINYINT, a9 TINYINT);
   

2. 加载数据:

   COPY 1450000000 OFFSET 2 RECORDS INTO tbl FROM 'D:\\es_export\\file.csv'
   USING DELIMITERS ',' NULL AS '' LOCKED;
   

3. 运行查询:

   SELECT COUNT(DISTINCT id) FROM tbl WHERE a1=22
   AND xb>=143455 AND yb>=90911 AND xa<=143615 AND ya<=91007
   AND a2 IN (2, 3, 4) AND a3 IN (0, 1, 2, 3, 4) AND a4 IN (0, 1, 2)
   AND a5 IN (-1, 1, 2, 3, 4, 5, 6, 7) AND a6 IN (-1, 11, 12, 13, 14);
   

当我第一次运行查询时花费了 (14m 52s)，第二次运行同一查询花费了 (3m 23s)，第三次连续运行相同的查询用了 (14s)，稍微重新排列的查询用了 (3m 11s)。

Answer 1

托马斯，

感谢您的计划和踪迹。我看到您将修改后的查询与范围谓词而不是 IN 谓词一起使用，并且该查询现在“仅仅”运行了 ~39 秒(与 ~15 分钟相比)---要么是因为范围谓词的评估效率高于IN 谓词，或者因为正如 Martin 所指出的，稍后运行的查询受益于 MonetDB 在评估第一个查询时自动构建的索引，或者因为两者。

在任何情况下，多次运行一个/每个查询(-version)是查看自动构建索引的可能效果的好主意。

此外，我看到您确实拥有一台 34 核机器，或者您的机器每个核心“只有”2 GB RAM --- 考虑到您有大约 42 GB 的数据集，其中每一列大小约为 1.5 GB 到 6 GB ...

因此，查询运行速度不超过 ~39 秒的主要原因可能是由于“缺乏”内存导致的 I/O 事件。

最好的，

斯特凡

附:
您可以检查对于这个特定的查询，减少(甚至避免)多核并行性是否有助于减少 I/O 抖动:
在使用

禁用 MonetDB 的“有丝 split ”优化器后尝试运行查询

set optimizer='no_mitosis_pipe';

您可以使用

重新启用完整的多核并行性

set optimzer='default_pipe';

最好的，
斯特凡