学习目标

学习检查数据库运行状态、锁信息、维护表、重建索引等数据库维护操作技术

课程作业

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

[omm@og3 datafs]$ ps -ef|grep gaussdb
omm        2569      1  5 Dec13 ?        00:57:11 /datafs/og3/app/bin/gaussdb -D /datafs/ogdata/dn
omm      128518   2470  0 16:19 pts/0    00:00:00 grep --color=auto gaussdb
[omm@og3 datafs]$ ps -Tp 2569
   PID   SPID TTY          TIME CMD
  2569   2569 ?        00:00:37 gaussdb
  2569   2570 ?        00:00:00 jemalloc_bg_thd
  2569   2575 ?        00:00:00 gaussdb
  2569   2576 ?        00:00:00 syslogger
  2569   2577 ?        00:00:13 alarm
  2569   2578 ?        00:00:00 reaper
  2569   2603 ?        00:00:09 checkpointer
  2569   2604 ?        00:00:00 Spbgwriter
  2569   2605 ?        00:00:04 pagewriter
  2569   2606 ?        00:00:05 pagewriter
  2569   2607 ?        00:00:04 pagewriter
  2569   2608 ?        00:10:44 pagewriter
  2569   2609 ?        00:00:06 pagewriter
  2569   2610 ?        00:00:12 WALwriter
  2569   2611 ?        00:00:00 WALwriteraux
  2569   2612 ?        00:00:00 AVClauncher
  2569   2613 ?        00:00:09 Jobscheduler
  2569   2614 ?        00:00:00 asyncundolaunch
  2569   2615 ?        00:00:01 globalstats
  2569   2616 ?        00:00:00 applylauncher
  2569   2617 ?        00:00:09 statscollector
  2569   2618 ?        00:00:10 txnsnapcapturer
  2569   2619 ?        00:00:00 CfsShrinker
  2569   2620 ?        00:02:28 percentworker
  2569   2621 ?        00:21:30 ashworker
  2569   2622 ?        00:05:28 TrackStmtWorker
  2569   2623 ?        00:00:00 auditor
  2569   2624 ?        00:00:10 2pccleaner
  2569   2625 ?        00:00:00 faultmonitor
  2569   2627 ?        00:02:47 undorecycler

[omm@og3 datafs]$ gs_ctl status -D /datafs/ogdata/dn
[2022-12-14 16:20:35.156][128659][][gs_ctl]: gs_ctl status,datadir is /datafs/ogdata/dn
gs_ctl: server is running (PID: 2569)
/datafs/og3/app/bin/gaussdb "-D" "/datafs/ogdata/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   |    15624 |    12135 |      |       |        |            |               |         |       |          | 10/243938          | 140035487487744 | 140035487487744 | AccessShareLock
| t       | t        | 3d08:2f67:0:0:0:0 | 0:0#0
 virtualxid |          |          |      |       |        | 10/243938  |               |         |       |          | 10/243938          | 140035487487744 | 140035487487744 | ExclusiveLock
| t       | t        | a:3b8e2: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)

[omm@og3 dn_6001]$ ls -ltr
total 36448
-rw-------. 1 omm dbgrp  142744 Nov 28 23:59 postgresql-2022-11-28_225855.log
-rw-------. 1 omm dbgrp 2496946 Nov 29 23:59 postgresql-2022-11-29_000000.log
-rw-------. 1 omm dbgrp 2227772 Nov 30 21:12 postgresql-2022-11-30_000000.log
-rw-------  1 omm dbgrp  298588 Nov 30 23:59 postgresql-2022-11-30_212317.log
-rw-------  1 omm dbgrp 2358049 Dec  1 22:25 postgresql-2022-12-01_000000.log
-rw-------  1 omm dbgrp  500775 Dec  2 23:59 postgresql-2022-12-02_203136.log
-rw-------  1 omm dbgrp 2618784 Dec  3 23:59 postgresql-2022-12-03_000000.log
-rw-------  1 omm dbgrp 2631233 Dec  4 23:59 postgresql-2022-12-04_000000.log
-rw-------  1 omm dbgrp 2511691 Dec  5 23:59 postgresql-2022-12-05_000000.log
-rw-------  1 omm dbgrp 2605140 Dec  6 23:59 postgresql-2022-12-06_000000.log
-rw-------  1 omm dbgrp 2616971 Dec  7 23:59 postgresql-2022-12-07_000000.log
-rw-------  1 omm dbgrp 2608414 Dec  8 23:59 postgresql-2022-12-08_000000.log
-rw-------  1 omm dbgrp 2538371 Dec  9 23:59 postgresql-2022-12-09_000000.log
-rw-------  1 omm dbgrp 2621963 Dec 10 23:59 postgresql-2022-12-10_000000.log
-rw-------  1 omm dbgrp 2613046 Dec 11 23:59 postgresql-2022-12-11_000000.log
-rw-------  1 omm dbgrp 2510647 Dec 12 23:02 postgresql-2022-12-12_000000.log
-rw-------  1 omm dbgrp  117050 Dec 13 23:59 postgresql-2022-12-13_230636.log
-rw-------  1 omm dbgrp 1821393 Dec 14 16:22 postgresql-2022-12-14_000000.log
[omm@og3 dn_6001]$ pwd
/datafs/log/omm/omm/pg_log/dn_6001
复制

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操作

testdb10=# VACUUM  t2;
VACUUM
testdb10=# vacuum full t2;
VACUUM
testdb10=# ANALYZE VERBOSE  t2;
INFO:  analyzing "public.t2"(dn_6001 pid=2569)
INFO:  ANALYZE INFO : "t2": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows(dn_6001 pid=2569)
ANALYZE
testdb10=# VACUUM ANALYZE t2;
VACUUM
复制

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

--创建表
testdb10=#  CREATE TABLE areaS(   area_ID NUMBER,   area_NAME VARCHAR2(25)  );
CREATE TABLE
testdb10=# CREATE INDEX areaS_idx ON areaS (area_id);
CREATE INDEX
testdb10=# \dS+ areas
                                 Table "public.areas"
  Column   |         Type          | Modifiers | Storage  | Stats target | Description
-----------+-----------------------+-----------+----------+--------------+-------------
 area_id   | numeric               |           | main     |              |
 area_name | character varying(25) |           | extended |              |
Indexes:
    "areas_idx" btree (area_id) TABLESPACE test10_tbs
Has OIDs: no
Options: orientation=row, compression=no


--重建索引
testdb10=#  REINDEX  table areas;
REINDEX
复制