由一条日志警告所做的调优分析

这个案例发生有段时间了，但是今天无意中看到当时的邮件，感觉还是收益匪浅，看来还是细节决定成败啊。从一些日志或trace 文件中的警告信息中我们可以发掘出潜在的问题。
当时系统中的用户数很小,所以每天都能抽时间看看日志记录,看有没有明显的问题。结果在grep的时候发现trace文件中有一些警告记录。
当时的库是10gR2的库，现在已经升级到了11gR2.
/xxxx/oracle/xxxxx/oradmp/udump/xxxxx01_ora_15070.trc 
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
ORACLE_HOME = /opt/app/oracle/xxxxxx/product/10.2.0 
System name:    Linux 
Node name:      xxxx 
Release:        2.6.18-308.el5 
Version:        #1 SMP Fri Jan 27 17:17:51 EST 2012 
Machine:        x86_64 
Instance name: xxxxxx
Redo thread mounted by this instance: 1 
Oracle process number: 472 
Unix process pid: 15070, image: oracle@xxxxx (TNS V1-V3) 
 
*** 2013-03-29 20:18:54.711 
*** ACTION NAME:() 2013-03-29 20:18:54.707 
*** MODULE NAME:(OGG-ECC_ARCG-GLOPEN_DATA_SOURCE) 2013-03-29 20:18:54.707 
*** SERVICE NAME:(SYS$USERS) 2013-03-29 20:18:54.707 
*** SESSION ID:(5029.3122) 2013-03-29 20:18:54.707 
WARNING:Could not increase the asynch I/O limit to 224 for SQL direct I/O. It is set to 128 
WARNING:Could not increase the asynch I/O limit to 256 for SQL direct I/O. It is set to 128 
WARNING:Could not increase the asynch I/O limit to 224 for SQL direct I/O. It is set to 128 
*** 2013-03-29 22:25:26.506 
WARNING:Could not increase the asynch I/O limit to 224 for SQL direct I/O. It is set to 128 
WARNING:Could not increase the asynch I/O limit to 256 for SQL direct I/O. It is set to 128 
WARNING:Could not increase the asynch I/O limit to 256 for SQL direct I/O. It is set to 128 
WARNING:Could not increase the asynch I/O limit to 288 for SQL direct I/O. It is set to 128 

从警告的信息可以看出，和IO相关，可能是什么参数的设置出问题了。查看MOS 发现有一篇相关的文章。
Warning:Could Not Increase The Asynch I/O Limit To XX For Sql Direct I/O (Doc ID 1302633.1)
其中给出的solution如下：

SOLUTION

The aio-max-size kernel parameter doesn't exist in the 2.6.x Linux kernels. 
This feature is now "automatic" in the 2.6.x kernel, based on the physical capabilities of the disk device driver.
This should mean that the Linux Kernel is ready to perform ASYNC I/O.

All install requirements should be met.

To ensure ASYNC I/O can be performed by Oracle Database you need to verify or set the following parameters in the Database:


Then shutdown and startup the database and check if the warning reappears.
An HCVE report (refer to Note 250262.1) should report no remaining issues

If the above doesn't resolve the problem, then increase fs.aio-max-nr

关于查看aio的内容可以使用如下的方式来查看。
cat /proc/sys/fs/aio-max-nr
/sbin/sysctl  -a |grep aio
当时得到的值是
fs.aio-max-nr = 65536
fs.aio-nr = 65472

可能一般来说问题处理到这个地方就告一段落了。
这个问题当时是叫给一个资深的专家来做的调优，他又要了一些其他的信息。
最后给出的建议如下：
-          Increase fs.aio-max-nr  as advised in my initial e-mail to  3145728
-          Change Oracle filesystemio_options to  “SETALL”  (enable both asynch and direct IO)
 
Following is suggested:
-          VXFS – change mount options to:
mincache=direct,convosync=direct   (Must be done along with the change of the Oracle filesystemio_options)
-          Enable ODM
Pending upon the VXFS version – and True VXFS license supporting ODM (should be by default with VXFS 5.X)
Enable Veritas ODM  - by doing “ln –s /opt/VRTSodm/lib64/libodm.so   $ORACLE_HOME/lib/libodm10.so”
-          Memory
As server has sufficient memory – consult with DBAs if it is worthwhile increasing Oracle cache
-          Power
Consider disabling power management savings for better response / latency (BIOS level)
-          Disable C-State
RH 5.X do not support C-State  (BIOS level)
 
Kernel parameters:
vm.min_free_kbytes = 32768
vm.dirty_expire_centisecs = 500
vm.dirty_ratio = 15
vm.dirty_writeback_centisecs = 100
vm.swappiness = 0
vm.hugetlb_shm_group = vm.nr_hugepages = 16384      # set hugepages to 32 GB  - if total of Oracle instances cache grows beyond must increase it accordingly it is 2 MB pages)
                                                    # any increase requires a reboot !!

其中关于huge page的部分还是有点意思。其中关于设置的Hugepage数还可以通过一个脚本来计算。
当时看得云里雾里，详细的学习了一下关于hugepage的设置，还是有所收获。
其实在官方文档中已经有详细的描述。hugepage的配置可以参考文档。
http://docs.oracle.com/cd/E11882_01/server.112/e10839/appi_vlm.htm#UNXAR396
发现官方文档还是很有权威性和价值的。
计算hugepage的脚本如下：

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`
# Start from 1 pages to be on the safe side and guarantee 1 free HugePage
NUM_PG=1
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[0-9][0-9]*"`
do
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
   fi
done
# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
esac
# End

所以问题的分析和解决思路还是最重要的，可能大多数时候碰到问题都是头疼医头，脚疼医脚。处理问题还是没有从整体的情况来做，以点带面，能够举一反三才是问题处理的高手。