回复
【我和openGauss的故事】利用Hybench对openGauss5.0.0进行压测
老老老JR老北
发布于 2023-8-29 15:50
浏览
0收藏
2023年3月30日，openGauss发布了第三个LTS版openGauss 5.0.0，该版本生命周期为3年。这次发布，修复了已知的BUG，并对功能做了不少增强。
Hybench是一款由中国软件评测中心、清华大学联合牵头，北京奥星贝斯科技有限公司、武汉达梦数据库股份有限公司、华为技术有限公司、腾讯云计算有限公司、阿里云计算有限公司共同研发的HTAP数据库基准测试工具。提供OLTP、OLAP、OLXP三类典型HTAP负载，支持不同规模的数据集，可以计算出TPS、QPS、XPS、新鲜度等不同维度的评价指标，最终给出统一评价指标H-Score。
一、环境准备
现在就尝试用Hybench测一下新版的openGauss 5.0.0。操作系统选用openEuler 2203lts sp2,对安装过程稍作记录。
操作系统按openGauss建议，安装所需要软件包，并复制一台，共装一主一备，IP地址分别是192.168.56.10,192.168.56.11
openGauss官网下载openGauss5.0.0的安装包
安装过程与其他版本一样，不再记录。集群启动后状态
[omm@db1 ~]$ gs_om -t status --detail
[ Cluster State ]
cluster_state : Normal
redistributing : No
current_az : AZ_ALL
[ Datanode State ]
nodenode_ip port instance state
------------------------------------------------------------------------------------------
1 db1 192.168.56.10 15400 6001 /opt/huawei/install/data/dn P Primary Normal
2 db2 192.168.56.11 15400 6002 /opt/huawei/install/data/dn S Standby Normal
tpcc=> select version();
version
------------------------------------------------------------------------------------------------------------------------------------------------------
(openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:37:13 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)hybench用到JDK17，先下载安装JDK17和maven
确认JDK和maven的版本
java -version
openjdk version "17.0.6" 2023-01-17
OpenJDK Runtime Environment BiSheng (build 17.0.6+11)
OpenJDK 64-Bit Server VM BiSheng (build 17.0.6+11, mixed mode, sharing)
mvn -v
Apache Maven 3.8.5 (3599d3414f046de2324203b78ddcf9b5e4388aa0)
Maven home: /opt/apache-maven-3.8.5
Java version: 17.0.6, vendor: BiSheng, runtime: /opt/bisheng-jdk-17.0.6
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "5.4.17-2136.308.9.el7uek.x86_64", arch: "amd64", family: "unix"下载Hybench
git clone https://gitee.com/cstc2023/hybench.git
cd hybench
mvn clean packagechmod +x hybench
二、创建测试库
openGauss=# create user tpcc identified by 'tpcc@110';
CREATE ROLE
openGauss=# create database tpcc dbcompatibility='A' owner tpcc;
CREATE DATABASE
openGauss=# grant all on database tpcc to tpcc;
GRANT
openGauss=# \c tpcc
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "tpcc" as user "omm".
tpcc=# grant all on schema public to tpcc;
GRANT
tpcc=# create schema tpcc;
CREATE SCHEMA
tpcc=# \c tpcc tpcc
Password for user tpcc:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "tpcc" as user "tpcc".修改pg_hba.conf,tpcc用户可以远程登录
[omm@db1 ~]$ gsql -d tpcc -Utpcc -h 192.168.56.10 -p 15400 -Wtpcc@110 -r
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:37:13 commit 0 last mr )
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
Type "help" for help.编辑数据库连接文件
vim conf/og.props
#support mysql postgreSQL and oracle
db=postgreSQL
classname=org.opengauss.Driver
username=tpcc
password=tpcc@110
url=jdbc:opengauss://192.168.56.10:15400,192.168.56.11:15400/tpcc?useUnicode=true&characterEncoding=utf-8
url_ap=jdbc:opengauss://192.168.56.10:15400,192.168.56.11:15400/tpcc?useUnicode=true&characterEncoding=utf-8
#classname_ap=org.opengauss.Driver
#username_ap=xxxx
#password_ap=xxxx
## support 1x, 10x
sf=1x
at1_percent=35
at2_percent=25
at3_percent=15
at4_percent=15
at5_percent=7
at6_percent=3
apclient=1
tpclient=1
fresh_interval=20
contention_num=100
apRunMins=1
tpRunMins=1
xpRunMins=1
xtpclient=1
xapclient=1
apround=1生成数据表./hybench -t sql -f conf/ddl_og.sql -c conf/og.props共8张表生成测试数据./hybench -t gendata -f conf/ddl_og.sql,默认生成1G数据，目前有1G和10G可选。占用硬盘空间du -sh517M用hybench导入数据到数据库中时，只有mysql顺利导入，PG(openGauss),oracle均不能完成。
将生成测试数据的文件夹给openGauss用户权限，从数据库导入
tpcc=> \copy company from 'Data_1x/company.csv' CSV DELIMITER ',' ;
tpcc=>
tpcc=> \copy transfer from 'Data_1x/transfer.csv' CSV DELIMITER ',' ;
tpcc=>
tpcc=> \copy checking from 'Data_1x/checking.csv' CSV DELIMITER ',' ;
tpcc=>
tpcc=> \copy checkingAccount from 'Data_1x/checkingAccount.csv' CSV DELIMITER ',' ;
tpcc=>
tpcc=> \copy savingAccount from 'Data_1x/savingAccount.csv' CSV DELIMITER ',' ;
tpcc=>
tpcc=> \copy loanApps from 'Data_1x/loanApps.csv' CSV DELIMITER ',' ;
tpcc=>
tpcc=> \copy loanTrans from 'Data_1x/loanTrans.csv' CSV DELIMITER ',' ;
tpcc=> select count(1) from transfer;
count
---------
6000000给表加主键和生成序列
./hybench -t sql -f conf/ddl_og_afterload.sql -c conf/og.props
2023-07-09 11:25:52,090 main ERROR File contains an invalid element or attribute "level"
2023-07-09 11:25:52 [main] INFO HyBench:324 - Hi~Bench, HyBench
2023-07-09 11:25:52 [main] INFO ConfigLoader:57 - ===============configuration==================
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - xapclient = 1
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - at2_percent = 25
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - at1_percent = 35
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - apclient = 1
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - xpRunMins = 1
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - at4_percent = 15
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - apround = 1
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - url = jdbc:opengauss://192.168.56.10:15400,192.168.56.11:15400/tpcc?useUnicode=true&characterEncoding=utf-8
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - tpRunMins = 1
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - at3_percent = 15
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - contention_num = 100
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - password = tpcc@110
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - tpclient = 1
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - fresh_interval = 20
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - sf = 1x
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - classname = org.opengauss.Driver
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - xtpclient = 1
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - apRunMins = 1
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - at5_percent = 7
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - db = postgreSQL
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - username = tpcc
2023-07-09 11:25:52 [main] INFO ConfigLoader:59 - at6_percent = 3
2023-07-09 11:25:52 [main] INFO ConfigLoader:61 - ===============configuration==================
2023-07-09 11:25:52 [main] INFO ConfigLoader:62 -
Jul 09, 2023 11:25:52 AM org.opengauss.core.v3.ConnectionFactoryImpl openConnectionImpl
INFO: [231d9f8a-c57c-4c21-9329-26b7ac2ada43] Try to connect. IP: 192.168.56.10:15400
Jul 09, 2023 11:25:52 AM org.opengauss.core.v3.ConnectionFactoryImpl openConnectionImpl
INFO: [192.168.56.10:53314/192.168.56.10:15400] Connection is established. ID: 231d9f8a-c57c-4c21-9329-26b7ac2ada43
Jul 09, 2023 11:25:52 AM org.opengauss.core.v3.ConnectionFactoryImpl openConnectionImpl
INFO: Connect complete. ID: 231d9f8a-c57c-4c21-9329-26b7ac2ada43
2023-07-09 11:25:52 [main] INFO ExecSQL:58 - execute query:alter table customer add constraint customer_pkey primary key (custID);
2023-07-09 11:25:52 [main] INFO ExecSQL:58 - execute query:alter table company add constraint company_pkey primary key (companyID);
2023-07-09 11:25:52 [main] INFO ExecSQL:58 - execute query:alter table savingAccount add constraint savingAccount_pkey primary key (accountID);
2023-07-09 11:25:52 [main] INFO ExecSQL:58 - execute query:alter table checkingAccount add constraint checkingAccount_pkey primary key (accountID);
2023-07-09 11:25:52 [main] INFO ExecSQL:58 - execute query:alter table transfer add constraint transfer_pkey primary key (id);
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:alter table checking add constraint checking_pkey primary key (id);
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:alter table loanapps add constraint loanapps_pkey primary key (id);
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:alter table loantrans add constraint loantrans_pkey primary key (id);
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:CREATE SEQUENCE transfer_id_seq;
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:SELECT SETVAL('transfer_id_seq', (SELECT max(id) FROM transfer));
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:ALTER TABLE transfer ALTER COLUMN id SET DEFAULT nextval('transfer_id_seq'::regclass);
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:ALTER SEQUENCE transfer_id_seq OWNED BY transfer.id;
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:CREATE SEQUENCE checking_id_seq;
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:SELECT SETVAL('checking_id_seq', (SELECT max(id) FROM checking));
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:ALTER TABLE checking ALTER COLUMN id SET DEFAULT nextval('checking_id_seq'::regclass);
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:ALTER SEQUENCE checking_id_seq OWNED BY checking.id;
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:CREATE SEQUENCE loanapps_id_seq;
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:SELECT SETVAL('loanapps_id_seq', (SELECT max(id) FROM loanapps));
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:ALTER TABLE loanapps ALTER COLUMN id SET DEFAULT nextval('loanapps_id_seq'::regclass);
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:ALTER SEQUENCE loanapps_id_seq OWNED BY loanapps.id;
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:CREATE SEQUENCE loantrans_id_seq;
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:SELECT SETVAL('loantrans_id_seq', (SELECT max(id) FROM loantrans));
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:ALTER TABLE loantrans ALTER COLUMN id SET DEFAULT nextval('loantrans_id_seq'::regclass);
2023-07-09 11:25:54 [main] INFO ExecSQL:58 - execute query:ALTER SEQUENCE loantrans_id_seq OWNED BY loantrans.id;建索引，脚本中重复的建序列语句，可以删除或忽略./hybench -t sql -f conf/create_index_pg.sql -c conf/og.props
三、测试
./hybench -t runall -f conf/stmt_opengauss.toml -c conf/og.props
====================Test Summary========================
Test starts at 2023-07-09 11:36:27
Test ends at 2023-07-09 11:37:27
AP Concurrency is 1
TP Concurrency is 1
Total amount of TP Transaction is 4311
TPS is 71.85
Total amount of AP Queries is 455
QPS is 7.41
XP-IQ Concurrency is 1
XP-AT Concurrency is 2
Total amount of XP-IQ Queries is 757
Total amount of XP-AT Transaction is 1382
Fresh-XP-QPS is 12.62
Fresh-XP-TPS is 23.03
Query/Transaction response time(ms) histogram :
------------AP-------------------
AP Query 1 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.37 | 95% rt : 1.00 | 99% rt : 1.00
AP Query 2 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.31 | 95% rt : 1.00 | 99% rt : 1.00
AP Query 3 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.51 | 95% rt : 1.00 | 99% rt : 1.00
AP Query 4 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.26 | 95% rt : 1.00 | 99% rt : 1.00
AP Query 5 : max rt : 2.00 | min rt : 1.00 | avg rt : 1.09 | 95% rt : 2.00 | 99% rt : 2.00
AP Query 6 : max rt : 15.00 | min rt : 10.00 | avg rt : 12.14 | 95% rt : 14.20 | 99% rt : 15.00
AP Query 7 : max rt : 6.00 | min rt : 3.00 | avg rt : 3.54 | 95% rt : 5.20 | 99% rt : 6.00
AP Query 8 : max rt : 41.00 | min rt : 33.00 | avg rt : 34.34 | 95% rt : 37.80 | 99% rt : 41.00
AP Query 9 : max rt : 159.00 | min rt : 127.00 | avg rt : 130.34 | 95% rt : 139.80 | 99% rt : 159.00
AP Query 10 : max rt : 878.00 | min rt : 859.00 | avg rt : 867.97 | 95% rt : 878.00 | 99% rt : 878.00
AP Query 11 : max rt : 69.00 | min rt : 44.00 | avg rt : 47.31 | 95% rt : 55.40 | 99% rt : 69.00
AP Query 12 : max rt : 834.00 | min rt : 335.00 | avg rt : 655.20 | 95% rt : 833.20 | 99% rt : 834.00
AP Query 13 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.34 | 95% rt : 1.00 | 99% rt : 1.00
------------TP-------------------
TP Transaction 1 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.13 | 95% rt : 1.00 | 99% rt : 1.00
TP Transaction 2 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.20 | 95% rt : 1.00 | 99% rt : 1.00
TP Transaction 3 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.20 | 95% rt : 1.00 | 99% rt : 1.00
TP Transaction 4 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.19 | 95% rt : 1.00 | 99% rt : 1.00
TP Transaction 5 : max rt : 2.00 | min rt : 0.00 | avg rt : 0.47 | 95% rt : 1.00 | 99% rt : 1.53
TP Transaction 6 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.27 | 95% rt : 1.00 | 99% rt : 1.00
TP Transaction 7 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.22 | 95% rt : 1.00 | 99% rt : 1.00
TP Transaction 8 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.23 | 95% rt : 1.00 | 99% rt : 1.00
TP Transaction 9 : max rt : 2.00 | min rt : 0.00 | avg rt : 0.61 | 95% rt : 1.00 | 99% rt : 1.00
TP Transaction 10 : max rt : 12.00 | min rt : 7.00 | avg rt : 8.19 | 95% rt : 9.00 | 99% rt : 10.00
TP Transaction 11 : max rt : 2.00 | min rt : 0.00 | avg rt : 0.67 | 95% rt : 1.00 | 99% rt : 1.18
TP Transaction 12 : max rt : 2.00 | min rt : 0.00 | avg rt : 0.49 | 95% rt : 1.00 | 99% rt : 1.00
TP Transaction 13 : max rt : 105.00 | min rt : 87.00 | avg rt : 88.70 | 95% rt : 91.00 | 99% rt : 96.80
TP Transaction 14 : max rt : 98.00 | min rt : 94.00 | avg rt : 95.17 | 95% rt : 97.00 | 99% rt : 98.00
TP Transaction 15 : max rt : 64.00 | min rt : 56.00 | avg rt : 57.45 | 95% rt : 59.00 | 99% rt : 62.44
TP Transaction 16 : max rt : 65.00 | min rt : 56.00 | avg rt : 57.80 | 95% rt : 59.60 | 99% rt : 63.56
TP Transaction 17 : max rt : 2.00 | min rt : 0.00 | avg rt : 0.52 | 95% rt : 1.00 | 99% rt : 1.00
TP Transaction 18 : max rt : 2.00 | min rt : 0.00 | avg rt : 0.54 | 95% rt : 1.00 | 99% rt : 1.00
-----------XP-IQ--------------------
Interative Query 1 : max rt : 2.00 | min rt : 0.00 | avg rt : 0.45 | 95% rt : 1.00 | 99% rt : 1.49
Interative Query 2 : max rt : 3.00 | min rt : 0.00 | avg rt : 0.90 | 95% rt : 2.00 | 99% rt : 2.33
Interative Query 3 : max rt : 4.00 | min rt : 1.00 | avg rt : 2.24 | 95% rt : 3.00 | 99% rt : 4.00
Interative Query 4 : max rt : 4.00 | min rt : 1.00 | avg rt : 2.45 | 95% rt : 3.00 | 99% rt : 3.50
Interative Query 5 : max rt : 5.00 | min rt : 0.00 | avg rt : 0.49 | 95% rt : 1.00 | 99% rt : 4.40
Interative Query 6 : max rt : 315.00 | min rt : 221.00 | avg rt : 256.21 | 95% rt : 278.20 | 99% rt : 306.60
-----------XP-AT--------------------
Analytical Transaction AT1 : max rt : 3.00 | min rt : 1.00 | avg rt : 1.48 | 95% rt : 2.00 | 99% rt : 2.00
Analytical Transaction AT2 : max rt : 3.00 | min rt : 0.00 | avg rt : 1.32 | 95% rt : 2.00 | 99% rt : 2.00
Analytical Transaction AT3 : max rt : 4.00 | min rt : 1.00 | avg rt : 1.81 | 95% rt : 3.00 | 99% rt : 3.00
Analytical Transaction AT4 : max rt : 4.00 | min rt : 1.00 | avg rt : 2.47 | 95% rt : 4.00 | 99% rt : 4.00
Analytical Transaction AT5 : max rt : 1.00 | min rt : 0.00 | avg rt : 0.48 | 95% rt : 1.00 | 99% rt : 1.00
Analytical Transaction AT6 : max rt : 2.00 | min rt : 1.00 | avg rt : 1.24 | 95% rt : 2.00 | 99% rt : 2.00
-----------HTAP-Summary--------------------
-----------AP-Part--------------------
QPS : 7.41
-----------TP-Part--------------------
TPS : 71.85
-----------XP-Part--------------------
XP-QPS : 12.62
XP-TPS : 23.03
-----------Avg-Freshness-Score--------------------
Freshness(ms) : 0.06
-----------HTAP-Score--------------------
Geometric Mean : 26.67
====================Thank you!========================
2023-07-09 11:37:27 [Thread-2] INFO Freshness:61 - Current freshness : 0
2023-07-09 11:37:27 [Thread-2] INFO HyBench:248 - Freshness checker was stopped in forceQPS 7.41，TPS71.85hybench刚发布，对每项参数理解不深，期望这个工具越来越好。
文章转载自公众号：openGauss
分类
标签
已于2023-8-29 15:50:32修改
赞
收藏
回复
相关推荐
