openGauss数据库维护管理指导（五）

已于 2023-03-30 16:36:04 修改
阅读量391 收藏
点赞数
分类专栏： openGauss 文章标签： 数据库
于 2022-11-17 14:23:43 首次发布
原文链接：https://www.hikunpeng.com/forum/thread-0218102754210873006-1-1.html
版权

上期我们介绍了openGauss 数据库日志检查和清理和最大连接数设置，接下来我们继续介绍openGauss数据库例行表、索引的维护相关内容。

6.例行表、索引的维护

6.1 实验介绍

6.1.1 关于本实验

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

  • VACUUM FULL可回收已更新或已删除的数据所占据的磁盘空间，同时将小数据文件合并；
  • VACUUM对每个表维护了一个可视化映射来跟踪包含对别的活动事务可见的数组的页。一个普通的索引扫描首先通过可视化映射来获取对应的数组，来检查是否对当前事务可见。若无法获取，再通过堆数组抓取的方式来检查。因此更新表的可视化映射，可加速唯一索引扫描；
  • **YZE可收集与数据库中表内容相关的统计信息。统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，生成最有效的执行计划。
  • 数据库经过多次删除操作后，索引页面上的索引键将被删除，造成索引膨胀。例行重建索引，可有效的提高查询效率。本实验主要是通过使用VACUUM、VACUUM FULL FULL来收缩表，用**YZE来收集表的统计信息以及对表上的索引进行重建。

6.1.2 实验目的

掌握VACUUM、VACUUM FULL FULL、**YZE基本的使用及如何重建索引；

6.2 场景设置及操作步骤

步骤 1 用root用户登录装有openGauss数据库服务的操作系统然后用 su - omm命令切换至omm用户环境，登录后信息如下。

Welcome to 4.19.90-2003.4.0.0036.oe1.aarch64
System information as of time: Tue Jul 27 16:21:11 CST 2020
System load: 0.01
Processes: 109
Memory used: 6.7%
Swap used: 0.0%
Usage On: 15%
IP address: 192.168.0.96
Users online: 1
[root@ecs-e1b3 ~]# su - omm
Last login: Fri Jul 27 16:22:11 CST 2020 on pts/0
Welcome to 4.19.90-2003.4.0.0036.oe1.aarch64
System information as of time: Tue Jul 27 16:21:11 CST 2020
System load: 0.01
Processes: 111
Memory used: 7.0%
Swap used: 0.0%
Usage On: 15%
IP address: 192.168.0.96
Users online: 1
[omm@ecs-e1b3 ~]$

步骤 2 启动服务器后，然后使用gsql客户端以管理员用户身份连接postgres数据库，假设端口号为26000。
启动数据库服务。

[omm@ecs-e1b3 ~]$ gs_om -t start;
Starting cluster.
=========================================
=========================================
Successfully started.

连接postgres数据库。

[omm@ecs-e1b3 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 1.1.0 build 38a9312a) compiled at 2020-05-27 14:56:08 commit 472 last mr 549 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

步骤 3 创建student表。

postgres=# drop table student;
postgres=# CREATE TABLE student
( std_id INT NOT NULL,
std_name VARCHAR(20) NOT NULL,
std_** VARCHAR(6),
std_birth DATE,
std_in DATE NOT NULL,
std_address VARCHAR(100)
);

CREATE TABLE

步骤 4 表数据插入。

INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (1,'张一','男','1993-01-01','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (2,'张二','男','1993-01-02','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (3,'张三','男','1993-01-03','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (4,'张四','男','1993-01-04','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (5,'张五','男','1993-01-05','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (6,'张六','男','1993-01-06','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (7,'张七','男','1993-01-07','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (8,'张八','男','1993-01-08','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (9,'张九','男','1993-01-09','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (10,'李一','男','1993-01-10','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (11,'李二','男','1993-01-11','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (12,'李三','男','1993-01-12','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (13,'李四','男','1993-01-13','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (14,'李五','男','1993-01-14','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (15,'李六','男','1993-01-15','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (16,'李七','男','1993-01-16','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (17,'李八','男','1993-01-17','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (18,'李九','男','1993-01-18','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (19,'王一','男','1993-01-19','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (20,'王二','男','1993-01-20','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (21,'王三','男','1993-01-21','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (22,'王四','男','1993-01-22','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (23,'王五','男','1993-01-23','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (24,'王六','男','1993-01-24','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (25,'王七','男','1993-01-25','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (26,'王八','男','1993-01-26','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (27,'王九','男','1993-01-27','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (28,'钱一','男','1993-01-28','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (29,'钱二','男','1993-01-29','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (30,'钱三','男','1993-01-30','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (31,'钱四','男','1993-02-01','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (32,'钱五','男','1993-02-02','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (33,'钱六','男','1993-02-03','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (34,'钱七','男','1993-02-04','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (35,'钱八','男','1993-02-05','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (36,'钱九','男','1993-02-06','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (37,'吴一','男','1993-02-07','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (38,'吴二','男','1993-02-08','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (39,'吴三','男','1993-02-09','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (40,'吴四','男','1993-02-10','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (41,'吴五','男','1993-02-11','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (42,'吴六','男','1993-02-12','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (43,'吴七','男','1993-02-13','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (44,'吴八','男','1993-02-14','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (45,'吴九','男','1993-02-15','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (46,'柳一','男','1993-02-16','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (47,'柳二','男','1993-02-17','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (48,'柳三','男','1993-02-18','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (49,'柳四','男','1993-02-19','2011-09-01','江苏省南京市雨花台区');
INSERT INTO student(std_id,std_name,std_**,std_birth,std_in,std_address) VALUES (50,'柳五','男','1993-02-20','2011-09-01','江苏省南京市雨花台区');

步骤 5 数据查询统计。

postgres=# select count(*) from student;
count
-------
50
(1 row)

postgres=# select * from student order by std_id;
std_id | std_name | std_** | std_birth | std_in | std_address
--------+----------+---------+---------------------+---------------------+----------------------
1 | 张一 | 男 | 1993-01-01 00:00:00 | 2011-09-01 00:00:00 | 江苏省南京市雨花台区
2 | 张二 | 男 | 1993-01-02 00:00:00 | 2011-09-01 00:00:00 | 江苏省南京市雨花台区
3 | 张三 | 男 | 1993-01-03 00:00:00 | 2011-09-01 00:00:00 | 江苏省南京市雨花台区
4 | 张四 | 男 | 1993-01-04 00:00:00 | 2011-09-01 00:00:00 | 江苏省南京市雨花台区
5 | 张五 | 男 | 1993-01-05 00:00:00 | 2011-09-01 00:00:00 | 江苏省南京市雨花台区
……………..

步骤 6 查看表信息。

postgres=# \d student
Table "public.student"
Column | Type | Modifiers
-------------+--------------------------------+-----------
std_id | integer | not null
std_name | character varying(20) | not null
std_** | character varying(6) |
std_birth | timestamp(0) without time zone |
std_in | timestamp(0) without time zone | not null
std_address | character varying(100) |

步骤 7 使用VACUUM命令，进行磁盘空间回收。

postgres=# vacuum student;
VACUUM

步骤 8 删除表中数据。

postgres=# delete from student where std_id>30;
DELETE 20

步骤 9 使用VACUUM FULL命令，进行磁盘空间回收。

postgres=# vacuum full student;
VACUUM

步骤 10 使用**YZE语句更新统计信息。

postgres=# **yze student;
**YZE

步骤 11 使用**YZE VERBOSE语句更新统计信息，并输出表的相关信息。

postgres=# **yze verbose student;
INFO: **yzing "public.student"(dn_6001 pid=37195)
INFO: **YZE INFO : "student": scanned 1 of 1 pages, containing 30 live rows and 20 dead rows; 30 rows in sample, 30 estimated total rows(dn_6001 pid=37195)
**YZE

步骤 12 执行VACUUM **YZE命令进行查询优化。

postgres=# vacuum **yze student;
VACUUM

步骤 13 查看特定表的统计信息

postgres=# select relname,n_tup_ins,n_tup_upd,n_tup_del,last_**yze,vacuum_count from PG_STAT_ALL_TABLES where relname='student';
relname | n_tup_ins | n_tup_upd | n_tup_del | last_**yze | vacuum_count
---------+-----------+-----------+-----------+------------------------------+--------------
student | 50 | 0 | 20 | 2020-07-27 17:07:19.17167+08 | 3
(1 row)
postgres=#

PG_STAT_ALL_TABLES视图将包含当前数据库中每个表的一行统计信息，以上查询结果中各列分别表示：
Relname 表名
n_tup_ins 插入行数
n_tup_upd 更新行数
n_tup_del 删除行数
last_**yze 上次手动分析该表的时间
vacuum_count 这个表被手动清理的次数

步骤 14 索引维护。
说明：

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

先在student表的std_name列上创建一个索引，如下：

postgres=# create index inx_stu01 on student(std_name);
CREATE INDEX
postgres=#

方式1：使用REINDEX语句重建索引，具体如下：

postgres=# reindex table student;
REINDEX
postgres=#

方式2：先删除索引（DROP INDEX），再创建索引（CREATE INDEX），具体如下：

postgres=# drop index inx_stu01;
DROP INDEX
postgres=# create index inx_stu01 on student(std_name);
CREATE INDEX
postgres=#

查看表结构信息，具体如下：

postgres=# \d student;
Table "public.student"
Column | Type | Modifiers
-------------+--------------------------------+-----------
std_id | integer | not null
std_name | character varying(20) | not null
std_** | character varying(6) |
std_birth | timestamp(0) without time zone |
std_in | timestamp(0) without time zone | not null
std_address | character varying(100) |
Indexes:
"inx_stu01" btree (std_name) TABLESPACE pg_default

步骤 15 退出数据库

postgres=#\q

例行表、索引的维护实验结束。

至此，openGauss数据库维护管理指导结束。

小嗑数据库
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
openGauss数据库管理系统-其他
06-13
openGauss是一款开源的关系型数据库管理系统，它具有多核高性能、全链路安全性、智能运维等企业级特性。 openGauss内核早期源自开源数据库PostgreSQL，融合了华为在数据库领域多年的内核经验，在架构、事务、存储...
EMP-SSL: TOWARDS SELF-SUPERVISED LEARNING IN ONETRAINING EPOCH
asd8705的专栏
07-26 1732
Recently, self-supervised learning (SSL) has achieved tremendous success in learning image representation. Despite the empirical success, most self-supervised learning methods are rather “inefficient” learners, typically taking hundreds of training epochs
建议收藏 | 在openGauss上遇到慢SQL该怎么办？
openGauss的博客
06-29 897
在数据库的日常使用中，难免会遇到慢SQL，遇到慢SQL本身并不可怕，困难之处在于如何识别慢SQL并对其优化，使它不至于拖慢整个系统的性能，避免危害到日常业务的正常进行。 ...
open Gauss 数据库-05 openGauss数据库备份恢复指导手册
最新发布
qq_62362934的博客
04-18 1596
open Gauss 数据库-05 openGauss数据库备份恢复指导手册
【HCIA-openEuler】实验手册—08【shell脚本语言基础】
ZXW_NUDT的博客
01-12 2689
【HCIA-openEuler】实验手册—08【shell脚本语言基础】
【MOGDB/openGauss数据库gs_dump备份脚本及清理】
小怪兽的博客
01-07 1608
有客户需要对MOGDB/openGauss进行每天逻辑备份。如下脚本分享给大家。当然专业的备份工具也可以了解一下云和恩墨的BRM备份工具。 一、备份脚本 1.脚本 c.sh (可以改名字) # database dump shell # you should change the GAUSSHOME GAUSSPORT GAUSSDATA DUMP_USER DUMP_PASSWORD #!/bin/bash source /etc/profile source /home/omm/.bash_p
基于Java与openGauss数据库的高校选课管理系统
11-27
针对现有的工作仍处于手工处理、纸面传输阶段，无法满足突破时空限制，提高选课工作效率和标准化水平的需要，用 IDEA 作为开发工具，使用 openGauss 数据库系统，采用了B/S模式开发出的高校学生选课管理系统。...
openGauss数据库容器化相关操作
09-24
华为开源关系型数据库openGauss，容器化相关操作包括 docker安装、持久化、镜像制作等
openGauss 数据库管理和开发工具
07-11
openGauss 数据库管理和开发工具。支持管理/创建数据库、模式、函数、存储过程、表、列、索引、约束、视图、外表、序列、同义词、触发器、表空间、用户/角色等多种数据库对象。支持执行SQL语句或SQL脚本。支持创建、...
CentOS系统安装OpenGauss数据库详细流程
03-27
虚拟机安装opengauss数据库的详细安装流程。包括CentOS7.9系统虚拟机（VMware）安装，系统环境配置，opengauss数据库安装配置，opengauss数据库使用。 包括全部需要的软件包的下载地址和安装说明（VMware软件，...
【openGauss数据库内核分析系列】：统计信息与行数估计
Gauss松鼠会
06-29 1657
SQL引擎执行查询主要经历了词法语法解析、查询重写、查询规划和计划执行等步骤。目前优化器进行路径的选择主要是基于估算的代价，在代价估算时，需要使用基表或连接表的行数，而在很多时候，优化器无法获得准确的行数值，因此需要对行数进行估算（Cardinality Estimation），然后再计算代价。............
openGauss的基本使用
m0_47977101的博客
10-30 4777
1、查看数据库中所含的表 SELECT distinct(tablename) FROM pg_tables WHERE SCHEMANAME = 'public'; 2、查看数据库的用户 SELECT * FROM pg_user;
postgres数据库学习vacuum
lltfjsy的博客
02-23 1342
pg数据库vacuum
vacuum和analyze最佳实践（译）
liuzhilong的博客
05-22 1030
vacuum和analyze最佳实践
【BIT数据库实验】openGauss数据库实验一：建立数据库
Alice2O3的博客
05-10 4906
BIT赵小林openGauss数据库实验一——建立数据库
数据库实验五--OpenGauss（完整性控制）
CK_0ff的博客
04-21 3832
1. 练习创建下面约束： 　　主键（PRIMARY KEY）约束； 　　唯一性（UNIQUE）约束； 　　将STUDENTS表中的SNAME列设为唯一键约束、COURSES表中的CNAME列设为唯一键约束； 　　检查（CHECK）约束等； 外键(FOREIGN KEY)约束：外键约束是为了强制实现表之间的参照完整性。级联参照完整性约束就是为了保证外键数据的关联性。 2. 创建触发器 　触发器是一种特殊类型的存储过程，通常用于实现强制业务规则和数据完整性。创建UPDATE触发器检查修改操作的业务规则，如：在S
PyQt5连接opengauss数据库（postgresql）创建表、删除表以及插入数据等操作
qq_38029916的博客
11-04 1373
1.建立数据库连接 2.调用游标对象 3.创建表 4.在表中插入一条数据 5.删除表中数据 6.提交更改和关闭连接 代码参考 import psycopg2 #建立数据库连接 # connect() 方法的参数一般包括: # database: 要连接的数据库名称 # user:连接数据库的用户名 # password: 连接数据库的密码 # host: 数据库端口的地址，一般为 “localhost”,或者主机的IP地址 # port: 门户 opengaus...
openGauss例行重建索引
Oliviahome的博客
04-11 234
结束snapshot的id，默认end_snap_id大于begin_snap_id（表snapshot.snaoshot中的snapshot_id）在重建索引前，用户可以通过临时增大maintenance_work_mem和psort_work_mem的取值来加快索引的重建。要查看的某段时间性能的开始的snapshot的id（表snapshot.snaoshot中的snapshot_id）集群维度的性能统计信息：CPU时间，DB时间，逻辑读/物理读，IO性能，登入登出，负载强度，负载性能表现等。
openGauss：(1)理解并掌握数据库的定义及技术发展史
tydo达令的博客
10-25 1609
openGauss：(1)理解并掌握数据库的定义及技术发展史
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值