Tpc-h测试greenplum性能

集群环境

首先一点忠告：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行列存储的查询性能对比

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