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

最新推荐文章于 2024-09-12 10:56:44 发布

小嗑数据库

最新推荐文章于 2024-09-12 10:56:44 发布

阅读量723

点赞数

分类专栏： openGauss 文章标签：数据库

原文链接：https://www.hikunpeng.com/forum/thread-0217102753937673013-1-1.html

版权

openGauss 专栏收录该内容

44 篇文章 2 订阅

订阅专栏

简介

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

前置条件

由于本实验主要是在openEuler操作系统上进行数据库维护管理，需要掌握Linux系统的基本操作和系统命令
数据库维护管理尤其是例行表、索引的维护需要掌握openGauss数据库的基本操作和SQL语法，openGauss数据库支持SQL2003标准语法。

实验环境说明

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

实验概览：

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.