In Linux, Oracle solves the swap partition problem by setting a large memory page.

During the monitoring process of the background database of a news website, the database often starts to use swap partitions, which leads to slow response of the entire application system and failure to provide services. The server memory is 8 GB, SGA is 4 GB, and PGA is 1 GB. The operating system is 64-bit.

According to the principle, even if the memory is insufficient, it should be because the disk reads and writes become more intense, rather than frequently using swap to swap partitions. In this way, find relevant information, if you configure a large memory page, you can forcibly lock the SGA to the memory. Large Memory pages cannot be swapped. At the same time, the relevant information also shows that when the system memory is larger than 8 GB, if the large memory page (2 MB) is not used ), the memory required for managing these memories will also increase dramatically. For the system, you can use the command cat/proc/meminf to view the PageTables item. The additional memory consumption is caused by the absence of Large Memory Page Management.

Procedure:

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. check whether a large memory page is used

More/proc/meminfo | grep-I HugePage
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 0

5. Configure the HugePages Pool size

Vi/etc/sysctl. conf

Add the following row www.bkjia.com at the end.

Vm. nr_hugepages = 2300 # (this value should be greater than or equal to SGA/2 m, that is, 4*1024 m/2 m)

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

Vi/etc/security/limits. conf

Add the following lines at the end

 
Oracle soft nproc 2047
Hard nproc 16384
Oracle soft nofile 1024
Oracle hard nofile 65536
Oracle soft memlock 5194304 # (here, the unit is KB, generally greater than or equal to SGA)

Oracle hard memlock 5194304

7. Make the configuration take effect

Sysctl-p

8. restart the system and start the oracle instance. The following is the result after we use the large memory page,

The additional memory (PageTables) required for managing the Memory Page is 55 MB.

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 the SGA of our system database is 4 GB, We have configured 2300 large memory pages to provide enough large memory pages for the SGA of the database. Another advantage of configuring large memory is that you can clearly see the memory usage of the system. Otherwise, Oracle will occupy the buffer and cache memory, making it difficult to calculate the actual memory space of the system.

We can see from the memory meminfo information that 2300 large memory pages are used 2300-264 = 2036 (this process dynamically changes when the database is started, until the SGA is full ).

During the configuration process, we found that our system kernel does not support the large memory page. After the configuration, we can see that HugePage_Total is 0, which indicates that the configuration is invalid and a new kernel needs to be installed. For more information about the configuration of the Large Memory Page, see related documents.

After the configuration is complete and the value of lock_sga is set to true, the Oracle database server will no longer use a large number of swap partitions, and no performance faults will occur.