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的数据库系统参数来提升查询性能。