Analysis of the low memory problem of Oracle with large concurrent connections under Linux
2010-01-28 20:06:21
Category: Oracle
A recently installed Rhel5.3 40G memory machine has an Oracle database, the database's SGA is set to 20G, when running a business, when a business peak, found swap frequent exchange, CPU 100%,load is very high, the basic embodiment of memory shortage. The number of connections at this time is around 600. According to the calculation of memory: Each connection occupies a basic memory in 5M, so that 600 connections only consumes 3G of memory, SGA memory 20G, the operating system occupies memory 1G, so the total memory is 24G, and total memory has 40G, how can memory shortage? At that time was baffled, so did a lot of stress test, first of all, wrote a Java program, start multiple threads, each thread and database to build a connection, and then loop to run a simple SQL, the SQL by a random function generated by the ID to query a large table (indexed). After starting 1000 connections, use Free-m to view memory: #free-M
Total used free shared buffers Cached
mem:40210 25842 14368 0 9 177
-/+ Buffers/cache: 25655 14554
swap:20481 479 20001 found that the memory value of free is very small, used memory value of the growth, after running about 20 minutes, when the free reduced to about 40M, the system's CPU suddenly to 100%,load from 15 to 600. From this result, it was still out of memory, when a script was written to see the memory of all Oracle processes, and no Oracle process was found to consume too much memory. So I have not found the reason. Finally try to use Cat/proc/meminfo to view the memory, finally found the reason, without pressure, cat/proc/meminfo see the result is:[email protected]:/proc/sys/vm>cat/proc/meminfo
memtotal:41175744 KB
memfree:27603324 KB
buffers:36572 KB
cached:13006240 KB
swapcached:232980 KB
active:304448 KB
inactive:12990616 KB
hightotal:0 KB
highfree:0 KB
lowtotal:41175744 KB
lowfree:27603324 KB
swaptotal:20972816 KB
swapfree:20070348 KB
dirty:1232 KB
writeback:0 KB
anonpages:240500 KB
mapped:354120 KB
slab:136980 KB
pagetables:34004 KB
nfs_unstable:0 KB
bounce:0 KB
commitlimit:41560688 KB
committed_as:17163928 KB
vmalloctotal:34359738367 KB
vmallocused:273756 KB
vmallocchunk:34359464051 KB
hugepages_total:0
hugepages_free:0
hugepages_rsvd:0
hugepagesize:2048 KBWhen the pressure comes up:[email protected]:/proc/sys/vm>cat/proc/meminfo
memtotal:41175744 KB
memfree:375212 KB
buffers:36444 KB
cached:13005200 KB
swapcached:232984 KB
active:16919192 KB
inactive:509908 KB
hightotal:0 KB
highfree:0 KB
lowtotal:41175744 KB
lowfree:375212 KB
swaptotal:20972816 KB
swapfree:20070340 KB
dirty:184 KB
writeback:0 KB
anonpages:4375088 KB
mapped:12889760 KB
slab:168916 KB
pagetables:23005464 KB
nfs_unstable:0 KB
bounce:0 KB
commitlimit:41560688 KB
committed_as:40413008 KB
vmalloctotal:34359738367 KB
vmallocused:273756 KB
vmallocchunk:34359464051 KB
hugepages_total:0
hugepages_free:0
hugepages_rsvd:0
hugepagesize:2048 KBYou can see the pressure coming up,Pagetablesconsumes up to 23G of memory. Pagetables is the space used by mapping tables when virtual memory is mapped to physical memory in Linux, and this mapping table takes up so much memory that it's amazing. In order to solve this problem, think of the large page management of Linux, the normal page size is 4k, and the large page management page size is 2M, after the large page management, the mapping table occupies a significantly less space. So the database stopped, start large page management, to large page management allocation of 20G Memory: Echo 10240 >/proc/sys/vm/nr_hugepages add root Soft memlock-1
Root Hard Memlock-1Oracle Soft Memlock-1
Oracle Hard Memlock-1Change the database Lock_sga to True, then do the stress test, the system finally stable operation, free-m see the free memory has been idle 13G:[email protected]:/etc/security>free-M
Total used free shared buffers Cached
mem:40210 26234 13976 0 20 184
-/+ buffers/cache:26029 14181
swap:20481 479 20001
