课程作业

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

--使用操作系统
omm@modb:~$ ps -ef|grep gaussdb
omm          1     0  0 11:55 ?        00:00:04 gaussdb
omm        431   295  0 12:07 pts/0    00:00:00 grep gaussdb
omm@modb:~$ ps -Tp 1
PID  SPID TTY          TIME CMD
1     1 ?        00:00:00 gaussdb
1   298 ?        00:00:00 jemalloc_bg_thd
1   304 ?        00:00:00 gaussdb
1   305 ?        00:00:00 syslogger
1   306 ?        00:00:00 jemalloc_bg_thd
1   307 ?        00:00:00 alarm
1   308 ?        00:00:00 reaper
1   309 ?        00:00:00 jemalloc_bg_thd
1   310 ?        00:00:00 jemalloc_bg_thd
1   354 ?        00:00:00 checkpointer
1   355 ?        00:00:00 Spbgwriter
1   356 ?        00:00:00 pagewriter
1   357 ?        00:00:00 pagewriter
1   358 ?        00:00:00 pagewriter
1   359 ?        00:00:00 pagewriter
1   360 ?        00:00:00 pagewriter
1   361 ?        00:00:00 WALwriter
1   362 ?        00:00:00 WALwriteraux
1   363 ?        00:00:00 AVClauncher
1   364 ?        00:00:00 Jobscheduler
1   365 ?        00:00:00 asyncundolaunch
1   366 ?        00:00:00 globalstats
1   367 ?        00:00:00 applylauncher
1   368 ?        00:00:00 statscollector
1   369 ?        00:00:00 percentworker
1   370 ?        00:00:01 ashworker
1   371 ?        00:00:00 TrackStmtWorker
1   372 ?        00:00:00 auditor
1   373 ?        00:00:00 2pccleaner
1   378 ?        00:00:00 undorecycler
1   374 ?        00:00:00 faultmonitor
1   375 ?        00:00:00 WLMworker
1   376 ?        00:00:00 WLMmonitor
1   377 ?        00:00:00 WLMarbiter
omm@modb:~$ 
--数据库工具检查实例状态
omm@modb:~$ gs_ctl status -D /var/lib/opengauss/data
[2022-12-14 12:09:32.857][441][][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、检查锁信息

--查询数据库中的锁信息

--查询等待锁的线程状态信息

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

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

--检查应用连接数
omm=# SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
 count 
-------
21
(1 row)
--查看现有最大连接数
omm=# SHOW max_connections;
max_connections 
-----------------
200
(1 row)

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

--创建模拟表
omm=# create table yangkai(id int,name char(20));
CREATE TABLE
omm=# insert into yangkai values(1,'yangkai');
INSERT 0 1
omm=# insert into yangkai values(2,'zhangsan');
INSERT 0 1
--没执行VACUUM 之前，查看行数
omm=# select relpages,reltuples from  pg_class where relname='yangkai';
relpages | reltuples 
----------+-----------
       0 |         0
(1 row)
omm=# VACUUM yangkai;
VACUUM
--执行VACUUM 之后，查看行数
omm=# select relpages,reltuples from  pg_class where relname='yangkai';
relpages | reltuples 
----------+-----------
    1 |         2
(1 row)
---VACUUM FULL
omm=# delete from yangkai;
DELETE 2
omm=# select relname,n_dead_tup from pg_stat_all_tables where relname='yangkai';
relname | n_dead_tup 
---------+------------
yangkai |          2
(1 row)
omm=# VACUUM FULL  yangkai;
VACUUM
omm=# select relname,n_dead_tup from pg_stat_all_tables where relname='yangkai';
relname | n_dead_tup 
---------+------------
 yangkai |          2
(1 row)
---VACUUM ANALYZE 操作
omm=# VACUUM ANALYZE yangkai; 
VACUUM
omm=# select relname,n_dead_tup from pg_stat_all_tables where relname='yangkai';
relname | n_dead_tup 
---------+------------
yangkai |          0
(1 row)

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

omm=# create table yangbaji(id int,name char(10));
omm=# CREATE TABLE
omm=# create index idx_yangbaji_id  on yangbaji(id);
CREATE INDEX
omm=# reindex table yangbaji;
REINDEX