1 使用操作系统命令和数据库工具检查实例状态
1.1 os检查
[omm@node1 ~]$ ps -ef|grep gauss
omm 23847 1 11 08:50 ? 00:09:47 /opt/huawei/install/app/bin/gaussdb
omm 26763 26687 0 10:17 pts/0 00:00:00 grep --color=auto gauss
[omm@node1 ~]$
[omm@node1 ~]$ ps -Tp 23847
PID SPID TTY TIME CMD
23847 23847 ? 00:00:11 gaussdb
23847 23848 ? 00:00:00 jemalloc_bg_thd
23847 23853 ? 00:00:00 gaussdb
23847 23855 ? 00:00:00 syslogger
23847 23856 ? 00:00:00 jemalloc_bg_thd
23847 23857 ? 00:00:01 alarm
23847 23858 ? 00:00:00 reaper
23847 23860 ? 00:00:00 jemalloc_bg_thd
23847 23861 ? 00:00:00 jemalloc_bg_thd
23847 23885 ? 00:00:00 checkpointer
23847 23886 ? 00:00:00 Spbgwriter
23847 23887 ? 00:00:24 pagewriter
23847 23888 ? 00:00:00 pagewriter
23847 23889 ? 00:00:00 pagewriter
23847 23890 ? 00:00:00 pagewriter
23847 23891 ? 00:00:00 pagewriter
23847 23892 ? 00:00:01 WALwriter
23847 23893 ? 00:00:00 WALwriteraux
23847 23894 ? 00:00:00 AVClauncher
23847 23895 ? 00:00:01 Jobscheduler
23847 23896 ? 00:00:00 asyncundolaunch
23847 23897 ? 00:00:00 globalstats
23847 23898 ? 00:00:00 applylauncher
23847 23899 ? 00:00:03 statscollector
23847 23900 ? 00:00:03 txnsnapcapturer
23847 23901 ? 00:00:00 CfsShrinker
23847 23902 ? 00:00:22 percentworker
23847 23903 ? 00:02:19 ashworker
23847 23904 ? 00:00:41 TrackStmtWorker
23847 23905 ? 00:00:00 auditor
23847 23906 ? 00:00:03 2pccleaner
23847 23907 ? 00:00:00 faultmonitor
23847 23909 ? 00:00:00 undorecycler
[omm@node1 ~]$
1.2 db
[omm@node1 ~]$ echo $PGDATA
/opt/huawei/install/data/dn
[omm@node1 ~]$ gs_ctl status
[2022-12-14 10:19:04.054][27346][][gs_ctl]: gs_ctl status,datadir is /opt/huawei/install/data/dn
gs_ctl: server is running (PID: 23847)
/opt/huawei/install/app/bin/gaussdb
[omm@node1 ~]$
testdb100=# show data_directory;
data_directory
-----------------------------
/opt/huawei/install/data/dn
(1 row)
testdb100=#
2 检查锁信息
2.1 查询数据库中的锁信息
SELECT * FROM pg_locks;
testdb100=# SELECT * FROM pg_locks;
locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | vi
rtualtransaction | pid | sessionid | mode | granted | fastpath | locktag | global
_sessionid
------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+---
-----------------+-----------------+-----------------+-----------------+---------+----------+-------------------+-------
-----------
relation | 33042 | 12135 | | | | | | | | | 10
/52796 | 140366876899072 | 140366876899072 | AccessShareLock | t | t | 8112:2f67:0:0:0:0 | 0:0#0
virtualxid | | | | | | 10/52796 | | | | | 10
/52796 | 140366876899072 | 140366876899072 | ExclusiveLock | t | t | a:ce3c:0:0:0:7 | 0:0#0
(2 rows)
另一个会话
testdb100=# \set AUTOCOMMIT off
testdb100=# \echo :AUTOCOMMIT
off
testdb100=# update t1 set id=2;
UPDATE 1
testdb100=#
### 再查
testdb100=# SELECT * FROM pg_locks;
locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pi
d | sessionid | mode | granted | fastpath | locktag | global_sessionid
---------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+---------
--------+-----------------+------------------+---------+----------+-------------------+------------------
relation | 33042 | 33255 | | | | | | | | | 11/16305 | 14036697
3499136 | 140366973499136 | RowExclusiveLock | t | t | 8112:81e7:0:0:0:0 | 0:0#0
virtualxid | | | | | | 11/16305 | | | | | 11/16305 | 14036697
3499136 | 140366973499136 | ExclusiveLock | t | t | b:3fb1:0:0:0:7 | 0:0#0
relation | 33042 | 12135 | | | | | | | | | 10/52799 | 14036687
6899072 | 140366876899072 | AccessShareLock | t | t | 8112:2f67:0:0:0:0 | 0:0#0
virtualxid | | | | | | 10/52799 | | | | | 10/52799 | 14036687
6899072 | 140366876899072 | ExclusiveLock | t | t | a:ce3f:0:0:0:7 | 0:0#0
transactionid | | | | | | | 93737 | | | | 11/16305 | 14036697
3499136 | 140366973499136 | ExclusiveLock | t | f | 16e29:0:0:0:0:6 | 0:0#0
(5 rows)
上面会话 commit 后， 反回记录数又变成两条
2.2 查询等待锁的线程状态信息
testdb100=# 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)
3 统计事件数据
SQL语句长时间运行会占用大量系统资源，用户可以通过查看事件发生的时间，占用内存大小来了解现在数据库运行状态。
3.1 pg_stat_activity
--查询事件的时间，查询事件的线程启动时间、事务启动时间、SQL启动时间以及状态变更时间。
SELECT backend_start,xact_start,query_start,state_change FROM pg_stat_activity;
testdb100=# 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:21:12.816738+08 | 2022-12-14 10:25:00.345418+08 | 2022-12-14 10:25:05.203568+08 | 2022-12-14 10:25:05.203934+08
2022-12-14 10:19:37.351655+08 | 2022-12-14 10:27:23.790075+08 | 2022-12-14 10:27:23.790075+08 | 2022-12-14 10:27:23.790083+08
2022-12-13 15:40:13.184034+08 | | |
2022-12-13 15:40:13.261397+08 | | | 2022-12-14 10:27:23.508714+08
2022-12-13 15:40:13.249659+08 | | | 2022-12-13 15:40:13.250451+08
2022-12-13 15:40:13.262213+08 | | | 2022-12-14 10:27:14.419307+08
2022-12-13 15:40:13.13397+08 | | |
2022-12-13 15:40:13.249606+08 | | | 2022-12-14 10:27:23.749101+08
2022-12-13 15:40:13.122318+08 | | |
(9 rows)
testdb100=# 另一个会话 commit一个事务后
testdb100=# 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:21:12.816738+08 | | 2022-12-14 10:27:30.828697+08 | 2022-12-14 10:27:30.828934+08
2022-12-14 10:19:37.351655+08 | 2022-12-14 10:27:37.860581+08 | 2022-12-14 10:27:37.860581+08 | 2022-12-14 10:27:37.860587+08
2022-12-13 15:40:13.184034+08 | | |
2022-12-13 15:40:13.261397+08 | | | 2022-12-14 10:27:36.971053+08
2022-12-13 15:40:13.249659+08 | | | 2022-12-13 15:40:13.250451+08
2022-12-13 15:40:13.262213+08 | | | 2022-12-14 10:27:34.480423+08
2022-12-13 15:40:13.13397+08 | | |
2022-12-13 15:40:13.249606+08 | | | 2022-12-14 10:27:37.791193+08
2022-12-13 15:40:13.122318+08 | | |
(9 rows)
3.2 查询当前服务器的会话计数信息
SELECT count(*) FROM pg_stat_activity;
testdb100=# SELECT count(*) FROM pg_stat_activity;
count
-------
9
(1 row)
---再开一个会话， 这里就多出一条
testdb100=# SELECT count(*) FROM pg_stat_activity;
count
-------
10
(1 row)
3.3 查询系统级统计信息，查询当前使用内存最多的会话信息。
testdb100=# SELECT * FROM pv_session_memory_detail() ORDER BY usedsize desc limit 10;
sessid | threadid | contextname | level | parent | totalsize | freesize | usedsize
--------+----------+-------------+-------+--------+-----------+----------+----------
(0 rows)
3.4 查询表统计信息
zzzdb=> select oid, relname from pg_class where relname ='t_seq';
oid | relname
-------+---------
32930 | t_seq
(1 row)
SELECT * FROM pg_statistic;
zzzdb=> select * from pg_statistic where starelid=32930; ---> 对应 表的 oid;
starelid | starelkind | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1
| staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1 | stavalues2 | stavalues3 | st
avalues4 | stavalues5 | stadndistinct | staextinfo
----------+------------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+-------
-+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------+------------+------------+---
---------+------------+---------------+------------
(0 rows)
4 查看数据库版本、日志信息
zzzdb=> 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)
-- 查看日志： 在 安装的xml文件中 gaussdbLogPath
[omm@node1 openGauss]$ cd /var/log/omm/omm/
[omm@node1 omm]$ ls
asp_data bin cm gs_profile mem_log om pg_audit pg_log pg_perf sql_monitor
[omm@node1 omm]$ ls -lrt
total 0
drwx------ 3 omm dbgrp 21 Nov 14 20:35 pg_log
drwx------ 3 omm dbgrp 21 Nov 14 20:35 pg_audit
drwx------ 3 omm dbgrp 20 Nov 14 20:36 cm
drwx------ 3 omm dbgrp 21 Nov 14 20:37 pg_perf
drwx------ 3 omm dbgrp 21 Nov 14 20:37 mem_log
drwx------ 3 omm dbgrp 21 Nov 14 20:37 sql_monitor
drwx------ 3 omm dbgrp 21 Nov 14 20:37 gs_profile
drwx------ 3 omm dbgrp 21 Nov 14 20:37 asp_data
drwx------ 2 omm dbgrp 162 Dec 9 16:09 om
drwx------ 9 omm dbgrp 115 Dec 13 11:27 bin
[omm@node1 omm]$ pwd
/var/log/omm/omm
[omm@node1 omm]$ tree -d -L 2 omm/
omm/
├── asp_data
│ └── dn_6001
├── bin
│ ├── gs_cgroup
│ ├── gs_ctl
│ ├── gs_dump
│ ├── gs_guc
│ ├── gs_initdb
│ ├── gs_obs
│ └── gs_restore
├── cm
│ └── cm_ctl
├── gs_profile
│ └── dn_6001
├── mem_log
│ └── dn_6001
├── om
├── pg_audit
│ └── dn_6001
├── pg_log
│ └── dn_6001
├── pg_perf
│ └── dn_6001
└── sql_monitor
└── dn_6001
25 directories
[omm@node1
5 检查应用连接数、查看现有最大连接数
SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
zzzdb=> SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
count
-------
20
(1 row)
zzzdb=>
zzzdb=> --查看现有最大连接数。
zzzdb=> SHOW max_connections
zzzdb-> ;
max_connections
-----------------
5000
(1 row)
zzzdb=> SELECT count(*) FROM pg_stat_activity;
count
-------
8
(1 row)
5、对表进行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;
zzzdb=> SELECT count(*) FROM pg_statistic;
count
-------
509
(1 row)
zzzdb=> drop table customer_t purge;
DROP TABLE
zzzdb=> SELECT count(*) FROM pg_statistic;
count
-------
509
(1 row)
5.1 VACUUM & VACUUM FULL
--使用VACUUM或VACUUM FULL命令，进行磁盘空间回收。
--使用VACUUM
VACUUM customer_t;
--使用VACUUM full
--需要向正在执行的表增加排他锁，且需要停止其他所有数据库操作。
VACUUM full customer_t;
zzzdb=> VACUUM customer_t;
VACUUM
zzzdb=> VACUUM full customer_t;
VACUUM
zzzdb=>
5.2 analyze 表分析
zzzdb=> ANALYZE customer_t;
ANALYZE
zzzdb=> SELECT count(*) FROM pg_statistic;
count
-------
513
(1 row)
--使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息。
ANALYZE VERBOSE customer_t;
zzzdb=> ANALYZE VERBOSE customer_t;
INFO: analyzing "public.customer_t"(dn_6001 pid=23847)
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 pid=23847)
ANALYZE
--也可以同时执行VACUUM ANALYZE命令进行查询优化。
zzzdb=> VACUUM ANALYZE customer_t;
VACUUM
zzzdb=>
zzzdb=> VACUUM ANALYZE VERBOSE customer_t;
INFO: vacuuming "public.customer_t"(dn_6001 pid=23847)
INFO: "customer_t": found 0 removable, 4 nonremovable row versions in 1 out of 1 pages(dn_6001 pid=23847)
DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.customer_t"(dn_6001 pid=23847)
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 pid=23847)
VACUUM
zzzdb=>
zzzdb=> select oid, relname from pg_class where relname ='customer_t';
oid | relname
-------+------------
33261 | customer_t
(1 row)
zzzdb=> select * from pg_statistic where starelid=33261;
starelid | starelkind | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1
| staop2 | staop3 | staop4 | staop5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | stavalues1
| stavalues2 | stavalues3 | stavalues4 | stavalues5 | stadndistinct | staextinfo
----------+------------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+-------
-+--------+--------+--------+--------+-------------+-------------+-------------+-------------+-------------+------------------------------------------
-----+------------+------------+------------+------------+---------------+------------
33261 | c | 2 | f | 0 | 6 | -1 | 2 | 3 | 0 | 0 | 0 | 1058
| 1058 | 0 | 0 | 0 | | {1} | | | | {"1 ","2 ","3 ","4 "}
| | | | | 0 |
33261 | c | 3 | f | 0 | 7 | -1 | 2 | 3 | 0 | 0 | 0 | 1058
| 1058 | 0 | 0 | 0 | | {.4} | | | | {"James ","Joes ","Lily ","Lucy "}
| | | | | 0 |
33261 | c | 4 | f | 0 | 9 | -1 | 2 | 3 | 0 | 0 | 0 | 1058
| 1058 | 0 | 0 | 0 | | {-.8} | | | | {"Baker ","Carter ","Cook ","Hunter
"} | | | | | 0 |
33261 | c | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97
| 97 | 0 | 0 | 0 | | {.6} | | | | {4321,6885,9500,9527}
| | | | | 0 |
(4 rows)
zzzdb=>
6 维护建议：
定期对部分大表做VACUUM FULL，在性能下降后为全库做VACUUM FULL，目前暂定每月做一次VACUUM FULL。
定期对系统表做VACUUM FULL，主要是PG_ATTRIBUTE。
启用系统自动清理线程（AUTOVACUUM）自动执行VACUUM和ANALYZE，回收被标识为删除状态的记录空间，并更新表的统计数据。
7 建表、索引，进行重建索引操作
在重建索引前，用户可以通过临时增大maintenance_work_mem和psort_work_mem的取值来加快索引的重建。
create table test001(
id bigint,
name varchar(50) not null, -- 创建列级not null约束
age int,
primary key(id) -- 创建表级约束
);
insert into test001 values(1,'user1',50);
zzzdb=> \d test001
Table "public.test001"
Column | Type | Modifiers
--------+-----------------------+-----------
id | bigint | not null
name | character varying(50) | not null
age | integer |
Indexes:
"test001_pkey" PRIMARY KEY, btree (id) TABLESPACE enmtbs
zzzdb=> reindex table test001;
REINDEX
zzzdb=> alter index test001_pkey rebuild;
REINDEX
zzzdb=>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
