| PostgreSQL-XC 分片表两表关联性能测试 原作者：francs / 谭峰 创作时间：2016-10-18 12:20:00+08 |
doudou586 发布于2016-10-18 12:20:00
 评论: 0
 浏览: 5843
 顶: 674
 踩: 844
|
PostgreSQL-XC 分片表两表关联性能测试
2016 Postgres大象会官方报名通道： 点此报名
作者: francs
PostgreSQL-XC 主要特性在于它的分片扩展功能，之前博客介绍过。
PostgreSQL-XC 的复制表和分片表模式，这篇博客选取了业务场景的一条两表关联 SQL，分别测试在复制表模式和分片表模式下的性能。
--测试环境
硬件环境：3台虚拟机
软件版本：Postgres-XC 1.2
--PGXC 环境
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id -----------+-----------+-----------+-----------+----------------+------------------+-------------- coord2 | C | 10000 | node2 | f | f | -1197102633 datanode2 | D | 10002 | node2 | t | t | -905831925 datanode3 | D | 10003 | node3 | f | f | -1894792127 coord3 | C | 10000 | node3 | f | f | 1638403545 (4 rows)
备注：两个协调节点，两个数据节点。
--业务场景 SQL
select a.* from tbl_operate a
join tbl_info b on a.applyid = b.id
where a.syskey = 'BOSS'
and a.operationid = '7'
and a.targetid = 'zhuhua1'
and (a.state in ('DataSaved', 'DataProc') or
b.state in ('MainBillDeptAdminApprove', 'MainBillDeptApprove') or
a.applyid = '8ace4a9e506c7af101508354dddd4d95');
备注：此条 SQL 为业务场景中的一条 SQL，其中 tbl_operate 记录数 1534437， tbl_info 表记录数 1699246, 目前采用的是复制表模式，两张表的 id 为主键。 注意关联字段为 a.applyid = b.id。
--场景一：tbl_operate: 复制表 tbl_info: 复制表
QUERY PLAN
---------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
(actual time=3.314..3.331 rows=3 loops=1)
Node/s: datanode2
Total runtime: 3.376 ms
(3 rows)
备注：两张表都是复制表模式下，执行时间为 3.376 ms。
--将两张表修改成分片表
alter table tbl_operate distribute by hash(id); alter table tbl_info distribute by hash(id);
备注： 此条命令会涉及到数据节点数据重分布，会锁表，命令执行过程中 coor 节点上先是有个 copy 进程，之后有个 REINDEX 进程，或许这是 PostgreSQL-XC 修改表分片方式的内部过程。
--场景二 两表都 hash(id) 分片
QUERY PLAN
---------------------------------------------------------------------------------------------------
Hash Join (cost=0.01..0.07 rows=1 width=4670) (actual time=31.442..769.754 rows=3 loops=1)
Hash Cond: ((b.id)::text = (a.applyid)::text)
Join Filter: (((a.state)::text = ANY ('{DataSaved,DataProc}'::text[])) OR ((b.state)::text = ANY ('{MainBillDeptAdminApprove,MainBillDeptApprove}'::text[])) OR
((a.applyid)::text = '8ace4a9e506c7af101508354dddd4d95'::text))
Rows Removed by Join Filter: 25
-> Data Node Scan on tbl_info "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=1000 width=208)
(actual time=0.573..484.255 rows=337473 loops=1)
Node/s: datanode2, datanode3
-> Hash (cost=0.00..0.00 rows=1000 width=4670) (actual time=2.590..2.590 rows=28 loops=1)
Buckets: 1024 Batches: 8 Memory Usage: 3kB
-> Data Node Scan on tbl_operate "_REMOTE_TABLE_QUERY__1" (cost=0.00..0.00 rows=1000 width=4670)
(actual time=1.626..1.850 rows=28 loops=1)
Node/s: datanode2, datanode3
Total runtime: 776.020 ms
(11 rows)
备注：将两张表都改成 HASH 分片后，执行时间需要 776.020 ms，效率降低 230 倍左右，执行计划也复杂得多。
--场景三 tbl_operate: 复制表 tbl_info: hash(id)
QUERY PLAN
-------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
(actual time=3.303..5.061 rows=3 loops=1)
Node/s: datanode2, datanode3
Total runtime: 5.106 ms
(3 rows)
备注：执行时间 5.106 ms。
--场景四 tbl_operate: hash(id) tbl_info: 复制表
QUERY PLAN
--------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
(actual time=3.065..3.218 rows=3 loops=1)
Node/s: datanode2, datanode3
Total runtime: 3.263 ms
(3 rows)
备注：执行时间 3.263 ms，之前的业务场景 SQL 关联字段有一个是非分区键，如果关联字段都是分片字段，情况如何呢？接着测试。
--创建测试表
create table t1(id int4,name character varying(32),create_time timestamp(0) without time zone
default clock_timestamp() ) distribute by hash(name);
create unique index idx_t1_name on t1 using btree(name);
insert into t1(id,name) select n,n||'_a' from generate_series(1,100000) n;
create table t2 as select name from t1;
create unique index idx_t2_name on t2 using btree(name);
alter table t2 add column flag boolean default 't';
--分区键关联SQL
select t1.id,t1.create_time,t2.name,t2.flag from t1,t2 where t1.name=t2.name and t1.name='1_a';
备注： 关联字段 name 分别是 t1，t2 表的分片字段。
--分片表执行计划
francs=> explain analyze select t1.id,t1.create_time,t2.name,t2.flag
from t1,t2
where t1.name=t2.name and t1.name='2_a';
QUERY PLAN
--------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
(actual time=1.243..1.244 rows=1 loops=1)
Node/s: datanode2, datanode3
Total runtime: 1.293 ms
(3 rows)
备注：执行时间 1.293 ms，根据执行计划可以看到扫描了两个数据节点。
--修改成复制表
alter table t1 distribute by replication; alter table t2 distribute by replication;
--复制表执行计划
francs=> explain analyze select t1.id,t1.create_time,t2.name,t2.flag
from t1,t2
where t1.name=t2.name and t1.name='2_a';
QUERY PLAN
-------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
(actual time=0.909..0.910 rows=1 loops=1)
Node/s: datanode2
Total runtime: 0.941 ms
(3 rows)
备注：执行时间 0.941 ms，公扫描 datanode2 节点，性能比分片情况稍降低。
--总结
PostgreSQL-XC 环境下，两表关联的业务场景，如果关联字段正好是两表的分片字段，性能会比复制表稍降低，如果关联字段不是分片字段，性能会比复制表大辐度降低， 分片表的使用场景需谨慎。
2016 Postgres大象会官方报名通道：http://www.huodongxing.com/event/8352217821400
扫描报名
请在登录后发表评论，否则无法保存。
