使用Benchmark对mogdb进行测试

前言

    随着国产数据库的蓬勃发展以及信创工作的推进，我也逐渐开始涉猎国产数据库，之前对pg的了解甚少，学习了几个SQL命令，我们都知道了解一款数据库光做功能性的演示是远远不够的。本文通过利用benchmarkSQL对mogdb进行演示测试的步骤，希望能够加深对mogdb的了解。

    根据官方文档介绍的测试环境，本文中涉及的TPC-C测试将使用BenchmarkSQL5.0 软件实现。BenchmarkSQL是一款基于JDBC实现的类似于OLTP的TPC-C标准测试工具，目前支持的数据库如：PostgreSQL、Oracle等。由于openGauss的接口与PostgreSQL兼容性较好， MogDB正好是基于openGauss的，所以也可以使用该工具对MogDB数据库进行TPC-C测试。

环境信息

利用PTK部署mogdb

A、安装PTK，需要服务器可以联网。

[root@db02 bin]# curl --proto ‘=https’ --tlsv1.2 -sSf https://cdn-mogdb.enmotech.com/ptk/install.sh | sh

Downloading ptk package…

Detected shell: bash

Shell profile: /root/.bash_profile

PATH already contains /root/.ptk/bin

Installed path: /root/.ptk/bin/ptk

B、修改配置文件，如果是单节点，默认配置就可以

[root@db02 ~]# cat config.yaml

# config.yaml

global:

cluster_name: mogdb1

user: omm

group: omm

base_dir: /opt/mogdb

db_servers:

- host: 127.0.0.1

db_port: 26000

C、执行检测

[root@db02 ~]# ptk checkos -f config.yaml             ===================Warning部分不用纠结

INFO[2022-08-30T09:46:54.522] prechecking dependent tools…

INFO[2022-08-30T09:46:54.535] platform: rhel_7.3_64bit host=11.0.1.3

INFO[2022-08-30T09:46:54.538] kernel version: 3.10.0-514.el7.x86_64 host=11.0.1.3

INFO[2022-08-30T09:46:54.574] locale: LANG=en_US.UTF-8 host=11.0.1.3

INFO[2022-08-30T09:46:54.578] timezone: +0800 host=11.0.1.3

INFO[2022-08-30T09:46:54.582] swap memory 0kB, total memory 3865552kB host=11.0.1.3

WARN[2022-08-30T09:46:54.597] net.core.netdev_max_backlog=1000, expect 65535 host=11.0.1.3

WARN[2022-08-30T09:46:54.604] net.ipv4.tcp_retries1=3, expect 5 host=11.0.1.3

WARN[2022-08-30T09:46:54.607] net.core.rmem_default=212992, expect 21299200 host=11.0.1.3

WARN[2022-08-30T09:46:54.610] net.ipv4.tcp_retries2=15, expect 12 host=11.0.1.3

WARN[2022-08-30T09:46:54.614] net.ipv4.tcp_max_syn_backlog=128, expect 65535 host=11.0.1.3

WARN[2022-08-30T09:46:54.625] net.ipv4.tcp_rmem=4096 87380 6291456, expect 8192 250000 16777216 host=11.0.1.3

WARN[2022-08-30T09:46:54.628] net.core.rmem_max=212992, expect 21299200 host=11.0.1.3

WARN[2022-08-30T09:46:54.636] net.ipv4.tcp_tw_recycle=0, expect 1 host=11.0.1.3

WARN[2022-08-30T09:46:54.639] net.core.wmem_max=212992, expect 21299200 host=11.0.1.3

WARN[2022-08-30T09:46:54.643] net.core.wmem_default=212992, expect 21299200 host=11.0.1.3

WARN[2022-08-30T09:46:54.647] net.ipv4.tcp_keepalive_intvl=75, expect 30 host=11.0.1.3

WARN[2022-08-30T09:46:54.651] net.ipv4.tcp_wmem=4096 16384 4194304, expect 8192 250000 16777216 host=11.0.1.3

WARN[2022-08-30T09:46:54.657] net.ipv4.tcp_syn_retries=6, expect 5 host=11.0.1.3

WARN[2022-08-30T09:46:54.664] net.ipv4.tcp_tw_reuse=0, expect 1 host=11.0.1.3

WARN[2022-08-30T09:46:54.667] net.ipv4.tcp_keepalive_time=7200, expect 30 host=11.0.1.3

WARN[2022-08-30T09:46:54.671] net.ipv4.tcp_syncookies=0, expect 1 host=11.0.1.3

WARN[2022-08-30T09:46:54.677] net.core.somaxconn=32768, expect 65535 host=11.0.1.3

WARN[2022-08-30T09:46:54.685] vm.min_free_kbytes=7862, expect 193277 host=11.0.1.3

WARN[2022-08-30T09:46:54.688] kernel.sem=250 32000 32 128, expect 250 6400000 1000 25600 host=11.0.1.3

INFO[2022-08-30T09:46:54.688] check kernel.core_pattern host=11.0.1.3

INFO[2022-08-30T09:46:54.695] check removeIPC value host=11.0.1.3

WARN[2022-08-30T09:46:54.721] device(/dev/sda) readahead value=256, expect 16384. host=11.0.1.3

WARN[2022-08-30T09:46:54.739] device(dm-0) ‘IO Request’=128, expect 256 host=11.0.1.3

WARN[2022-08-30T09:46:54.739] device(dm-1) ‘IO Request’=128, expect 256 host=11.0.1.3

WARN[2022-08-30T09:46:54.739] device(sda) ‘IO Request’=128, expect 256 host=11.0.1.3

WARN[2022-08-30T09:46:54.739] device(sr0) ‘IO Request’=128, expect 256 host=11.0.1.3

INFO[2022-08-30T09:46:57.099] check port 26000 host=11.0.1.3

INFO[2022-08-30T09:46:57.113] port 26000 is free host=11.0.1.3

INFO[2022-08-30T09:46:57.113] all checkers finished

# Check Results

Item | Level

------------------------------------±---------

A1.Check_OS_Version | OK

A2.Check_Kernel_Version | OK

A3.Check_Unicode | OK

A4.Check_TimeZone | OK

A5.Check_Swap_Memory_Configure | OK

A6.Check_SysCtl_Parameter | Warning

A7.Check_FileSystem_Configure | OK

A8.Check_Disk_Configure | OK

A9.Check_BlockDev_Configure | Warning

A9.Check_Logical_Block | OK

A10.Check_IO_Request | Warning

A10.Check_Asynchronous_IO_Request | OK

A10.Check_IO_Configure | OK

A11.Check_Network_Configure | OK

A12.Check_Time_Consistency | OK

A13.Check_Firewall_Service | OK

A14.Check_THP_Service | OK

A15.Check_Dependent_Package | OK

A16.Check_CPU_Instruction_Set | OK

A17.Check_Port | OK

Total count 20, abnormal count 0, warning count 3

D、执行安装，自动安装最新的mogdb

[root@db02 ~]# ptk install -f config.yaml

INFO[2022-08-30T09:47:24.972] no package specified, use online package: https://cdn-mogdb.enmotech.com/mogdb-media/3.0.1/MogDB-3.0.1-CentOS-x86_64.tar.gz

INFO[2022-08-30T09:47:24.972] start check operating system

INFO[2022-08-30T09:47:24.975] prechecking dependent tools…

INFO[2022-08-30T09:47:24.989] platform: rhel_7.3_64bit host=11.0.1.3

INFO[2022-08-30T09:47:24.992] kernel version: 3.10.0-514.el7.x86_64 host=11.0.1.3

INFO[2022-08-30T09:47:25.024] locale: LANG=en_US.UTF-8 host=11.0.1.3

INFO[2022-08-30T09:47:25.028] timezone: +0800 host=11.0.1.3

INFO[2022-08-30T09:47:25.031] swap memory 0kB, total memory 3865552kB host=11.0.1.3

WARN[2022-08-30T09:47:25.041] vm.min_free_kbytes=7862, expect 193277 host=11.0.1.3

WARN[2022-08-30T09:47:25.045] net.ipv4.tcp_keepalive_time=7200, expect 30 host=11.0.1.3

WARN[2022-08-30T09:47:25.048] net.ipv4.tcp_wmem=4096 16384 4194304, expect 8192 250000 16777216 host=11.0.1.3

WARN[2022-08-30T09:47:25.052] net.ipv4.tcp_max_syn_backlog=128, expect 65535 host=11.0.1.3

WARN[2022-08-30T09:47:25.056] net.ipv4.tcp_syn_retries=6, expect 5 host=11.0.1.3

WARN[2022-08-30T09:47:25.060] net.ipv4.tcp_rmem=4096 87380 6291456, expect 8192 250000 16777216 host=11.0.1.3

WARN[2022-08-30T09:47:25.067] net.core.netdev_max_backlog=1000, expect 65535 host=11.0.1.3

WARN[2022-08-30T09:47:25.071] net.core.rmem_max=212992, expect 21299200 host=11.0.1.3

WARN[2022-08-30T09:47:25.074] kernel.sem=250 32000 32 128, expect 250 6400000 1000 25600 host=11.0.1.3

WARN[2022-08-30T09:47:25.077] net.core.wmem_max=212992, expect 21299200 host=11.0.1.3

WARN[2022-08-30T09:47:25.091] net.ipv4.tcp_retries1=3, expect 5 host=11.0.1.3

WARN[2022-08-30T09:47:25.094] net.ipv4.tcp_syncookies=0, expect 1 host=11.0.1.3

WARN[2022-08-30T09:47:25.099] net.core.somaxconn=32768, expect 65535 host=11.0.1.3

WARN[2022-08-30T09:47:25.103] net.ipv4.tcp_tw_reuse=0, expect 1 host=11.0.1.3

WARN[2022-08-30T09:47:25.109] net.ipv4.tcp_tw_recycle=0, expect 1 host=11.0.1.3

WARN[2022-08-30T09:47:25.113] net.ipv4.tcp_retries2=15, expect 12 host=11.0.1.3

WARN[2022-08-30T09:47:25.124] net.core.rmem_default=212992, expect 21299200 host=11.0.1.3

WARN[2022-08-30T09:47:25.130] net.ipv4.tcp_keepalive_intvl=75, expect 30 host=11.0.1.3

WARN[2022-08-30T09:47:25.137] net.core.wmem_default=212992, expect 21299200 host=11.0.1.3

INFO[2022-08-30T09:47:25.140] check kernel.core_pattern host=11.0.1.3

INFO[2022-08-30T09:47:25.146] check removeIPC value host=11.0.1.3

WARN[2022-08-30T09:47:25.171] device(/dev/sda) readahead value=256, expect 16384. host=11.0.1.3

WARN[2022-08-30T09:47:25.190] device(dm-1) ‘IO Request’=128, expect 256 host=11.0.1.3

WARN[2022-08-30T09:47:25.190] device(sda) ‘IO Request’=128, expect 256 host=11.0.1.3

WARN[2022-08-30T09:47:25.190] device(sr0) ‘IO Request’=128, expect 256 host=11.0.1.3

WARN[2022-08-30T09:47:25.190] device(dm-0) ‘IO Request’=128, expect 256 host=11.0.1.3

INFO[2022-08-30T09:47:27.508] check port 26000 host=11.0.1.3

INFO[2022-08-30T09:47:27.521] port 26000 is free host=11.0.1.3

=============================

global:

cluster_name: mogdb1

user: omm

group: omm

app_dir: /opt/mogdb/app

data_dir: /opt/mogdb/data

log_dir: /opt/mogdb/log

tool_dir: /opt/mogdb/tool

tmp_dir: /opt/mogdb/tmp

db_servers:

- host: 11.0.1.3

db_port: 26000

role: primary

az_name: AZ1

az_priority: 1

=============================

Is cluster topo correct?[Y|Yes](default=N) y

INFO[2022-08-30T09:47:31.962] use cache /root/.ptk/cache/MogDB-3.0.1-CentOS-x86_64.tar.gz

INFO[2022-08-30T09:47:33.383] validation: installation package is safe

INFO[2022-08-30T09:47:33.383] parse version.cfg from MogDB-3.0.1-CentOS-64bit.tar.gz

INFO[2022-08-30T09:47:35.222] detected db version: MogDB-3.0.1, number: 92.605, commit_id: 1a363ea9

INFO[2022-08-30T09:47:35.230] [stage=precheck]: start host=11.0.1.3

INFO[2022-08-30T09:47:35.230] check user ‘omm’ host=11.0.1.3

INFO[2022-08-30T09:47:35.234] [stage=precheck]: successful host=11.0.1.3

Please enter db password (8~16 characters):

Retype db password:

INFO[2022-08-30T09:47:44.972] scp file from /root/.ptk/cache/MogDB-3.0.1-CentOS-x86_64.tar.gz to 11.0.1.3:/tmp/MogDB-3.0.1-CentOS-x86_64.tar.gz host=11.0.1.3

INFO[2022-08-30T09:47:45.122] [stage=initial]: start host=11.0.1.3

INFO[2022-08-30T09:47:45.127] total memory(3GB) is less then 4GB, use default guc config host=11.0.1.3

INFO[2022-08-30T09:47:45.127] create os user omm, group omm host=11.0.1.3

INFO[2022-08-30T09:47:45.145] set ulimits host=11.0.1.3

INFO[2022-08-30T09:47:45.149] set user omm profiles host=11.0.1.3

INFO[2022-08-30T09:47:45.193] add c library /usr/local/lib to /etc/ld.so.conf.d/libc.conf host=11.0.1.3

INFO[2022-08-30T09:47:45.198] mkdir /opt/mogdb/tool host=11.0.1.3

INFO[2022-08-30T09:47:45.289] decompress MogDB-3.0.1-CentOS-x86_64.tar.gz to dir /opt/mogdb/tool host=11.0.1.3

INFO[2022-08-30T09:47:46.218] remove files /tmp/MogDB-3.0.1-CentOS-x86_64.tar.gz host=11.0.1.3

INFO[2022-08-30T09:47:46.246] decompress *-om.tar.gz to dir /opt/mogdb/tool host=11.0.1.3

INFO[2022-08-30T09:47:46.631] fix psutil lib host=11.0.1.3

INFO[2022-08-30T09:47:46.647] change /opt/mogdb/tool owner to omm host=11.0.1.3

INFO[2022-08-30T09:47:46.655] mkdir /opt/mogdb/app host=11.0.1.3

INFO[2022-08-30T09:47:46.761] decompress MogDB-3.0.1-CentOS-64bit.tar.gz to dir /opt/mogdb/app host=11.0.1.3

INFO[2022-08-30T09:47:49.314] fix dynamic library for gs_initdb host=11.0.1.3

INFO[2022-08-30T09:47:49.373] fix dynamic library for mogdb host=11.0.1.3

INFO[2022-08-30T09:47:49.430] change /opt/mogdb/app owner to omm host=11.0.1.3

INFO[2022-08-30T09:47:49.436] mkdir /opt/mogdb/log/gs_profile,/opt/mogdb/log/pg_log,/opt/mogdb/log/pg_audit,/opt/mogdb/log/bin,/opt/mogdb/log/pg_log/dn_6001,/opt/mogdb/log/pg_audit/dn_6001 host=11.0.1.3

INFO[2022-08-30T09:47:50.220] mkdir /opt/mogdb/tmp host=11.0.1.3

INFO[2022-08-30T09:47:50.307] save version to /opt/mogdb/app/bin/upgrade_version host=11.0.1.3

INFO[2022-08-30T09:47:50.349] create cluster_manual_start file host=11.0.1.3

INFO[2022-08-30T09:47:50.391] generate static config to /opt/mogdb/app/bin/cluster_static_config host=11.0.1.3

INFO[2022-08-30T09:47:50.397] change /opt/mogdb/app/bin/cluster_static_config owner to omm host=11.0.1.3

INFO[2022-08-30T09:47:50.400] mkdir /opt/mogdb/data host=11.0.1.3

INFO[2022-08-30T09:47:50.488] change /opt/mogdb/data owner to omm host=11.0.1.3

INFO[2022-08-30T09:47:50.492] initial database host=11.0.1.3

INFO[2022-08-30T09:48:07.004] set 11.0.1.3 postgresql.conf host=11.0.1.3

INFO[2022-08-30T09:48:07.062] set 11.0.1.3 hba config host=11.0.1.3

INFO[2022-08-30T09:48:07.115] [stage=initial]: successful host=11.0.1.3

INFO[2022-08-30T09:48:07.115] [stage=launch]: start host=11.0.1.3

INFO[2022-08-30T09:48:07.115] start 11.0.1.3 database by gs_ctl host=11.0.1.3

INFO[2022-08-30T09:48:10.374] alter initial user password host=11.0.1.3

INFO[2022-08-30T09:48:10.625] [stage=launch]: successful host=11.0.1.3

INFO[2022-08-30T09:48:10.626] Time elapsed: 46s

cluste_name | host | user | port | stage | status | message

--------------±---------±-----±------±-------±--------------±---------

mogdb1 | 11.0.1.3 | omm | 26000 | launch | start_success | success

D、查看集群状态

二、部署benchmark

A、配置benchmarksql 5.0

1)、下载benchmark，本文利用的是benchmarksql 5.0

https://blog.opengauss.org/zh/post/optimize/images/benchmarksql-5.0.zip

2)、下载ant，选择1.9版本二进制的即可，用来编译benchmarksql。

https://ant.apache.org/bindownload.cgi

3）、编译benchmarksql。

B、下载jdk-8u341-linux-x64.tar，并解压，配置环境变量

下载连接：https://www.oracle.com/java/technologies/javase/javase-jdk8-downloads.html

解压到/usr/local目录下

在/etc/profile里添加以下内容：

export JAVA_HOME=/usr/local/jdk1.8.0_341

export PATH=$JAVA\_HOME/bin:$PATH

export CLASSPATH=.:$JAVA\_HOME/lib/dt.jar:$JAVA_HOME/lib/tool.jar

C、下载jdb驱动包

https://docs.mogdb.io/zh/mogdb/v3.0/java）：=====》openGauss-3.0.0-JDBC.tar.gz，放到目录/benchmarksql-5.0/lib/postgres下，并解压。

D、修改props.pg文件，

重要参数如下：

db ============🡺db类型，选择postgres

Driver==========🡺驱动类型

Conn===========🡺连接串

User============🡺数据库用户

Password========🡺数据库用户密码

Warehouse=======🡺测试数据仓的个数，一个仓库数据量大概70M左右，

E、在数据库中建测试用户

create user benchmarksql identified by ‘Benchmarksql123’ profile default;

alter user benchmarksql sysadmin;

grant all privilege to benchmarksql;

create database benchmarksql ;

三、灌入测试数据

在benchmark的run目录下，执行./runDatabaseBuild.sh props.pg

运行过程中遇到的问题：

1、远程连接数据库报错：FATAL: no pg_hba.conf entry for host

修改/opt/mogdb/data/pg_hba.conf目录中的配置文件pg_hba.conf，将远程请求连接的机器ip添加到“# IPv4 local connections:”

2、远程连接数据库报错：“Forbid remote connection with trust method!”

修改/opt/mogdb/data/pg_hba.conf目录中的配置文件pg_hba.conf，将远程请求连接的机器ip的认证方式由trust改为sha256的认证方式

四、执行测试

在benchmark的run目录下，执行./runBenchmark.sh props.pg

五、小结

1、利用ptk工具部署mogdb还是挺方便的，基本上属于一键部署。

2、我一开始用的容器版的，镜像下载下来以后，拉不起来，报错也没找到是啥原因。

3、以上测试是在本机测试环境，如果想要达到比较高的tpm，需要较高的硬件配置和优化。