暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第21天 | openGauss的数据库例行维护

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

原创 洋 2022-12-14
1055

0.学习内容与环境进入

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

  • 空间回收
    VACUUM FULL

  • 分析
    ANALYZE VERBOSE

  • 查锁
    SELECT * FROM pg_locks

  • 查因锁等待线程
    SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock';

  • 进入环境

su - omm
gsql -r

gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

  • 查看全部数据库
\l
List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
| template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
| (4 rows)

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

  • 查找gaussdb进程
\! ps -ef|grep gaussdb
omm 1 0 0 07:06 ? 00:00:49 gaussdb
omm 1298 1296 0 10:29 pts/0 00:00:00 sh -c ps -ef|grep gaussdb
omm 1300 1298 0 10:29 pts/0 00:00:00 grep gaussdb

  • gaussdb进程所有线程
\! ps -Tp 1
PID SPID TTY TIME CMD
1 1 ? 00:00:00 gaussdb
1 293 ? 00:00:00 jemalloc_bg_thd
1 299 ? 00:00:00 gaussdb
1 300 ? 00:00:00 syslogger
1 301 ? 00:00:00 jemalloc_bg_thd
1 302 ? 00:00:01 alarm
1 303 ? 00:00:00 reaper
1 304 ? 00:00:00 jemalloc_bg_thd
1 305 ? 00:00:00 jemalloc_bg_thd
1 349 ? 00:00:00 checkpointer
1 350 ? 00:00:00 Spbgwriter
1 351 ? 00:00:07 pagewriter
1 353 ? 00:00:00 pagewriter
1 354 ? 00:00:00 pagewriter
1 352 ? 00:00:00 pagewriter
1 356 ? 00:00:00 WALwriter
1 355 ? 00:00:00 pagewriter
1 359 ? 00:00:00 Jobscheduler
1 360 ? 00:00:00 asyncundolaunch
1 361 ? 00:00:00 globalstats
1 358 ? 00:00:00 AVClauncher
1 362 ? 00:00:00 applylauncher
1 363 ? 00:00:00 statscollector
1 364 ? 00:00:05 percentworker
1 365 ? 00:00:22 ashworker
1 366 ? 00:00:04 TrackStmtWorker
1 357 ? 00:00:00 WALwriteraux
1 367 ? 00:00:00 auditor
1 368 ? 00:00:00 2pccleaner
1 369 ? 00:00:00 faultmonitor
1 370 ? 00:00:00 WLMworker
1 371 ? 00:00:01 WLMmonitor
1 372 ? 00:00:00 WLMarbiter
1 373 ? 00:00:00 undorecycler
1 1297 ? 00:00:00 worker

  • 查看状态
\! gs_ctl status
[2022-12-14 10:30:40.855][1308][][gs_ctl]: gs_ctl status,datadir is /var/lib/opengauss/data
gs_ctl: server is running (PID: 1)
/usr/local/opengauss/bin/gaussdb

2、检查锁信息

 SELECT * FROM pg_locks;
locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualt
ransaction | pid | sessionid | mode | granted | fastpath | locktag | global_sessionid
-----------+-----------------+-----------------+-----------------+---------+----------+-------------------+------------------
relation | 16384 | 12010 | | | | | | | | | 11/3249
| 281471376719088 | 281471376719088 | AccessShareLock | t | t | 4000:2eea:0:0:0:0 | 0:0#0
virtualxid | | | | | | 11/3249 | | | | | 11/3249
| 281471376719088 | 281471376719088 | ExclusiveLock | t | t | b:cb1:0:0:0:7 | 0:0#0
virtualxid | | | | | | 8/2472 | | | | | 8/2472
| 281471553338608 | 281471553338608 | ExclusiveLock | t | t | 8:9a8:0:0:0:7 | 0:0#0
(3 rows)

- 查看因锁造成等待 
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、查看数据库版本、日志信息

  • 查看数据库版本
SELECT version();
version

------------------------------------------------------------------------------------------------
(openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr on aarch64-unknown-linux-gnu, compiled by
g++ (GCC) 7.3.0, 64-bit
(1 row)

  • 查看日志位置
\! ls /var/lib/opengauss/data/pg_log
postgresql-2022-12-14_070711.log postgresql-2022-12-14_070713.log

  • 查看日志最近10行
\! tail /var/lib/opengauss/data/pg_log/postgresql-2022-12-14_070713.log
2022-12-14 10:33:22.467 [unknown] [unknown] localhost 281472297762032 0[0:0#0] 0 [BACKEND] LOG: attempting to remove WAL segments older than log file 000000010000000000000000
2022-12-14 10:33:31.170 omm postgres localhost 281471290735856 0[0:0#0] 0 [BACKEND] LOG: clean statement thread start
2022-12-14 10:34:22.723 [unknown] [unknown] localhost 281472297762032 0[0:0#0] 0 [DBL_WRT] LOG: [batch flush] DW truncate end: file_head[dwn 0, start 61], total_pages 0
2022-12-14 10:34:22.728 [unknown] [unknown] localhost 281472297762032 0[0:0#0] 0 [BACKEND] LOG: keep all the xlog segments, because current segno = 2, less than wal_keep_segments = 16
2022-12-14 10:34:22.728 [unknown] [unknown] localhost 281472297762032 0[0:0#0] 0 [BACKEND] WARNING: replicationSlotMinLSN is InvalidXLogRecPtr!!!
2022-12-14 10:34:22.728 [unknown] [unknown] localhost 281472297762032 0[0:0#0] 0 [BACKEND] WARNING: replicationSlotMaxLSN is InvalidXLogRecPtr!!!
2022-12-14 10:34:22.728 [unknown] [unknown] localhost 281472297762032 0[0:0#0] 0 [BACKEND] LOG: CreateCheckPoint PrintCkpXctlControlFile: [checkPoint] oldCkpLoc:0/23097F8, oldRedo:0/2309778, newCkpLoc:0/2309998, newRedo:0/2309918, preCkpLoc:0/2309658
2022-12-14 10:34:22.728 [unknown] [unknown] localhost 281472297762032 0[0:0#0] 0 [BACKEND] LOG: will update control file (create checkpoint), shutdown:0
2022-12-14 10:34:22.732 [unknown] [unknown] localhost 281472297762032 0[0:0#0] 0 [BACKEND] LOG: attempting to remove WAL segments older than log file 000000010000000000000000
2022-12-14 10:34:31.263 omm postgres localhost 281471336873200 0[0:0#0] 0 [BACKEND] LOG: clean statement thread start

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

  • 检查应用连接数
SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
count
-------
21
(1 row)

  • 查看现有最大连接数
SHOW max_connections;
max_connections
-----------------
200
(1 row)

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

DROP tablespace IF EXISTS test_tbs;
CREATE TABLESPACE test_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE DATABASE testdb  WITH TABLESPACE = test_tbs;
\c testdb
CREATE TABLE t1(id serial, sk integer, desp char(35));
INSERT INTO t1(sk, desp) VALUES(1, 'This is for t1.1');
INSERT INTO t1(sk, desp) VALUES(2, 'This is for t1.2');
CREATE TABLESPACE
CREATE DATABASE
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "omm".
CREATE TABLE
INSERT 0 1
INSERT 0 1

VACUUM t1;
VACUUM FULL t1;
VACUUM
VACUUM

ANALYZE VERBOSE t1;
INFO: analyzing "public.t1"(gaussdb pid=1)
INFO: ANALYZE INFO : "t1": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows(gaussdb pid=1)
ANALYZE

# 6、建表、索引，进行重建索引操作 
CREATE TABLE t2(id serial, sk integer, desp char(35));
INSERT INTO t2(sk, desp) VALUES(1, 'This is for t2.1');
INSERT INTO t2(sk, desp) VALUES(2, 'This is for t2.2');
INSERT INTO t2(sk, desp) VALUES(3, 'This is for t2.3');
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1

CREATE INDEX  ON t2(id);
\di
REINDEX  table t2;
CREATE INDEX
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+-----------+-------+-------+-------+---------
public | t2_id_idx | index | omm | t2 |
(1 row)
REINDEX

墨力计划opengaussopengauss每日一练
最后修改时间：2022-12-14 11:30:47
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论