YMatrix SQL 查询常见问题

本文档介绍 YMatrix SQL 查询的常见问题。

1 ERROR: EvalPlanQual can not handle subPlan with Motion node


问题分析

当参数 gp_enable_global_deadlock_detector 设置为 on 后,锁的模式可能下降为 RowExclusiveLock,当执行更新分布键时,UPDATE操作会被拆分为 DELETE + INSERT, 此时并发的更新分布键,DELETE 操作不能执行 EvalPlanQual,INSERT 操作也不会 blocked,可能会生成多余的数据,为了防止这种现象,抛出这类错误。

复现步骤

session 0: create table test_valplanqual (c1 int, c2 int) distributed by(c1);
CREATE
session 0: insert into test_valplanqual values(1,1);
INSERT 1
session 0: select * from test_valplanqual;
 c1 | c2
----+----
 1  | 1
(1 row)

session 1: begin;
BEGIN
session 2: begin;
BEGIN
session 1: update test_valplanqual set c1 = c1 + 1 where c1 = 1;
UPDATE 1
session 2: update test_valplanqual set c1 = c1 + 1 where c1 = 1;  <waiting ...>
session 1: end;
END
session 2<:  <... completed>
ERROR:  EvalPlanQual can not handle subPlan with Motion node  (seg1 127.0.1.1:7003 pid=34629)
session 2: end;
END
session 0: select * from test_valplanqual;
 c1 | c2
----+----
 2  | 1
(1 row)
session 0: drop table test_valplanqual;
DROP

解决方案

避免更新分布键。

2 ERROR: Too many unresolved insertion xids, please do a vacuum


问题分析

当前 mars2 存储引擎控制一个 BLOCK 上存在的 INSERT XID 数量,算法如下:

#define INSERTXIDSPERBLOCK      ((BLCKSZ - PAGE_RESERVED_SPACE) / sizeof(TransactionId))
#define PAGE_RESERVED_SPACE(MAXALIGN(sizeof(PageHeaderData)) + MAXALIGN(sizeof(SortHeapPageOpaqueData)))

大概约 (block_size - 24 + 24) / 4 个子事务,默认 block_size 为 8K 时,约为 2036,当子事务数超过该值时,会出现该错。

复现步骤

session 0: create table test_xids(id int) using mars2;
CREATE TABLE
session 0: create index ON test_xids using mars2_btree (id);
CREATE INDEX
session 0: 
 DO $$
DECLARE 
    i int; 
    _start timestamptz;
BEGIN
FOR i IN 1..3000 LOOP 
    _start = clock_timestamp();
    INSERT INTO test_xids VALUES (i);
    RAISE NOTICE 'value: % escape: % ', i, clock_timestamp() - _start; 
END LOOP;
END;
$$language plpgsql;

psql: NOTICE:  value: 1 escape: 00:00:00.019476
psql: NOTICE:  value: 2 escape: 00:00:00.002501
psql: NOTICE:  value: 3 escape: 00:00:00.00218
psql: NOTICE:  value: 4 escape: 00:00:00.002098
psql: NOTICE:  value: 5 escape: 00:00:00.002938
psql: NOTICE:  value: 6 escape: 00:00:00.001891
psql: NOTICE:  value: 7 escape: 00:00:00.001794
...
...
psql: NOTICE: value: 2069 escape: 00:00:10.001794
psql: NOTICE: value: 2070 escape: 00:00:10.001495
psql: NOTICE: value: 2071 escape: 00:00:10.001251
psql: NOTICE: ERROR:  Too many unresolved insertion xids, please do a vacuum (sortheap_external_sort.c:404)  (seg0 172.16.100.197:6000 pid=12992) (sortheap_external_sort.c:404)
CONTEXT:  SQL statement "insert into test_xids values(i)"
PL/pgSQL function inline_code_block line 7 at SQL statement

解决方案

避免在一个事务中多次执行 INSERT INTO tablename VALUES(...) 操作,最友好的做法是,每一个事务只执行一次 INSERT 操作,并且使用 INSERT INTO tablename VALUES(...),(...),(...) 的形式批量插入据。 对于 mars2 表,官方更加推荐使用 mxgate工具 更高效的写入数据。

3 ERROR: modification of distribution columns in OnConflictUpdate is not supported


问题分析

当参数 gp_enable_global_deadlock_detector 设置为 on 后,锁的模式可能下降为 RowExclusiveLock,当执行更新分布键时,INSERT...ON CONFLICT DO UPDATE SET... 语句在执行 UPDATE 操作会被拆分为 DELETE + INSERT, 此时更新分布键,抛出这类错误。

复现步骤

session 0: create table test_upsert(id int, name text, primary key(id)) distributed by(id);
CREATE TABLE
session 0: insert into test_upsert select 1, 'a';
INSERT 0 1
session 0: insert into test_upsert select 1, 'a' on conflict (id) do update set id =2;
psql: ERROR:  modification of distribution columns in OnConflictUpdate is not supported
session 0: insert into test_upsert select 1, 'a' on conflict (id) do update set name = 2;
INSERT 0 1
session 0: drop table test_upsert;
DROP

解决方案

执行 UPSERT 时,避免更新分布键。

4 UI 客户端收到 log:server closed the connection unexpectedly


UI 客户端访问远程数据库,长查询过程中或长时间 idle 后发查询,客户端有些时候会收到 log:

server closed the connection unexpectedly

问题分析

客户端存在查询超时 cancel 设置,或 idle 超时 cancel 连接设置。

解决方案

更改客户端超时设置,取消超时。

5 PARTITION 表简单 Filter 操作的 UNION ALL 查询比 IN 查询慢


问题分析

PARTITION 表的 IN 查询,分区裁剪后只有 1 个 DEFAULT 分区,但 UNION ALL 查询中每个子查询都裁剪到了 DEFAULT 分区,做了多次 DEFAULT 分区的扫描,性能影响明显。

解决方案

对于 PARTITION 表: 尽量避免 DEFAULT 分区、尽量不用 UNION 而用 IN 子句。

6 插入 int 类型数据,单独 SELECT 查询跑很快,放到 plpgsql function 里很慢


问题分析

plpgsql function 内的查询是通过 SPI 运行,SPI Plan 输出结果里是两表 Join,采用了 nestloop,语句 rows = 1,没有 ANALYSE。

解决方案

执行 ANALYZE。

7 PARTITION 分区裁剪更新操作,两个会话(Session)独立更新会导致互锁


问题分析

分布式死锁。

解决方案

打开分布式死锁检测。

gpconfig -c gp_enable_global_deadlock_detector -v on

8 自定义 type 使用及单表字段扩展


自定义type使用

  1. 创建type
    CREATE TYPE public.fhpm AS
    (
    avgval double precision,
    minval double precision,
    maxval double precision,
    minval_interval integer,
    maxval_interval integer
    );
  2. 创建表
    CREATE TABLE datapool.test                
    (                                         
     portindex integer,                    
     begintime timestamp without time zone,
     a_1 fhpm,                             
     s_2 integer                           
    );               
  3. 插入数据样例
    insert into datapool.test values(1,'2022-01-01','(1,1,1,1,1)',1);          
    insert into datapool.test values(2,'2022-01-01','(2,,2,,)',2);   
  4. type类型属性数据查询样例
    
    SELECT * FROM datapool.test;                             
    portindex |      begintime      |     a_1     | s_2 
    -----------+---------------------+-------------+-----
          1 | 2022-01-01 00:00:00 | (1,1,1,1,1) |   1 
          2 | 2022-01-01 00:00:00 | (2,,2,,)    |   2 
    (2 rows)        

select (a_1).avgval from datapool.test ; avgval

  1                                                                      
  2              

(2 rows)

select (a_1).minval from datapool.test ;
minval

  1                                 

(2 rows)

**psql: ERROR:  tables can have at most 1600 columns**

使用自定义 type 可以实现单表突破1600字段的限制。有兴趣的可以自己尝试一下,以下是实现样例。

CREATE TYPE public.fhpm1 AS ( a0001 double precision, a0002 double precision, ... a1600 double precision);

CREATE TABLE datapool.test1
( portindex integer, begintime timestamp without time zone, a_1 fhpm1, s_2 integer, a_3 fhpm2 );


生成1600列 type 脚本

echo 'CREATE TYPE public.fhpm1 AS(' > test.sql for i in {1..1599};do echo 'a'$i' double precision,' >> test.sql done echo 'a1600 double precision);' >> test.sql