发表到鼎甲公众号 技术干货 | 如何提高大数据量时的内存使用效率？
文章目录
- 配置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
256000000use_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
********************************************************************
支付宝扫一扫
微信扫一扫
