내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
에러 : alter log Large Pages Information, Total Shared Global Region in Large Pages = 0 KB (0%)
sga_target 값 증설 후 alert log에 발생한 Large Pages Information 내용
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | $ vi alert_orcl.log ****************** Large Pages Information ***************** Per process system memlock (soft) limit = UNLIMITED Total 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) (alloc incr 32 MB) Large Pages configured system wide = 0 (0 KB) Large Page size = 2048 KB RECOMMENDATION: Total Shared Global Region size is 1.6 GB. For optimal performance, prior to the next instance restart: 1. Increase the number of unused Large Pages by at least 2048 KB Large Pages (total size 1.6 GB) system wide to get 100% of the System Global Area allocated with large pages *********************************************************** |
해결 방법 : HugePage 설정
HugePage 란?
일반적인 컴퓨팅 시스템은 물리적 메모리 크기를 극복하기 위해 가상메모리 기법을 사용하며,
상이한 두 메모리를 매핑하기 위해 Page Table이 존재하고 Page 단위로 관리됨
리눅스 시스템의 경우 보통 4k로 되어있음
대용량 메모리를 장착한 시스템의 효율적인 Page Table 관리를 위해 Kernel 2.6부터는 HugePage 기술이 도입되어 더 큰 Page 크기(예 : 2M ~ 256M)로 관리 할 수 있게 되었음
HugePage 사용 시 장점
1. Normal Page와는 다르게 HugePage는 Swap되지 않음. 따라서 Page-in/.Page-Out의 Overhead가 없음
2. HugePage는 상대적으로 적은 수의 Meta 정보로 좀 많은 물리적 메모리를 Cover하므로 CPU내의 Buffer(=Translation Lookaside Buffer, TLB)좀 더 많은 Page Entry를 보관할 수 있어 Page Hit율이 높아짐
3. Page Table에서 관리하는 물리적 메모리의 Entry 개수가 줄어들고 더 적은 메모리 공간을 차지함
따라서 오라클 관점에서 보면 오라클 프로세스의 계산이나 PGA에 좀 더 많은 메모리가 제공됨
4. Normal Page의경우, 50GB의 메모리를 관리하기 위해서는
1,300 만개의 page(13,107,200 = 50 * 1024* 1024 KB / 4 KB)가 유지되어야함
HugePages를 사용할 경우 25,600 개의 Page가 관리되므로
메모리 관리을 위한 Overhead가 줄어들고 자원사용율도 절약됨
Oracle 11.2.0.2 이상부터는 use_large_pages = ( TRUE | FALSE | ONLY ) 파라미터를 사용하여
Oracle SGA 의 HugePage 사용을 관리하고 문제의 발생을 예방함
Oracle 11.2.0.3 부터는 HugePages가 SGA에 비해 작을 경우, HugePages와 Normal Pages를 동시에 사용함
HugePage 설정 방법
1. 서버에 있는 모든 인스턴스 OPEN
1 2 3 4 5 6 7 8 9 10 | SQL> startup ORACLE instance started. Total System Global Area 1677718256 bytes Fixed Size 8897264 bytes Variable Size 536870912 bytes Database Buffers 1124073472 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. |
2. DB가 AMM을 사용하고 있는지 확인(AMM 사용시 HugePage 적용불가)
1 2 3 4 5 | SQL> show parameter memory_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ memory_target big integer 0 |
현재 AMM 미사용
3. Doc. 401749.1 에 있는 권장 HugePage 값을 확인하는 스크립트 저장
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | $ vi 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" ;; *) echo "Kernel version $KERN is not supported by this script (yet). Exiting." ;; esac # End |
4. 실행 권한 부여
1 | $ chmod +x hugepages_settings.sh |
5. 권장 HugePage 값 확인 쉘 실행
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | $ ./hugepages_settings.sh 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... Recommended setting: vm.nr_hugepages = 808 |
권장값이 808로 나옴
해당 값으로 hugapages 설정
1 2 | # sysctl -w vm.nr_hugepages=808 vm.nr_hugepages = 808 |
서버 재기동 이후에도 변경되지 않도록 /etc/sysctl.conf 에 기록
1 2 | # vi /etc/sysctl.conf vm.nr_hugepages=808 |
오라클 use_large_pages 파라미터 변경 전 Huge Page사용량 확인
1 2 3 4 5 6 7 8 | $ grep Huge /proc/meminfo AnonHugePages: 0 kB ShmemHugePages: 0 kB HugePages_Total: 808 HugePages_Free: 7 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB |
Huge Page Total이 808이고 Free가 7임을 확인 할 수 있음
오라클 접속 후 파라미터 변경
1 2 3 4 5 6 7 8 9 | SQL> show parameter use_large_pages NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ use_large_pages string TRUE SQL> alter system set use_large_pages=only scope=spfile; System altered. |
기존 값 TRUE에서 ONLY로 변경
재기동
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup Total System Global Area 1677718256 bytes Fixed Size 8897264 bytes Variable Size 536870912 bytes Database Buffers 1124073472 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. |
*DB 기동시 ORA-27106 발생 시 서버 재기동 후 다시 시도
1 2 3 4 | SQL> startup ORA-27106: system pages not available to allocate memory Additional information: 6122 Additional information: 1 |
오라클 use_large_pages 파라미터 변경 후 Huge Page사용량 확인
1 2 3 4 5 6 7 8 | $ grep Huge /proc/meminfo AnonHugePages: 0 kB ShmemHugePages: 0 kB HugePages_Total: 808 HugePages_Free: 6 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB |
Huge Page Total이 808이고 Free가 6임을 확인 할 수 있음
오라클 use_large_page =only 설정 및 재기동으로 HugePages_Free가 더 줄어듦 확인 ( 7에서 6으로 변경됨 )
원인 : 장애나 문제가 아닌 오라클에서 권고하는 메세지임(RECOMMENDATION)
성능 향상에 도움을 줄 것으로 예상되서 오라클에서 DBA에게 권고하는 메세지
참조 :
http://haisins.epac.to/wordpress/?p=1052
Doc. 401749.1
https://otsteam.tistory.com/183
https://docs.oracle.com/database/121/REFRN/GUID-1B0F4D27-8222-439E-A01D-E50758C88390.htm#REFRN10320
https://darkturtle.tistory.com/entry/Oracle-Linux-Huge-Page