[RHEL] Tuning Red Hat Enterprise Linux for Oracle and Oracle RAC performance

Tuning Red Hat Enterprise Linux for Oracle and Oracle RAC performance

05시 10분 2014년 5월 24일 업데이트


문제
How can I tune my Red Hat Enterprise Linux system for Oracle 10g/11g?
High memory consumption on Oracle database system
High CPU consumption on Oracle hosts
How should I configure swap memory for an Oracle database?
Slower than expected IO performance running Oracle on RHEL
What is the maximum number of huge pages I should be using per system?

환경
Red Hat Enterprise Linux 6
Red Hat Enterprise Linux 5
Red Hat Enterprise Linux 4
Oracle 9g
Oracle 10g
Oracle 11g

해결
Please see the below subsections for kernel tuning recommendations:

Note: All the numbers mentioned in this article are not generic and have been shown to have a positive effect on Database workloads resident on Red Hat Enterprise Linux.
That being said, the specific values depend on user environments and may require further adjustment.

Memory settings in /etc/sysctl.conf:

Swapping for Oracle is not ideal and should be avoided as much as possible. The following tunable will tune the kernel to swap less aggressively.

vm.swappiness=10

Maximum percentage of active memory that can have dirty pages:

For example if a system has 1000 pages of memory and dirty_background_ratio is set to 3%, writeback will begin when 30 pages have been dirtied.

vm.dirty_background_ratio=3

Maximum percentage of total memory that can have dirty pages:

If it is set to 15% on a 1000 page system, a process dirtying pages will be made to wait once the 150th page is dirtied.

This mechanism will, thus, slow the dirtying of pages while the system catches up.

vm.dirty_ratio=15

How long data can be in page cache before being expired:

vm.dirty_expire_centisecs=500

How often pdflush is activated to clean dirty pages (in hundreths of a second):

vm.dirty_writeback_centisecs=100

HugePages

Obtain the Hugepagesize from /proc/meminfo

$ grep Huge /proc/meminfo
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
Hugepagesize:       2048 kB

Using Hugepagesize and the size of your SGA and PGA values, calculate the recommended number of HugePages.

More information about how to obtain SGA and PGA values can be sourced from the Oracle Wiki.

(SGA+PGA+(20KB * # of Oracle processes running)) / 2MB

For example:

(20GB SGA + 10GB PGA + (20KB * 1,000 Oracle processes)) / 2MB = 15369

/etc/sysctl.conf setting:

vm.nr_hugepages=15369

Once the above setting has been tested and shown to provide the performance required, 
it is recommended to move HugePage allocation to the kernel line in /boot/grub/grub.conf as a boot option.
This allows for earlier and cleaner allocation of HugePages:

If Hugepages are not set in kernel, they are dynamically allocated by setting the value in nr_hugepages.

When the system is under memory pressure, the huge pages cannot be swapped.

kernel /vmlinuz-<kernel-version> ro root=/dev/vg01/lv01 hugepages=15369 <other boot options>

Change the Oracle database configuration to use the Huge Pages. Contact Oracle support if assistance is needed

Limits setting in /etc/security/limits.conf:

Note: In order for Oracle database to use Huge Pages in Red Hat Enterprise Linux 4, 5 or 6, you may also need to increase the ulimit parameter "memlock" for the oracle user in /etc/security/limits.conf. The memlock setting is specified in KB and must match the memory size of the number of Huge Pages that Oracle should be able to allocate. So, if the Oracle database should be able to use 512 Huge Pages, then memlock must be set to at least (512 * Hugepagesize), which on a default system would be 1048576 KB (512*2048).

oracle     soft     memlock     1048576  
oracle     hard     memlock     1048576

Shared Memory

In a terminal window, obtain the total memory from the system:
mem=$(free|grep Mem|awk '{print $2}')

Convert the value of $mem to bytes:
totmem=$(echo "$mem*1024"|bc)

Get the Hugepagesize from /proc/meminfo:
huge=$(grep Hugepagesize /proc/meminfo|awk '{print $2}')

Calculate what 75% of the total memory on the system for SHMMAX:
max=$(echo "$totmem*75/100"|bc)

Divide the SHMMAX value by the Hugepagesize to get SHMALL:
all=$(echo "$max/$huge"|bc)

Set the SHMMAX value in the /etc/sysctl.conf file:
echo "kernel.shmmax = $max" >> /etc/sysctl.conf

Set the SHMALL value in the /etc/sysctl.conf file:
echo "kernel.shmall = $all" >> /etc/sysctl.conf

Setting the maximum number of shared memory segments with SHMMNI.
kernel.shmmni=4096


Semaphores

Recommended minimums for semaphore operations:
kernel.sem="250 32000 100 128"

The first value, SEMMSL, is the maximum number of semaphores per semaphore set
The second value, SEMMNS, defines the total number of semaphores for the system
The third value, SEMOPM, defines the maximum number of semaphore operations per semaphore call
The last value, SEMMNI, defines the number of entire semaphore sets for the system

#sysctl -w "kernel.sem = 250 32000 100 128"

Open file descriptors for oracle user

#vi /etc/security/limits.conf
#<domain>      <type>  <item>         <value>
oracle          hard    nofile          10000

Disabling transparent hugepages (THP) recommended just for Red Hat Enterprise Linux 6

Disable tuned and ktune services if you have it.

Eg:
# service tuned stop
# chkconfig tuned off
# service ktune stop
# chkconfig ktune off

OR

# tuned-adm off

Note: The tuned-adm command will revert all your settings to what they were before tuned started and disable the tuning services from running at boot. As an alternative, a customized tuned profile can be created, taking over the old settings and only disabling THP. Refer to kbase Disabling transparent hugepages (THP) on Red Hat Enterprise Linux 6 is not taking effect. for details.

Append "transparent_hugepage=never" to kernel command line in /boot/grub/grub.conf file.

Eg:
kernel /boot/vmlinuz-2.6.32-358.el6.x86_64 ro root=UUID=a216d1e5-884f-4e5c-859a-6e2e2530d486 rhgb quiet transparent_hugepage=never
Reboot the server for changes to take effect.

Eg:
# reboot

I/O scheduler

The default CFQ I/O scheduler is appropriate for most workloads, but does not offer optimal performance for database environments.
Instead, the deadline scheduler is recommended in these environments, further detailed in What is the recommended I/O scheduler for an Oracle database in RHEL?.

Reference

* 2013 - Deploying Oracle RAC 11g R2 Database on Red Hat Enterprise Linux 6 - Best Practices