发表到鼎甲公众号技术干货 | 如何提高大数据量时的内存使用效率？

文章目录

配置HugePages的必要性
系统调整之前的状态
- Oracle数据库版本和参数
- 操作系统版本和参数
- 内核参数
- 检查HugePages内存
- 检查共享内存
- alert log中的警告
计算应配置的HugePages的大小
- 关闭AMM
- 计算HugePages大小的脚本
修改Linux内核参数
use_large_pages参数的设置
修改完成后检查
文章下方是我的微信，欢迎加我。👇

配置HugePages的必要性

HugePages是Linux内核2.6以后的版本上的一个内存管理特性，它将原来4K的内存页管理单位改成更大页的管理单位，通常是2M，极大地提高了大数据量时的内存使用效率。Oracle推荐在内存大于4GB的服务器上将SGA放入到HugePages内存中管理，现在的服务器基本100%符合这个条件。如果没有配置成HugePages，那么每次数据库启动就会在alert log里面提示警告，看来Oracle真的对HugePages很在乎。本文举例阐述这个特性的配置过程。

系统调整之前的状态

Oracle数据库版本和参数

SQL> show parameter large_pagesNAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
use_large_pages 		     string	 TRUE
SQL> select * from v$version;BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

操作系统版本和参数

[oracle@base-test-01 ~]$ grep Hugepagesize /proc/meminfo
Hugepagesize:       2048 kB
[oracle@base-test-01 ~]$ cat /etc/*release
BigCloud Enterprise Linux release 7.4.1807 (Core)
[oracle@base-test-01 ~]$ uname -r
3.10.0-693.el7.x86_64

内核参数

物理内存256G

[root@base-test-01 oracle]# sysctl -p
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 67108864
kernel.shmmax = 256000000000
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.swappiness = 1

检查HugePages内存

目前内存里的Hugepage为零

$ 
AnonHugePages:   2156544 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

检查共享内存

[root@base-test-01 oracle]# ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 504037376  oracle     640        536870912  72                      
0x00000000 504070145  oracle     640        107911053312 72                      
0x42e38fd0 504102915  oracle     640        2097152    72                      
0x00000000 1212420    root       644        80         2                       
0x00000000 1245189    root       644        16384      2                       
0x00000000 1277958    root       644        280        2                       
0x00000000 297435143  root       600        524288     2          dest         
0x00000000 508821513  root       600        832920     2          dest         
0x00000000 297762851  root       600        4194304    2          dest         
0x00000000 297861156  root       600        524288     2          dest

alert log中的警告

Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 64 KBTotal Shared Global Region in Large Pages = 0 KB (0%)Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KBRECOMMENDATION:Total System Global Area size is 101 GB. For optimal performance,prior to the next instance restart:1. Increase the number of unused large pages byat least 51713 (page size 2048 KB, total size 101 GB) system wide toget 100% of the System Global Area allocated with large pages2. Large pages are automatically locked into physical memory.Increase the per process memlock (soft) limit to at least 101 GB to lock100% System Global Area's large pages into physical memory
********************************************************************

计算应配置的HugePages的大小

关闭AMM

要将SGA放入到HugePages中，首先要关闭AMM(Automatic Memory Management)，方法是把两个参数MEMORY_TARGET 和MEMORY_MAX_TARGET设为0。

SQL> alter system set  MEMORY_MAX_TARGET=0 scope=spfile;
System altered.
SQL>  alter system set memory_target=0 scope=spfile;
System altered.
SQL> alter system set sga_target=101G  scope=spfile;
System altered.

计算HugePages大小的脚本

HugePages的大小需要根据OS的版本和SGA的大小进行计算得出，因为太小了SGA放不下，大了浪费。下面是计算脚本：

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# on Oracle Linux
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
#
# This script is provided by Doc ID 401749.1 from My Oracle Support
# http://support.oracle.com# Welcome text
echo "
This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:* For ASM instance, it needs to configure ASMM instead of AMM.* The 'pga_aggregate_target' is outside the SGA andyou should accommodate this while calculating the overall size.* In case you changes the DB SGA size,as the new SGA will not fit in the previous HugePages configuration,it had better disable the whole HugePages,start the DB with new SGA size and run the script again.
And make sure that:* Oracle Database instance(s) are up and running* Oracle Database 11g Automatic Memory Management (AMM) is not setup(See Doc ID 749851.1)* The shared memory segments can be listed by command:# ipcs -mPress Enter to proceed..."read# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
if [ -z "$HPG_SZ" ];thenecho "The hugepages may not be supported in the system where the script is being executed."exit 1
fi# Initialize the counter
NUM_PG=0# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
doMIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`if [ $MIN_PG -gt 0 ]; thenNUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`fi
doneRES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; thenecho "***********"echo "** ERROR **"echo "***********"echo "Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:# ipcs -mof a size that can match an Oracle Database SGA. Please make sure that:* Oracle Database instance is up and running* Oracle Database 11g Automatic Memory Management (AMM) is not configured"exit 1
fi# Finish with results
case $KERN in'2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;'2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;'3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;'3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;'4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;'4.14') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;*) echo "Kernel version $KERN is not supported by this script (yet). Exiting." ;;
esac# End

Recommended setting: vm.nr_hugepages = 51719

修改Linux内核参数

根据前面的计算结果，修改 /etc/sysctl.conf 加入vm.nr_hugepages = 51719。

[root@base-test-01 ~]# sysctl -p
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 67108864
kernel.shmmax = 256000000000
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.swappiness = 1vm.nr_hugepages = 51719

修改/etc/security/limits.conf文件，以K为单位，必须大于sga_max_size,这里设定为256000000

[root@base-test-01 ~]# tail /etc/security/limits.conf 
#@student        -       maxlogins       4# End of file
oracle 	soft    nproc   2047
oracle	hard    nproc   16384
oracle	soft    nofile  1024
oracle	hard    nofile  65536
oracle	soft	stack	10240
oracle  soft    memlock 256000000
oracle  hard    memlock 256000000

从新登入Oracle用户，可以看到修改的参数起作用了。

[oracle@base-test-01 ~]$ ulimit -l
64
[oracle@base-test-01 ~]$ exit
logout
Connection to 192.168.87.205 closed.
scutech@Yao:~/software$ ssh oracle@192.168.87.205
oracle@192.168.87.205's password: 
Last login: Wed Feb 26 17:09:31 2020
[oracle@base-test-01 ~]$ ulimit -l
256000000

use_large_pages参数的设置

use_large_pages可设置的值包括：FALSE，ONLY，TRUE。

false: Oracle实例将不会使用HugePages；
true：Oracle实例将可以使用HugePages；
only：Oracle实例只能使用HugePages；

这个参数默认是true，推荐将其配置成only，这样将确保只有当数据库实例的SGA从HugePages中获得所有的内存才能被启动，否则系统无法启动。这里就避免实例的SGA运行在常规内存而DBA并不知道。

修改完成后检查

sysctl -p 后系统已经分配大页空间（这段是后面补充的）

[root@localhost ~]# cat /proc/meminfo |grep -i HugePage
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
[root@localhost ~]# sysctl -p
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
vm.nr_hugepages = 97284
[root@localhost ~]# cat /proc/meminfo |grep -i HugePage
AnonHugePages:         0 kB
HugePages_Total:   97284
HugePages_Free:    97284
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
[root@localhost ~]#

从新启动数据库后，检查HugePages，发现已经使用。

# grep Huge /proc/meminfo
AnonHugePages:   2164736 kB
HugePages_Total:   51719
HugePages_Free:    46024
HugePages_Rsvd:    46018
HugePages_Surp:        0
Hugepagesize:       2048 kB

Oracle的alert log中已经没有关于大页的警告了，相反是SGA已经纳入HugePages管理的提示信息。

Thu Feb 27 10:31:43 2020
Starting Oracle instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 244 GBTotal Shared Global Region in Large Pages = 101 GB (100%)Large Pages used by this instance: 51713 (101 GB)
Large Pages unused system wide = 6 (12 MB)
Large Pages configured system wide = 51719 (101 GB)
Large Page size = 2048 KB
********************************************************************

为Oracle数据库在linux上配置hugepage内存管理—发表到鼎甲公众号

文章目录

配置HugePages的必要性

系统调整之前的状态

Oracle数据库版本和参数

操作系统版本和参数

内核参数

检查HugePages内存

检查共享内存

alert log中的警告

计算应配置的HugePages的大小

关闭AMM

计算HugePages大小的脚本

修改Linux内核参数

use_large_pages参数的设置

修改完成后检查

文章下方是我的微信，欢迎加我。👇

Published by

风君子

发表回复取消回复

文章目录

配置HugePages的必要性

系统调整之前的状态

Oracle数据库版本和参数

操作系统版本和参数

内核参数

检查HugePages内存

检查共享内存

alert log中的警告

计算应配置的HugePages的大小

关闭AMM

计算HugePages大小的脚本

修改Linux内核参数

use_large_pages参数的设置

修改完成后检查

文章下方是我的微信，欢迎加我。👇

Published by

风君子

发表回复 取消回复

发表回复取消回复