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

原创那纸忧伤 2022-12-14

260

实操作业

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

--使用系统进程查看命令查看gaussdb是否在运行
[omm@opengauss-pri ~]$ ps -ef |grep gaussdb
omm         7725       1  0 11:02 ?        00:00:00 ssh-agent -a /home/omm/gaussdb_tmp/gauss_socket_tmp
omm        29018       1  2 11:25 ?        00:01:30 /opt/huawei/install/app/bin/gaussdb -D /opt/huawei/install/data/dn -M primary
omm       528782  104836  0 12:37 pts/0    00:00:00 grep --color=auto gaussdb
--使用gs_ctl工具查看实例状态
[omm@opengauss-pri ~]$ gs_ctl  status  -D /opt/huawei/install/data/dn/
[2022-12-14 12:41:16.547][555951][][gs_ctl]: gs_ctl status,datadir is /opt/huawei/install/data/dn 
gs_ctl: server is running (PID: 29018)
/opt/huawei/install/app/bin/gaussdb "-D" "/opt/huawei/install/data/dn" "-M" "primary"
--如果是集群部署，可以使用gs_check工具查看集群状态
[omm@opengauss-pri ~]$ gs_check -U omm -i CheckClusterState
Parsing the check items config file successfully
Distribute the context file to remote hosts successfully
Start to health check for the cluster. Total Items:1 Nodes:2

Checking...               [=========================] 1/1
Start to analysis the check result
CheckClusterState...........................OK
The item run on 2 nodes.  success: 2 

Analysis the check result successfully
Success.        All check items run completed. Total:1   Success:1    
For more information please refer to /opt/huawei/install/om/script/gspylib/inspection/output/CheckReport_2022121445877580573.tar.gz
--查看数据库数据目录
show data_directory;
--查看系统所有参数
show all;

2、检查锁信息

锁机制是数据库保证数据一致性的重要手段，检查相关信息可以检查数据库的事务和运行状况。

--查询数据库中的锁信息。
SELECT * FROM pg_locks;
--查询等待锁的线程状态信息。
SELECT * FROM pg_thread_wait_status WHERE wait_status = 'acquire lock';
--查询事件的时间，查询事件的线程启动时间、事务启动时间、SQL启动时间以及状态变更时间。
SELECT backend_start,xact_start,query_start,state_change FROM pg_stat_activity;
--查询当前服务器的会话计数信息
 SELECT count(*) FROM pg_stat_activity;
--查询系统级统计信息，查询当前使用内存最多的会话信息。
  SELECT * FROM pv_session_memory_detail() ORDER BY usedsize desc limit 10;
--查询表统计信息
 SELECT * FROM pg_statistic;

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

--数据库版本 
SELECT version();
--查看数据库日志位置
--通过系统命令查看数据库日志路径
root@modb:~# find / -name pg_log
/var/lib/opengauss/data/pg_log
--通过环境变量查看数据库日志路径
[omm@opengauss-pri ~]$ echo $GAUSSLOG
/var/log/omm/
--查看表大小
SELECT pg_table_size('table_name');
--查看库大小
SELECT pg_database_size('database_name');

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

如果应用程序与数据库的连接数超过最大值，则新的连接无法建立。建议每天检查连接数，及时释放空闲的连接或者增加最大连接数。

--查看当前连接到数据库的应用连接数
openGauss=# SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
 count 
-------
    24
(1 row)
--查看当前最大连接数
openGauss=# show max_connections ;
 max_connections 
-----------------
 5000
(1 row)

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

为了保证数据库的有效运行，数据库必须在插入/删除操作后，基于客户场景，定期做VACUUM FULL和ANALYZE，更新统计信息，以便获得更优的性能。

使用VACUUM、VACUUM FULL和ANALYZE命令定期对每个表进行维护，主要有以下原因：

VACUUM FULL可回收已更新或已删除的数据所占据的磁盘空间，同时将小数据文件合并。
VACUUM对每个表维护了一个可视化映射来跟踪包含对别的活动事务可见的数组的页。一个普通的索引扫描首先通过可视化映射来获取对应的数组，来检查是否对当前事务可见。若无法获取，再通过堆数组抓取的方式来检查。因此更新表的可视化映射，可加速唯一索引扫描。
VACUUM可避免执行的事务数超过数据库阈值时，事务ID重叠造成的原有数据丢失。
ANALYZE可收集与数据库中表内容相关的统计信息。统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，生成最有效的执行计划。
维护建议**：
- 定期对部分大表做VACUUM FULL，在性能下降后为全库做VACUUM FULL，目前暂定每月做一次VACUUM FULL。
- 定期对系统表做VACUUM FULL，主要是PG_ATTRIBUTE。
- 启用系统自动清理线程（AUTOVACUUM）自动执行VACUUM和ANALYZE，回收被标识为删除状态的记录空间，并更新表的统计数据。

--对表执行VACUUM操作,可以与数据库操作命令并行运行。（执行期间，可正常使用的语句：SELECT、INSERT、UPDATE和DELETE。不可正常使用的语句：ALTER TABLE）。
openGauss=# vacuum newproducts;
VACUUM
--需要向正在执行的表增加排他锁，且需要停止其他所有数据库操作。
openGauss=# vacuum FULL products ;
VACUUM
--使用ANALYZE语句更新统计信息。
openGauss=# ANALYZE products;
ANALYZE
--使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息。
openGauss=# ANALYZE VERBOSE products;
ANALYZE
--也可以同时执行VACUUM ANALYZE命令进行查询优化。
openGauss=# VACUUM ANALYZE products;
VACUUM

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

数据库经过多次删除操作后，索引页面上的索引键将被删除，造成索引膨胀。例行重建索引，可有效的提高查询效率。数据库支持的索引类型为B-tree索引，例行重建索引可有效的提高查询效率。

如果数据发生大量删除后，索引页面上的索引键将被删除，导致索引页面数量的减少，造成索引膨胀。重建索引可回收浪费的空间。
新建的索引中逻辑结构相邻的页面，通常在物理结构中也是相邻的，所以一个新建的索引比更新了多次的索引访问速度要快。
重建索引的两种方式:
- 先运行DROP INDEX语句删除索引，再运行CREATE INDEX语句创建索引
- 使用REINDEX语句重建索引

--测试表相信
openGauss=# \d+ products 
                                 Table "public.products"
    Column    |         Type          | Modifiers | Storage  | Stats target | Description 
--------------+-----------------------+-----------+----------+--------------+-------------
 product_id   | integer               |           | plain    |              | 
 product_name | character varying(60) |           | extended |              | 
 category     | character varying(60) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=nos
--通过以上信息发现表products没有索引，创建索引
openGauss=# create index  products_product_id_idx on products ( product_id);
CREATE INDEX
--查看索引
openGauss=# \di+
                                      List of relations
 Schema |          Name           | Type  | Owner |  Table   | Size  | Storage | Description 
--------+-------------------------+-------+-------+----------+-------+---------+-------------
 public | products_product_id_idx | index | omm   | products | 16 kB |         | 
(1 row)
--通过新建索引重建索引
openGauss=# drop index if exists products_product_id_idx;
DROP INDEX
openGauss=# create index  products_product_id_idx_new on products ( product_id);
CREATE INDEX
--使用REINDEX语句重建索引
openGauss=# reindex table products ;
REINDEX
--注：在重建索引前，用户可以通过临时增大maintenance_work_mem和psort_work_mem的取值来加快索引的重建。
openGauss=#  show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 128MB
(1 row)

openGauss=# show psort_work_mem;
 psort_work_mem 
----------------
 512MB
(1 row)
--修改参数大小
openGauss=# set maintenance_work_mem TO 262144;
SET
openGauss=# show maintenance_work_mem ;
 maintenance_work_mem 
----------------------
 256MB
(1 row)
openGauss=# set psort_work_mem to 1048576;
SET
openGauss=# show psort_work_mem ;
 psort_work_mem 
----------------
 1GB
(1 row)

openGauss=#

opengauss opengauss每日一练

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者