学习目标
学习检查数据库运行状态、锁信息、维护表、重建索引等数据库维护操作技术
前面每日一练链接
openGauss 每日一练第 1 天 | openGauss 数据库状态查看
openGauss 每日一练第 2 天 | 学习 gsql 命令行的使用
openGauss 每日一练第 3 天 | openGauss 数据库状态查看
openGauss 每日一练第 4 天 | openGauss 中一个数据库可以被多个用户访问
openGauss 每日一练第 5 天 | openGauss 中一个用户可以访问多个数据库
openGauss 每日一练第 6 天 | openGauss 中用户一次只能连接到一个数据库
openGauss 每日一练第 7 天 | openGauss 中一个数据库中可以创建多个模式
openGauss 每日一练第 8 天 | openGauss 中一个数据库可以存储在多个表空间中
openGauss 每日一练第 9 天 | openGauss 中一个表空间可以存储多个数据库
openGauss 每日一练第 10 天 | openGauss 逻辑结构：表空间管理
openGauss 每日一练第 11 天 | openGauss 逻辑结构：数据库管理
openGauss 每日一练第 12 天 | openGauss 逻辑结构：模式管理
openGauss 每日一练第 13 天 |openGauss 逻辑结构：表管理 1
openGauss 每日一练第 14 天 |openGauss 逻辑结构：表管理 2
openGauss 每日一练第 15 天 |openGauss 逻辑结构：表管理 3
openGauss 每日一练第 16 天 |openGauss 逻辑结构：表管理 4
openGauss 每日一练第 17 天 |openGauss 逻辑结构：索引管理
openGauss 每日一练第 18 天 |openGauss 逻辑结构：视图管理
openGauss 每日一练第 19 天 |openGauss 用户和角色管理
openGauss 每日一练第 20 天 |openGauss 数据库的逻辑备份与恢复
课程学习
1.检查 openGauss 状态
--操作系统命令查看实例状态
su - omm
ps -ef|grep gaussdb
[omm@jiekexu1 ~]$ ps -ef|grep gaussdb
omm 24405 1 3 Dec08 ? 04:18:43 /opt/opengauss3.1/app/bin/gaussdb -D /opt/opengauss3.1/data/db -M primary
root 27261 1 0 Dec02 ? 00:00:00 ssh-agent -a /root/gaussdb_tmp/gauss_socket_tmp
omm 31319 1 0 Dec02 ? 00:00:04 ssh-agent -a /home/omm/gaussdb_tmp/gauss_socket_tmp
omm 128044 46930 0 10:18 pts/0 00:00:00 grep --color=auto gaussdb
ps -Tp 24405
[omm@jiekexu1 ~]$ ps -Tp 24405
PID SPID TTY TIME CMD
24405 24405 ? 00:01:15 gaussdb
24405 24406 ? 00:00:00 jemalloc_bg_thd
24405 24411 ? 00:00:00 gaussdb
24405 24412 ? 00:00:03 syslogger
24405 24413 ? 00:00:00 jemalloc_bg_thd
24405 24414 ? 00:00:49 alarm
24405 24415 ? 00:00:02 reaper
24405 24416 ? 00:00:00 jemalloc_bg_thd
24405 24417 ? 00:00:00 jemalloc_bg_thd
24405 24442 ? 00:00:56 checkpointer
24405 24443 ? 00:00:00 Spbgwriter
24405 24444 ? 00:18:59 pagewriter
24405 24445 ? 00:00:09 pagewriter
24405 24446 ? 00:00:09 pagewriter
24405 24447 ? 00:00:09 pagewriter
24405 24448 ? 00:00:09 pagewriter
24405 24449 ? 00:00:58 WALwriter
24405 24450 ? 00:00:00 WALwriteraux
24405 24451 ? 00:00:01 AVClauncher
24405 24452 ? 00:00:52 Jobscheduler
24405 24453 ? 00:00:00 asyncundolaunch
24405 24454 ? 00:00:11 globalstats
24405 24455 ? 00:00:00 applylauncher
24405 24456 ? 00:00:44 statscollector
24405 24457 ? 00:00:36 txnsnapcapturer
24405 24458 ? 00:00:00 CfsShrinker
24405 24459 ? 00:15:38 percentworker
24405 24460 ? 02:02:58 ashworker
24405 24461 ? 00:38:09 TrackStmtWorker
24405 24462 ? 00:00:04 auditor
24405 24463 ? 00:00:47 2pccleaner
24405 24464 ? 00:00:00 faultmonitor
24405 24466 ? 00:00:53 heartbeat
24405 24467 ? 00:17:29 undorecycler
24405 22591 ? 00:00:03 WalSender
--使用数据库工具 gs_ctl 查看实例状态
gs_ctl status -D /opt/opengauss3.1/data/db
[omm@jiekexu1 ~]$ gs_ctl status -D /opt/opengauss3.1/data/db
[2022-12-14 10:20:17.650][128325][][gs_ctl]: gs_ctl status,datadir is /opt/opengauss3.1/data/db
gs_ctl: server is running (PID: 24405)
/opt/opengauss3.1/app/bin/gaussdb "-D" "/opt/opengauss3.1/data/db" "-M" "primary"
--检查参数
gsql -r
show data_directory;
[omm@jiekexu1 ~]$ gsql -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.
omm=# show data_directory;
data_directory
---------------------------
/opt/opengauss3.1/data/db
(1 row)
2.检查锁信息
锁机制是数据库保证数据一致性的重要手段，检查相关信息可以检查数据库的事务和运行状况。
--查询数据库中的锁信息
SELECT * FROM pg_locks;
--查询等待锁的线程状态信息
SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock';
3.统计事件数据
SQL 语句长时间运行会占用大量系统资源，用户可以通过查看事件发生的时间，占用内存大小来了解现在数据库运行状态。
--查询事件的时间，查询事件的线程启动时间、事务启动时间、SQL启动时间以及状态变更时间。
SELECT backend_start,xact_start,query_start,state_change FROM pg_stat_activity;
--查询当前服务器的会话计数信息
SELECT count(*) FROM pg_stat_activity;
--查询系统级统计信息，查询当前使用内存最多的会话信息。
SELECT * FROM pv_session_memory_detail() ORDER BY usedsize desc limit 10;
--查询表统计信息
SELECT * FROM pg_statistic;
4.数据库版本、日志信息
--数据库版本
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)
--在 gsql 中
\! ls /var/lib/opengauss/data/pg_log
--注意每个人的环境有可能不同，日志位置不一样，我的日志在 /var/log/opengauss3.1/omm/pg_log
\q
--数据库日志位置
cd /var/log/opengauss3.1/omm/pg_log
ls /var/log/opengauss3.1/omm/pg_log
5.检查应用连接数
如果应用程序与数据库的连接数超过最大值，则新的连接无法建立。建议每天检查连接数，及时释放空闲的连接或者增加最大连接数。
--执行如下 SQL 语句查看连接数。
gsql -r
SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
--查看现有最大连接数。
SHOW max_connections;
6.例行维护表：VACUUM、VACUUM FULL和ANALYZE
为了保证数据库的有效运行，数据库必须在插入/删除操作后，基于客户场景，定期做VACUUM FULL和ANALYZE，更新统计信息，以便获得更优的性能。
VACUUM 和 ANALYZE 会导致 I/O 流量的大幅增加，这可能会影响其他活动会话的性能。
--创建表、插入数据、查看数据
CREATE TABLE customer_t
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
(6885, 1, 'Joes', 'Hunter'),
(4321, 2, 'Lily','Carter'),
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');
select count(*) from customer_t;
--使用 VACUUM 或 VACUUM FULL 命令，进行磁盘空间回收。
--使用 VACUUM
VACUUM customer_t;
--使用 VACUUM full
--需要向正在执行的表增加排他锁，且需要停止其他所有数据库操作。
VACUUM FULL customer_t;
--使用ANALYZE语句更新统计信息。
ANALYZE customer_t;
--使用 ANALYZE VERBOSE 语句更新统计信息，并输出表的相关信息。
ANALYZE VERBOSE customer_t;
--也可以同时执行 VACUUM ANALYZE 命令进行查询优化。
VACUUM ANALYZE customer_t;
维护建议：
定期对部分大表做 VACUUM FULL，在性能下降后为全库做 VACUUM FULL，目前暂定每月做一次 VACUUM FULL。
定期对系统表做 VACUUM FULL，主要是 PG_ATTRIBUTE。
启用系统自动清理线程（AUTOVACUUM）自动执行 VACUUM 和 ANALYZE，回收被标识为删除状态的记录空间，并更新表的统计数据。
7.重建索引
在重建索引前，用户可以通过临时增大 maintenance_work_mem 和 psort_work_mem 的取值来加快索引的重建。
--环境准备
CREATE TABLESPACE EXAMPLE RELATIVE LOCATION 'tablespace/example1';
CREATE schema HR;
CREATE TABLE HR.areaS(area_ID NUMBER,area_NAME VARCHAR2(25) )tablespace EXAMPLE;
\d HR.areaS
CREATE INDEX areaS_idx ON HR.areaS (area_id);
--使用REINDEX TABLE语句重建索引。
REINDEX table HR.areaS;
课程作业
1、使用操作系统命令和数据库工具检查实例状态
[omm@jiekexu1 ~]$ ps -ef|grep gaussdb
omm 1349 129043 0 10:42 pts/1 00:00:00 grep --color=auto gaussdb
omm 24405 1 3 Dec08 ? 04:19:35 /opt/opengauss3.1/app/bin/gaussdb -D /opt/opengauss3.1/data/db -M primary
root 27261 1 0 Dec02 ? 00:00:00 ssh-agent -a /root/gaussdb_tmp/gauss_socket_tmp
omm 31319 1 0 Dec02 ? 00:00:04 ssh-agent -a /home/omm/gaussdb_tmp/gauss_socket_tmp
[omm@jiekexu1 ~]$ gs_ctl status -D /opt/opengauss3.1/data/db
[2022-12-14 10:20:17.650][128325][][gs_ctl]: gs_ctl status,datadir is /opt/opengauss3.1/data/db
gs_ctl: server is running (PID: 24405)
/opt/opengauss3.1/app/bin/gaussdb "-D" "/opt/opengauss3.1/data/db" "-M" "primary"
--检查主备信息
[omm@jiekexu1 ~]$ gs_om -t status --detail
[ Cluster State ]
cluster_state : Normal
redistributing : No
current_az : AZ_ALL
[ Datanode State ]
node node_ip port instance state
---------------------------------------------------------------------------------------------
1 jiekexu1 192.168.75.11 5432 6001 /opt/opengauss3.1/data/db P Primary Normal
2 jiekexu2 192.168.75.12 5432 6002 /opt/opengauss3.1/data/db S Standby Normal
[omm@jiekexu1 ~]$ gs_om -t status
---------------------------------------------------------------------
cluster_name : dbCluster
cluster_state : Normal
redistributing : No
---------------------------------------------------------------------
2、检查锁信息
--查询数据库中的锁信息
SELECT * FROM pg_locks;
--查询等待锁的线程状态信息
SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock';
3、查看数据库版本、日志信息
--数据库版本
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)
--在 gsql 中
\! ls /var/lib/opengauss/data/pg_log
--注意每个人的环境有可能不同，日志位置不一样，我的日志在 /var/log/opengauss3.1/omm/pg_log
\q
--数据库日志位置
cd /var/log/opengauss3.1/omm/pg_log
ls /var/log/opengauss3.1/omm/pg_log
[omm@jiekexu1 dn_6001]$ tail -40f ./dn_6001/postgresql-2022-12-14_000000.log
2022-12-14 10:40:51.511 6391d833.6125 [unknown] 140247876757248 dn_6001 0 dn_6001_6002 00000 0 [BACKEND] LOG: will update control file (create checkpoint), shutdown:0
2022-12-14 10:40:51.512 6391d833.6125 [unknown] 140247876757248 dn_6001 0 dn_6001_6002 00000 0 [BACKEND] LOG: attempting to remove WAL segments older than log file 000000010000000000000000
2022-12-14 10:41:12.099 639937c8.5061 postgres 140246960101120 Clean Statement thread 0 dn_6001_6002 00000 0 [BACKEND] LOG: clean statement thread start
2022-12-14 10:41:45.991 639937e9.6123 [unknown] 140246960101120 dn_6001 0 dn_6001_6002 00000 0 [BACKEND] LOG: start autovacuum on database "postgres"
4、检查应用连接数、查看现有最大连接数
SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
count
-------
22
(1 row)
--查看现有最大连接数。
omm=# SHOW max_connections;
max_connections
-----------------
5000
(1 row)
5、对表进行 VACUUM、VACUUM FULL和ANALYZE操作
CREATE TABLE cut
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
INSERT INTO cut (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
(6885, 1, 'Joes', 'Hunter'),
(4321, 2, 'Lily','Carter'),
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker'),
(9876,5,'Lili','Cook');
select count(*) from cut;
--使用 VACUUM 或 VACUUM FULL 命令，进行磁盘空间回收。
--使用 VACUUM
VACUUM cut;
--使用 VACUUM full
--需要向正在执行的表增加排他锁，且需要停止其他所有数据库操作。
VACUUM FULL cut;
--使用ANALYZE语句更新统计信息。
ANALYZE cut;
--使用 ANALYZE VERBOSE 语句更新统计信息，并输出表的相关信息。
ANALYZE VERBOSE cut;
--也可以同时执行 VACUUM ANALYZE 命令进行查询优化。
VACUUM ANALYZE cut;
6、建表、索引，进行重建索引操作
CREATE TABLE HR.ar(area_ID NUMBER,area_NAME VARCHAR2(25)) tablespace EXAMPLE;
\d HR.areaS
CREATE INDEX ar_idx ON HR.ar (area_id);
--使用REINDEX TABLE语句重建索引。
REINDEX table HR.ar;
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
