Oracle solves using swap partition problem by setting large memory pages under Linux __ios

In the background of a news web site monitoring process, often the database began to use the swap partition, and in turn, the entire application system response is very slow, can not provide services. The server's memory is set to 4G,PGA set to 1G for 8G,SGA. The operating system is 64 bits.

According to the truth, even if the memory is not enough, it should be disk read and write more intense, rather than start frequent use swap swap partition, along this train of thought, look for relevant data, found that if the configuration of large memory page can forcibly lock the SGA into memory, large memory page is not allowed to be exchanged. At the same time, the relevant data also shows that in the case of large memory, when the system memory is more than 8G, if not the use of large memory page (2M), then the memory required to manage these memory will also increase dramatically. For systems that can use command Cat/proc/meminf to view pagetables items, the additional memory consumption is due to the lack of large memory page management.

Specific steps to operate:

1. View system memory

Cat/proc/meminfo | grep memtotal

memtotal:8177384 KB

2. View SGA and PGA

Sql> Show parameter SGA;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
LOCK_SGA Boolean TRUE
PRE_PAGE_SGA Boolean FALSE
Sga_max_size Big Integer 4G
Sga_target Big Integer 4G
Sql> Show parameter PGA;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Pga_aggregate_target Big Integer 1G

4. See what happens when you don't use a large memory page

More/proc/meminfo |grep-i Hugepage
hugepages_total:0
hugepages_free:0
hugepages_rsvd:0
hugepagesize:0

5. Configure Hugepages Pool Size

Vi/etc/sysctl.conf

Add the following line at the end

Vm.nr_hugepages = 2300 # (This value should be greater than or equal to sga/2m, i.e. 4*1024m/2m)

6. Configure the maximum number of threads for Oracle users, file handles, and maximum locked memory

Vi/etc/security/limits.conf

Add the following line at the end

Oracle Soft Nproc 2047
Oracle Hard Nproc 16384
Oracle Soft Nofile 1024
Oracle Hard Nofile 65536
Oracle Soft Memlock 5194304 # (this is in KB, generally greater than or equal to the SGA)

Oracle Hard Memlock 5194304

7, make the configuration effective

Sysctl-p

8, restart the system and start the Oracle instance, the following is our use of large memory page results,

See the extra memory (Pagetables) required for managing memory pages is 55M

Cat/proc/meminfo
memtotal:8177384 KB
memfree:950472 KB
buffers:16824 KB
cached:1365136 KB
swapcached:170652 KB
active:1274416 KB
inactive:1082156 KB
hightotal:0 KB
highfree:0 KB
lowtotal:8177384 KB
lowfree:950472 KB
swaptotal:10420208 KB
swapfree:10056116 KB
dirty:148 KB
writeback:0 KB
anonpages:982112 KB
mapped:52944 KB
slab:64568 KB
pagetables:55580 KB
nfs_unstable:0 KB
bounce:0 KB
commitlimit:12153700 KB
committed_as:2584964 KB
vmalloctotal:34359738367 KB
vmallocused:267584 KB
vmallocchunk:34359470407 KB
hugepages_total:2300
hugepages_free:264
Hugepages_rsvd:13
hugepagesize:2048 KB

As a result of our system database SGA is 4G, so we have 2,300 large memory pages configured so that we can provide enough large memory pages for the SGA use of the database. One additional benefit of configuring large memory is the clear view of the system using memory, or Oracle will occupy buffer and cache memory, it is difficult to calculate the amount of free real memory.

As can be seen from memory meminfo information, 2,300 large memory pages are used 2300-264 = 2036 (This process changes dynamically when the database is started, until the SGA is full).

In the configuration process, we found that our system kernel does not support large memory page, after configuration to see Hugepage_total 0, indicating that the configuration is invalid, need to install a new kernel. The configuration of a large memory page can refer to related documentation.

When the configuration is complete, setting LOCK_SGA to the True,oracle database server will no longer be a significant use of swap partitions, and no performance failure occurs.