鲲鹏社区首页
开发者
资源
支持
积分兑换 NEW
博客
openGauss数据库维护管理指导（上）
openGauss数据库维护管理指导（上）
发表于2022/11/02
1120

简介

本实验适用于 openGauss数据库，通过该实验可以顺利完成对数据库各项日常基本维护管理。主要内容为操作系统参数检查、openGauss健康状态检查、数据库性能检查、日志检查和清理、时间一致性检查、应用连接数检查、例行维护表等。

前置条件

  • 由于本实验主要是在openEuler操作系统上进行数据库维护管理，需要掌握Linux系统的基本操作和系统命令

  • 数据库维护管理尤其是例行表、索引的维护需要掌握openGauss数据库的基本操作和SQL语法，openGauss数据库支持SQL2003标准语法。

实验环境说明

组网说明：本实验环境为华为云 ECS 服务器 + openGauss数据库。
设备介绍：为了满足数据库原理与实践课程实验需要，建议每套实验环境采用以下配置：

设备名称

设备型号

软件版本

数据库

openGauss

openGauss 1.1.0

操作系统

openEuler

openEuler 20.3LTS

实验概览：

1.操作系统参数检查

1.1 实验介绍

1.1.1 关于本实验

gs_checkos工具用来帮助检查操作系统、控制参数、磁盘配置等内容，并对系统控制参数、I/O配置、网络配置和THP服务等信息进行配置。
本实验主要是通过gs_checkos工具来检查操作系统参数设置是否合理。先进行场景设置，然后根据检查结果进行参数调整。

1.1.2 实验目的

掌握gs_checkos工具的基本使用

1.2 场景设置及操作步骤

步骤 1 用root用户登录装有openGauss数据库服务的操作系统，登录后信息如下：

Welcome to 4.19.90-2003.4.0.0036.oe1.aarch64
System information as of time: 	Mon Jul 20 16:41:11 CST 2020
System load: 	0.00
Processes: 	113
Memory used: 	7.0%
Swap used: 	0.0%
Usage On: 	15%
IP address: 	192.168.0.96
Users online: 	2
[root@ecs-e1b3 ~]#

步骤 2 在root用户下执行gs_checkos先对系统参数进行检查。

[root@ecs-e1b3 ~]# gs_checkos -i A
Checking items:
    A1. [ OS version status ]                                   : Normal
    A2. [ Kernel version status ]                               : Normal
    A3. [ Unicode status ]                                      : Normal
    A4. [ Time zone status ]                                    : Normal
    A5. [ Swap memory status ]                                  : Normal
    A6. [ System control parameters status ]                    : Warning
    A7. [ File system configuration status ]                    : Normal
    A8. [ Disk configuration status ]                           : Normal
    A9. [ Pre-read block size status ]                          : Normal
    A10.[ IO scheduler status ]                                 : Normal
BondMode Null
    A11.[ Network card configuration status ]                   : Warning
    A12.[ Time consistency status ]                             : Warning
    A13.[ Firewall service status ]                             : Normal
    A14.[ THP service status ]                                  : Normal
Total numbers:14. Abnormal numbers:0. Warning numbers:3.

说明事项：
Normal 为正常项，Abnormal为必须处理项，Warning可以不处理。
Total numbers:14. Abnormal numbers:0. Warning numbers:3。
表示：总共检查14项，其中Abnormal必须处理项为0，Warning告警项为3。

步骤 3 调整系统参数值。
在参数配置文件（/etc/sysctl.conf）中将参数 vm.min_free_kbytes(表示：内核内存分配保留的内存量) 的值调整为3488。输入“i”进入INSERT模式，进行修改。

[root@ecs-e1b3 ~]# vi /etc/sysctl.conf
net.ipv4.conf.default.accept_redirects=0
net.ipv4.conf.all.secure_redirects=0
net.ipv4.conf.default.secure_redirects=0
net.ipv4.icmp_echo_ignore_broadcasts=1
net.ipv4.icmp_ignore_bogus_error_responses=1
………......
net.ipv4.tcp_rmem = 8192 250000 16777216
net.ipv4.tcp_wmem = 8192 250000 16777216
vm.min_free_kbytes = 3488
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.core.somaxconn = 65535

参数值修改好后，按” ESC”键退出编辑模式，然后输入 :wq 后回车进行保存。接着通过执行sysctl -p 命令使刚才修改的参数生效，具体如下：

[root@ecs-e1b3 ~]# sysctl -p
kernel.sysrq = 0
net.ipv4.ip_forward = 0
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
…………..
net.core.rmem_default = 21299200
net.sctp.sctp_mem = 94500000 915000000 927000000
net.sctp.sctp_rmem = 8192 250000 16777216
net.sctp.sctp_wmem = 8192 250000 16777216
kernel.sem = 250 6400000 1000 25600
net.ipv4.tcp_rmem = 8192 250000 16777216
net.ipv4.tcp_wmem = 8192 250000 16777216
vm.min_free_kbytes = 3488
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.core.somaxconn = 65535
kernel.shmall = 1152921504606846720
kernel.shmmax = 18446744073709551615

步骤 4 再执行gs_checkos 对系统参数进行检查。

[root@ecs-e1b3 ~]# gs_checkos -i A
Checking items:
    A1. [ OS version status ]                                   : Normal
    A2. [ Kernel version status ]                               : Normal
    A3. [ Unicode status ]                                      : Normal
    A4. [ Time zone status ]                                    : Normal
    A5. [ Swap memory status ]                                  : Normal
    A6. [ System control parameters status ]                    : Abnormal
    A7. [ File system configuration status ]                    : Normal
    A8. [ Disk configuration status ]                           : Normal
    A9. [ Pre-read block size status ]                          : Normal
    A10.[ IO scheduler status ]                                 : Normal
BondMode Null
    A11.[ Network card configuration status ]                   : Warning
    A12.[ Time consistency status ]                             : Warning
    A13.[ Firewall service status ]                             : Normal
    A14.[ THP service status ]                                  : Normal
Total numbers:14. Abnormal numbers:1. Warning numbers:2.
Do checking operation finished. Result: Abnormal.

此时A6. [ System control parameters status ] 的状态为Abnormal为必须处理项；
Total numbers:14. Abnormal numbers:1. Warning numbers:2。
表示：总共检查14项，其中Abnormal必须处理项为1，Warning告警项为2。

步骤 5 通过执行gs_checkos -i A --detail 查看更详细的信息。

[root@ecs-e1b3 ~]# gs_checkos -i A --detail
Checking items:
    A1. [ OS version status ]                                   : Normal     
        [ecs-e1b3]
        openEuler_20.03_64bit
     A2. [ Kernel version status ]                               : Normal     
        The names about all kernel versions are same. The value is "4.19.90-2003.4.0.0036.oe1.aarch64".
    A3. [ Unicode status ]                                      : Normal     
        The values of all unicode are same. The value is "LANG=en_US.UTF-8".
    A4. [ Time zone status ]                                    : Normal     
        The informations about all timezones are same. The value is "+0800".
    A5. [ Swap memory status ]                                  : Normal     
        The value about swap memory is correct.            
    A6. [ System control parameters status ]                    : Abnormal   
        [ecs-e1b3]
        Abnormal reason: variable 'vm.min_free_kbytes' RealValue '3488' ExpectedValue '348844'.
        Warning reason: variable 'net.ipv4.tcp_retries1' RealValue '3' ExpectedValue '5'.
        Warning reason: variable 'net.ipv4.tcp_syn_retries' RealValue '6' ExpectedValue '5'.
        Warning reason: variable 'net.sctp.path_max_retrans' RealValue '5' ExpectedValue '10'.
        Warning reason: variable 'net.sctp.max_init_retransmits' RealValue '8' ExpectedValue '10'.
        Check_SysCtl_Parameter failed.
    A7. [ File system configuration status ]                    : Normal     
        Both soft nofile and hard nofile are correct.      
    A8. [ Disk configuration status ]                           : Normal     
        The value about XFS mount parameters is correct.   
    A9. [ Pre-read block size status ]                          : Normal     
        The value about Logical block size is correct.     
    A10.[ IO scheduler status ]                                 : Normal     
        The value of IO scheduler is correct.              
BondMode Null
    A11.[ Network card configuration status ]                   : Warning    
        [ecs-e1b3]
BondMode Null
        Warning reason: Failed to obtain the network card speed value. Maybe the network card "eth0" is not working.
    A12.[ Time consistency status ]                             : Warning    
        [ecs-e1b3]
        The NTPD not detected on machine and local time is "2020-07-20 17:16:41".
    A13.[ Firewall service status ]                             : Normal     
        The firewall service is stopped.                   
    A14.[ THP service status ]                                  : Normal     
        The THP service is stopped.                        
Total numbers:14. Abnormal numbers:1. Warning numbers:2.
Do checking operation finished. Result: Abnormal.

在详细信息中，可以明确看出那些参数设置有问题，并给出了问题参数要求修改的参考值，如下：

A6. [ System control parameters status ] : Abnormal
[ecs-e1b3]
Abnormal reason: variable ‘vm.min_free_kbytes’ RealValue ‘3488’ ExpectedValue ‘348844’.
Warning reason: variable ‘net.ipv4.tcp_retries1’ RealValue ‘3’ ExpectedValue ‘5’.
Warning reason: variable ‘net.ipv4.tcp_syn_retries’ RealValue ‘6’ ExpectedValue ‘5’.
Warning reason: variable ‘net.sctp.path_max_retrans’ RealValue ‘5’ ExpectedValue ‘10’.
Warning reason: variable ‘net.sctp.max_init_retransmits’ RealValue ‘8’ ExpectedValue ‘10’.
Check_SysCtl_Parameter failed.

步骤 6 按详细信息中的修改说明对系统参数进行修改。

vm.min_free_kbytes的值由3488调整为348844
net.ipv4.tcp_retries1的值由3调整为5.
net.ipv4.tcp_syn_retries的值由6调整为5.
net.sctp.path_max_retrans的值由5调整为10
net.sctp.max_init_retransmits的值由8调整为10
具体设置如下：
vm.min_free_kbytes = 348844
net.ipv4.tcp_retries1 = 5
net.ipv4.tcp_syn_retries = 5
net.sctp.path_max_retrans = 10
net.sctp.max_init_retransmits = 10
在系统参数文件中进行修改（输入“i”进入INSERT模式，进行修改。）：

[root@ecs-e1b3 ~]# vi /etc/sysctl.conf
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
kernel.sysrq=0
net.ipv4.ip_forward=0
net.ipv4.conf.all.send_redirects=0
net.ipv4.conf.default.send_redirects=0
net.ipv4.conf.all.accept_source_route=0
net.ipv4.conf.default.accept_source_route=0
net.ipv4.conf.all.accept_redirects=0
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
kernel.sysrq=0
net.ipv4.ip_forward=0
net.ipv4.conf.all.send_redirects=0
net.ipv4.conf.default.send_redirects=0
net.ipv4.conf.all.accept_source_route=0
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
kernel.sysrq=0
net.ipv4.ip_forward=0
net.ipv4.conf.all.send_redirects=0
net.ipv4.conf.default.send_redirects=0
net.ipv4.conf.all.accept_source_route=0
net.ipv4.conf.default.accept_source_route=0
…………..
net.sctp.sctp_rmem = 8192 250000 16777216
net.sctp.sctp_wmem = 8192 250000 16777216
kernel.sem = 250 6400000 1000 25600
net.ipv4.tcp_rmem = 8192 250000 16777216
net.ipv4.tcp_wmem = 8192 250000 16777216
vm.min_free_kbytes = 348844
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.core.somaxconn = 65535
kernel.shmall = 1152921504606846720
kernel.shmmax = 18446744073709551615
net.ipv4.tcp_retries1 = 5
net.ipv4.tcp_syn_retries = 5
net.sctp.path_max_retrans = 10
net.sctp.max_init_retransmits = 10

参数值修改好后，按”ESC”键退出编辑模式，然后输入:wq 后回车进行保存。接着通过执行sysctl -p 命令使刚才修改的参数生效，具体如下：

[root@ecs-e1b3 ~]# sysctl -p
kernel.sysrq = 0
net.ipv4.ip_forward = 0
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.secure_redirects = 0
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.icmp_ignore_bogus_error_responses = 1
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.default.rp_filter = 1
net.ipv4.tcp_syncookies = 1
kernel.dmesg_restrict = 1
net.ipv6.conf.all.accept_redirects = 0
net.ipv6.conf.default.accept_redirects = 0
vm.swappiness = 0
net.ipv4.tcp_max_tw_buckets = 10000
net.ipv4.tcp_tw_reuse = 1
…………….
net.ipv4.tcp_rmem = 8192 250000 16777216
net.ipv4.tcp_wmem = 8192 250000 16777216
vm.min_free_kbytes = 348844
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.core.somaxconn = 65535
kernel.shmall = 1152921504606846720
kernel.shmmax = 18446744073709551615
net.ipv4.tcp_retries1 = 5
net.ipv4.tcp_syn_retries = 5
net.sctp.path_max_retrans = 10
net.sctp.max_init_retransmits = 10

步骤 7 再次通过执行gs_checkos -i A 查看系统参数检查是否能通过。

[root@ecs-e1b3 ~]# gs_checkos -i A
Checking items:
    A1. [ OS version status ]                                   : Normal
    A2. [ Kernel version status ]                               : Normal
    A3. [ Unicode status ]                                      : Normal
    A4. [ Time zone status ]                                    : Normal
    A5. [ Swap memory status ]                                  : Normal
    A6. [ System control parameters status ]                    : Normal
    A7. [ File system configuration status ]                    : Normal
    A8. [ Disk configuration status ]                           : Normal
    A9. [ Pre-read block size status ]                          : Normal
    A10.[ IO scheduler status ]                                 : Normal
BondMode Null
    A11.[ Network card configuration status ]                   : Warning
    A12.[ Time consistency status ]                             : Warning
    A13.[ Firewall service status ]                             : Normal
    A14.[ THP service status ]                                  : Normal
Total numbers:14. Abnormal numbers:0. Warning numbers:2.

从检查结果可以看出，系统参数检查已经通过。其中A6. [ System control parameters status ]的状态由原来的Abnormal变为了Normal。
操作系统参数检查实验结束。

2.openGauss运行健康状态检查

2.1 实验介绍

2.1.1 关于本实验

gs_check能够帮助用户在openGauss运行过程中，全量的检查openGauss运行环境，操作系统环境，网络环境及数据库执行环境，也有助于在openGauss重大操作之前对各类环境进行全面检查，有效保证操作执行成功。
本实验主要是通过gs_check工具来检查openGauss数据库运行状态。先进行场景设置，然后根据检查结果进行数据库调整。
语法如下：

  • 单项检查：

    gs_check -i ITEM [...] [-U USER] [-L] [-l LOGFILE] [-o OUTPUTDIR] [--skip-root-items][--set][--routing]

  • 场景检查：

    gs_check -e SCENE_NAME [-U USER] [-L] [-l LOGFILE] [-o OUTPUTDIR] [--hosts] [--skip-root-items] [--time-out=SECS][--set][--routing][--skip-items]

    场景检查项。默认的场景有inspect（例行巡检）、upgrade（升级前巡检）、binary_upgrade（就地升级前巡检）、health（健康检查巡检）、install(安装)，等，用户可以根据需求自己编写场景。

  • 显示帮助信息。

    gs_check -? | --help

2.1.2 实验目的

掌握gs_check工具的基本使用

2.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 21 09: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 10 19:05:39 CST 2020 on pts/0
Welcome to 4.19.90-2003.4.0.0036.oe1.aarch64
System information as of time: 	Tue Jul 21 09:21:25 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 确认openGauss数据库服务是否启动。

[omm@ecs-e1b3 ~]$ gs_om -t status;
----------------------------------------------------------------------
cluster_state   : Normal
redistributing  : No
----------------------------------------------------------------------

cluster_state : Normal 表示已启动，可以正常使用。如果状态为非Normal表示不可用
为了实验场景设置，如果数据库服务已经启动，请执行步骤3先关闭服务。

步骤 3 关闭openGauss数据库服务。

[omm@ecs-e1b3 ~]$ gs_om -t stop;
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.

步骤 4 检查openGauss实例连接。

[omm@ecs-e1b3 ~]$ gs_check -i CheckDBConnection
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:1

Checking...               [=========================] 1/1
Start to **ysis the check result
CheckDBConnection...........................NG
The item run on 1 nodes.  ng: 1 
The ng[ecs-e1b3] value:
The database can not be connected.

**ysis the check result successfully
Failed.	All check items run completed. Total:1     NG:1  
For more information please refer to /opt/huawei/wisequery/script/gspylib/inspection/output/CheckReport_2020072139449163171.tar.gz

说明：
CheckDBConnection…NG 表示连接检查项无用；
The database can not be connected. 表示实例不能连接；
Failed. All check items run completed. Total:1 NG:1 表示共检查1项并且检查结果未通过。

步骤 5 启动openGauss数据库服务。

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

步骤 6 确认openGauss数据库服务已启动。

[omm@ecs-e1b3 ~]$ gs_om -t status;
----------------------------------------------------------------------
cluster_state   : Normal
redistributing  : No
----------------------------------------------------------------------
[omm@ecs-e1b3 ~]$

步骤 7 再次检查openGauss实例连接。

[omm@ecs-e1b3 ~]$ gs_check -i CheckDBConnection
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:1

Checking...               [=========================] 1/1
Start to **ysis the check result
CheckDBConnection...........................OK
The item run on 1 nodes.  success: 1 

**ysis the check result successfully
Success.	All check items run completed. Total:1   Success:1    
For more information please refer to /opt/huawei/wisequery/script/gspylib/inspection/output/CheckReport_2020072140672174672.tar.gz

说明：
CheckDBConnection…OK 表示连接检查项正常；
Success. All check items run completed. Total:1 Success:1 表示共检查1项并且检查结果成功。
openGauss数据库运行健康状态检查实验结束。

收藏举报
Level 1
0
帖子
0
粉丝
0
获赞
我们使用cookie来确保您的高速浏览体验。继续浏览本站，即表示您同意我们使用cookie。查看详情