本文主要分享 openGauss 的单机部署过程，总体感觉非常方便。
openGauss 数据库简介
openGauss是一款开源关系型数据库管理系统，采用木兰宽松许可证v2发行。openGauss内核深度融合华为在数据库领域多年的经验，结合企业级场景需求，持续构建竞争力特性。
openGauss 企业版下载
openGauss下载地址：https://opengauss.org/zh/download.html 。这里选择 Centos x86_64，openGuass 2.1 企业版本。
wget https://opengauss.obs.cn-south-1.myhuaweicloud.com/2.1.0/x86/openGauss-2.1.0-CentOS-64bit-all.tar.gz
mkdir -p /opt/software/openGauss
chmod 755 /opt/software/
tar zxvfopenGauss-2.1.0-CentOS-64bit-all.tar.gz -C /opt/software/openGauss
openGauss-2.1.0-CentOS-64bit-om.tar.gz
openGauss-2.1.0-CentOS-64bit.tar.bz2
openGauss-2.1.0-CentOS-64bit-om.sha256
openGauss-2.1.0-CentOS-64bit.sha256
upgrade_sql.tar.gz
upgrade_sql.sha256
tar zxvfopenGauss-2.1.0-CentOS-64bit-om.tar.gz
openGauss 部署
企业版安装文档参考：https://opengauss.org/zh/docs/2.1.0/docs/installation/%E5%AE%89%E8%A3%85%E5%87%86%E5%A4%87.html
这里只展示关键的一些步骤。
环境准备
硬件
操作系统
- 关闭SELINUX
vim /etc/selinux/configSELINUX=disabled
--命令行下关闭# setenforce 0
- 关闭防火墙
systemctl disable firewalld.servicesystemctl stop firewalld.service
软件包
yum install -y zlib-develbzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-develgcc make yum -y install libaio-devel flex bisonncurses-devel glibc-devel patch redhat-lsb-core readline-devel
用户（可选）
可以手动创建用户，也可以在后面脚本里自动创建。建议手动创建，并打通 root到该用户的ssh免密通道。 groupadd dbgrpuseradd omm -g dbgrp -G dbgrp
安装openGauss
安装配置文件
配置文件（xml）指定安装的基本信息：集群名、目录、服务器等。这里安装的是单机版的openGauss，只有一台机器。cd /opt/software/openGaussvim sfx_cluster_config.xml<?xml version="1.0"encoding="utf-8"?><ROOT> <CLUSTER> <PARAM name="clusterName" value="ogsfx" /> <PARAM name="nodeNames" value="sfx111188"/> <PARAM name="gaussdbAppPath"value="/opt/huawei/install/app" /> <PARAM name="gaussdbLogPath"value="/home/omm/log" /> <PARAM name="tmpMppdbPath"value="/data2/opengaussdb/tmp"/> <PARAM name="gaussdbToolPath" value="/opt/huawei/install/om"/> <PARAM name="corePath"value="/data2/opengaussdb/corefile"/> <PARAM name="backIp1s" value="192.168.111.188"/> </CLUSTER> <DEVICELIST> <DEVICE sn="sfx111188"> <PARAM name="name" value="sfx111188"/> <PARAM name="azName" value="AZ1"/> <PARAM name="azPriority" value="1"/> <PARAM name="backIp1" value="192.168.111.188"/> <PARAM name="sshIp1" value="192.168.111.188"/> <!-- dn --> <PARAM name="dataNum" value="1"/> <PARAM name="dataPortBase" value="15400"/> <PARAM name="dataNode1"value="/data2/opengaussdb/dn"/> <PARAM name="dataNode1_syncNum" value="0"/> <PARAM name="dataNodeXlogPath1"value="/data2/opengaussdb/gauss_xlog" /> </DEVICE> </DEVICELIST></ROOT>
初始化用户和数据库目录
root下用户执行。cd /opt/software/openGauss/script./gs_preinstall -U omm -G dbgrp -X ../sfx_cluster_config.xml
执行安装
在 omm 用户下执行安装，一步到位。su – ommgs_install -X /opt/software/openGauss/sfx_cluster_config.xml
安装成功后，可以查看监听15400成功。
也可以查看安装状态。通过 gs_om 操作数据库状态。[omm@sfx111188 ~]$ gs_om -t status----------------------------------------------------------------------- cluster_name : ogsfxcluster_state : Normalredistributing : No -----------------------------------------------------------------------[omm@sfx111188 ~]$ gs_om -t stopStopping cluster.=========================================Successfully stopped cluster.=========================================End stop cluster.
目录说明
数据库参数修改
数据库参数这里选择使用命令行下修改，重启实例后生效。这里参数不一定是最优的，仅供参考。
gs_guc reload -N all -I all -c "random_page_cost = '2'"
gs_guc reload -N all -I all -c "max_parallel_workers_per_gather = '0'"
gs_guc reload -N all -I all -c "maintenance_work_mem = '2GB'"
gs_guc reload -N all -I all -c "wal_compression = on" --
gs_guc reload -N all -I all -c "checkpoint_completion_target = 0.9"
gs_guc reload -N all -I all -c "min_wal_size = '1GB'" ---
gs_guc reload -N all -I all -c "max_wal_size = '48GB'" ---
gs_guc reload -N all -I all -c "wal_keep_segments = 1000"
gs_guc reload -N all -I all -c "checkpoint_timeout='20min'"
gs_guc reload -N all -I all -c "log_statement = ddl"
gs_guc reload -N all -I all -c "effective_cache_size='90GB'"
gs_guc reload -N all -I all -c "checkpoint_segments=128"
gs_guc reload -N all -I all -c "password_encryption_type=0"
gs_guc reload -N all -I all -c "shared_buffers='40GB'"
gs_guc reload -N all -I all -c "max_process_memory='90GB'"
gs_guc reload -N all -I all -c "maintenance_work_mem='10GB'"
gs_guc reload -N all -I all -c "enable_wdr_snapshot=on"
gs_guc reload -N all -I all -c "autovacuum_max_workers=10"
BenchmarkSQL测试
数据库环境准备
新建用户和表空间
create user jack with sysadmin identifiedby 'gauss@123'; create tablespace tbs1 location '/data/opengaussdb/data/tbs1';create tablespace tbs2 location '/data/opengaussdb/data/tbs2';create database tpcc1w;
配置用户连接权限
下面命令设置用户的网络连接权限。gs_guc reload -N all -I all -h "host tpcc1w jack 192.168.0.0/16 sha256" 命令生效后会体现在数据目录下的配置文件 pg_hba.conf 里。
建表语句
可以手动建表，也可以通过BenchmarkSQL的脚本runSQL.sh 建表。下面的几个大表，指定了填充因子 FILLFACTOR 为50，以提升更新性能。底层 SSD 使用可计算存储CSD时，CSD内部有压缩，所以实际存储空间不会浪费。
create table bmsql_config ( cfg_name varchar(30) primarykey, cfg_value varchar(50)); create table bmsql_warehouse ( w_id integer notnull, w_ytd decimal(12,2), w_tax decimal(4,4), w_name varchar(10), w_street_1 varchar(20), w_street_2 varchar(20), w_city varchar(20), w_state char(2), w_zip char(9))WITH (FILLFACTOR=50); create table bmsql_district ( d_w_id integer notnull, d_id integer notnull, d_ytd decimal(12,2), d_tax decimal(4,4), d_next_o_id integer, d_name varchar(10), d_street_1 varchar(20), d_street_2 varchar(20), d_city varchar(20), d_state char(2), d_zip char(9))WITH (FILLFACTOR=50); create table bmsql_customer ( c_w_id integer notnull, c_d_id integer notnull, c_id integer notnull, c_discount decimal(4,4), c_credit char(2), c_last varchar(16), c_first varchar(16), c_credit_lim decimal(12,2), c_balance decimal(12,2), c_ytd_payment decimal(12,2), c_payment_cnt integer, c_delivery_cnt integer, c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_middle char(2), c_data varchar(500))WITH (FILLFACTOR=50) tablespace tbs1; create sequence bmsql_hist_id_seq; create table bmsql_history ( hist_id integer, h_c_id integer, h_c_d_id integer, h_c_w_id integer, h_d_id integer, h_w_id integer, h_date timestamp, h_amount decimal(6,2), h_data varchar(24))WITH (FILLFACTOR=50) tablespace tbs2; create table bmsql_new_order ( no_w_id integer notnull, no_d_id integer notnull, no_o_id integer notnull)WITH (FILLFACTOR=50) tablespace tbs1; create table bmsql_oorder ( o_w_id integer notnull, o_d_id integer notnull, o_id integer notnull, o_c_id integer, o_carrier_id integer, o_ol_cnt integer, o_all_local integer, o_entry_d timestamp)WITH (FILLFACTOR=50) tablespace tbs2; create table bmsql_order_line ( ol_w_id integer notnull, ol_d_id integer notnull, ol_o_id integer notnull, ol_number integer notnull, ol_i_id integer notnull, ol_delivery_d timestamp, ol_amount decimal(6,2), ol_supply_w_id integer, ol_quantity integer, ol_dist_info char(24))WITH (FILLFACTOR=50) tablespace tbs2; create table bmsql_item ( i_id integer notnull, i_name varchar(24), i_price decimal(5,2), i_data varchar(50), i_im_id integer) tablespace tbs1; create table bmsql_stock ( s_w_id integer notnull, s_i_id integer notnull, s_quantity integer, s_ytd integer, s_order_cnt integer, s_remote_cnt integer, s_data varchar(50), s_dist_01 char(24), s_dist_02 char(24), s_dist_03 char(24), s_dist_04 char(24), s_dist_05 char(24), s_dist_06 char(24), s_dist_07 char(24), s_dist_08 char(24), s_dist_09 char(24), s_dist_10 char(24))WITH (FILLFACTOR=50) tablespace tbs1;
BenchmarkSQL 安装
Yum源修改
为了方便，就使用华为云的Yum源。## 配置华为YUM源mkdir -p /etc/yum.repos.d/repo_bak/mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/repo_bak/wget -O /etc/yum.repos.d/CentOS-Base.repo https://repo.huaweicloud.com/repository/conf/CentOS-7-reg.repo ## 配置Epel源yum remove -y epel-releaseyum install -y https://repo.huaweicloud.com/epel/epel-release-latest-7.noarch.rpmcd /etc/yum.repos.d/rm -rf epel-testing.repo sed -i "s/#baseurl/baseurl/g" /etc/yum.repos.d/epel.reposed -i "s/mirrorlist/#mirrorlist/g" /etc/yum.repos.d/epel.reposed -i "s@http://download.fedoraproject.org/pub@https://repo.huaweicloud.com@g" /etc/yum.repos.d/epel.repo ## 顺刷新缓存yum clean allyum makecacheyum repolist all
安装依赖的软件包
yum install gcc glibc-headers gcc-c++gcc-gfortran readline-devel libXt-develpcre-devel libcurl libcurl-devel -yyum install ncurses ncurses-develautoconf automake zlib zlib-devel bzip2 bzip2-devel xz-devel -yyum install java-1.8.0-openjdk ant -y
替换BenchmarkSQL中的驱动
BenchmarkSQL 从github开源网站下载。openGauss的JDBC驱动从官网下载，将里面的postgresql.jar复制到~ /benchmarksql-5.0/lib/postgres/ 下。
编译安装
[root@sfx111188 ~]# cd /root/benchmarksql-5.0/[root@sfx111188 benchmarksql-5.0]# antBuildfile:/soft/benchmarksql-5.0/build.xmlinit: [mkdir] Created dir: /root/benchmarksql-5.0/buildcompile: [javac] Compiling 11 source files to /root/benchmarksql-5.0/builddist: [mkdir] Created dir: /root/benchmarksql-5.0/dist [jar] Building jar: /root/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jarBUILD SUCCESSFULTotal time: 2 seconds
BenchmarkSQL运行
BenchmarkSQL配置文件
§ 参数warehouses决定了数据量的规模，这里选择10000仓，数据总量大小大概在1.7T 左右。§ 参数loadWorkers 决定了初始化的并发数。这里根据实际机器内存选择。如果内存不大，并发设置的太大，BenchmarkSQL写入速度太快，很可能会碰到机器可用内存不足（也可能是openGauss的某个内存参数这里设置的不合理导致）。
[root@sfx111188run]# cat props.openGaussdb=postgresdriver=org.postgresql.Driverconn=jdbc:postgresql://127.1:15400/tpcc1w?prepareThreshold=1&batchMode=on&fetchsize=100user=jackpassword=gauss@123warehouses=10000loadWorkers=50terminals=1runTxnsPerTerminal=0runMins=30limitTxnsPerMin=0terminalWarehouseFixed=falsenewOrderWeight=45paymentWeight=43orderStatusWeight=4deliveryWeight=4stockLevelWeight=4resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tSosCollectorScript=./misc/os_collector_linux.pyosCollectorInterval=1 //osCollectorSSHAddr=//osCollectorDevices=
建表（可选）
如果前面手动建表了，这里就不用。sh runSQL.sh props.openGausssql.common/tableCreates.sql
初始化数据
sh runLoader.sh props.openGauss
参考
- opengauss.org
- BenchmarkSQL性能测试(openGauss) - 知乎 (zhihu.com)
- ScaleFlux正式加入openGauss社区【附测试报告】
