PG中文社区 /
mdi-home
首页 社区新闻 中文文档 加入ACE {{ item.text }} 登录
mdi-home 首页 mdi-chat-processing 社区新闻 mdi-book-open-variant 中文文档 mdi-account-multiple-check 加入ACE mdi-file-multiple-outline 相关资料 mdi-blank {{item.text}} mdi-exit-to-app 退出账号
PostgreSQL-XC 分片表两表关联性能测试

原作者:francs / 谭峰  创作时间:2016-10-18 12:20:00+08  
doudou586 发布于2016-10-18 12:20:00           评论: 2   浏览: 6543   顶: 757  踩: 895 

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

扫描报名


/images/news/2016/pgconf2016_qrcode.jpg


/images/news/2016/pgconf2016_plus_logo_cn.png


评论:2   浏览: 6543                   顶: 757  踩: 895 

请在登录后发表评论,否则无法保存。

1# __ xcvxcvsdf 回答于 2024-11-12 09:57:18+08
https://aihuishou.tiancebbs.cn/sh/2231.html https://zulin.tiancebbs.cn/sh/4935.html https://aihuishou.tiancebbs.cn/sh/2357.html https://aihuishou.tiancebbs.cn/sh/320.html https://www.tiancebbs.cn/ershoufang/468917.html https://zulin.tiancebbs.cn/sh/1902.html https://zulin.tiancebbs.cn/sh/3627.html https://su.tiancebbs.cn/hjzl/462194.html https://www.tiancebbs.cn/fabu/4564.html https://su.tiancebbs.cn/hjzl/460860.html https://sh.tiancebbs.cn/hjzl/473979.html https://aihuishou.tiancebbs.cn/sh/258.html https://bs.tiancebbs.cn/qths/458370.html https://zulin.tiancebbs.cn/sh/2169.html https://www.tiancebbs.cn/ershoufang/468392.html https://zulin.tiancebbs.cn/sh/4147.html https://su.tiancebbs.cn/hjzl/468253.html

2# __ xiaowu 回答于 2024-04-22 09:23:49+08
期待爱情的句子:https://www.nanss.com/yulu/1957.html 开导别人放下一段感情:https://www.nanss.com/shenghuo/1613.html 对父母的亏欠和感恩:https://www.nanss.com/yulu/1753.html 结婚十一年纪念日朋友圈说说:https://www.nanss.com/wenan/1559.html 休息一天个人发朋友圈的说说:https://www.nanss.com/wenan/1744.html 甜到炸的早安情话:https://www.nanss.com/yulu/1715.html 高考当天祝福语:https://www.nanss.com/xuexi/1526.html 哄女生早点睡觉的句子:https://www.nanss.com/yulu/1660.html 死亡骑士名字:https://www.nanss.com/mingcheng/1829.html 饿了说说朋友圈:https://www.nanss.com/wenan/1998.html 钱包空空发朋友圈的句子:https://www.nanss.com/wenan/1649.html 老人去世安慰的话:https://www.nanss.com/yulu/1508.html 群名称高级霸气:https://www.nanss.com/mingcheng/1617.html 很小众却很惊艳的五言绝句:https://www.nanss.com/xuexi/1629.html 祝自己55岁生日快乐:https://www.nanss.com/yulu/1903.html 终止感情的网名:https://www.nanss.com/mingcheng/1929.html 想离开一个人的说说:https://www.nanss.com/wenan/1545.html 2022最能旺财旺运的个性签名:https://www.nanss.com/yulu/1609.html 快手关注又取消的说说:https://www.nanss.com/wenan/1711.html 形容自己永远是外人的句子:https://www.nanss.com/yulu/1569.html 以最好的心态面对每一天:https://www.nanss.com/xuexi/1663.html 女人靠自己的霸气说说:https://www.nanss.com/wenan/1859.html 努力变优秀的简短句子:https://www.nanss.com/xuexi/1953.html 带燕字的微信网名:https://www.nanss.com/mingcheng/1692.html 登山感悟心情的句子:https://www.nanss.com/yulu/1863.html 容颜不老的唯美句子:https://www.nanss.com/yulu/1563.html 姑姑给侄女生日祝福:https://www.nanss.com/yulu/1920.html 夸优秀职业女性的句子:https://www.nanss.com/gongzuo/1867.html 期待下一次重逢的唯美句子:https://www.nanss.com/yulu/1733.html 二字id干净温柔:https://www.nanss.com/mingcheng/1522.html



发表评论:
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
© PostgreSQL中文社区 ... (自2010年起)