openGauss每日一练第21天 | openGauss数据库例行维护
opengauss数据库例行维护，比如查看数据库运行状态，检查锁信息，例行维护表，索引等。
1、使用操作系统命令和数据库工具检查实例状态
su - omm
--启动opengauss
gs_ctl start -D /opt/software/openGauss/data/single_node/
--查看gaussdb进程
[omm@ky01 ~]$ ps -ef |grep gaussdb
omm 1676 1 5 10:23 ? 00:00:08 /opt/software/openGauss/bin/gaussdb -D /opt/software/openGauss/data/single_node
omm 1764 1595 0 10:26 pts/0 00:00:00 grep gaussdb
--根据进程号1676，查询线程
[omm@ky01 ~]$ ps -Tp 1676
PID SPID TTY TIME CMD
1676 1676 ? 00:00:02 gaussdb
1676 1677 ? 00:00:00 jemalloc_bg_thd
1676 1682 ? 00:00:00 gaussdb
1676 1683 ? 00:00:00 syslogger
1676 1684 ? 00:00:00 alarm
1676 1685 ? 00:00:00 jemalloc_bg_thd
1676 1686 ? 00:00:00 reaper
1676 1687 ? 00:00:00 jemalloc_bg_thd
1676 1688 ? 00:00:00 jemalloc_bg_thd
1676 1713 ? 00:00:00 checkpointer
1676 1714 ? 00:00:00 Spbgwriter
1676 1715 ? 00:00:01 pagewriter
1676 1716 ? 00:00:00 pagewriter
1676 1717 ? 00:00:00 pagewriter
1676 1718 ? 00:00:00 pagewriter
1676 1719 ? 00:00:00 pagewriter
1676 1720 ? 00:00:00 WALwriter
1676 1721 ? 00:00:00 WALwriteraux
1676 1722 ? 00:00:00 AVClauncher
1676 1723 ? 00:00:00 Jobscheduler
1676 1724 ? 00:00:00 asyncundolaunch
1676 1725 ? 00:00:00 globalstats
1676 1726 ? 00:00:00 applylauncher
1676 1727 ? 00:00:00 statscollector
1676 1728 ? 00:00:00 percentworker
1676 1729 ? 00:00:00 ashworker
1676 1730 ? 00:00:00 TrackStmtWorker
1676 1731 ? 00:00:00 auditor
1676 1732 ? 00:00:00 2pccleaner
1676 1733 ? 00:00:00 faultmonitor
1676 1734 ? 00:00:00 WLMworker
1676 1735 ? 00:00:00 WLMmonitor
1676 1736 ? 00:00:00 WLMarbiter
1676 1737 ? 00:00:00 undorecycler
[omm@ky01 ~]$ gsql -d postgres -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:19 commit 0 last mr )
NOTICE : The password has been expired, please change the password.
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
--查看数据目录
openGauss=# show data_directory;
data_directory
------------------------------------------
/opt/software/openGauss/data/single_node
(1 row)
2、检查锁信息
--锁机制是数据库保证数据一致性的重要手段，检查相关信息可以检查数据库的事务和运行状况。
--查询数据库中的锁信息
SELECT * FROM pg_locks;
--查询等待锁的线程状态信息
SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock';
3、查看数据库版本、日志信息
--查看数据库版本
openGauss=# SELECT version();
version
-----------------------------------------------------------------------------------------
(openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:19 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by
g++ (GCC) 7.3.0, 64-bit
(1 row)
--查看日志，日志目录位于数据目录下面的pg_log文件夹
[omm@ky01 ~]$ ls /opt/software/openGauss/data/single_node/pg_log
postgresql-2022-07-08_131845.log postgresql-2022-08-23_142709.log postgresql-2022-09-01_093110.log
postgresql-2022-07-08_142444.log postgresql-2022-08-23_143457.log postgresql-2022-09-02_093615.log
postgresql-2022-07-08_143135.log postgresql-2022-08-24_093536.log postgresql-2022-09-02_095605.log
postgresql-2022-08-23_095836.log postgresql-2022-08-27_151752.log postgresql-2022-09-02_100348.log
postgresql-2022-08-23_135155.log postgresql-2022-08-28_000000.log postgresql-2022-09-02_101349.log
postgresql-2022-08-23_140148.log postgresql-2022-08-28_115853.log postgresql-2022-09-02_102453.log
postgresql-2022-08-23_140646.log postgresql-2022-08-28_133401.log postgresql-2022-09-08_092629.log
postgresql-2022-08-23_140941.log postgresql-2022-08-29_093428.log postgresql-2022-12-14_102348.log
postgresql-2022-08-23_141337.log postgresql-2022-08-30_092802.log
postgresql-2022-08-23_141714.log postgresql-2022-08-31_100009.log
[omm@ky01 ~]$
4、检查应用连接数、查看现有最大连接数
--查看应用连接数
openGauss=# SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
count
-------
21
(1 row)
--查看现有最大连接数。
openGauss=# SHOW max_connections;
max_connections
-----------------
200
(1 row)
5、对表进行VACUUM、VACUUM FULL和ANALYZE操作
openGauss=# \c testdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "omm".
testdb=# \dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(10 rows)
testdb=# select current_database(),current_user,current_schema;
current_database | current_user | current_schema
------------------+--------------+----------------
testdb | omm | public
(1 row)
testdb=# create table t1(id int,ename varchar(50));
CREATE TABLE
testdb=# insert into t1 values(1,'app1'),(2,'app2'),(3,'app3'),(4,'app4'),(5,'app5');
INSERT 0 5
testdb=# select * from t1;
id | ename
----+-------
1 | app1
2 | app2
3 | app3
4 | app4
5 | app5
(5 rows)
testdb=# VACUUM t1;
VACUUM
--使用VACUUM full
--需要向正在执行的表增加排他锁，且需要停止其他所有数据库操作。
testdb=# VACUUM full t1;
VACUUM
--使用ANALYZE语句更新统计信息。
testdb=# ANALYZE t1;
ANALYZE
--使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息。
testdb=# ANALYZE VERBOSE t1;
INFO: analyzing "public.t1"(sgnode pid=1676)
INFO: ANALYZE INFO : "t1": scanned 1 of 1 pages, containing 5 live rows and 0 dead rows; 5 rows in sample, 5 estimated total rows(sgnode pid=1676)
ANALYZE
--也可以同时执行VACUUM ANALYZE命令进行查询优化。
testdb=# VACUUM ANALYZE t1;
VACUUM
6、建表、索引，进行重建索引操作
testdb=# create table t2 as select * from t1;
INSERT 0 5
--创建索引
testdb=# create index idx_t2_ename on t2(ename);
CREATE INDEX
testdb=# explain select * from t2 where ename='app5';
QUERY PLAN
----------------------------------------------------
Seq Scan on t2 (cost=0.00..1.06 rows=1 width=122)
Filter: ((ename)::text = 'app5'::text)
(2 rows)
--hint指定使用索引
testdb=# explain select /*+indexscan(t2 idx_t2_ename) */ * from t2 where ename='app5';
QUERY PLAN
-------------------------------------------------------------------------
[Bypass]
Index Scan using idx_t2_ename on t2 (cost=0.00..8.27 rows=1 width=122)
Index Cond: ((ename)::text = 'app5'::text)
(3 rows)
--使用REINDEX TABLE语句重建索引。
testdb=# reindex table t2;
REINDEX
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
