时间戳

首页 > 解决方案 > Vertica REFRESH_COLUMNS 失败并出现“Join inner did not fit in memory”错误

sql - Vertica REFRESH_COLUMNS 失败并出现“Join inner did not fit in memory”错误

问题描述

我有一个内部连接，用于实体用户、user_profiles 和 user_custom_profiles 之间的分析，这会导致一个包含大约 500 列的大实体，这些表之间的关系是 1 比 1。

然后，我在一个扁平表中转换了用户，其中使用SET USING从其他两个表中获取数据创建了大约 350 列。我没有使用DEFAULT，因为所有这些表每天都会更新，所以那些SET USING 列需要每天更新。users 表的 create 语句如下所示：

CREATE TABLE public.users
(
    user_id varchar(100) NOT NULL,
    tenant_id int NOT NULL,
    user_domain varchar(100) not null,
    import_file_id int DEFAULT NULL::int,
    target_id int DEFAULT NULL::int,
    customer_id varchar(100) DEFAULT NULL,
    loyalty_id varchar(100) DEFAULT NULL,+
    [...]
    -- columns from user_profiles table
    customer_base varchar(100) SET USING (
      select customer_base 
      from user_profiles 
      where users.tenant_id = user_profiles.tenant_id 
        and users.user_id = user_profiles.user_id 
        and users.user_domain = user_profiles.user_domain
    ),
    purchases int SET USING (
      select purchases 
      from user_profiles 
      where users.tenant_id = user_profiles.tenant_id 
      and users.user_id = user_profiles.user_id 
      and users.user_domain = user_profiles.user_domain
    ),
    customer_type INT SET USING (
      select customer_type 
      from user_profiles 
      where users.tenant_id = user_profiles.tenant_id 
      and users.user_id = user_profiles.user_id 
      and users.user_domain = user_profiles.user_domain
    ),
    [...]
    -- columns from user_custom_profiles table
    ucp_custom_11 VARCHAR(100) SET USING (
      select custom_11 
      from user_custom_profiles 
      where users.tenant_id = user_custom_profiles.tenant_id 
      and users.user_id = user_custom_profiles.user_id 
      and users.user_domain = user_custom_profiles.user_domain
    ),
    ucp_custom_12 VARCHAR(100) SET USING (
      select custom_12 from user_custom_profiles 
      where users.tenant_id = user_custom_profiles.tenant_id 
      and users.user_id = user_custom_profiles.user_id 
      and users.user_domain = user_custom_profiles.user_domain
    ),
    ucp_custom_13 VARCHAR(100) SET USING (
      select custom_13 from user_custom_profiles 
      where users.tenant_id = user_custom_profiles.tenant_id 
      and users.user_id = user_custom_profiles.user_id 
      and users.user_domain = user_custom_profiles.user_domain
    ),
    [...]
);

到目前为止一切正常，问题是当我尝试执行SELECT REFRESH_COLUMNS('users_7', '', 'REBUILD');更新所有列时，这个函数似乎需要大量内存并且它失败并出现以下错误：

SQL Error [3815] [53200]: [Vertica][VJDBC](3815) ROLLBACK: 
Join inner did not fit in memory [(public.users_super x public.user_custom_profiles) 
using previous join and subquery (PATH ID: 2)]

我已经测试了执行此操作，其中有几列要更新和工作。但我想做得更简单，我不知道 Vertica 在后台做什么，但似乎正在尝试将用户、user_profiles 和 user_custom_profiles 之间的连接结果加载到内存中。我已经为用户与 user_profiles 和 user_custom_profiles 之间的连接创建了预测。

真正让我不安的是，这些表没有太多数据，我使用了此处提供的查询：table-size来找出这些表的压缩大小，并且没有那么大。

我在具有 6 个内核和 60 Gb RAM 的单个节点中使用 Vertica CE 9.1。

有没有办法改进这个功能，所以不会使用那么多内存？

标签： sqlvertica

解决方案

您的连接列始终是：

user_id varchar(100) NOT NULL,
tenant_id int NOT NULL,
user_domain varchar(100) not null,

对于这种类型的连接，您必须期望所有连接列都必须实现。

我希望刷新 350 列中的每一列的哈希连接。尝试使用其中一个连接来解释 SELECT，并将其发布在这里...

即使 VARCHAR 可以包含从零字节到最大可能长度的任何内容，Vertica 也不会提前知道每个 VARCHAR 的长度。因此，它将使用要连接的每一行的最大可能长度为 350 个必要连接中的每一个分配一个哈希表。

那将是：350 个连接 *（user_id 为 100 字节 +tenant_id 为 8 字节 + user_domain 为 100 字节）* 220 万行。

如果我计算正确，那相当于 160.160 GB 的内存。这几乎是您的单节点必须提供的 3 倍。

我的建议：

  1. 尽可能避免使用包含数百列的表格。
  2. 如果您经常连接表（通常 350 个派生列就足够了），请重新设计您的模型以允许对整数进行等值连接。HASH(user_id,tenant_id,user_domain)要么使用（哈希冲突风险足够低）来获取代理整数键，要么如下所示为 3 个表中的每一个创建一个帮助表，然后将代理键放入 3 个表中。然后，您可以在 INTEGER 上使用 equi-join 进行连接。对于连接的 350 个哈希表中的每个条目，您将需要 8 个字节而不是 208 个字节。

这是您的辅助表的设计和填充：

CREATE TABLE helper(
  surrkey     IDENTITY
, user_id     VARCHAR(100) -- does it really have to be that big?
, tenant_id   INT
, user_domain VARCHAR(100)
)
ORDER BY user_id,tenant_id,user_domain,surrkey
SEGMENTED BY HASH(surrkey) ALL NODES;

INSERT /*+DIRECT */ INTO helper
SELECT DISTINCT user_id,tenant_id,user_domain FROM users;

简而言之：独立于 DBMS：永远不要 JOIN 或 GROUP BY 列，如果有任何方法可以避免的话，如果实现的话，会占用相当多的十几个字节。在 Vertica 扁平表上下文中，在您的情况下，是 350 次。

推荐阅读