Shulou(Shulou.com)06/01 Report--

Background: ORACLE 11.2 SGA 64G PGA 8G, stress test shows that the operating system runs out of memory

WARNING: Heavy swapping observed on system in last 5 mins.

Pct of memory swapped in [1.94%] pct of memory swapped out [1.32%].

Analysis:

1. Each system connection process connected to the database will take about 10m, and 1000 connections will be close to 10G.

2. In a 32-bit system, when a process accesses the memory of 1GB, it will produce a page table of 1m (11024102424ax). If it is in a 64-bit system, it will grow to 2m.

SGA is set to 64g, and there are 1000 ORACLE user processes (in linux, each process has its own page table). On 64-bit LINUX systems, the largest page table occupies memory: 642 million 1000amp 1024x125g

Solution:

1. Control the size of SGA. Sga+pga should be controlled within 50% of physical memory, and pga memory should not operate 20% of sga memory.

2. When the physical memory of the host is 64 GB and SGA > = 32 GB, it is recommended to open a large page.

Memory is divided in the form of pages, and each page is 4K by default, which means that if the physical memory is large, there will be a large number of entries in the mapping table, which will affect the retrieval efficiency of CPU. Because the memory size is fixed, the only way to reduce the number of entries in the mapping table is to increase the page size.

Configuring hugepage in Linux can improve the performance of oracle and reduce page swapping in oracle sga, similar to lagepage in aix.

Setting method (refer to third-party materials):

1. Turn off AMM (Automatic Memory Management) in Oracle Database 11g, that is, set the two parameters MEMORY_TARGET / MEMORY_MAX_TARGET to 0

If the alter system set MEMORY_MAX_TARGET=0 scope=spfile; is not changed to 0 after restart, you can set it by alter system reset memory_max_target;.

2. Refer to the script provided by metalink (document ID 401749.1) to calculate the size of hugepages.

3. Grant executable permissions to the script hugepages_settings.sh

Chmod + x hugepages_settings.sh

4. Execute the hugepages_settings.sh to get the recommended value

It is concluded that the size of a large page is 1028 pages (Note: a page is 2m, this value is unchangeable, 1028*2M=2056M). In fact, hugepages is related to the parameter sga_max_size, which is slightly larger than the value of sga_max_size (at least one more page than SGA_MAX_SIZE, 2m pages should not be allocated more than sga_max_size, which will cause a waste of memory).

Note: using Hugepage memory is shared memory, it will always keep in memory, will not be swapped out, that is to say, memory using hurgepage can not be used by other processes, so be sure to set this value reasonably to avoid waste. For servers that only use Oracle, set the Hugepage_pool to be larger than the SGA size to be used by Oracle.

SQL > show parameter sga_max_size

NAME TYPE VALUE

Sga_max_size big integer 2G

5. Set hugepages and add a line to the kernel parameters, vi / etc/sysctl.conf

Vm.nr_hugepages = 1028

6. The modification of kernel parameters takes effect immediately

[root@el5] # sysctl-p

7, do not forget to set the memory lock memlock, in K units, the number of memlock should be greater than the number of large pages, of course, also greater than sga_max_size, here set to 2056000, set to-1, indicating no limit.

[root@el5 ~] # vi / etc/security/limits.conf

Oracle soft memlock 2056000

Oracle hard memlock 2056000

8. Check whether the limits is correct

[root@el5 ~] # su-oracle

[oracle@el5 ~] ulimit-l

2056000

9. Restart the database

10. View the large page, which has been used

[oracle@el5 ~] $watch-N1 'cat / proc/meminfo | grep-I HugePage'

HugePages_Total: total pages

HugePages_Free: free pages

HugePages_Rsvd: the operating system promises reserved pages to Oracle

Hugepagesize: 2048 kB is 2m per page and cannot be modified

Such as:

HugePages_Total: 1028-1028 pages in total

HugePages_Free: 869 pages idle, that is, the current large page is used 1028-869 pages 159, that is, 1592M=118M is used, which is less than sga_target.

HugePages_Rsvd: 842-the operating system promises to reserve 842 pages for Oracle, that is, 8422M=1684M (1684+118==SGA_MAX_SIZE)

After using hugepage, SGA defaults that pin is in memory, so you don't need lock sga. Next let's take a look at the parameter: pre_page_sga, which defaults to false, and I'll turn it on.

Sys@OCM > alter system set pre_page_sga=true scope=spfile

Sys@OCM > show parameter sga

NAME TYPE VALUE

Lock_sga boolean FALSE

Pre_page_sga boolean TRUE

Sga_max_size big integer 2G

Sga_target big integer 1G

HugePages_Total: 1028-1028 pages in total

HugePages_Free: idle 548 pages, that is, the current large page is used 1028-548mm 480 pages, that is, 4802M=960M is used, which is about equal to sga_target. The parameter pre_page_sga works.

HugePages_Rsvd: 521-the operating system promises to reserve 521 pages for Oracle, that is, 5212M=1042M (understood as sga_max_size-sga_target)

Hugepagesize: 2048 kB-2m per page and cannot be modified

Reference metalink:USE_LARGE_PAGES To Enable HugePages (document ID 1392497.1)

Supplement OverCommit on memory request

The main purpose of the OverCommit mechanism under Linux is to deal with the impact of a large number of possible abnormal memory requests on OS itself.

Linux has three OverCommit mechanisms, which can be configured through: / proc/sys/vm/overcommit_memory. The specific meaning of the three configurations:

0: heuristic strategy, Overcommit with more serious consequences will not succeed, while slight Overcommit will be allowed.

1: always allow Overcommit, this strategy is suitable for applications that cannot afford memory allocation failures, such as some scientific computing applications.

2: never prohibit Overcommit. In this case, the memory allocated by the system will not exceed the swap+RAM* coefficient (/ proc/sys/vm / overcmmit_ratio, 50% by default, which you can adjust). If so many resources have been used up, any subsequent attempt to apply for memory will return an error, which usually means that no new program can be run at this time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.