课后作业
1、使用操作系统命令和数据库工具检查实例状态
root@modb:~# su - omm
omm@modb:~$ ps -ef|grep gaussdb
omm 1 0 0 10:20 ? 00:00:04 gaussdb
omm 450 446 0 10:34 pts/0 00:00:00 grep gaussdb
omm@modb:~$ ps -Tp 1
PID SPID TTY TIME CMD
1 1 ? 00:00:00 gaussdb
1 294 ? 00:00:00 jemalloc_bg_thd
1 300 ? 00:00:00 gaussdb
1 301 ? 00:00:00 syslogger
1 302 ? 00:00:00 jemalloc_bg_thd
1 303 ? 00:00:00 alarm
1 304 ? 00:00:00 reaper
1 305 ? 00:00:00 jemalloc_bg_thd
1 306 ? 00:00:00 jemalloc_bg_thd
1 350 ? 00:00:00 checkpointer
1 351 ? 00:00:00 Spbgwriter
1 352 ? 00:00:00 pagewriter
1 353 ? 00:00:00 pagewriter
1 354 ? 00:00:00 pagewriter
1 355 ? 00:00:00 pagewriter
1 356 ? 00:00:00 pagewriter
1 357 ? 00:00:00 WALwriter
1 358 ? 00:00:00 WALwriteraux
1 359 ? 00:00:00 AVClauncher
1 360 ? 00:00:00 Jobscheduler
1 361 ? 00:00:00 asyncundolaunch
1 362 ? 00:00:00 globalstats
1 363 ? 00:00:00 applylauncher
1 364 ? 00:00:00 statscollector
1 365 ? 00:00:00 percentworker
1 366 ? 00:00:01 ashworker
1 367 ? 00:00:00 TrackStmtWorker
1 368 ? 00:00:00 auditor
1 369 ? 00:00:00 2pccleaner
1 370 ? 00:00:00 faultmonitor
1 371 ? 00:00:00 WLMworker
1 372 ? 00:00:00 WLMmonitor
1 373 ? 00:00:00 WLMarbiter
1 374 ? 00:00:00 undorecycler
omm@modb:~$ gs_ctl status -D /var/lib/opengauss/data
[2022-12-14 10:34:19.086][452][][gs_ctl]: gs_ctl status,datadir is /var/lib/opengauss/data
gs_ctl: server is running (PID: 1)
/usr/local/opengauss/bin/gaussdb
omm@modb:~$ 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.
omm=# show data_directory;
data_directory
-------------------------
/var/lib/opengauss/data
(1 row)
复制2、检查锁信息
omm=# SELECT * FROM pg_locks;
locktype | database | relation | page | tuple | bucket | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | session
id | mode | granted | fastpath | locktag | global_sessionid
------------+----------+----------+------+-------+--------+------------+---------------+---------+-------+----------+--------------------+-----------------+-----------
------+-----------------+---------+----------+-------------------+------------------
relation | 16384 | 12010 | | | | | | | | | 11/192 | 281471431245040 | 2814714312
45040 | AccessShareLock | t | t | 4000:2eea:0:0:0:0 | 0:0#0
virtualxid | | | | | | 11/192 | | | | | 11/192 | 281471431245040 | 2814714312
45040 | ExclusiveLock | t | t | b:c0:0:0:0:7 | 0:0#0
virtualxid | | | | | | 9/164 | | | | | 9/164 | 281471638863088 | 2814716388
63088 | ExclusiveLock | t | t | 9:a4:0:0:0:7 | 0:0#0
(3 rows)
omm=# 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
-----------+---------+-------------+----------+-----+-----------+-------+------------+--------+-------+-------------+------------+---------+----------+----------------
-+------------------
复制3、查看数据库版本、日志信息
omm=# 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)
复制4、检查应用连接数、查看现有最大连接数
omm@local:/opt/huawei/tmp [postgres]=#SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
count
-------
20
(1 row)
omm@local:/opt/huawei/tmp [postgres]=#SHOW max_connections;
max_connections
-----------------
5000
(1 row)
复制5、对表进行VACUUM、VACUUM FULL和ANALYZE操作
omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE day21 RELATIVE LOCATION 'tablespace/day21';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE xxstestdb WITH TABLESPACE = day21;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#CREATE USER xxs IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#ALTER USER xxs SYSADMIN;
ALTER ROLE
omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLE test_tb
openGauss-# ( c_customer_sk integer,
openGauss(# c_customer_id char(5),
openGauss(# c_first_name char(6),
openGauss(# c_last_name char(8)
openGauss(# ) ;^C
omm@local:/opt/huawei/tmp [postgres]=#
omm@local:/opt/huawei/tmp [postgres]=#
omm@local:/opt/huawei/tmp [postgres]=#\c xxstestdb xxs
Password for user xxs:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "xxstestdb" as user "xxs".
xxs@local:/opt/huawei/tmp [xxstestdb]=>CREATE TABLE test_tb
xxstestdb-> ( c_customer_sk integer,
xxstestdb(> c_customer_id char(5),
xxstestdb(> c_first_name char(6),
xxstestdb(> c_last_name char(8)
xxstestdb(> ) ;
CREATE TABLE
xxs@local:/opt/huawei/tmp [xxstestdb]=>INSERT INTO test_tb (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
xxstestdb-> (6885, 1, 'Joes', 'Hunter'),
xxstestdb-> (4321, 2, 'Lily','Carter'),
xxstestdb-> (9527, 3, 'James', 'Cook'),
xxstestdb-> (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
xxs@local:/opt/huawei/tmp [xxstestdb]=>select count(*) from test_tb;
count
-------
4
(1 row)
xxs@local:/opt/huawei/tmp [xxstestdb]=>
xxs@local:/opt/huawei/tmp [xxstestdb]=>VACUUM test_tb; <-----------------------使用VACUUM或VACUUM FULL命令，进行磁盘空间回收。
VACUUM
xxs@local:/opt/huawei/tmp [xxstestdb]=>VACUUM FULL test_tb; <-----------------------需要向正在执行的表增加排他锁，且需要停止其他所有数据库操作。
VACUUM
xxs@local:/opt/huawei/tmp [xxstestdb]=>ANALYZE test_tb; <-----------------------使用ANALYZE语句更新统计信息。
ANALYZE
xxs@local:/opt/huawei/tmp [xxstestdb]=>ANALYZE VERBOSE test_tb; <-----------------------使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息。
INFO: analyzing "public.test_tb"(dn_6001_6002 pid=29053)
INFO: ANALYZE INFO : "test_tb": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows(dn_6001_6002 pid=29053)
ANALYZE
xxs@local:/opt/huawei/tmp [xxstestdb]=>VACUUM ANALYZE test_tb; <-----------------------也可以同时执行VACUUM ANALYZE命令进行查询优化。
VACUUM
复制6、建表、索引，进行重建索引操作
xxs@local:/opt/huawei/tmp [xxstestdb]=>CREATE INDEX idx_xxs ON test_tb (c_customer_id);
CREATE INDEX
xxs@local:/opt/huawei/tmp [xxstestdb]=>\di+
List of relations
Schema | Name | Type | Owner | Table | Size | Storage | Description
--------+------------+-------+--------+-----------+-------+---------+-------------
public | idx_xxs | index | xxs | test_tb | 16 kB | |
(1 row)
xxs@local:/opt/huawei/tmp [xxstestdb]=>REINDEX table test_tb;
REINDEX
复制最后修改时间：2022-12-14 10:48:55
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
墨天轮-雪宝君
2年前
评论
0
作业审核合格，一起参与21天openGauss学习打卡活动！
活动详情：https://www.modb.pro/db/5516192年前
点赞 评论
相关阅读
2025年3月国产数据库大事记
墨天轮编辑部
877次阅读
2025-04-03 15:21:16
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
287次阅读
2025-04-17 10:41:41
openGauss 7.0.0-RC1 版本正式发布!
Gauss松鼠会
205次阅读
2025-04-01 12:27:03
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
云和恩墨
186次阅读
2025-04-16 09:52:02
openGauss 7.0.0-RC1 版本体验：一主一备快速安装指南
孙莹
180次阅读
2025-04-01 10:30:07
鲲鹏RAG一体机解决方案正式发布 openGauss DataVec向量数据库助力DeepSeek行业应用
Gauss松鼠会
124次阅读
2025-03-31 10:00:29
荣誉时刻！openGauss认证证书快递已发，快来看看谁榜上有名！
墨天轮小教习
112次阅读
2025-04-23 17:39:13
openGauss6.0.0适配操作系统自带的软件，不依赖三方库
来杯拿铁
75次阅读
2025-04-18 10:49:53
opengauss使用gs_probackup进行增量备份恢复
进击的CJR
70次阅读
2025-04-09 16:11:58
Postgresql数据库单个Page最多存储多少行数据
maozicb
60次阅读
2025-04-23 16:02:19
