一、项目概述
测试目的:针对已有数据库(pg、oracle、 mysql),对这些数据库在相同级别数据量、单机环境下测试各自的性能表现。
二、测试策略
1、 相关测试工具
TPC-H,用于生成数据库一定量的测试数据
Jmeter,模拟若干客户端即虚拟用户,并发执行相应的 SQL 语句
Nmon,用来监控数据库资源的使用情况
2、 相关指标使用情况
2.1 事务响应时间
2.2 每秒处理事务数(tps)
2.3 支持最大并发数
2.4 系统资源使用情况:内存占用率分别低于 cpu70%,80%,以及 IO、带宽使用情况
3、 相关人员
Db 人员:负责数据库相关调优工作,协助测试人员导入测试数据
运维人员:负责监控软件的安装,协助测试人员监控相关指标
测试人员:负责方案制定、测试数据准备、测试脚本准备、测试执行、测试结果分析、测试报告编写等工作
4、 测试场景
场景一:10GB 数据量
用户数 描述 事务请求响应时间 资源使用情况 TPS
单用户 分别执行单个查询
多用户 500 混合执行 N 条查询 +2 条 dml
1000 混合执行 N 条查询 +2 条 dml
场景二:30G
场景三:100G
场景四:300G
场景五:1000G
三、测试准备
1、测试环境准备:
1.1 用于模拟多线程请求的客户机
1.2 PG、Orcale、Mysql 数据库搭建
1.3 Nmon 监控环境搭建
2、测试数据准备:
生成数据:
下载 TPC-H 压缩包,解压后再 dbgen 目录下找到 tpch.sln 文件,用 microsoft visual studio 打开,自动转换解决方案
生成解决方案,生成成功后会在 dbgen/Debug 下生成一个 dbgen.exe
复制到 dbgen 目录下
然后用命令行输入以下命令
dbgen.exe -vf -s 10 (生成 10G 的数据)
成功后会在 dbgen 目录下生成 8 个.tbl 文件,这个就是生成好的数据文件
创建数据表:
8 张表(customer、lineitem、nation、orders、part、partsupp、region、supplier)
表结构:
-- Sccsid: @(#)dss.ddl 2.1.8.1
CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152));
CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152));
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL );
CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INTEGER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL);
CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,
PS_SUPPKEY INTEGER NOT NULL,
PS_AVAILQTY INTEGER NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL );
CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL);
CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL);
CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL);
导入数据:
Postgresql 数据库导入数据:
\copy nation from 'D:\2.17.3\dbgen\nation.tbl' WITH DELIMITER AS '|';
\COPY customer FROM 'D:\2.17.3\dbgen\customer.tbl' WITH DELIMITER as '|';
\copy orders from 'D:\2.17.3\dbgen\orders.tbl' WITH DELIMITER as '|';
\copy lineitem FROM 'D:\2.17.3\dbgen\lineitem.tbl' WITH DELIMITER as '|';
\copy partsupp FROM 'D:\2.17.3\dbgen\partsupp.tbl' WITH DELIMITER as '|';
\copy part FROM 'D:\2.17.3\dbgen\part.tbl' WITH DELIMITER as '|';
\copy region from 'D:\2.17.3\dbgen\region.tbl' WITH DELIMITER as '|';
\copy supplier FROM 'D:\2.17.3\dbgen\supplier.tbl'WITH DELIMITER as '|';
8 个 tbl 文件最后面需要去掉’|’ 这个符号才能满足该数据库的格式
通过 java 编写脚本,把 tbl 文件中 ‘|’ 符号去掉,生成新的 tbl 文件
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
public class readFile {
public static void main(String args[]) throws IOException {
File file = new File("D:\\tbl\\nation.tbl");
BufferedReader reader = null;
BufferedWriter writer = new BufferedWriter(new FileWriter("D:\\tbl\\nation1.tbl"));
try {
System.out.println("以行为单位读取文件内容,一次读一整行:");
reader = new BufferedReader(new FileReader(file));
String tempString = null;
// 一次读入一行,直到读入null为文件结束
while ((tempString = reader.readLine()) != null) {
System.out.println(tempString);
int len = tempString.length();
int i = len-1;
String newString = tempString.substring(0, i);
System.out.println(newString);
writer.write(newString);
writer.newLine();
writer.flush();
}
reader.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (reader != null) {
try {
reader.close();
} catch (IOException e1) {
}
}
}
}
}
Mysql 数据库导入数据:
LOAD DATA LOCAL INFILE 'E:\\tpch_2_14_3\\dbgen\\customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\orders.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\nation.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\part.tbl' INTO TABLE PART FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\region.tbl' INTO TABLE REGION FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
Oracle 数据导入:
建立控制文件,test.ctl,内容如下
1. LOAD DATA INFILE 'C:\Users\ForestNeo\Desktop\tbl\region.tbl'
2. INTO TABLE REGION
3. (r_regionkey terminated by '|',
4. r_name terminated by '|',
5. r_comment terminated by '|')
打开命令提示符,输入以下语句
Sqlldr userid=SYSTEM/password control = “desktop/test/ctl”
添加外键、主键
载入数据后,就可以添加外键、主键
对每张表添加外键、主键,修改 tpch-dbgen 文件夹中的 dss.ri,将 dss.ri 所有添加外键和主键的信息都按照以下修改,并注释掉 connect to tpcd 语句。
PG 数据库添加外键、主键:
-- For table REGION
ALTER TABLE REGION
ADD PRIMARY KEY (R_REGIONKEY);
-- For table NATION
ALTER TABLE NATION
ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE NATION
ADD FOREIGN KEY (N_REGIONKEY) references REGION;
COMMIT WORK;
-- For table PART
ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);
COMMIT WORK;
-- For table SUPPLIER
ALTER TABLE SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE SUPPLIER
ADD FOREIGN KEY (S_NATIONKEY) references NATION;
COMMIT WORK;
-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
COMMIT WORK;
-- For table CUSTOMER
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE CUSTOMER
ADD FOREIGN KEY (C_NATIONKEY) references NATION;
COMMIT WORK;
-- For table LINEITEM
ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
COMMIT WORK;
-- For table ORDERS
ALTER TABLE ORDERS
ADD PRIMARY KEY (O_ORDERKEY);
COMMIT WORK;
-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD FOREIGN KEY (PS_SUPPKEY) references SUPPLIER;
COMMIT WORK;
ALTER TABLE PARTSUPP
ADD FOREIGN KEY (PS_PARTKEY) references PART;
COMMIT WORK;
-- For table ORDERS
ALTER TABLE ORDERS
ADD FOREIGN KEY (O_CUSTKEY) references CUSTOMER;
COMMIT WORK;
-- For table LINEITEM
ALTER TABLE LINEITEM
ADD FOREIGN KEY (L_ORDERKEY) references ORDERS;
COMMIT WORK;
ALTER TABLE LINEITEM
ADD FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references PARTSUPP;
COMMIT WORK;
Mysql 添加主键、外键:
我这里按网上的教材修改添加主键外键的命令老是报错,最后我用了效率最低的办法自己手动添加了外键跟主键(难过)
通过 dbgen/queries 目录下会生成 22 条 sql 查询语句,我们借助这 22 条 sql 语句用来做性能测试的查询操作。不过这 22 条语句需要修改下里面的一些参数才能用。
可以借助下面文档说明来修改。
https://wenku.baidu.com/view/024e682cbd64783e09122bf4.html
最后就是编写 jmeter 脚本了,这里就忽略了,可以搜索 jmeter 连接 mysql、oracle、pg 的教程来编写。