openGauss每日一练第21天 | openGauss数据库例行维护

1、使用操作系统命令和数据库工具检查实例状态

[omm@node1 script]$ ps -ef | grep gaussdb
omm      10419     1  4 18:34 ?        00:00:16 /opt/huawei/install/app/bin/gaussdb -D /opt/huawei/install/data/dn
omm      11758  8334  0 18:41 pts/0    00:00:00 grep --color=auto gaussdb

[omm@node1 script]$ gs_ctl status -D /opt/huawei/install/data/dn
[2022-12-14 18:43:33.552][12569][][gs_ctl]: gs_ctl status,datadir is /opt/huawei/install/data/dn 
gs_ctl: server is running (PID: 10419)
/opt/huawei/install/app/bin/gaussdb "-D" "/opt/huawei/install/data/dn"

2、检查锁信息

openGauss=# select * from pg_locks ;
  locktype  | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  
     pid       |    sessionid    |      mode       | granted | fastpath |      locktag      | global_sessionid 
------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+--
---------------+-----------------+-----------------+---------+----------+-------------------+------------------
 relation   |    15626 |    12135 |      |       |        |            |               |         |       |          | 10/512             | 1
40470924474112 | 140470924474112 | AccessShareLock | t       | t        | 3d0a:2f67:0:0:0:0 | 0:0#0
 virtualxid |          |          |      |       |        | 10/512     |               |         |       |          | 10/512             | 1
40470924474112 | 140470924474112 | ExclusiveLock   | t       | t        | a:200:0:0:0:7     | 0:0#0
(2 rows)
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)

3、查看数据库版本、日志信息

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=# \! ls /var/log/omm/omm/pg_log
dn_6001
openGauss=# \! ls /var/log/omm/omm/pg_log/dn_6001
postgresql-2022-12-14_183459.log

[omm@node1 ~]$ cd /var/log/omm/omm/pg_log
[omm@node1 pg_log]$ ls
dn_6001
[omm@node1 pg_log]$ ls dn_6001/
postgresql-2022-12-14_183459.log

4、检查应用连接数、查看现有最大连接数

openGauss=# SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
 count 
-------
    20
(1 row)openGauss=# SHOW max_connections;
 max_connections 
-----------------
 5000
(1 row)

5、对表进行VACUUM、VACUUM FULL和ANALYZE操作

openGauss=# create table t1 (
openGauss(# id int,
openGauss(# name varchar(10),
openGauss(# age int);
CREATE TABLEopenGauss=# insert into t1 values 
openGauss-# (1,'li',20),
openGauss-# (2,'liu',21),
openGauss-# (3,'wang',22),
openGauss-# (4,'yu',22);
INSERT 0 4

openGauss=# vacuum t1;
VACUUM
openGauss=# vacuum FULL t1;
VACUUM
openGauss=# analyze t1;
ANALYZE
openGauss=# analyze VERBOSE t1;
INFO:  analyzing "public.t1"(dn_6001 pid=10419)
INFO:  ANALYZE INFO : "t1": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows(dn_6001 pid=10419)
ANALYZE
openGauss=# vacuum ANALYZE t1;
VACUUM

6、建表、索引，进行重建索引操作

openGauss=# create table t2 (
openGauss(# id int,
openGauss(# name varchar(10)
openGauss(# );
CREATE TABLE
openGauss=#  insert into t2 values 
openGauss-# (1,'liu'),
openGauss-# (2,'lu'),
openGauss-# (3,'wang'),
openGauss-# (4,'sun');
INSERT 0 4openGauss=# create index idx_t2 on t2 (id);
CREATE INDEX
openGauss=# reindex table t2;
REINDEX