合 Oracle数据库配置大页
简介
有关大页和透明大页的介绍：https://www.dbaup.com/linux-biaozhundayehetoumingdaye.html
在Linux中配置hugepage可以提高oracle的性能，减少oracle sga的页交换，类似于aix中的lagepage。
当你主机的物理内存为64G，设SGA>=32G时，建议开启大页。
要查看是否存在大页表的问题，可以使用如下的命令来检查页表的大小：
1 2 | [root@lhrdb ~]# cat /proc/meminfo | grep PageTables PageTables: 1932200 kB |
如果发现页表的大小不是几十兆，而是达到了1GB以上，就说明数据库存在此问题。基于共享内存的多进程架构的程序都会存在此问题。
Oracle 官方是推荐我们使用 Huge pages 的，它拥有以下的好处：
Larger Page Size and Less # of Pages: Default page size is 4K whereas the HugeTLB size is 2048K. That meansthe system would need to handle 512 times less pages.
Reduced Page Table Walking:Since a HugePage covers greater contiguous virtual address range than a regularsized page, a probability of getting a TLB hit per TLB entry with HugePages arehigher than with regular pages. This reduces the number of times page tablesare walked to obtain physical address from a virtual address.
Less Overhead for MemoryOperations: On virtual memory systems (any modern OS) each memory operation isactually two abstract memory operations. With HugePages, since there are lessnumber of pages to work on, the possible bottleneck on page table access isclearly avoided.
Less Memory Usage: From theOracle Database perspective, with HugePages, the Linux kernel will use lessmemory to create pagetables to maintain virtual to physical mappings for SGAaddress range, in comparison to regular size pages. This makes more memory tobe available for process-private computations or PGA usage.
No Swapping: We must avoidswapping to happen on Linux OS at all Document 1295478.1. HugePages are notswappable (whereas regular pages are). Therefore there is no page replacementmechanism overhead. HugePages are universally regarded as pinned.
No 'kswapd' Operations: kswapdwill get very busy if there is a very large area to be paged (i.e. 13 millionpage table entries for 50GB memory) and will use an incredible amount of CPUresource. When HugePages are used, kswapd is not involved in managing them. Seealso Document 361670.1
Huge pages 和 Oracle 11g新 特性 AMM （ Automatic Memory Management ）是相互冲突的，但是 ASMM （ Automatic Shared Memory Management ）仍然可以继续使用。
Oracle 官方推荐我们使用 标准大页（Huge pages ），但是却建议我们关闭 透明大页（Transparent Huge pages） ，因为透明大页存在一些问题：
- 在 RAC 环境下 透明大页（ TransparentHugePages ）会导致异常节点重启，和性能问题；
在单机环境中，透明大页（ TransparentHugePages ） 也会导致一些异常的性能问题；
如何关闭透明大页请参考：https://www.dbaup.com/linux-biaozhundayehetoumingdaye.html
如何配置开启大页
参考：HugePages on Oracle Linux 64-bit (Doc ID 361468.1)
- 在 /etc/security/limits.conf 加入以下参数，单位为KB，值比内存稍微少一点即可，例如内存为16GB，那么可以如下设置：
1 2 3 4 5 6 7 | # 该参数只会影响共享内存段的个数 cat >> /etc/security/limits.conf <<"EOF" * soft memlock 15265196 * hard memlock 15265196 EOF |
memlock 参数的值只需略小于内存的值即可，但是必须大于sga_max_size的值。
也可以直接配置-1，表示不限制：
1 2 3 4 5 6 7 8 9 10 11 12 | # 该参数只会影响共享内存段的个数 cat >> /etc/security/limits.conf <<"EOF" * soft memlock -1 * hard memlock -1 EOF -- 如下配置也可以 oracle soft memlock unlimited oracle hard memlock unlimited |
注意：这里需要仔细查看文件/etc/security/limits.conf的内容，若文件有如下内容，则需要手动注释，或删除如下内容：
1 2 | oracle soft memlock 60397 oracle hard memlock 60397 |
- 重新以oracle用户登录服务器验证设置
1 2 | [oracle@oracle-rac2 ~]$ ulimit -l 15265196 |
在 Oracle 11g 中禁用 AutomaticMemory Management (AMM) 特性，即 设置MEMORY_TARGET和MEMORY_MAX_TARGET 值为0 。因为AMM和大页特性不兼容。注意：若有ASM实例，那么ASM实例也应该使用ASMM。
在Linux操作系统推荐配置HugePages，以至于ASM和数据库实例的SGA都能使用HugePage来提高性能。ASM实例的SGA同样有必要使用HugePage来提高性能。
1 2 3 4 5 6 7 8 9 10 11 | SYS@orcl2> show parameter MEMORY_TARGET NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ memory_target big integer 0 SYS@orcl2> show parameter MEMORY_MAX_TARGET NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ memory_max_target big integer 0 |
这里的环境并没有启用 AMM 特性，如果启用了AMM，那么请使用以下语句关闭，并从spfile中删除该参数：
1 2 3 4 5 6 | alter system set MEMORY_TARGET=0 scope=spfile sid='*'; alter system set MEMORY_MAX_TARGET=0 scope=spfile sid='*'; alter system reset memory_target scope=spfile ; alter system reset memory_max_target scope=spfile ; |
重启实例后生效。
Note: Starting in 11.2.0.3, AMM is no longer configured by default if DBCA detects that the machine has more than 4GB of RAM. See Document 1453227.1 for details.
- 确认所有需要使用 Hugepage 的数据库实例都是开启的 ( 包括 ASM 实例 ) ，然后运行脚本 hugepages_settings.sh(具体脚本参考文档 Document 401749.1)来计算
*vm.nr_hugepages*kernel的值。
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 | [root@oracle-rac2 ~]# ll hugepages_settings.sh -rw-r--r-- 1 root root 3485 Jul 30 17:25 hugepages_settings.sh [root@oracle-rac2 ~]# chmod +x hugepages_settings.sh [root@oracle-rac2 ~]# ./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 = 5122 |
实际上hugepages与参数sga_max_size有关，比sga_max_size的值稍微大一点点（比SGA_MAX_SIZE最少要多加一页，2M的页不要分配超过sga_max_size太多，会造成内存的浪费）。
