[2023-04-11]配置大页和SGA_MAX_SIZE、SGA_TARGET的关系

qxy0503

于 2023-04-11 16:38:22 发布

阅读量257

点赞数

文章标签： linux 数据库 oracle

本文链接：https://blog.csdn.net/m15217321304/article/details/130087391

版权

1、配置大页脚本hugepages_settings.sh

#!/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 and
   you 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 -m



Press 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" ];then
    echo "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]*"`
do
    MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
    if [ $MIN_PG -gt 0 ]; then
        NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
    fi
done

RES_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 ]; then
    echo "***********"
    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 -m

of 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" ;;
    '4.18') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '5.4') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Kernel version $KERN is not supported by this script (yet). Exiting." ;;
esac

# End

官网提供脚本，计算配置大页的算法为ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*”

也就是把所有的共享内存段加一起（也包括了root等其他用户的，root等其他用户的共享内存段很小，可以忽略不计）也可以稍微改动脚本，grep时只过滤oracle用户的。

2、数据库启动时，会根据sga_max_size的大小预分配地址（没有实际分配大小 It's somewhat OS dependent, but in general sga_max_size does not allocate physical memory - it's held in swap or virtual space, and the amount of physical memory allocated is only the amount being currently used.）如果物理内存+swap的大小大于sga_max_size，则数据库可以正常启动，如果物理内存+swap的大小小于sga_max_size，则会启动失败。

测试如下：

当前物理+swap的大小6G左右

[oracle@11g ~]$ free -m
              total        used        free      shared  buff/cache   available
Mem:           2969        1261         948         162         760         572
Swap:          4095         864        3231
[oracle@11g ~]$

配置数据库sga_max_size =6G, sga_target=1000M,数据库可以正常启动

SQL>  alter system set sga_max_size=6G scope=spfile;

System altered.

SQL>  alter system set sga_target=1000M scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.


Total System Global Area 6413680640 bytes
Fixed Size                  2265224 bytes
Variable Size            5922361208 bytes
Database Buffers          469762048 bytes
Redo Buffers               19292160 bytes
Database mounted.
Database opened.
SQL>

查看共享内存信息，可以看到oracle所有共享内存段加一起和SGA_MAX_SIZE一样

[oracle@11g ~]$ ipcs -m 


------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 4521984    oracle     640        50331648   25                      
0x00000000 4554753    oracle     640        771751936  25                      
0x00000000 4587522    oracle     640        16777216   25                      
0x00000000 1703939    root       600        524288     2          dest         
0x00000000 1736708    root       600        4194304    2          dest         
0x00000000 1900549    root       600        4194304    2          dest         
0x00000000 1933318    root       600        1048576    2          dest         
0x00000000 2064391    root       600        393216     2          dest         
0x00000000 4620296    oracle     640        1526726656 25                      
0x00000000 4653065    oracle     640        1526726656 25                      
0x00000000 4685834    oracle     640        1526726656 25                      
0x00000000 4718603    oracle     640        1023410176 25                      
0x3f7ff218 4751372    oracle     640        2097152    25                      


[oracle@11g ~]$ bc
bc 1.06.95
Copyright 1991-1994, 1997, 1998, 2000, 2004, 2006 Free Software Foundation, Inc.
This is free software with ABSOLUTELY NO WARRANTY.
For details type `warranty'. 
1023410176+1526726656+1526726656+1526726656+2097152+16777216+771751936+50331648
6444548096
6444548096/1024/1024
6146

如果sga_max_size > 物理内存+swap，启动数据库会直接报错

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set sga_max_size =9G scope=spfile;

System altered.

SQL> startup force
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Additional information: 1275068416
Additional information: 1
SQL> exit