openGauss每日一练第21天

学习数据库的例行维护

学习数据库的日常维护，检查数据库运行状态、锁信息、维护表、重建索引等，理解如何保证数据安全，避免事故发生，完成课后作业。

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

omm=# \! ps -ef|grep gaussdb
omm            1       0 83 Nov30 ?        11-05:21:23 gaussdb
omm        54185   54183  0 02:01 pts/16   00:00:00 grep gaussdb
omm=# \! ps -Tp 1
    PID    SPID TTY          TIME CMD
      1       1 ?        00:00:10 gaussdb
      1     209 ?        00:00:00 jemalloc_bg_thd
      1     213 ?        00:00:00 gaussdb
      1     214 ?        00:00:04 syslogger
      1     215 ?        00:00:00 jemalloc_bg_thd
      1     216 ?        00:02:06 alarm
      1     217 ?        00:00:01 reaper
      1     219 ?        00:00:00 jemalloc_bg_thd
      1     220 ?        00:00:00 jemalloc_bg_thd
      1     244 ?        00:00:25 checkpointer
      1     245 ?        00:18:39 pagewriter
      1     246 ?        00:00:16 pagewriter
      1     247 ?        00:00:08 bgwriter
      1     248 ?        00:00:08 bgwriter
      1     249 ?        9-15:03:18 WALwriter
      1     250 ?        00:00:00 WALwriteraux
      1     251 ?        00:00:03 AVClauncher
      1     252 ?        00:01:32 Jobscheduler
      1     253 ?        00:00:10 statscollector
      1     254 ?        00:09:53 snapshotworker
      1     255 ?        1-12:18:52 percentworker
      1     256 ?        01:00:12 ashworker
      1     257 ?        00:15:42 TrackStmtWorker
      1     258 ?        00:00:02 auditor
      1     259 ?        00:00:36 2pccleaner
      1     260 ?        00:00:00 faultmonitor
      1     261 ?        00:00:16 WLMworker
      1     262 ?        00:02:34 WLMmonitor
      1     263 ?        00:00:01 WLMarbiter
      1   54153 ?        00:00:00 worker
复制

使用数据库工具gs_ctl查看实例状态

omm=# \! gs_ctl status -D /var/lib/opengauss/data
[2022-12-14 02:02:41.119][54191][][gs_ctl]: gs_ctl status,datadir is /var/lib/opengauss/data 
gs_ctl: server is running (PID: 1)
/usr/local/opengauss/bin/gaussdb
复制

检查锁信息

连接数据库后使用sql查看锁信息

omm=# SELECT * FROM pg_locks;
  locktype  | database | relation | page | tuple | bucket | virtualxid | tra
nsactionid | classid | objid | objsubid | virtualtransaction |       pid    
   |    sessionid    |      mode       | granted | fastpath |      locktag  
    
------------+----------+----------+------+-------+--------+------------+----
-----------+---------+-------+----------+--------------------+--------------
---+-----------------+-----------------+---------+----------+---------------
----
 relation   |    16384 |    11835 |      |       |        |            |    
           |         |       |          | 10/175461          | 1402632775902
72 | 140263277590272 | AccessShareLock | t       | t        | 4000:2e3b:0:0:
0:0
 virtualxid |          |          |      |       |        | 10/175461  |    
           |         |       |          | 10/175461          | 1402632775902
72 | 140263277590272 | ExclusiveLock   | t       | t        | a:2ad65:0:0:0:
7
 virtualxid |          |          |      |       |        | 1/232612   |    
           |         |       |          | 1/232612           | 1402634968327
68 | 140263496832768 | ExclusiveLock   | t       | t        | 1:38ca4:0:0:0:
7
复制

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

omm=# SELECT version();
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
-----
 PostgreSQL 9.2.4 (openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:04:0
3 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64
-bit
(1 row)
omm=# \! ls /var/lib/opengauss/data/pg_log
postgresql-2022-11-30_145919.log  postgresql-2022-12-07_000000.log
postgresql-2022-11-30_145931.log  postgresql-2022-12-08_000000.log
postgresql-2022-12-01_000000.log  postgresql-2022-12-09_000000.log
postgresql-2022-12-02_000000.log  postgresql-2022-12-10_000000.log
复制

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

omm=# SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
 count 
-------
    18
复制

查看当前最大连接数量

omm=# SHOW max_connections;
 max_connections 
-----------------
 200
复制

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

初始化customer_t表

omm=# CREATE TABLE customer_t
omm-# (  c_customer_sk             integer,   
omm(#   c_customer_id             char(5),    
omm(#   c_first_name              char(6),    
omm(#   c_last_name               char(8) 
omm(# ) ;
CREATE TABLE
omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES    
omm-# (6885, 1, 'Joes', 'Hunter'),    
omm-# (4321, 2, 'Lily','Carter'),    
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
复制

使用VACUUM命令，对customer_t表进行磁盘空间回收，使用ANALYZE语句对customer_t表更新统计信息。

omm=# VACUUM customer_t;
VACUUM
omm=# ANALYZE customer_t;
ANALYZE
复制

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

在customer_t中对c_customer_id创建索引

omm=# CREATE INDEX customer_id_idx ON customer_t (c_customer_id);
CREATE INDEX
复制

对customer_t表进行重建索引操作

omm=# REINDEX table customer_t;
REINDEX
复制

总结

对opengauss数据库的日常维护，有查看实例信息，检查锁信息，查看实例版本等，查看连接数量，对数据的维护有对表进行进行VACUUM、VACUUM FULL和ANALYZE操作，对表索引进行重建等