Parallel join based on distributed system OceanBase
-
摘要: 随着应用数据的飞速增长以及分布式数据库系统的不断涌现，数据存储在物理独立的节点已经成为一种趋势.在这种情况下，当应用需要进行复杂join查询时，就会不可避免地产生非常多的网络传输代价.所以，如何提高分布式系统中join查询的效率成为研究热点.本文在分析分布式数据库系统OceanBase执行nested loop join、Hashjoin、semi-join等算法的基础上，提出了合理利用硬件资源采用多线程并行执行join操作的优化思想，并在OceanBase数据库中分别对nested loop join、Hashjoin、semi-join等算法进行了并行改造.实验结果表明，在一定线程数内join算法执行效率与并行度呈正相关.Abstract: With the rapid growth of application data and the continued development of distributed database systems, data storage in physical independent nodes has become a trend. In this trend, when the application needs to perform complex join queries, it inevitably generates a lot of network traffic. Therefore, improving the efficiency of join query in distributed system is a hot topic. Based on the analysis of the nested loop join, Hash join, semi-join in the OceanBase, this paper puts forward the optimization idea of using hardware resources reasonably and using multithread to execute join operations in parallel. We implement experiment on OceanBase with nested loop join algorithm, Hash join algorithm, semi-join algorithm respectively. The experimental results confirm that the efficiency of join algorithm is positively related to parallelism in a certain number of threads.
-
Key words:
- query /
- semi-join /
- OceanBase /
- parallel join
-
图 1 nested loop join、merge sort join、Hash join执行计划
Fig. 1 Nested loop join, merge sort join, Hash join execution plan
图 7 不同并行度单表数据查询的响应时间
Fig. 7 Response time for single table query with different parallel number
图 8 不同并行度下连接算法的执行效率
Fig. 8 Execution efficiency of join algorithms with different parallel number
图 9 不同并行度下基于semi-join的join执行效率
Fig. 9 Execution efficiency of join based on semi-join in different parallel number
表 1 集群服务器配置
Tab. 1 The cluster server configuration
角色 CPU 内存/GB 磁盘/TB 网络 CS 6核12线程(Intel(R)Xeon(R)CPU E5-2620 V2@ 2.10 GHz)*2 64 3 千兆网 UPS/RS 6核12线程(Intel(R)Xeon(R)CPU E5-2620 V3@ 2.30 GHz)*2 165 1.5 千兆网 
下载: 导出CSV
表 2 测试表的模式
Tab. 2 The schema of the test table
属性名称 是否为主键 数据类型 数据大小 ID 是 Int 4 Col 1 否 Varchar 64 Col 2 否 Varchar 64
下载: 导出CSV
表 3 测试数据表信息
Tab. 3 Test data table information
表名 数据分布(主键) 数据量(行数)/万 $R$ 1 连续 10 $R$ 2 连续 100 $R$ 3 连续 1 000 $S$ 1 连续 10 $S$ 2 连续 100 $S$ 3 连续 1 000
下载: 导出CSV
-
[1] 杨传辉.大规模分布式存储系统[M].北京:机械工业出版社, 2013. [2] BERNSTEIN P A, GOODMAN N, WONG E, et al. Query processing in a system for distributed databases (SDD-1)[J]. ACM Transactions on Database Systems, 1981, 6(4):602-625. doi: 10.1145/319628.319650 [3] ZHANG X F, CHEN L, WANG M. Efficient multi-way theta-join processing using MapReduce[J]. Proceedings of the VLDB Endowment, 2012, 11(5):1184-1195. http://dl.acm.org/citation.cfm?doid=2350229.2350238 [4] BLASGEN M W, ESWARAN K P. Storage and access in relational databases[J]. IBM Systems Journal, 1977, 16(4):363-377. doi: 10.1147/sj.164.0363 [5] ZHOU J R, ROSS K A. Implementing database operations using SIMD instructions[C]//Proceedings of the 2002 ACM SIGMOD International Conference on Management of Data. 2002:145-156. [6] ZAHARIA M, CHOWDHURY M, FRANKLIN M J, et al. Spark:Cluster computing with working sets[C/OL]//Proceedings of the 2nd USENIX Conference on Hot Topics in Cloud Computing. (2010-06-25)[2017-04-01]. https://www.usenix.org/legacy/events/hotcloud10/tech/fullpapers/Zaharia.pdf?CFID=973306186&CFTOKEN=67460167. [7] MERRETT T H. Why sort-merge gives the best implementation of the natural join[J]. ACM Sigmod Record, 1983, 13(2):39-51. doi: 10.1145/984523 [8] KIM C, PARK J, SATISH N, et al. CloudRAMSort:Fast and efficient large-scale distributed RAM sort on shared-nothing cluster[C]//ACM SIGMOD International Conference on Management of Data. ACM, 2012:841-850. [9] BABB E. Implementing a relational database by means of specialzed hardware[J]. ACM Transactions on Database Systems, 1979, 4(1):1-29. doi: 10.1145/320064.320065 [10] BONCZ P A, ZUKOWSKI M, NES N. MonetDB/X100:Hyper-pipelining query execution[C/OL]//Proceedings of the 2005 CIDR Conference on Innovative Data Systems Research. 2005:225-237[2017-04-01]. https://www.researchgate.net/publication/45338800 MonetDBX 100 Hyper-Pipelining Query Execution. -
点击查看大图
计量
- 文章访问数: 259
- HTML全文浏览量: 106
- PDF下载量: 511
- 被引次数: 0