瀚高数据库在我好多年前预学习PostgreSQL时就有耳闻，有瀚高公司维护，该公司在PostgreSQL国际社区贡献度中也是中国排名靠前的组织，发起了另一个开源项目IvorySQL，同样是兼容oracle的postgresql数据库，两者都是在PostgreSQL路线做了大量的创新与生态工具，可能因为一些非技术因素一直不温不火，我记录体验一把HighGo企业版。

HighGo企业版 V6  on CentOS7，  下载介质可以去官方申请。整体安装还是比较方便(1小时内)。

安装测试

[root@localhost ~]# ls -lrt
total 323832
...
-rw-r--r--   1 root   root    70655836 Oct 29 05:50 1b73693f2c91fd7f38acbc7d3882cf6c.rpm

[root@localhost ~]# mv 1b73693f2c91fd7f38acbc7d3882cf6c.rpm highgov6.rpm
[root@localhost ~]# rpm -ivh highgov6.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:hgdb-enterprise-6.0.4-1.el7.cento################################# [100%]
无遗留数据文件
Created symlink from /etc/systemd/system/multi-user.target.wants/hgdb-enterprise-6.0.4.service to /usr/lib/systemd/system/hgdb-enterprise-6.0.4.service.
Created symlink from /etc/systemd/system/graphical.target.wants/hgdb-enterprise-6.0.4.service to /usr/lib/systemd/system/hgdb-enterprise-6.0.4.service.

[root@localhost ~]# systemctl -a|grep hgdb
  hgdb-enterprise-6.0.4.service                                                                                  loaded    inactive dead      highgodb-6.0.4

文件为70M左右的RPM， 到这里软件就安装完成，同时创建了system serivce， 这里吐槽一下，官方文档安装手册都没有V6，还停留在4.5，已经发生一些改动。这里盲猜安装。

[root@localhost ~]# systemctl start hgdb-enterprise-6.0.4.service
[root@localhost ~]# systemctl status hgdb-enterprise-6.0.4.service
● hgdb-enterprise-6.0.4.service - highgodb-6.0.4
   Loaded: loaded (/usr/lib/systemd/system/hgdb-enterprise-6.0.4.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Sun 2023-10-29 05:53:10 EDT; 8s ago
  Process: 2289 ExecStop=/opt/HighGo6.0.4-cluster/etc/hgdb-enterprise-6.0.4 stop (code=exited, status=0/SUCCESS)
  Process: 2286 ExecStart=/opt/HighGo6.0.4-cluster/etc/hgdb-enterprise-6.0.4 start (code=exited, status=0/SUCCESS)
  Process: 2285 ExecStartPre=/usr/bin/sleep 30 (code=exited, status=0/SUCCESS)

Oct 29 05:52:40 localhost.localdomain systemd[1]: Starting highgodb-6.0.4...
Oct 29 05:53:10 localhost.localdomain hgdb-enterprise-6.0.4[2286]: [2023-10-29 05:53:10] [ERROR] following errors were found in the configuration file:
Oct 29 05:53:10 localhost.localdomain hgdb-enterprise-6.0.4[2286]: "node_id": required parameter was not found
Oct 29 05:53:10 localhost.localdomain hgdb-enterprise-6.0.4[2286]: "node_name": required parameter was not found
Oct 29 05:53:10 localhost.localdomain hgdb-enterprise-6.0.4[2286]: "data_directory": required parameter was not found
Oct 29 05:53:10 localhost.localdomain hgdb-enterprise-6.0.4[2286]: "conninfo": required parameter was not found
Oct 29 05:53:10 localhost.localdomain hgdb-enterprise-6.0.4[2286]: [2023-10-29 05:53:10] [DETAIL] configuration file is: "/opt/HighGo6.0.4-cluster/conf/hg_repmgr.conf"
Oct 29 05:53:10 localhost.localdomain systemd[1]: Started highgodb-6.0.4.
[root@localhost ~]# more /etc/systemd/system/graphical.target.wants/hgdb-enterprise-6.0.4.service
[Unit]
Description=highgodb-6.0.4
Requires=network.target local-fs.target
After=network.target local-fs.target

[Service]
Type=forking
User=highgo

OOMScoreAdjust=-1000
ExecStartPre=/usr/bin/sleep 30
ExecStart=/opt/HighGo6.0.4-cluster/etc/hgdb-enterprise-6.0.4 start
ExecStop=/opt/HighGo6.0.4-cluster/etc/hgdb-enterprise-6.0.4 stop
TimeoutSec=1800

[Install]
WantedBy=multi-user.target graphical.target
[root@localhost ~]#

[root@localhost ~]# cd /opt/HighGo6.0.4-cluster/etc/
[root@localhost etc]# ls
desktop  hgdb-enterprise-6.0.4  hgdb-enterprise-6.0.4.cluster  hgdb-enterprise-6.0.4.service  hgdb-enterprise-6.0.4.single  highgodb.env  jdbc-forward  setup
[root@localhost etc]# cat highgodb.env
export HG_BASE=/opt/HighGo6.0.4-cluster
export HGDB_HOME=/opt/HighGo6.0.4-cluster
export PGPORT=5866
export PGDATABASE=highgo
export PGDATA=$HGDB_HOME/data
export PATH=$HGDB_HOME/bin:$PATH
[root@localhost etc]#

[highgo@localhost bin]$ ls
check_lic  createuser  ecpg     oid2name           pg_basebackup  pg_config       pg_dump     pg_receivewal   pg_restore  pgsql2shp      pg_test_timing  postgres    raster2pgsql  repmgrd      shp2pgsql
clusterdb  dropdb      hg_rman  pdr_report         pgbench        pg_controldata  pg_dumpall  pg_recvlogical  pg_rewind   pg_standby     pg_upgrade      postmaster  reindexdb     runipsql.sh  vacuumdb
createdb   dropuser    initdb   pg_archivecleanup  pg_checksums   pg_ctl          pg_isready  pg_resetwal     pg_rman     pg_test_fsync  pg_waldump      psql        repmgr        runpsql.sh   vacuumlo

这里直接启动service失败，根据错误提示，默认这是一个集群软件服务，使用了开源高可用repmgr产品，我本次只是在虚机安装体验一下功能，仅使用单实例即可，所以自带的service无法使用。软件安装HOME在/opt/HighGo6.0.4-cluster/

[root@localhost etc]# . highgodb.env
[root@localhost etc]# which initdb
/opt/HighGo6.0.4-cluster/bin/initdb

[root@localhost etc]# initdb
initdb: error: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
[root@localhost etc]#

[root@localhost etc]# tail  /etc/passwd
tss:x:59:59:Account used by the trousers package to sandbox the tcsd daemon:/dev/null:/sbin/nologin
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
zabbix:x:996:994:Zabbix Monitoring System:/var/lib/zabbix:/sbin/nologin
apache:x:48:48:Apache:/usr/share/httpd:/sbin/nologin
mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin
grafana:x:995:993:grafana user:/usr/share/grafana:/sbin/nologin
oprofile:x:16:16:Special user account to be used by OProfile:/var/lib/oprofile:/sbin/nologin
oracle:x:1000:1000::/home/oracle:/bin/bash
highgo:x:1001:1001::/home/highgo:/bin/bash

[root@localhost etc]# ll
total 24
drwxr-xr-x 2 highgo highgo   67 Oct 29 05:51 desktop
lrwxrwxrwx 1 highgo highgo   58 Oct 29 05:51 hgdb-enterprise-6.0.4 -> /opt/HighGo6.0.4-cluster/etc/hgdb-enterprise-6.0.4.cluster
-rwxr-xr-x 1 highgo highgo 3635 Jan 26  2022 hgdb-enterprise-6.0.4.cluster
-rw-r--r-- 1 highgo highgo  403 Jan 26  2022 hgdb-enterprise-6.0.4.service
-rwxr-xr-x 1 highgo highgo 4740 Jan 26  2022 hgdb-enterprise-6.0.4.single
-rw-r--r-- 1 highgo highgo  189 Jan 26  2022 highgodb.env
drwxr-xr-x 2 highgo highgo  112 Oct 29 05:51 jdbc-forward
-rwxr-xr-x 1 highgo highgo 1957 Jan 26  2022 setup

[root@localhost etc]# su - highgo
Last login: Sun Oct 29 06:01:31 EDT 2023 on pts/0


[highgo@localhost ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH
source /opt/HighGo6.0.4-cluster/etc/highgodb.env

软件不允许启动在ROOT用户下， 默认创建了highgo用户，同时配置了用户环境变量.

[root@localhost HighGo6.0.4-cluster]# echo highgo|passwd --stdin highgo
Changing password for user highgo.
passwd: all authentication tokens updated successfully.

[highgo@localhost HighGo6.0.4-cluster]$ initdb
The files belonging to this database system will be owned by user "highgo".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.
Data encryption is disabled.

Enter new superuser password:
Enter it again:
initdb: error:  The password contains unsupported character:'_'
initdb: error: Password must contain upper and lower case letters, numbers and special characters.

Enter new superuser password:
Enter it again:

creating directory /opt/HighGo6.0.4-cluster/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "md5" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /opt/HighGo6.0.4-cluster/data -l logfile start


[highgo@localhost data]$ cat pg_hba.conf |egrep -v "^(#|$)"
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5


[root@localhost HighGo6.0.4-cluster]# cat shdebug
Executing the command start
Execute new Version Repmgr
Execute new Version Repmgr fail
Executing the command stop
Stopping Repmgrd
HighGo Database Server is not initialized.
Executing the command start
Execute new Version Repmgr
Execute new Version Repmgr fail
Executing the command stop
Stopping Repmgrd
Stopping HighGo Database
Executing the command start
Execute new Version Repmgr
Execute new Version Repmgr fail
Executing the command stop
Stopping Repmgrd
Stopping HighGo Database

[highgo@localhost data]$ pg_ctl start
waiting for server to start....2023-10-29 06:37:00.466 EDT [2714] LOG:  starting HighGo Database 6 Release 6.0.4-64-bit Production
2023-10-29 06:37:00.466 EDT [2714] LOG:  listening on IPv6 address "::1", port 5866
2023-10-29 06:37:00.467 EDT [2714] LOG:  listening on IPv4 address "127.0.0.1", port 5866
2023-10-29 06:37:00.789 EDT [2714] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5866"
2023-10-29 06:37:00.836 EDT [2714] LOG:  This is a trial edition, validate until 2024-10-28 06:11:50.
2023-10-29 06:37:00.850 EDT [2715] LOG:  database system was shut down at 2023-10-29 06:11:51 EDT
2023-10-29 06:37:00.866 EDT [2714] LOG:  database system is ready to accept connections
 done
server started

使用highgo用户创建了数据库。可以使用pg_ctl启动数据库, 使用systemctl 启用服务会失败错误日志中提示如上因为repmgr失败。

[highgo@localhost data]$ psql
Password for user highgo:
psql (HighGo Database 6 Release 6.0.4-64-bit Production)

PSQL: Release HighGo Database 6 Release 6.0.4-64-bit Production
Type "help" for help.

highgo=# select version();
                      version
---------------------------------------------------
 HighGo Database 6 Release 6.0.4-64-bit Production
(1 row)

highgo=#

highgo=# \l+
                                                                 List of databases
   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace |                Description
-----------+--------+----------+-------------+-------------+-------------------+---------+------------+--------------------------------------------
 highgo    | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 9529 kB | pg_default | default administrative connection database
 template0 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +| 9385 kB | pg_default | unmodifiable empty database
           |        |          |             |             | highgo=CTc/highgo |         |            |
 template1 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +| 9385 kB | pg_default | default template for new databases
           |        |          |             |             | highgo=CTc/highgo |         |            |
(3 rows)

数据库已创建完成，自带1个highgo数据库

pgbench压力测试

[highgo@localhost bin]$ pgbench -i
Password:
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.32 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
[highgo@localhost bin]$ pgbench -n -r -T 30 -j 10
Password:
transaction type: 
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 30 s
number of transactions actually processed: 5768
latency average = 5.201 ms
tps = 192.262564 (including connections establishing)
tps = 192.376437 (excluding connections establishing)
statement latencies in milliseconds:
         0.006  \set aid random(1, 100000 * :scale)
         0.001  \set bid random(1, 1 * :scale)
         0.002  \set tid random(1, 10 * :scale)
         0.001  \set delta random(-5000, 5000)
         0.093  BEGIN;
         0.337  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.184  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.203  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.190  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.164  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         4.016  END;

注：
这里使用的默认安装，在我们只配置4G sata HDD的vbox虚拟机中，性能并不能做为参考。

测试PDR（AWR）

# vi postgresql.conf

shared_preload_libraries= 'worker_pg_pdr,pg_stat_statements,pg_wait_sampling'

[highgo@localhost data]$ vi postgresql.conf
[highgo@localhost data]$ pg_ctl stop
waiting for server to shut down....2023-10-29 06:58:54.953 EDT [2714] LOG:  received fast shutdown request
2023-10-29 06:58:55.129 EDT [2714] LOG:  aborting any active transactions
2023-10-29 06:58:55.131 EDT [2809] FATAL:  terminating connection due to administrator command
2023-10-29 06:58:55.174 EDT [2714] LOG:  background worker "logical replication launcher" (PID 2721) exited with exit code 1
2023-10-29 06:58:55.524 EDT [2716] LOG:  shutting down
2023-10-29 06:58:55.565 EDT [2714] LOG:  database system is shut down
 done
server stopped
[highgo@localhost data]$ pg_ctl start
waiting for server to start....2023-10-29 06:58:58.429 EDT [2821] LOG:  starting HighGo Database 6 Release 6.0.4-64-bit Production
2023-10-29 06:58:58.430 EDT [2821] LOG:  listening on IPv6 address "::1", port 5866
2023-10-29 06:58:58.430 EDT [2821] LOG:  listening on IPv4 address "127.0.0.1", port 5866
2023-10-29 06:58:58.450 EDT [2821] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5866"
2023-10-29 06:58:58.474 EDT [2821] LOG:  This is a trial edition, validate until 2024-10-28 06:11:50.
2023-10-29 06:58:58.483 EDT [2822] LOG:  database system was shut down at 2023-10-29 06:58:55 EDT
2023-10-29 06:58:58.503 EDT [2821] LOG:  database system is ready to accept connections
 done
server started

[highgo@localhost data]$ ps -ef|grep postgre
highgo    2821     1  0 06:58 ?        00:00:00 /opt/HighGo6.0.4-cluster/bin/postgres
highgo    2823  2821  0 06:58 ?        00:00:00 postgres: checkpointer
highgo    2824  2821  0 06:58 ?        00:00:00 postgres: background writer
highgo    2825  2821  0 06:58 ?        00:00:00 postgres: walwriter
highgo    2826  2821  0 06:58 ?        00:00:00 postgres: autovacuum launcher
highgo    2827  2821  0 06:58 ?        00:00:00 postgres: stats collector
highgo    2828  2821  2 06:58 ?        00:00:02 postgres: pg_wait_sampling collector
highgo    2830  2821  2 06:58 ?        00:00:02 postgres: performance diagnosis collector
highgo    2831  2821  0 06:58 ?        00:00:00 postgres: logical replication launcher
highgo    2837  2543  0 07:00 pts/1    00:00:00 grep --color=auto postgre
[highgo@localhost data]$

修改配置文件后，重启数据库，会发现增加了关于PDR的后台进程。

[highgo@localhost data]$ psql

highgo=# select pg_pdr_new_snap() ;
2023-10-29 07:01:55.029 EDT [2859] LOG:
2023-10-29 07:01:55.029 EDT [2859] STATEMENT:  select pg_pdr_new_snap() ;
 pg_pdr_new_snap
-----------------
 t
(1 row)

highgo=# select pg_pdr_new_snap()
highgo-# ;
2023-10-29 07:02:06.101 EDT [2859] LOG:
2023-10-29 07:02:06.101 EDT [2859] STATEMENT:  select pg_pdr_new_snap()
        ;
 pg_pdr_new_snap
-----------------
 t
(1 row)

highgo=# \l
                               List of databases
   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
 highgo    | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +
           |        |          |             |             | highgo=CTc/highgo
 template1 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +
           |        |          |             |             | highgo=CTc/highgo
(3 rows)

highgo=# \d
                 List of relations
 Schema |           Name           | Type  | Owner
--------+--------------------------+-------+--------
 public | pg_stat_statements       | view  | highgo
 public | pg_wait_sampling_current | view  | highgo
 public | pg_wait_sampling_history | view  | highgo
 public | pg_wait_sampling_profile | view  | highgo
 public | pgbench_accounts         | table | highgo
 public | pgbench_branches         | table | highgo
 public | pgbench_history          | table | highgo
 public | pgbench_tellers          | table | highgo
(8 rows)

highgo=# exit

手动创建了2个快照，默认数据存储在highgo数据库中。

生成PDR（AWR）报告

[highgo@localhost data]$ pdr_report
================================================================================
-----------------------------welcome to pdr report------------------------------
================================================================================
please input system user name:
highgo
please input password for highgo:

please input password for highgo:

please input the database where the snapshot is stored:
highgo
please input which database you want to be reported:
highgo
--------------------------------------------------------------------------------
snap_id =snap_id =you can make report from 1(2023-10-29 07:01:55) to 2(2023-10-29 07:02:06)
--------------------------------------------------------------------------------
please input begin snapid(from 1):
1
snap_begin=[1]
please input end snapid(up to 2):
2
snap_end=[2]
please input html file name for this report(***.html):
anbob_highgo.html
report ./anbob_highgo.html has been successfully created.
[highgo@localhost data]$ ls

PDR文件html格式已生成，因为数据库无负载，我们仅参考内空，用浏览器打开。

PDR样例

熟悉的文件样式，就是一个精简版的Oracle AWR, 关于wait event, profile , io, sql, segment..

PERFORMANCE DIAGNOSIS REPORT

Report Summary

Load Profile

Top 10 Timed Foreground Events

Main Report

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Segment Statistics

Back to Top

Wait Events Statistics

• LWLock Wait Statistics
• Lock Wait Statistics
• BufferPin Wait Statistics
• Client Wait Statistics
• Extension Wait Statistics
• IPC Wait Statistics
• Timeout Wait Statistics
• IO Wait Statistics

Back to Top

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by User I/O Wait Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• Complete List of SQL Text

Back to Top

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
• %Total – Elapsed Time as a percentage of Total DB time
• %CPU – CPU Time as a percentage of Elapsed Time
• %IO – User I/O Time as a percentage of Elapsed Time

Segment Statistics

• Segments by Logical Reads
• Segments by Physical Reads
• Segments by Table Scans

Back to Top

Segments by Logical Reads

— enjoy —

打赏

微信扫一扫，打赏作者吧～