OLAP(TPC-H)

2019独角兽企业重金招聘Python工程师标准>>>

TPC- H
主要目的:是评价特定查询的决策支持能力，强调服务器在数据挖掘、分析处理方面的能力。查询是决策支持应用的最主要应用之一，数据仓库中的复杂查询可以分成两种类型：一种是预先知道的查询，如定期的业务报表；另一种则是事先未知的查询，称为动态查询（Ad- Hoc Query）。
衡量指标:它模拟决策支持系统中的数据库操作，测试数据库系统复杂查询的响应时间，以每小时执行的查询数(TPC-H QphH@Siz)作为度量指标
运行环境:TPC- H 测试模型为数据库服务器连续7×24 小时工作，可能只有1 次/月的维护；多用户并发执行复杂的动态查询，同时有并发执行表修改操作。
帮助工具：ANSI C 语言源程序包，即DBGEN 和QGEN。DBGEN 用于产生被测试数据，用户通过命令行参数控制执行结果。QGEN 用于生产测试所需要的22 个SELECT、RF1 和RD2 两个更新操作。
数据量：由于数据量的大小对查询速度有直接的影响，TPC- H 标准对数据库系统中的数据量有严格、明确的规定。用SF 描述数据量，1SF 对应1 GB 单位，SF 由低到高依次是1、10、30、100、300、1 000、3 000、10 000。需要强调，SF 规定的数据量只是8个基本表的数据量，不包括索引和临时表。
从TPC- H 测试全程来看，需要的数据存储空较大，一般包括有基本表、索引、临时表、数据文件和备份文件，基本表的大小为x；索引和临时空间的经验值为3-5 位，取上限5x；DBGEN产生的数据文件的大小为x；备份文件大小为x；总计需要的存储空间为8x。就是说SF=1，需要准备8 倍，即8 GB 存储空间，才能顺利地进行测试
22 个查询语句：TPC- H 测试围绕22 个SELECT 语句展开，每个SELECT严格定义，遵守SQL- 92语法，并且不允许用户修改。标准中从4 个方面定义每个SELECT 语句，即商业问题、SELECT 的语法、参数和查询确认。这些SELECT 语句的复杂程度超过大多数实际的OLTP 应用，一个SELECT 执行时间少则几十秒，多则达15 小时以上，22 个查询语句执行一遍需数个小时。
2 个更新操作：为了逼真地模拟数据仓库的实际应用环境，在22 个查询执行的同时，还有一对更新操作RF1 和RF2 并发地执行。RF1向Order 表和Lineitem 表中插入原行数的0.1%的新行，模拟新销售业务的数据加入到数据库中；RF2 从Order 表和Lineitem表中删除等量与RF1 增加的数据，模拟旧的销售数据被淘汰。RF1 和RF2 的执行必须保证数据库的ACID 约束，并保持测试前后的数据库中的数据量不变。更新操作除输出成功或失败信息外，不产生其它输出信息
3 个测试：TPC-H 测试分解为3 个子测试：数据装载测试、Power 测试和Throughput 测试。建立测试数据库的过程被称为装载数据，装载测试是为测试DBMS 装载数据的能力。装载测试是第一项测试，测试装载数据的时间，这项操作非常耗时。Power 测试是在数据装载测试完成后，数据库处于初始状态，未进行其它任何操作，特别是缓冲区还没有被测试数据库的数据，被称为raw查询。Power 测试要求22 个查询顺序执行1 遍，同时执行一对RF1 和RF2 操作。最后进行Throughput 测试，也是最核心和最复杂的测试，它更接近于实际应用环境，与Power 测试比对SUT 系统的压力有非常大的增加，有多个查询语句组，同时有一对RF1 和RF2 更新流。
度量指标：测试中测量的基础数据都与执行时间有关，这些时间又可分为：装载数据的每一步操作时间、每个查询执行时间和每个更新操作执行时间，由这些时间可计算出五个度量指标
    数据装载时间 
    Power@Size 
    Throughput@Size 
   QphH@Size 
   $/QphH@Siz

 

TPC-DS（将逐步替代TPC-H）
采用星型、雪花型等多维数据模式。它包含7张事实表，17张纬度表平均每张表含有18列。其工作负载包含99个SQL查询，覆盖SQL99和2003的核心部分以及OLAP。这个测试集包含对大数据集的统计、报表生成、联机查询、数据挖掘等复杂应用，测试用的数据和值是有倾斜的，与真实数据一致。可以说TPC-DS是与真实场景非常接近的一个测试集，也是难度较大的一个测试集。
TPC-DS的这个特点跟大数据的分析挖掘应用非常类似。Hadoop等大数据分析技术也是对海量数据进行大规模的数据分析和深度挖掘，也包含交互式联机查询和统计报表类应用，同时大数据的数据质量也较低，数据分布是真实而不均匀的。因此TPC-DS成为客观衡量多个不同Hadoop版本以及SQL on Hadoop技术的最佳测试集。
这个基准测试有以下几个主要特点：
  一共99个测试案例，遵循SQL'99和SQL 2003的语法标准，SQL案例比较复杂
  分析的数据量大，并且测试案例是在回答真实的商业问题
  测试案例中包含各种业务模型（如分析报告型，迭代式的联机分析型，数据挖掘型等）
  几乎所有的测试案例都有很高的IO负载和CPU计算需求

 

TPC-H vs TPC-DS

              TPC-H                       TPC_DS
数据模型   关系模型第三范式            关系模型、星型模型、雪花模型
SQL标准   SQL92                     SQL99、SQL2003、OLAP
工作负载   8张表，22个SQL             24张表，99个SQL
性能测试   严重依赖索引，容易被Hack     健壮性好，比较客观

 

TPC-DS for pgsql

#install tps-ds

sudo apt-get install gcc make flex bison byacc git
git clone https://github.com/databricks/tpcds-kit.git
cd tpcds-kit/tools
make OS=LINUX

#genergate query sql (between fix the templates)

mkdir query
/dsqgen -DIRECTORY ../query_templates -INPUT ../query_templates/templates.lst -VERBOSE Y -QUALIFY Y -SCALE 10 -DIALECT ansi -OUTPUT_DIR ./query

#generate the test data

mkdir data
./dsdgen  -SCALE 10 -DIR ./data
#./dsdgen  -SCALE 10 -parallel 4 -DIR ./data

#prepare the table ddl

mkdir ddl
cp *.sql ddl/

#execute ddl (create table)

psql -h 192.168.0.112 -p 5432 -U postgres postgres < ddl/tpcds.sql
#psql -h 192.168.0.112 -p 5432 -U postgres postgres < ddl/tpcds_source.sql

#fix dat file

dat文件每一行去掉最后一个|,因为数据多了一列
cd data
mkdir pgdata
touch fixdata.sh
    
    #replace the last |
    datadir=pgdata
    for i in `ls *.dat`
        do
         name="$datadir/$i"
         echo $name
         `touch $name`
         `chmod 777 $name`
         sed 's/|$//' $i >> $name;
        done

bash fixdata.sh
cd ..

#prepare load shell loaddat.sql

copy time_dim from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/time_dim.dat' with delimiter as '|' NULL '';
copy date_dim from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/date_dim.dat' with delimiter as '|' NULL '';

copy item from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/item.dat' with delimiter as '|' NULL '';
copy reason from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/reason.dat' with delimiter as '|' NULL '';
copy customer_address from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/customer_address.dat' with delimiter as '|' NULL '';

copy call_center from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/call_center.dat' with delimiter as '|' NULL '';

copy household_demographics from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/household_demographics.dat' with delimiter as '|' NULL '';
copy customer_demographics from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/customer_demographics.dat' with delimiter as '|' NULL '';
copy customer from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/customer.dat' with delimiter as '|' NULL '';

copy catalog_page from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/catalog_page.dat' with delimiter as '|' NULL '';
copy catalog_returns from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/catalog_returns.dat' with delimiter as '|' NULL '';
copy catalog_sales from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/catalog_sales.dat' with delimiter as '|' NULL '';

copy income_band from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/income_band.dat' with delimiter as '|' NULL '';

copy warehouse from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/warehouse.dat' with delimiter as '|' NULL '';
copy inventory from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/inventory.dat' with delimiter as '|' NULL '';

copy promotion from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/promotion.dat' with delimiter as '|' NULL '';

copy ship_mode from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/ship_mode.dat' with delimiter as '|' NULL '';
copy store from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/store.dat' with delimiter as '|' NULL '';
copy store_returns from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/store_returns.dat' with delimiter as '|' NULL '';
copy store_sales from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/store_sales.dat' with delimiter as '|' NULL '';

copy web_page from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/web_page.dat' with delimiter as '|' NULL '';
copy web_returns from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/web_returns.dat' with delimiter as '|' NULL '';
copy web_sales from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/web_sales.dat' with delimiter as '|' NULL '';
copy web_site from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/web_site.dat' with delimiter as '|' NULL '';

copy dbgen_version from '/home/zhaomeng/tmp/tpc-ds/tpcds-kit/tools/data/pgdata/dbgen_version.dat' with delimiter as '|' NULL '';

#load dat file

psql -h 192.168.0.112 -p 5432 -U postgres postgres <  loaddat.sql

#create foreign keys

psql -h 192.168.0.112 -p 5432 -U postgres postgres < ../ddl/tpcds_ri.sql

#fix the queries ..\query\query_0.sql

1.open the timing at the beginning
\timing on

2.top 100 => limit 100

#execute queries

psql -h 192.168.0.112 -p 5432 -U postgres postgres < ../query/query_0.sql 

 

转载于:https://my.oschina.net/igooglezm/blog/821588