ANBOB™

Some time we will face  login to the local oracle database instance using sqlplus “/ as sysdba” may take 1 minute or even longer,  This is usually not normal and needs to be diagnosed for the reason.

Troubleshooting
Check for CPU and memory usage at the OS level
Check alert log, sqlnet.log, listener.log and OS logs
Maybe due to DISM on Solaris
use tool  like  truss or trace to debug  e.g.

trace -fo /tmp/test_conn.log sqlplus / as sysdba
truss -aefdD -o /tmp/test_conn.log   sqlplus '/as sysdba'

A few days ago, I encountered a case, a 11.2.0.4 three-node Oracle RAC database on RHEL 6.6 , when trying to login to the database instance using sqlplus “/ as sysdba” on the third node, It’s very slow, and vmstat show that there is a very large  swap in and out, but there is still a lot of memory free space . Just like the picture below

vmstat can be used to live check active swapping. Values in the si and so columns indicate memory being swapped in and out.

swap memory is considerably less performant than actual RAM. it is not recommended to use swap as a substitute for memory. Adding swap serves to give yourself time to further troubleshoot the issue, and either free up or add additional memory to the system.

Why use Swap when memory is 50% free?

First of all, this database instance is not configured with hugepage,  The host has 200GB of physical memory, and the SGA and PGA of the database configuration are allocated about 110G in total. it is  free 100GB currutly. si and so columns tald us it had swap used.

Swap space is the area on a hard disk which is part of the Virtual Memory of your machine, which is a combination of accessible physical memory (RAM) and the swap space. Swap space temporarily holds memory pages that are inactive. Swap space is used when your system decides that it needs physical memory for active processes and there is insufficient unused physical memory available. If the system happens to need more memory resources or space, inactive pages in physical memory are then moved to the swap space therefore freeing up that physical memory for other uses.

The Linux kernel automatically moves RAM reserved by programs–but not really used–into swap, so that it can serve the better purpose of extending cached memory,   by default, Linux may choose to swap out a process or some of its data due to low usage even if it is not running low on RAM.

The Linux kernel will migrate some data from memory which has not been accessed for some time out to swap to make room for a possible future demand. This standard paging from memory to swap is performed to optimize the overall system performance.

The swappiness parameter controls the tendency of the kernel to move processes out of physical memory and onto the swap disk. Controlling the paging activity of the kernel using the /proc/sys/vm/swappiness setting.

What is swappiness and how do I change it?

The swappiness parameter controls the tendency of the kernel to move processes out of physical memory and onto the swap disk. Because disks are much slower than RAM, this can lead to slower response times for system and applications if processes are too aggressively moved out of memory.

• swappiness can have a value of between 0 and 100
• swappiness=0 tells the kernel to avoid swapping processes out of physical memory for as long as possible
• swappiness=100 tells the kernel to aggressively swap processes out of physical memory and move them to swap cache

The default setting which is 60 on RHEL 5 & 6 and 30 on RHEL 7. Reducing the default value of swappiness will probably improve overall performance for a typical Ubuntu desktop installation. A value of swappiness=10 is recommended, but feel free to experiment.

Increasing this value will make the system more inclined to swap inactive memory pages to disk, rather than dropping pages from the page cache, leaving more memory free for cached I/O. This may be preferred for heavy I/O workloads. Decreasing this value will make the system less inclined to swap, and more inclined to drop pages from the page cache.

Note: while vm.swappiness accepts 0-100 values, it should not be set to 0, as this can cause an unexpected behaviour on the system. 0 is a special value which works differently depending on the RHEL version in use:

• Prior to RHEL 6.4: vm.swappiness = 0 disables swapping for the most part, except to avoid an out-of-memory situation.
• RHEL 6.4 and later: vm.swappines = 0 disables swapping in most cases, If we setswappiness==0, the kernel does not swap out completely (for global reclaim until the amount of free pages and filebacked pages in a zone has been reduced to something very very small (nr_free + nr_filebacked < high watermark)).

You can use the following script to get the swap manually back into RAM:

err="not enough RAM to write swap back, nothing done"
mem=`free|grep Mem:|awk '{print $4}'`
swap=`free|grep Swap:|awk '{print $3}'`
test $mem -lt $swap && echo -e $err && exit 1
swapoff -a && swapon -a &&
exit 0

set the vm.swappiness parameter. e.g.

Online dynamic modification takes effect immediately
echo '10'> /proc/sys/vm/swappiness
To make this setting persistent across reboots  append /etc/sysctl.conf
vm.swappiness = 10

In the ORAClE environment, the swappness value is the big or the small?

Oracle increase VS  Red Hat decrese

Oracle recommended to increase ,  The reason Oracle provides is: In stress testing, we found that setting swapiness to 100 can reduce or delay node eviction caused by memory exhaustion caused by logon storms.

RedHAT recommented to decrease, Tuning Red Hat Enterprise Linux for Oracle and Oracle RAC This document mentions Swapping for Oracle is bad and recommends setting the vm.swappiness parameter to 0.

the two opinions are the opposite, so who should I listen to?

RHEL: Mainly from the perspective of performance. Very simple, in the case of enough memory, you should avoid using swap, because in terms of performance, continuous page breaks will only lead to poor performance.

Oracle: Mainly from the perspective of usability. There is a typical scenario, two-node RAC, one of which is down for some reason, this time the connection will automatically transfer to another node, another node can not handle the instantaneous large number of connections due to insufficient memory and hang.

In environments with more physical memory free  space, I recommend decrease this value. After all, using SWAP can cause performance problems.

Reference  Why is SWAP being used instead of available physical memory? (Doc ID 2404462.1) and RHEL online docs.

打赏

微信扫一扫，打赏作者吧～