PostgreSQLsharding:citus系列2TPCH

作者

digoal

日期

2018-08-29

标签

PostgreSQL , citus , tpc-h

背景

紧接着上一篇文档，本文测试citus的tpc-h能力（包括兼容性）.

《PostgreSQL sharding : citus 系列1 - 多机部署（含OLTP(TPC-B)测试）》

《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》

https://github.com/digoal/gp_tpch

实际测试过程中，发现CITUS对TPC-H的SQL支持并不完整。

citus tpc-h 测试

1、下载gp_tpch包

git clone https://github.com/digoal/gp_tpch

2、生成200G测试数据

cd gp_tpch
ln -s `pwd` /tmp/dss-data
./dbgen -s 200

3、将数据转换为PG识别的格式

for i in `ls *.tbl`; do sed 's/|$//' $i > ${i/tbl/csv}; echo $i; done;

4、生成测试SQL

```

SF=200

mkdir dss/queries

for q in seq 1 22

do

DSS_QUERY=dss/templates ./qgen -s $SF $q > dss/queries/$q.sql

sed 's/^select/explain select/' dss/queries/$q.sql > dss/queries/$q.explain.sql

done

```

5、修改citus 的几个参数，确保在跑两类QUERY的时候不报错（末尾会提到报错原因）。

alter role postgres set citus.enable_repartition_joins =on;
alter role postgres set citus.max_intermediate_result_size =-1;

6、测试TPC-H(与coordinator同一台主机上测试)

./tpch.sh ./results 127.0.0.1 port tpch-db tpch-user password citus

tpc-h 性能

有些SQL不支持，显示0.

2018-08-29 19:24:30 [1535541870] : query 1 finished OK (12 seconds)
2018-08-29 19:24:30 [1535541870] : query 2 finished OK (0 seconds)
2018-08-29 19:25:48 [1535541948] : query 3 finished OK (77 seconds)
2018-08-29 19:25:50 [1535541950] : query 4 finished OK (2 seconds)
2018-08-29 19:29:45 [1535542185] : query 5 finished OK (234 seconds)
2018-08-29 19:29:47 [1535542187] : query 6 finished OK (1 seconds)
2018-08-29 19:37:33 [1535542653] : query 7 finished OK (465 seconds)
2018-08-29 19:44:30 [1535543070] : query 8 finished OK (415 seconds)
2018-08-29 19:58:29 [1535543909] : query 9 finished OK (837 seconds)
2018-08-29 20:00:26 [1535544026] : query 10 finished OK (116 seconds)
2018-08-29 20:00:26 [1535544026] : query 11 finished OK (0 seconds)
2018-08-29 20:00:32 [1535544032] : query 12 finished OK (6 seconds)
2018-08-29 20:00:33 [1535544033] : query 13 finished OK (0 seconds)
2018-08-29 20:01:40 [1535544100] : query 14 finished OK (67 seconds)
2018-08-29 20:05:33 [1535544333] : query 15 finished OK (232 seconds)
2018-08-29 20:05:34 [1535544334] : query 16 finished OK (0 seconds)
2018-08-29 20:05:34 [1535544334] : query 17 finished OK (0 seconds)
2018-08-29 20:05:34 [1535544334] : query 18 finished OK (0 seconds)
2018-08-29 20:06:51 [1535544411] : query 19 finished OK (76 seconds)
2018-08-29 20:06:51 [1535544411] : query 20 finished OK (0 seconds)
2018-08-29 20:06:52 [1535544412] : query 21 finished OK (0 seconds)
2018-08-29 20:06:52 [1535544412] : query 22 finished OK (0 seconds)
2018-08-29 20:06:52 [1535544412] : finished TPC-H benchmark

citus tpc-h SQL文件讲解

1、分片字段

select create_distributed_table('part','p_partkey');
select create_distributed_table('region','r_regionkey');
select create_distributed_table('nation','n_nationkey');
select create_distributed_table('supplier','s_suppkey');
select create_distributed_table('customer','c_custkey');
select create_distributed_table('partsupp','ps_suppkey');
select create_distributed_table('orders','o_orderkey');
select create_distributed_table('lineitem','l_orderkey');

2、colocate(默认情况下是同一个分组，不需要colocate)

```

SELECT mark_tables_colocated('part', ARRAY['region', 'nation', 'supplier', 'customer', 'partsupp', 'orders', 'lineitem']);

默认分组,在创建分片表时colocate默认为default,

只要表的shard数量、分片类型(append, or hash)、colocate组 都一致，则他们就是colocate的。

create_distributed_table

参数：colocate_with: (Optional) include current table in the co-location group of another table.
By default tables are co-located when they are distributed by columns of the same type,
have the same shard count, and have the same replication factor.
Possible values for colocate_with are default, none to start a new co-location group,
or the name of another table to co-locate with that table. (See Co-Locating Tables.)
```

3、索引

```

ALTER TABLE PART ADD constraint pk1 PRIMARY KEY (P_PARTKEY);

ALTER TABLE SUPPLIER ADD constraint pk2 PRIMARY KEY (S_SUPPKEY);

ALTER TABLE PARTSUPP ADD constraint pk3 PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY);

ALTER TABLE CUSTOMER ADD constraint pk4 PRIMARY KEY (C_CUSTKEY);

ALTER TABLE ORDERS ADD constraint pk5 PRIMARY KEY (O_ORDERKEY);

ALTER TABLE LINEITEM ADD constraint pk6 PRIMARY KEY (L_ORDERKEY, L_LINENUMBER);

ALTER TABLE NATION ADD constraint pk7 PRIMARY KEY (N_NATIONKEY);

ALTER TABLE REGION ADD constraint pk8 PRIMARY KEY (R_REGIONKEY);

CREATE INDEX idx_nation_regionkey ON public.nation USING btree (n_regionkey);

CREATE INDEX idx6 ON public.nation USING btree (n_nationkey, n_regionkey);

CREATE INDEX idx5 ON public.region USING btree (r_name, r_regionkey);

CREATE INDEX IDX_CUSTOMER_NATIONKEY ON CUSTOMER (C_NATIONKEY);

CREATE INDEX IDX_LINEITEM_PART_SUPP ON LINEITEM (L_PARTKEY,L_SUPPKEY);

CREATE INDEX idx_lineitem_shipdate ON public.lineitem USING btree (l_shipdate, l_discount, l_quantity);

CREATE INDEX idx_lineitem__2 ON public.lineitem USING btree (l_partkey);

CREATE INDEX idx_lineitem__3 ON public.lineitem USING btree (l_suppkey);

CREATE INDEX idx_lineitem__11 ON public.lineitem USING btree (l_shipdate);

CREATE INDEX idx_lineitem_orderkey ON public.lineitem USING btree (l_orderkey);

CREATE INDEX idx1 ON public.lineitem USING btree (l_orderkey) WHERE (l_commitdate

CREATE INDEX idx_orders__6 ON public.orders USING btree (o_orderpriority);

CREATE INDEX idx_orders_orderdate ON public.orders USING btree (o_orderdate);

CREATE INDEX idx_orders_custkey ON public.orders USING btree (o_custkey)

CREATE INDEX idx_part__5 ON public.part USING btree (p_type);

CREATE INDEX idx_part__6 ON public.part USING btree (p_size);

CREATE INDEX idx_part_1 ON public.part USING btree (p_container, p_brand);

CREATE INDEX idx_supplier_nation_key ON public.supplier USING btree (s_nationkey);

CREATE INDEX idx4 ON public.supplier USING btree (s_suppkey, s_nationkey);

CREATE INDEX idx ON public.partsupp USING btree (ps_partkey, ps_suppkey, ps_supplycost);

CREATE INDEX idx_partsupp_partkey ON public.partsupp USING btree (ps_partkey);

CREATE INDEX idx_partsupp_suppkey ON public.partsupp USING btree (ps_suppkey);

CREATE INDEX idx_partsupp__4 ON public.partsupp USING btree (ps_supplycost);

```

citus测试TPC-H遇到的兼容性报错与解决方法

错误1

ERROR: could not run distributed query with subquery outside the FROM and WHERE clauses
HINT: Consider using an equality filter on the distributed table's partition column.

报错SQL

select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_natiOnkey= n_nationkey
and n_name = 'SAUDI ARABIA'
group by
ps_partkey having
sum(ps_supplycost * ps_availqty) > (
select
sum(ps_supplycost * ps_availqty) * 0.0000005000
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_natiOnkey= n_nationkey
and n_name = 'SAUDI ARABIA'
)
order by
value desc
LIMIT 1;

改成

```

select

ps_partkey,

sum(ps_supplycost * ps_availqty) as value

from

partsupp,

supplier,

nation

where

ps_suppkey = s_suppkey

and s_natiOnkey= n_nationkey

and n_name = 'SAUDI ARABIA'

group by

ps_partkey having

sum(ps_supplycost * ps_availqty) >

( sum(ps_supplycost * ps_availqty) filter

(where

ps_suppkey = s_suppkey

and s_natiOnkey= n_nationkey

and n_name = 'SAUDI ARABIA'

)

) * 0.0000005000

order by

value desc

LIMIT 1;

```

错误2

ERROR: the query contains a join that requires repartitioning
HINT: Set citus.enable_repartition_joins to on to enable repartitioning

报错SQL

select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'AUTOMOBILE'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate and l_shipdate > date '1995-03-08'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
LIMIT 10;

有些SQL需要repartition join，需要设置如下参数

alter role postgres set citus.enable_repartition_joins =on;

错误3

ERROR: the intermediate result size exceeds citus.max_intermediate_result_size (currently 1048576 kB)
DETAIL: Citus restricts the size of intermediate results of complex subqueries and CTEs to avoid accidentally pulling large result sets into once place.
HINT: To run the current query, set citus.max_intermediate_result_size to a higher value or -1 to disable.

有些SQL的中间结果比较大，可以设置为无限制，重新测试

alter role postgres set citus.max_intermediate_result_size =-1;

错误4

ERROR: 0A000: complex joins are only supported when all distributed tables are joined on their distribution columns with equal operator

复杂JOIN，仅支持分布键作为JOIN的字段，并且只能使用等值JOIN。

```

postgres=# explain select

sum(l_extendedprice) / 7.0 as avg_yearly

from

lineitem join

part on (p_partkey = l_partkey and p_brand = 'Brand#13' and p_cOntainer= 'SM PKG') join

(SELECT l_partkey AS agg_partkey, 0.2 * avg(l_quantity) AS avg_quantity FROM lineitem GROUP BY l_partkey) part_agg

on (agg_partkey = l_partkey) limit 10;

ERROR: 0A000: complex joins are only supported when all distributed tables are joined on their distribution columns with equal operator

LOCATION: DeferErrorIfUnsupportedSubqueryPushdown, query_pushdown_planning.c:469

```

对应的代码如下

https://github.com/citusdata/citus/blob/d63cbf382288358533b71d907f6a3a7fc8a1df5f/src/backend/distributed/planner/query_pushdown_planning.c

else if (!RestrictionEquivalenceForPartitionKeys(plannerRestrictionContext))
{
return DeferredError(ERRCODE_FEATURE_NOT_SUPPORTED,
"complex joins are only supported when all distributed tables are "
"joined on their distribution columns with equal operator",
NULL, NULL);
}

错误5

ERROR: failed to execute task 18

报错SQL

```

select

ps_partkey,

sum(ps_supplycost * ps_availqty) as value

from

partsupp,

supplier,

nation

where

ps_suppkey = s_suppkey

and s_natiOnkey= n_nationkey

and n_name = 'SAUDI ARABIA'

group by

ps_partkey having

sum(ps_supplycost * ps_availqty) > ( sum(ps_supplycost * ps_availqty) filter (where

ps_suppkey = s_suppkey

and s_natiOnkey= n_nationkey

and n_name = 'SAUDI ARABIA') ) * 0.0000005000

order by

value desc

LIMIT 1;

```

错误6

ERROR: 0A000: cannot run outer join query if join is not on the partition column
DETAIL: Outer joins requiring repartitioning are not supported.
LOCATION: FixedJoinOrderList, multi_join_order.c:189

报错SQL

select
c_count,
count(*) as custdist
from
(
select
c_custkey,
count(o_orderkey)
from
customer left outer join orders on
c_custkey = o_custkey
and o_comment not like '%unusual%requests%'
group by
c_custkey
) as c_orders (c_custkey, c_count)
group by
c_count
order by
custdist desc,
c_count desc
LIMIT 1;

小结

citus的复杂SQL的语法支持还不是非常完备，如果要实现比较复杂的运算，建议Writing in SQL, thinking in MapReduce

https://www.citusdata.com/blog/2018/08/17/breaking-down-citus-real-time-executor/

这样的话，基本上你可以避免掉复杂的SQL，使用分解动作来实现。

citus也有专门的tpch测试软件

https://github.com/dimitri/tpch-citus

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.