Traditional Virtual Memory Page Size is only 4K and as as Linux does not have intimate shared memory model unlike Solaris the size of Page Table in Linux can grow significantly too large to become major slow down factor as number of dedicated connections(or shared servers - which is rare case) grows and SGA has large size - slowing down system by consuming system in system CPU usage to scan large Page Table. Also as we know processor has very small portion set for TLB(a portion of page memory resides in processor - rest linked) it is very important to tune Page Memory size by reducing its size if it is slowing down system-Another good reason to tune Page table is incurs heavy overhead in context switching when portion of page table in CPU is flushed. System CPU usage on Linux box can be tracked by checking sys% vmstat and grep -i pagesize /proc/meminfo. If sys% is significantly high then check the size of Page Table during your typical or peak workload hours on Linux DB server.
Linux has option of configuring Large Page size 2MB so say if Page Table size for 8GB SGA with 100 connections is 1GB then it reduces to less than 100MB with Huge pages. RHEL 6 Linux configures transparent huge pages also but Oracle does not go well with feature and recommends you to disable transparent Huge pages. Oracle's own flavour OEL has it disabled for UEK kernel.
Do you need Huge Pages:
Check system CPU usage by vmstat or sar .It should not be high. Check Page Table size by command:
grep -i Page /proc/meminfo It should not be high.
If system CPU usage and Page Table both are re significantly high or Page Table size is too high then go for Huge pages. You can better check the pattern of system usage of CPU by history of sar and compare it with number of concurrent sessions in that window with AWR report.
Advantages :
1) From Oracle 11g onward DB alert log begins with information with memory aread from SGA is allocated where Huge page on Linux system has been configure or not. How much of the Huge Menory pool is used by Oracle SGA and how much of the SGA memory from non Huge pool is allocated to SGA ]. Oracle 11g also comes with parameter use_large_pages and you can set it to 'ONLY' so that Oracle instance does not start if system lacks in required number of Huge pages so that Oracle instance memory area is not split in two - one portion in Huge pool and another in non Huge pool which slows down oracle database. Do not tempt to use parameter PRE_PGA_SGA=true, this parameter is not good for OLTP database, as it can slow down connection setup time. I've tested it with large number of concurrent connects and found it was 25% slower. It can be more with fewer connection due large cache effect of my heavily concurrent connection setup.
2) Huge Pages pool can not be used with AMM(automatic memory management introduced from 11g) but can be used with ASMM(automatic shared memory management 10g feature). I personally do not like either of these two and I like to configure SGA and PGA depending on typical need to avoid bugs(10gr2 has plenty of Shared Pool memory leakage bugs) and overhead of shrink and expansion of SGA components at run time.
Configuring Huge Pages: It is simple. Changes needs in OS setting only, so transparent to Database. You just optionally want to configure parameter use_large_page in 11g
oracle soft memlock 8400896
oracle hard memlock 8400896
Check Huge Pages are being used by Oracle :
Check number of Huge Pages which should not be 0 now and check for free number of Huge pages by command: grep -i Page /proc/meminfo It should not be high.
--------------You are done ------------
You will also notice the reduced Page Table Size from output of above command. If your system allows you can benchmark before and after configuring Huge pages.
Another note of caution:
If you are system is very low on memory by configuring hugepages you are always grabbing memoy's defined portion and so free space may be low if you are short of free memory. So your PGA or any other processes may system may go for swapping. Anyway SGA will never be swapped. So make sure there are enough free memory in system after Hugepages have been allocated.
I first used huge pages in 2009 on 10.2.0.4 database It was Dataware house with 30GB SGA but there were approx only 25 concurrent sessions but memory foot print of page table was much less than other DB servers with similar configuration and I was happy though I did not have any bench mark as this Prod system 24*7 was build from scratch and there was no outage.
If you are using virtualized environment and you want more free memory you can set it for small SGA and fewer connections as well. I tested it with 1.4GB SGA and 32 connection in each 3 RAC instances and it saved me memory approx 70MB in each node.
You are wondering Whether Oracle EXADATA is equiped with pre configured Huge Pages. No it does not want because different clients may have different SGA sizing need depending on DB nature OLTP/Dataware house so it does not.
When you not want Huge Pages: If SGA is small or very few dedicated connections or many instances on same host
Linux has option of configuring Large Page size 2MB so say if Page Table size for 8GB SGA with 100 connections is 1GB then it reduces to less than 100MB with Huge pages. RHEL 6 Linux configures transparent huge pages also but Oracle does not go well with feature and recommends you to disable transparent Huge pages. Oracle's own flavour OEL has it disabled for UEK kernel.
Do you need Huge Pages:
Check system CPU usage by vmstat or sar .It should not be high. Check Page Table size by command:
grep -i Page /proc/meminfo It should not be high.
If system CPU usage and Page Table both are re significantly high or Page Table size is too high then go for Huge pages. You can better check the pattern of system usage of CPU by history of sar and compare it with number of concurrent sessions in that window with AWR report.
Advantages :
- Configuring Huge pages reduces the page table scan time and saves CPU resources
- By configuring Huge Pages the memory freed by small page table can be assigned to Oracle SGA and PGA so more better memory utilization.
- Another advantage is SGA is allocated from Huge Pages pool so it is locked in memory and is not swapped(you do not need to set LOCK_SGA=TRUE init parameter] [ make sure whole SGA fits in Huge Page poo, if some of SGA is allocated from Huge pages pool and some from normal memory then this will slow down system and it may restart the system(I obsever same in one my machine RHEL 4.4 with less Huge pages and DB was Oracle 10.2.0.5 ) or DB won't be able to start Oracle instance ]
- If your SGA is small and you do not have large number of dedicated connections your benefit from configure Huge pages may be insignificant.
1) From Oracle 11g onward DB alert log begins with information with memory aread from SGA is allocated where Huge page on Linux system has been configure or not. How much of the Huge Menory pool is used by Oracle SGA and how much of the SGA memory from non Huge pool is allocated to SGA ]. Oracle 11g also comes with parameter use_large_pages and you can set it to 'ONLY' so that Oracle instance does not start if system lacks in required number of Huge pages so that Oracle instance memory area is not split in two - one portion in Huge pool and another in non Huge pool which slows down oracle database. Do not tempt to use parameter PRE_PGA_SGA=true, this parameter is not good for OLTP database, as it can slow down connection setup time. I've tested it with large number of concurrent connects and found it was 25% slower. It can be more with fewer connection due large cache effect of my heavily concurrent connection setup.
2) Huge Pages pool can not be used with AMM(automatic memory management introduced from 11g) but can be used with ASMM(automatic shared memory management 10g feature). I personally do not like either of these two and I like to configure SGA and PGA depending on typical need to avoid bugs(10gr2 has plenty of Shared Pool memory leakage bugs) and overhead of shrink and expansion of SGA components at run time.
Configuring Huge Pages: It is simple. Changes needs in OS setting only, so transparent to Database. You just optionally want to configure parameter use_large_page in 11g
- To configure huge pages check output of SGA shared memory segment size shown in ipcs -m and divide it by the Huge Page size which is 2MB by default and add 4 to this number and set this obtained number as parameter vm.nr_Huge_Pages=X in /etc/systctl.conf. Say your SGA is of size 8GB and ipcs is showing it 8194MB then divide it by 2 which gives 4097 now add 5 in this number(to be safe) and this 4102 will the Huge Pages required for you - this is X for you to set in mentioned parameter. This parameter can be enable dynamically by sysctl -p but it is good to restart system. Another way to set Huge pages temporarily is run echo 4012 > /proc/sys/vm/nr_hugepages but it may not free up space as Huge Pages needs contiguous free space and your system may not have this much contiguous free space so recommended is to set from sysctl.conf file followed by reboot as sysctl -p also may have issue.
- Now parameter oracle soft memlock Y and oracle hard memlock Y needs to be set in /etc/security/limits.conf .Here Y is size of Huge pages memory pool in KB. So for SGA of 8192 MB for whose number of Huge Pages came to be 4102 in above calculation in 1. it would be 2*1024*4102 = 8400896. So add parameters as below and check its effectivness by ulimit -l after log off/ login
oracle soft memlock 8400896
oracle hard memlock 8400896
Check Huge Pages are being used by Oracle :
Check number of Huge Pages which should not be 0 now and check for free number of Huge pages by command: grep -i Page /proc/meminfo It should not be high.
--------------You are done ------------
You will also notice the reduced Page Table Size from output of above command. If your system allows you can benchmark before and after configuring Huge pages.
Another note of caution:
If you are system is very low on memory by configuring hugepages you are always grabbing memoy's defined portion and so free space may be low if you are short of free memory. So your PGA or any other processes may system may go for swapping. Anyway SGA will never be swapped. So make sure there are enough free memory in system after Hugepages have been allocated.
I first used huge pages in 2009 on 10.2.0.4 database It was Dataware house with 30GB SGA but there were approx only 25 concurrent sessions but memory foot print of page table was much less than other DB servers with similar configuration and I was happy though I did not have any bench mark as this Prod system 24*7 was build from scratch and there was no outage.
If you are using virtualized environment and you want more free memory you can set it for small SGA and fewer connections as well. I tested it with 1.4GB SGA and 32 connection in each 3 RAC instances and it saved me memory approx 70MB in each node.
You are wondering Whether Oracle EXADATA is equiped with pre configured Huge Pages. No it does not want because different clients may have different SGA sizing need depending on DB nature OLTP/Dataware house so it does not.
When you not want Huge Pages: If SGA is small or very few dedicated connections or many instances on same host
Posts
