一、学习目标
本节课是本次实训的第二十一节课，也是本次实训的最后一节课。
本节课的学习目标是学习检查数据库运行状态、锁信息、维护表、重建索引等数据库维护操作技术，对于日常运维openGauss非常重要。
二、测试练习
2.1 使用操作系统命令和数据库工具检查实例状态
– 以下查询来源于自己集群测试环境
– 检查openGauss状态
– 通过ps 来查看实例具体进程及相关线程状态
[root@opengauss-node1 ~]# su - omm
Last login: Wed Dec 14 10:38:00 CST 2022 on pts/0
[omm@opengauss-node1 ~]$ ps -ef|grep gaussdb
root 3632 1 0 Nov18 ? 00:00:01 ssh-agent -a /root/gaussdb_tmp/gauss_socket_tmp
omm 6523 1 0 Nov18 ? 00:00:17 ssh-agent -a /home/omm/gaussdb_tmp/gauss_socket_tmp
omm 23571 1 2 Nov18 ? 14:46:08 /opt/gaussdb/install/app/bin/om_monitor -L /opt/gaussdb/log/omm/cm/om_monitor
omm 30554 23571 12 Nov18 ? 3-03:00:30 /opt/gaussdb/install/app/bin/cm_agent
omm 30567 1 41 Nov18 ? 10-18:55:05 /opt/gaussdb/install/app/bin/cm_server
omm 30578 1 16 Nov18 ? 4-06:04:37 /opt/gaussdb/install/app/bin/gaussdb -D /opt/gaussdb/install/data/db1 -M pending
omm 30585 1 0 Nov18 ? 00:00:04 gaussdb fenced UDF master process
omm 2539850 2539784 0 10:38 pts/0 00:00:00 grep --color=auto gaussdb
[omm@opengauss-node1 ~]$
[omm@opengauss-node1 ~]$ ps -Tp 30578
PID SPID TTY TIME CMD
30578 30578 ? 00:32:37 gaussdb
30578 30586 ? 00:00:00 jemalloc_bg_thd
30578 30655 ? 00:00:00 gaussdb
30578 30660 ? 00:00:36 syslogger
30578 30661 ? 00:17:50 alarm
30578 30662 ? 00:00:00 jemalloc_bg_thd
30578 30663 ? 00:00:13 reaper
30578 30664 ? 00:00:00 jemalloc_bg_thd
30578 30665 ? 00:00:00 jemalloc_bg_thd
30578 30693 ? 00:19:04 checkpointer
30578 30694 ? 00:00:01 Spbgwriter
30578 30696 ? 00:43:05 pagewriter
30578 30697 ? 17:28:03 pagewriter
30578 30698 ? 00:42:41 pagewriter
30578 30699 ? 00:42:50 pagewriter
30578 30701 ? 00:42:56 pagewriter
30578 30703 ? 00:15:39 statscollector
30578 30708 ? 00:02:28 auditor
30578 30709 ? 00:00:01 CfsShrinker
30578 30712 ? 12:53:58 TrackStmtWorker
30578 30729 ? 05:13:53 worker
30578 30745 ? 00:00:01 WALwriteraux
30578 30779 ? 00:00:00 worker
30578 30787 ? 00:12:39 WALwriter
30578 30788 ? 00:00:32 AVClauncher
30578 30789 ? 00:11:39 Jobscheduler
30578 30790 ? 00:00:01 asyncundolaunch
30578 30791 ? 00:03:20 globalstats
30578 30792 ? 00:00:04 applylauncher
30578 30793 ? 00:10:47 txnsnapcapturer
30578 30794 ? 02:28:49 percentworkerX
30578 30795 ? 1-10:00:54 ashworker
30578 30796 ? 00:15:57 2pccleaner
30578 30797 ? 00:00:09 faultmonitor
30578 30799 ? 06:12:25 undorecycler
30578 30807 ? 00:10:25 heartbeat
30578 30813 ? 00:09:35 WalSender
[omm@opengauss-node1 ~]$ ps -Tp 23571
PID SPID TTY TIME CMD
23571 23571 ? 14:46:11 om_monitor
[omm@opengauss-node1 ~]$ ps -Tp 30554
PID SPID TTY TIME CMD
30554 30554 ? 00:16:09 cm_agent
30554 30555 ? 1-12:09:10 cm_agent
30554 30557 ? 01:25:41 cm_agent
30554 30558 ? 1-12:07:56 cm_agent
30554 30559 ? 00:00:36 cm_agent
30554 30560 ? 00:11:02 cm_agent
30554 30561 ? 00:10:37 cm_agent
30554 30562 ? 00:29:51 cm_agent
30554 30564 ? 00:01:42 cm_agent
30554 30565 ? 00:07:58 cm_agent
–使用数据库工具gs_ctl查看实例状态
[omm@opengauss-node1 ~]$ gs_ctl status -D /opt/gaussdb/install/data/db1
[2022-12-14 10:46:35.589][2543988][][gs_ctl]: gs_ctl status,datadir is /opt/gaussdb/install/data/db1
gs_ctl: server is running (PID: 30578)
/opt/gaussdb/install/app/bin/gaussdb "-D" "/opt/gaussdb/install/data/db1" "-M" "primary"
–检查参数
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# show data_directory;
data_directory
-------------------------------
/opt/gaussdb/install/data/db1
(1 row)
openGauss=#
2.2 检查锁信息
– 通过pg_locks查询锁信息
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
-- 发现在集群环境下无法直接查询pg_locks，会报如下错误
openGauss=# select * frpm pg_locks;
ERROR: syntax error at or near "frpm"
LINE 1: select * frpm pg_locks;
^
-- 但是用如下语句查询正常
openGauss=# select * from pg_locks pl left join pg_stat_activity psa on pl.pid = psa.pid;
locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | sessionid | mode | granted | fastpath | locktag | global_sessionid | datid | datname |
pid | sessionid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | enqueue |
state | resource_pool | query_id | query | connection_info | unique_sql_id | tra
ce_id
------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+-----------------+-----------------+-----------------+---------+----------+-------------------+------------------+-------+----------+
-----------------+-----------------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+---------+-
-------+---------------+-----------------+-------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------+---------------+----
------
relation | 15626 | 2677 | | | | | | | | | 13/6016384 | 140273269274368 | 140273269274368 | AccessShareLock | t | t | 3d0a:a75:0:0:0:0 | 0:0#0 | 15626 | postgres |
140273269274368 | 140273269274368 | 10 | omm | gsql | | | -1 | 2022-12-14 10:51:17.613292+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451907+08 | f | |
active | default_pool | 844424950136814 | select * from pg_locks pl left join pg_stat_activity psa on pl.pid = psa.pid; | {"driver_name":"libpq","driver_version":"(openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr "} | 1818537146 |
relation | 15626 | 2676 | | | | | | | | | 13/6016384 | 140273269274368 | 140273269274368 | AccessShareLock | t | t | 3d0a:a74:0:0:0:0 | 0:0#0 | 15626 | postgres |
140273269274368 | 140273269274368 | 10 | omm | gsql | | | -1 | 2022-12-14 10:51:17.613292+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451907+08 | f | |
active | default_pool | 844424950136814 | select * from pg_locks pl left join pg_stat_activity psa on pl.pid = psa.pid; | {"driver_name":"libpq","driver_version":"(openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr "} | 1818537146 |
relation | 15626 | 2672 | | | | | | | | | 13/6016384 | 140273269274368 | 140273269274368 | AccessShareLock | t | t | 3d0a:a70:0:0:0:0 | 0:0#0 | 15626 | postgres |
140273269274368 | 140273269274368 | 10 | omm | gsql | | | -1 | 2022-12-14 10:51:17.613292+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451907+08 | f | |
active | default_pool | 844424950136814 | select * from pg_locks pl left join pg_stat_activity psa on pl.pid = psa.pid; | {"driver_name":"libpq","driver_version":"(openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr "} | 1818537146 |
relation | 15626 | 2671 | | | | | | | | | 13/6016384 | 140273269274368 | 140273269274368 | AccessShareLock | t | t | 3d0a:a6f:0:0:0:0 | 0:0#0 | 15626 | postgres |
140273269274368 | 140273269274368 | 10 | omm | gsql | | | -1 | 2022-12-14 10:51:17.613292+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451907+08 | f | |
active | default_pool | 844424950136814 | select * from pg_locks pl left join pg_stat_activity psa on pl.pid = psa.pid; | {"driver_name":"libpq","driver_version":"(openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr "} | 1818537146 |
relation | 15626 | 1260 | | | | | | | | | 13/6016384 | 140273269274368 | 140273269274368 | AccessShareLock | t | t | 3d0a:4ec:0:0:0:0 | 0:0#0 | 15626 | postgres |
140273269274368 | 140273269274368 | 10 | omm | gsql | | | -1 | 2022-12-14 10:51:17.613292+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451907+08 | f | |
active | default_pool | 844424950136814 | select * from pg_locks pl left join pg_stat_activity psa on pl.pid = psa.pid; | {"driver_name":"libpq","driver_version":"(openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr "} | 1818537146 |
relation | 15626 | 1262 | | | | | | | | | 13/6016384 | 140273269274368 | 140273269274368 | AccessShareLock | t | t | 3d0a:4ee:0:0:0:0 | 0:0#0 | 15626 | postgres |
140273269274368 | 140273269274368 | 10 | omm | gsql | | | -1 | 2022-12-14 10:51:17.613292+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451907+08 | f | |
active | default_pool | 844424950136814 | select * from pg_locks pl left join pg_stat_activity psa on pl.pid = psa.pid; | {"driver_name":"libpq","driver_version":"(openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr "} | 1818537146 |
relation | 15626 | 12248 | | | | | | | | | 13/6016384 | 140273269274368 | 140273269274368 | AccessShareLock | t | t | 3d0a:2fd8:0:0:0:0 | 0:0#0 | 15626 | postgres |
140273269274368 | 140273269274368 | 10 | omm | gsql | | | -1 | 2022-12-14 10:51:17.613292+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451907+08 | f | |
active | default_pool | 844424950136814 | select * from pg_locks pl left join pg_stat_activity psa on pl.pid = psa.pid; | {"driver_name":"libpq","driver_version":"(openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr "} | 1818537146 |
relation | 15626 | 12135 | | | | | | | | | 13/6016384 | 140273269274368 | 140273269274368 | AccessShareLock | t | t | 3d0a:2f67:0:0:0:0 | 0:0#0 | 15626 | postgres |
140273269274368 | 140273269274368 | 10 | omm | gsql | | | -1 | 2022-12-14 10:51:17.613292+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451907+08 | f | |
active | default_pool | 844424950136814 | select * from pg_locks pl left join pg_stat_activity psa on pl.pid = psa.pid; | {"driver_name":"libpq","driver_version":"(openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr "} | 1818537146 |
virtualxid | | | | | | 13/6016384 | | | | | 13/6016384 | 140273269274368 | 140273269274368 | ExclusiveLock | t | t | d:5bcd80:0:0:0:7 | 0:0#0 | 15626 | postgres |
140273269274368 | 140273269274368 | 10 | omm | gsql | | | -1 | 2022-12-14 10:51:17.613292+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451887+08 | 2022-12-14 10:51:40.451907+08 | f | |
active | default_pool | 844424950136814 | select * from pg_locks pl left join pg_stat_activity psa on pl.pid = psa.pid; | {"driver_name":"libpq","driver_version":"(openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr "} | 1818537146 |
(9 rows)
–查询等待锁的线程状态信息
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# select * from pg_thread_wait_status where wait_status = 'acquire lock';
node_name | db_name | thread_name | query_id | tid | sessionid | lwtid | psessionid | tlevel | smpid | wait_status | wait_event | locktag | lockmode | block_sessionid | global_sessionid
-----------+---------+-------------+----------+-----+-----------+-------+------------+--------+-------+-------------+------------+---------+----------+-----------------+------------------
(0 rows)
2.3 统计事件数据
查询事件的时间，查询事件的线程启动时间、事务启动时间、SQL启动时间以及状态变更时间
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# select backend_start,xact_start,query_start,state_change from pg_stat_activity;
backend_start | xact_start | query_start | state_change
-------------------------------+-------------------------------+-------------------------------+-------------------------------
2022-12-14 10:56:04.140012+08 | 2022-12-14 10:56:06.051629+08 | 2022-12-14 10:56:06.051629+08 | 2022-12-14 10:56:06.051637+08
2022-11-18 16:11:41.080737+08 | | | 2022-12-14 10:56:05.561524+08
2022-11-18 16:11:41.000501+08 | | |
2022-11-18 16:11:40.998446+08 | | |
2022-11-18 16:11:41.069605+08 | | | 2022-12-14 10:55:57.880256+08
2022-11-18 16:11:41.058638+08 | | | 2022-12-14 10:56:05.92925+08
2022-11-18 16:11:39.799366+08 | | 2022-11-18 16:11:41.833668+08 | 2022-11-18 16:11:41.838186+08
2022-11-18 16:11:35.489094+08 | | 2022-12-14 10:56:05.859095+08 | 2022-12-14 10:56:05.859454+08
2022-11-18 16:11:35.071407+08 | | | 2022-11-18 16:11:35.071489+08
2022-11-18 16:11:34.989532+08 | | |
(10 rows)
–**查询当前服务器的会话计数信息
**
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# select count(*) from pg_stat_activity;
count
-------
10
(1 row)
2.3 查看数据库版本及日志信息
–查看数据库版本
-- gsql登陆时其实已经显示版本信息，但不是特别详细
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------------------
(openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)
openGauss=#
– 查看日志信息
– 在部署时，如果配置了环境变量，可以查询omm用户.bashrc下日志所在环境变量，查看日志具体信息
– 不同环境可能不同
[omm@opengauss-node1 ~]$ more .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
[omm@opengauss-node1 ~]$ more .bashrc
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
export SSH_AUTH_SOCK=/home/omm/gaussdb_tmp/gauss_socket_tmp
export SSH_AGENT_PID=6523
export GPHOME=/opt/gaussdb/gausstools/om
export PATH=$GPHOME/script/gspylib/pssh/bin:$GPHOME/script:$PATH
export LD_LIBRARY_PATH=$GPHOME/lib:$LD_LIBRARY_PATH
export PYTHONPATH=$GPHOME/lib
export GAUSSHOME=/opt/gaussdb/install/app
export PATH=$GAUSSHOME/bin:$PATH
export LD_LIBRARY_PATH=$GAUSSHOME/lib:$LD_LIBRARY_PATH
export S3_CLIENT_CRT_FILE=$GAUSSHOME/lib/client.crt
export GAUSS_VERSION=3.1.0
export PGHOST=/opt/gaussdb/tmp
export GAUSSLOG=/opt/gaussdb/log/omm
umask 077
export GAUSS_ENV=2
export GS_CLUSTER_NAME=openGSDB
export PGDATA=/opt/gaussdb/install/data/db1
[omm@opengauss-node1 ~]$ cd /opt/gaussdb/log/omm
[omm@opengauss-node1 omm]$ ll
total 40
drwx------ 3 omm dbgrp 4096 Nov 18 16:04 asp_data
drwx------ 8 omm dbgrp 4096 Dec 13 19:53 bin
drwx------ 7 omm dbgrp 4096 Nov 18 16:04 cm
drwx------ 3 omm dbgrp 4096 Dec 14 08:19 gs_profile
drwx------ 3 omm dbgrp 4096 Nov 18 16:04 mem_log
drwx------ 2 omm dbgrp 4096 Dec 14 11:03 om
drwx------ 3 omm dbgrp 4096 Nov 18 16:03 pg_audit
drwx------ 3 omm dbgrp 4096 Nov 18 16:03 pg_log
drwx------ 3 omm dbgrp 4096 Nov 18 16:04 pg_perf
drwx------ 3 omm dbgrp 4096 Nov 18 16:04 sql_monitor
[omm@opengauss-node1 omm]$ cd pg_log
[omm@opengauss-node1 pg_log]$ ll
total 4
drwx------ 2 omm dbgrp 4096 Dec 14 00:19 dn_6001
[omm@opengauss-node1 pg_log]$ cd dn_6001/
[omm@opengauss-node1 dn_6001]$ ll
total 4312
-r-------- 1 omm dbgrp 11107 Nov 18 16:41 postgresql-2022-11-18_160436.log.gz
-r-------- 1 omm dbgrp 9227 Nov 18 16:41 postgresql-2022-11-18_160803.log.gz
-r-------- 1 omm dbgrp 41646 Nov 19 00:11 postgresql-2022-11-18_161134.log.gz
-r-------- 1 omm dbgrp 120397 Nov 20 00:11 postgresql-2022-11-19_000000.log.gz
-r-------- 1 omm dbgrp 120007 Nov 21 00:11 postgresql-2022-11-20_000000.log.gz
-r-------- 1 omm dbgrp 156308 Nov 22 00:12 postgresql-2022-11-21_000000.log.gz
-r-------- 1 omm dbgrp 122985 Nov 23 00:12 postgresql-2022-11-22_000000.log.gz
-r-------- 1 omm dbgrp 121995 Nov 24 00:12 postgresql-2022-11-23_000000.log.gz
-r-------- 1 omm dbgrp 131793 Nov 25 00:12 postgresql-2022-11-24_000000.log.gz
-r-------- 1 omm dbgrp 125522 Nov 26 00:13 postgresql-2022-11-25_000000.log.gz
-r-------- 1 omm dbgrp 122175 Nov 27 00:13 postgresql-2022-11-26_000000.log.gz
-r-------- 1 omm dbgrp 122025 Nov 28 00:13 postgresql-2022-11-27_000000.log.gz
-r-------- 1 omm dbgrp 127178 Nov 29 00:13 postgresql-2022-11-28_000000.log.gz
-r-------- 1 omm dbgrp 128511 Nov 30 00:14 postgresql-2022-11-29_000000.log.gz
-r-------- 1 omm dbgrp 136559 Dec 1 00:14 postgresql-2022-11-30_000000.log.gz
-r-------- 1 omm dbgrp 127495 Dec 2 00:14 postgresql-2022-12-01_000000.log.gz
-r-------- 1 omm dbgrp 127203 Dec 3 00:15 postgresql-2022-12-02_000000.log.gz
-r-------- 1 omm dbgrp 126843 Dec 4 00:15 postgresql-2022-12-03_000000.log.gz
-r-------- 1 omm dbgrp 126900 Dec 5 00:15 postgresql-2022-12-04_000000.log.gz
-r-------- 1 omm dbgrp 126220 Dec 6 00:16 postgresql-2022-12-05_000000.log.gz
-r-------- 1 omm dbgrp 126594 Dec 7 00:16 postgresql-2022-12-06_000000.log.gz
-r-------- 1 omm dbgrp 128125 Dec 8 00:16 postgresql-2022-12-07_000000.log.gz
-r-------- 1 omm dbgrp 126815 Dec 9 00:17 postgresql-2022-12-08_000000.log.gz
-r-------- 1 omm dbgrp 130280 Dec 10 00:17 postgresql-2022-12-09_000000.log.gz
-r-------- 1 omm dbgrp 128900 Dec 11 00:18 postgresql-2022-12-10_000000.log.gz
-r-------- 1 omm dbgrp 130275 Dec 12 00:18 postgresql-2022-12-11_000000.log.gz
-r-------- 1 omm dbgrp 134795 Dec 13 00:19 postgresql-2022-12-12_000000.log.gz
-r-------- 1 omm dbgrp 135390 Dec 14 00:19 postgresql-2022-12-13_000000.log.gz
-rw------- 1 omm dbgrp 1078955 Dec 14 11:03 postgresql-2022-12-14_000000.log
2.4 检查应用连接数及现有最大连接数
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
-- 查看应用连接数
openGauss=# select count(*) from (select pg_stat_get_backend_idset() as backendid) as s;
count
-------
24
(1 row)
-- 查看数据库设置的最大连接数
openGauss=# show max_connections;
max_connections
-----------------
5000
(1 row)
2.5 对表进行VACUUM、VACUUM FULL和ANALYZE操作
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# CREATE TABLE customer_t
openGauss-# ( c_customer_sk integer,
openGauss(# c_customer_id char(5),
openGauss(# c_first_name char(6),
openGauss(# c_last_name char(8)
openGauss(# ) ;
CREATE TABLE
openGauss=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
openGauss-# (6885, 1, 'Joes', 'Hunter'),
openGauss-# (4321, 2, 'Lily','Carter'),
openGauss-# (9527, 3, 'James', 'Cook'),
openGauss-# (9500, 4, 'Lucy', 'Baker');
select count(*) from customer_t;
INSERT 0 4
openGauss=# select count(*) from customer_t;
count
-------
4
(1 row)
-- --使用VACUUM或VACUUM FULL命令，进行磁盘空间回收
openGauss=# VACUUM customer_t;
VACUUM
--使用VACUUM full
--需要向正在执行的表增加排他锁，且需要停止其他所有数据库操作
openGauss=# VACUUM FULL customer_t;
VACUUM
--使用ANALYZE语句更新统计信息
openGauss=# ANALYZE customer_t;
ANALYZE
--使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息
openGauss=# ANALYZE VERBOSE customer_t;
INFO: analyzing "public.customer_t"(dn_6001_6002 pid=30578)
INFO: ANALYZE INFO : "customer_t": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows(dn_6001_6002 pid=30578)
ANALYZE
--也可以同时执行VACUUM ANALYZE命令进行查询优化
openGauss=# VACUUM ANALYZE customer_t;
VACUUM
openGauss=#
--定期对部分大表做VACUUM FULL，在性能下降后为全库做VACUUM FULL，目前暂定每月做一次VACUUM FULL。
--定期对系统表做VACUUM FULL，主要是PG_ATTRIBUTE。
--启用系统自动清理线程（AUTOVACUUM）自动执行VACUUM和ANALYZE，回收被标识为删除状态的记录空间，并更新表的统计数据。
2.6 建表和索引并进行索引重建
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# CREATE TABLESPACE EXAMPLE RELATIVE LOCATION 'tablespace/EXAMPLE';
CREATE TABLESPACE
openGauss=# CREATE schema HR;
CREATE SCHEMA
openGauss=# CREATE TABLE HR.areaS(area_ID NUMBER,
openGauss(# area_NAME VARCHAR2(25)
openGauss(# )tablespace EXAMPLE;
CREATE TABLE
openGauss=# \d HR.areaS
Table "hr.areas"
Column | Type | Modifiers
-----------+-----------------------+-----------
area_id | numeric |
area_name | character varying(25) |
Tablespace: "example"
openGauss=# CREATE INDEX areaS_idx ON HR.areaS (area_id);
CREATE INDEX
--使用REINDEX TABLE语句重建索引
openGauss=# REINDEX table HR.areaS;
REINDEX
openGauss=#
三、学习心得
通过这21天的学习，对openGauss知识有了初步的理解，也感谢提供了这次实训学习机会，以后也会抽出时间多好好研究openGauss。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
