Tpc-h测试greenplum性能

Tpc-h测试greenplum性能

集群环境

主机名

ip

内存

磁盘

Node1

192.168.71.11

2G

80G

Node2

192.168.71.12

1.5G

80G

Node3

192.168.71.13

1.5G

80G

 

首先一点忠告:tpch不要到官网下,下不下来

建议到csdn下载:tpch-dbgen.tar.gz

然后TPCH是什么?

TPC-H包括8张表(表上有些约束等需要满足,参见TPC-H规范,不再罗列),如下:
PART:表示零件的信息
SUPPLIER:表示供货商的信息
PARTSUPP:表示供货商的零件的信息
CUSTOMER:表示消费者的信息
ORDERS:表示订单的信息
LINEITEM:表示在线商品的信息
NATION:表示国家的信息
REGION:表示地区的信息
这8张表之间的关系,如图所示

 

1.     安装tpch并生成10G测试数据

Tar -zxvf tpch-dbgen.tar.gz

解压玩安装包之后,会多出一个dbgen文件夹,执行cd dbgen到degen目录下

cp makefile.sute Makefile

vim Makefile

修改如下:

CC = gcc

DATABASE= ORACLE
MACHINE = LINUX
WORKLOAD = TPCH

然后执行make,生成tpch的仿真数据,dbgen下面会多了8个表名命名的.tbl文件

注意:有的安装包如果发现包含makefile文件的内容已修改,可直接make,按上面的操作反而会报错。

 

执行下面的命令生成10G仿真数据: ./dbgen -s 10 -f(依照机器性能的不同所需时间不同,大概几分钟)

 

然后将测试数据转化为GP能够识别的格式,删除末尾的分隔符|。

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

 

2.     下载pg_tpch并关联tpch

wget https://github.com/tvondra/pg_tpch/archive/master.zip

解压安装

在他的dss目录下面有加载tpch数据到gp的脚本

其中tpch-load.sql是列式存储,tpch-load_pg.sql是行存储,具体的优化熟悉gp用法之后自行修改优化。其他几个脚本是创建表的脚本。

 

将pg_tpch的文件逗拷贝到dbgen下面:

cp -r pg_tpch-master/*       /dbgen

 

创建一个queries目录,用于存放转换后的tpc-h 测试SQL。

mkdir dss/queries

 

生成测试SQL

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

3.     Load数据到GP

在greenplum数据库中创建数据库和用户(也可以不创建,只要有就可以)

 

配置pg_hba.conf

$ vi $MASTER_DATA_DIRECTORY/pg_hba.conf

host all all 127.0.0.1/32 trust

$ gpstop -u

设置几个参数:

gpconfig -c enable_nestloop -v off

gpconfig -c work_mem -v 256MB

gpstop -u

测试,使用digoal用户连接到postgres数据库,结果输出到./results目录:
自动创建表,加载数据。详见tpch.sh脚本

./tpch.sh ./results tpch-db tpch-user (机器性能不同所需时间不同,大概需要半小时)

结束后,可以使用以下方法生成CSV报告。

php process.php ./results output.csv

4.     基于mondrian-web测试gp性能

首先将tpch所有的表join到一张大表中(tpch_join),一个大sql来jion的话本机的gp太慢,分为几个小表分别join,最后再合到一起。见下图和sql。

 

create table oc_join as select o.* ,c .* from orders o left join customer c on o.o_custkey=c.c_custkey distributed by (o_orderkey)

 

create table nr_join as select n.* ,r.* from nation n left join region r on n.n_regionkey=r.r_regionkey distributed by (n_nationkey)

 

 

create table snr_join as select s.*,nr.* from supplier s left join nr_join nr on s.s_nationkey=nr.n_nationkey distributed by (s_suppkey)

 

create table pps_join as select ps.*,p.*,snr.* from partsupp ps left join part p on ps.ps_partkey=p.p_partkey left join snr_join snr on ps.ps_suppkey=snr.s_suppkey distributed by (ps_partkey,ps_suppkey)

 

列式存储

create table tpch_join  with (appendonly=true, compresstype=quicklz, compresslevel=1, orientation=column) as select lt.*,pps.*,oc.* from lineitem lt left join pps_join pps on lt.l_partkey=pps.ps_partkey and lt.l_suppkey=pps.ps_suppkey left join oc_join oc on lt.l_orderkey=oc.o_orderkey distributed by (l_linenumber,l_orderkey)

 

行式存储

create table tpch_join  with (appendonly=true, compresstype=quicklz, compresslevel=1) as select lt.*,pps.*,oc.* from lineitem lt left join pps_join pps on lt.l_partkey=pps.ps_partkey and lt.l_suppkey=pps.ps_suppkey left join oc_join oc on lt.l_orderkey=oc.o_orderkey distributed by (l_linenumber,l_orderkey)

 

查看表空间

select pg_size_pretty(pg_relation_size('tpch_join'));

select pg_size_pretty(pg_total_relation_size('tpch_join'));

select pg_size_pretty(pg_database_size('testtpch'));

 

创建主键和索引

alter table tpch_join add primary key(l_linenumber,l_orderkey);

create index indx_tpch_join_linenumber on tpch_join (l_linenumber);

create index indx_tpch_join_orderkey on tpch_join (l_orderkey);

create index indx_tpch_join_partkey on tpch_join (l_partkey);

create index indx_tpch_join_suppkey on tpch_join (l_suppkey);

 

注意:

       最后生成的tpch_join大表如果使用行式存储会有80几个G,加上主键和索引之后同时优化数据库配置,尽管最简单的查询速度回巨慢无比,所以后来改为列式存储。

数据库优化配置如下(配置完之后对查询效率的提升没有太大的效果,对mondrian查询提升只有十几秒,可能受限于我的虚拟机的资源的问题):

       编辑$MASTER_DATA_DIRECTORY/postgresql.conf

       shared_buffers:刚开始可以设置一个较小的值,比如总内存的15%,然后逐渐增加,过程中监控性能提升和swap的情况

       effective_cache_size : 这个参数告诉PostgreSQL的优化器有多少内存可以被用来缓存数据,以及帮助决定是否应该使用索引。这个数值越大,优化器使用索引的可能性也越大。 因此这个数值应该设置成shared_buffers加上可用操作系统缓存两者的总量。通常这个数值会超过系统内存总量的50%

       temp_buffers: 即临时缓冲区,拥有数据库访问临时数据,GP中默认值为1M,在访问比较到大的临时表时,对性能提升有很大帮助。

 

转用列式存储之后最后的大表是22G左右,能够支持mondrian对gp的操作,相应时间1-2分钟,但是遇到group by 后面的字段将全表数据全部扫出来的时候,集群内存会爆掉。

gp行列存储的查询性能对比

存储方式

Tpch_join大小

压缩方式

主键

索引

MDX

Mondrian生成查询语句

查询时间

行式

84G

L_linenumber,

l_orderkey

indx_tpch_join_linenumber,

indx_tpch_join_orderkey,

indx_tpch_join_partkey,

indx_tpch_join_suppkey

select{[Lineitem].[LineitemInfo].[l_linenumber].members} on columns,

[measures].[l_extendedprice] on rows

from [Lineitem]

Select "lineitem"."l_linenumber" as "c0", sum("lineitem"."l_extendedprice") as "m0" from "lineitem" as "lineitem" group by "c0"

 

----

行式

36G

quicklz

同上

同上

同上

同上

13min24s

列式

22G

quicklz

同上

同上

同上

同上

75s

 

所以使用gp和mondrian对于某个业务做多维分析的时候,join出来的大表首先一定要使用列式存储,在保证查询的前提下,再去根据自身集群的资源配置gp的数据库系统参数来提升查询性能。

上一篇:使用MDX实现SQL函数


下一篇:Ignite自定义函数注意事项