前言
隨著國產數據庫的蓬勃發展以及信創工作的推進，我也逐漸開始涉獵國產數據庫，之前對pg的了解甚少，學習了幾個SQL命令，我們都知道了解一款數據庫光做功能性的演示是遠遠不夠的。本文通過利用benchmarkSQL對mogdb進行演示測試的步驟，希望能夠加深對mogdb的了解。
根據官方文檔介紹的測試環境，本文中涉及的TPC-C測試將使用BenchmarkSQL5.0 軟件實現。BenchmarkSQL是一款基于JDBC實現的類似于OLTP的TPC-C標準測試工具，目前支持的數據庫如：PostgreSQL、Oracle等。由于openGauss的接口與PostgreSQL兼容性較好， MogDB正好是基于openGauss的，所以也可以使用該工具對MogDB數據庫進行TPC-C測試。
環境信息
| 數據庫服務器 | 壓力發起機 | |
|---|---|---|
| CPU | 2C | 2C |
| 內存 | 4G | 4G |
| 硬盤 | 40G | 20G |
| IP | 11.0.1.3 | 11.0.1.4 |
利用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=PATH
export CLASSPATH=.: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，需要較高的硬件配置和優化。
