使用TPC-H测试oracle&infobright&postgresql的性能
来源:互联网 发布:sql server 连接查询 编辑:程序博客网 时间:2024/05/23 00:42
- 下载tpch压缩包
http://www.tpc.org/tpch/此网页的右中间有
- DBGEN & Reference Data Set zip (23 MB)
- 上传至linux并解压
[root@dn210120 software]# unzip tpch_2_14_3.zip
- 重命名makfile.suite为makefile并修改其中的内容
[root@dn210120 software]# cd dbgen/[root@dn210120 dbgen]# cp makefile.suite makefile[root@dn210120 dbgen]# vim makefile修改成如下脚本:################## CHANGE NAME OF ANSI COMPILER HERE################CC = gcc# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)# SQLSERVER, SYBASE, ORACLE, VECTORWISE# Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS, # SGI, SUN, U2200, VMS, LINUX, WIN32 # Current values for WORKLOAD are: TPCHDATABASE= MYSQLMACHINE = LINUXWORKLOAD = TPCH
- 增加mysql的脚本支持
[root@dn210120 dbgen]# vim tpcd.h增加如下脚本:#ifdef MYSQL#define GEN_QUERY_PLAN ""#define START_TRAN "START TRANSACTION"#define END_TRAN "COMMIT"#define SET_OUTPUT ""#define SET_ROWCOUNT "limit %d;\n"#define SET_DBASE "use %s;\n"#endif
- 编译tpch
[root@dn210120 dbgen]# make会多了很多*.o文件,生成了dbgen和qgen可执行文件,生成了dss.ddl和dss.ri文件
- 生成数据脚本
[root@dn210120 dbgen]# ./dbgen -s 1可通过查看README来了解其用法
- 修改dss.ddl和dss.ri文件
[root@dn210120 dbgen]# cat dss.ddl | tr A-Z a-z > dss-lower.ddl[root@dn210120 dbgen]# cat dss.ri |tr A-Z a-z > dss-lower.ri[root@dn210120 dbgen]# sed -i "s/tpcd./tpch./g" dss-lower.ri[root@dn210120 dbgen]# sed -i "s/commit work/--commit work/g" dss-lower.ri[root@dn210120 dbgen]# sed -i "s/connect to tpch/--connect to tpch/g" dss-lower.ri大小写转换、文本内容替换
- 创建数据库tpch
mysql> create database tpch;Query OK, 1 row affected (0.00 sec)
- 创建用户tpch
mysql> create user 'tpch'@'%' identified by 'tpch';Query OK, 0 rows affected (0.00 sec)
- 赋予用户tpch权限
mysql> grant all on tpch.* to 'tpch'@'%';Query OK, 0 rows affected (0.00 sec)
- 切换至数据库tpch并执行dss-lower.ddl 以便创建表
mysql> use tpch;Database changedmysql> \. ~/software/dbgen/dss-lower.ddlQuery OK, 0 rows affected (0.02 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.02 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)mysql> show tables;+----------------+| Tables_in_tpch |+----------------+| customer || lineitem || nation || orders || part || partsupp || region || supplier |+----------------+8 rows in set (0.00 sec)
- 在数据库tpch下导入数据
mysql> LOAD DATA LOCAL INFILE '~/software/dbgen/customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|';Query OK, 150000 rows affected (1.76 sec)Records: 150000 Deleted: 0 Skipped: 0 Warnings: 0mysql> LOAD DATA LOCAL INFILE '~/software/dbgen/lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|';Query OK, 6001215 rows affected (1 min 12.30 sec)Records: 6001215 Deleted: 0 Skipped: 0 Warnings: 0mysql> LOAD DATA LOCAL INFILE '~/software/dbgen/nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|';Query OK, 25 rows affected (0.00 sec)Records: 25 Deleted: 0 Skipped: 0 Warnings: 0mysql> LOAD DATA LOCAL INFILE '~/software/dbgen/orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|';Query OK, 1500000 rows affected (14.87 sec)Records: 1500000 Deleted: 0 Skipped: 0 Warnings: 0mysql> LOAD DATA LOCAL INFILE '~/software/dbgen/part.tbl' INTO TABLE part FIELDS TERMINATED BY '|';Query OK, 200000 rows affected (2.31 sec)Records: 200000 Deleted: 0 Skipped: 0 Warnings: 0mysql> LOAD DATA LOCAL INFILE '~/software/dbgen/partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|';Query OK, 800000 rows affected (8.26 sec)Records: 800000 Deleted: 0 Skipped: 0 Warnings: 0mysql> LOAD DATA LOCAL INFILE '~/software/dbgen/region.tbl' INTO TABLE region FIELDS TERMINATED BY '|';Query OK, 5 rows affected (0.01 sec)Records: 5 Deleted: 0 Skipped: 0 Warnings: 0mysql> LOAD DATA LOCAL INFILE '~/software/dbgen/supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|';Query OK, 10000 rows affected (0.15 sec)Records: 10000 Deleted: 0 Skipped: 0 Warnings: 0
- 修改dss-lower.ri以致于匹配到mysql的语法标准
注意mysql的注释:--后要空格1.注释掉:commit work;2.注释掉:connect to tpch;add foreign key nation_fk1 (n_regionkey) references tpch.region;=>add foreign key nation_fk1 (n_regionkey) references tpch.region(r_regionkey);add foreign key supplier_fk1 (s_nationkey) references tpch.nation;=>add foreign key supplier_fk1 (s_nationkey) references tpch.nation(n_nationkey);add foreign key customer_fk1 (c_nationkey) references tpch.nation;=>add foreign key customer_fk1 (c_nationkey) references tpch.nation(n_nationkey);add foreign key partsupp_fk1 (ps_suppkey) references tpch.supplier; =>add foreign key partsupp_fk1 (ps_suppkey) references tpch.supplier(s_suppkey);add foreign key partsupp_fk2 (ps_partkey) references tpch.part;=>add foreign key partsupp_fk2 (ps_partkey) references tpch.part(p_partkey);add foreign key orders_fk1 (o_custkey) references tpch.customer;=>add foreign key orders_fk1 (o_custkey) references tpch.customer(c_custkey);add foreign key lineitem_fk1 (l_orderkey) references tpch.orders;=>add foreign key lineitem_fk1 (l_orderkey) references tpch.orders(o_orderkey);add foreign key lineitem_fk2 (l_partkey,l_suppkey) references tpch.partsupp;=>add foreign key lineitem_fk2 (l_partkey,l_suppkey) references tpch.partsupp(ps_partkey,ps_suppkey);注意:一旦表中的数据依赖关系不全时,建立外键索引会报错
- 在数据库tpch下执行dss-lower.ri 以便建立表之间的关联关系
先插入数据再建索引,效率高mysql> use tpch;Database changedmysql> \. ~/software/dbgen/dss-lower.ri
- 获取22条查询语句
[root@dn210120 dbgen]# cp ./queries*.sql ./[root@dn210120 dbgen]# ./qgen -s 1 > query.sql
- 修改query.sql中的sql
##去除 ^M 字符[root@dn210120 queries]# cat tpch_query.sql | col -b > tpch_query_new.sql:%s/;\nlimit/ limit/g:%s/limit -1/limit 1/g:wq##将 (3) 去除l_shipdate <= date '1998-12-01' - interval '77' day (3)count(o_orderkey)=>count(o_orderkey) as c_count) as c_orders (c_custkey, c_count)=>) as c_ordersdrop view revenue0 limit 1;=>drop view revenue0;
- 将每条sql语句单独以文件保存
脚本执行测试有如下方法:
- 自行开发测试工具
- 研究tpchDriver测试工具
- 结合shell脚本,手工执行脚本进行测试统计
测试使用到的软件:
- jmeter
- nmon/ganglia
- tpch(dbgen、qgen)
- oracle11.0.2
- infobright 4.5
- postgresql 9.3
测试维度:
- 单用户
- 多用户
- 数据量级(1GB、10GB、30GB、100GB、300GB、1000GB)
测试方法:
- 搭建数据库
- 创建表
- 导入数据
- 建立索引
- 单步顺序执行22条sql+2条dml
- 获取单步执行响应时间
- 并发执行随机选取的N条sql
- 获取并发执行响应时间
- 格式化nmon数据形成折线图或者直接使用ganglia进行查阅历史消耗资源情况
测试中用到的命令:
- oracle
sqlloader略
- infobright
mysql -h172.*.*.* -P5029 --local-infile=1 -utpch -ptpch tpch
LOAD DATA LOCAL INFILE '/data/wonhigh/data_scale/1/customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/data/wonhigh/data_scale/1/orders.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/data/wonhigh/data_scale/1/lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/data/wonhigh/data_scale/1/nation.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/data/wonhigh/data_scale/1/partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/data/wonhigh/data_scale/1/part.tbl' INTO TABLE PART FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/data/wonhigh/data_scale/1/region.tbl' INTO TABLE REGION FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/data/wonhigh/data_scale/1/supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|';
- postgresql
time psql -h172.17.209.102 -Utpch -c "COPY nation FROM stdin WITH (FORMAT csv, DELIMITER '|')" < /data/wonhigh/data_scale/1/nation.tbl
time psql -h172.17.209.102 -Utpch -c "COPY region FROM stdin WITH (FORMAT csv, DELIMITER '|')" < /data/wonhigh/data_scale/1/region.tbl
time psql -h172.17.209.102 -Utpch -c "COPY part FROM stdin WITH (FORMAT csv, DELIMITER '|')" < /data/wonhigh/data_scale/1/part.tbl
time psql -h172.17.209.102 -Utpch -c "COPY supplier FROM stdin WITH (FORMAT csv, DELIMITER '|')" < /data/wonhigh/data_scale/1/supplier.tbl
time psql -h172.17.209.102 -Utpch -c "COPY partsupp FROM stdin WITH (FORMAT csv, DELIMITER '|')" < /data/wonhigh/data_scale/1/partsupp.tbl
time psql -h172.17.209.102 -Utpch -c "COPY customer FROM stdin WITH (FORMAT csv, DELIMITER '|')" < /data/wonhigh/data_scale/1/customer.tbl
time psql -h172.17.209.102 -Utpch -c "COPY orders FROM stdin WITH (FORMAT csv, DELIMITER '|')" < /data/wonhigh/data_scale/1/orders.tbl
time psql -h172.17.209.102 -Utpch -c "COPY lineitem FROM stdin WITH (FORMAT csv, DELIMITER '|')" < /data/wonhigh/data_scale/1/lineitem.tbl
测试报告:
- 响应时间上
oracle的响应时间是三者中最快的,postgresql稍微略胜infobright
- 资源消耗上
oracle的在cpu、内存、IO上的使用没有太大的波动infobright在cpu、IO上消耗波动比较大,内存的消耗上稍微小于postgresqlpostgresql在大数据量时容易吃爆内存
0 0
- 使用TPC-H测试oracle&infobright&postgresql的性能
- PostgreSQL TPC-H测试
- TPC-H tools的使用
- postgresql安装、TPC-H测试和代码调试
- 使用TPC-H对Hive测试
- Hive的TPC-DS和TPC-H基准测试工具
- Deepgreen与Greenplum TPC-H性能测试对比(使用VitesseData脚本)
- Greenplum平台上的TPC-H测试
- [笔记] 大家一起来测试,benchmark起来(MySQL下的TPC-C,TPC-H,TPC-W)
- tpc-ds 性能测试
- TPC-H 使用
- 转载:TPC-H 使用
- oracle的tpc-c测试及方法
- TPC-H测试数据表生成,以及在Impala中的使用
- TPC-H数据导入postgresql教程
- TPC-H工具使用:生成数据集及转换到PostgreSQL数据库中
- mysql怎么使用TPC-H的数据集
- 大数据基准测试 TPC-H、TPC-DS
- 黑马程序员———集合(上)
- 预测分析表【LL(1)分析表】的构造
- 黑马程序员———集合(下)[包含Collections和Arrays类]
- [Leetcode]Recover Binary Search Tree
- 学习问题
- 使用TPC-H测试oracle&infobright&postgresql的性能
- Perl 正则分组
- linux shell 不同进制数据转换(二进制,八进制,十六进制,base64)
- OpenCV学习(八)之OpenCV中IplImage和CvMat图像数据结构
- 天声人語 20141201
- java IO相关API探索之Closeable和InputStream接口
- ASP.NET基础
- Java线程(五):JUC包线程同步工具
- 5、黑马程序员-C语言的scanf函数