Linux 内核参数优化(for oracle)

Oracle 不同平台的数据库安装指导为我们部署Oracle提供了一些系统参数设置的建议值，然而建议值是在通用的情况下得出的结论，并非能完全满足不同的需求。使用不同的操作系统内核参数将使得数据库性能相差甚远。本文描述了linux下几个主要内核参数的设置，供参考。

1、Linux共享内存
  共享内存是在系统内核分配的一块缓冲区，多个进程都可以访问该缓冲区。
  由于进程可以直接读写内存，避免了在内核空间与用户空间的切换，所以共享内存读写效率很高。
  当一个进程改变了这块地址中的内容的时候，其它进程都会察觉到这个更改。共享内存类似与windows环境编程中的内存映像文件。

  Linux的IPC(Interprocess Communication)通信机制:是指多个进程之间相互通信，交换信息的方法。
  通过使用共享内存允许两个或多个进程共享一定的存储区，因为不需要拷贝数据。
  Oracle SGA即是基于此方式来实现Oracle进程之间数据共享。因此SGA的合理设置对Oracle性能有重大的影响。
  可以通过ipcs -lm来查看所有的共享内存设置。

2、参数修改的方式
  由于Linux的内核参数信息都存在内存中，因此可以通过命令直接修改，并且修改后直接生效。
    但是，当系统重新启动后，原来设置的参数值就会丢失，而系统每次启动时都会自动去/etc/sysctl.conf文件中读取内核参数。
  如果是临时修改，则可以采用echo或sysctl -w方式，如果永久修改，建议修改sysctl.conf文件
  下面是几种参数修改方式的描述
  a、echo 方式
    echo <value> > <file_name>
    将值输出到文件，该方式使得内核参数修改立即生效，无需重启系统，但重启后失效(以sysctl.conf永久配置文件为准)
  b、sysctl 命令方式
    sysctl -w <parameter_name>=<value>
    使用sysctl 命令行方式是修改的运行时的内核参数，参数值当重启后失效，同上
  c、永久性更改
    vi /etc/sysctl.conf #直接修改对应参数的值，然后执行命令使更改立即生效# sysctl -p
    echo "kernel.shmmax=2147483648" >> /etc/sysctl.conf #不太建议该方式，这样子同一参数会有多个值出现在sysctl.conf

3、sysctl.conf配置文件
几乎所有的重要参数都保持在sysctl.conf配置文件中，因此对于永久性修改，直接修改该文件是最简单不过的方式了
下面是基于x86_64 RHEL5 下Oracle 给出的参数建议值

  Oracle10gR2 On RHEL 5/OEL 5 (x86_64) [ID 421308.1]
    kernel.shmall = physical RAM size / pagesize
      For most systems, this will be the value 2097152. See Note 301830.1 for more information.
    kernel.shmmax = 1/2 of physical RAM.
      This would be the value 2147483648 for a system with 4Gb of physical RAM. See Note 567506.1 for more information.
    kernel.shmmni = 4096
    kernel.sem = 250 32000 100 128
    fs.file-max = 512 x processes (for example 65536 for 128 processes)
    Development recommends a minimum of 327679 for active systems.
    net.ipv4.ip_local_port_range = 9000 65500
    (The runInstaller (OUI) checks may expect this to be the old guidance of 1024 65000.
    The new guidance from Oracle development is 9000 65500.
    Please allow the runInstaller (OUI) to proceed with the new guidance from Oracle development.)
    net.core.rmem_default = 262144
    net.core.rmem_max = 2097152
    net.core.wmem_default = 262144
    net.core.wmem_max = 1048576

  Oralce 11g R2 on RHEL (and OEL) 5 on AMD64/EM64T [ID 880989.1]
    kernel.shmall = physical RAM size / pagesize
      For most systems, this will be the value 2097152. See Note 301830.1 for more information.
      kernel.shmmax = 1/2 of physical RAM.
        This would be the value 2147483648 for a system with 4Gb of physical RAM.
      kernel.shmmni = 4096
      kernel.sem = 250 32000 100 128
      fs.file-max = 512 x processes (for example 6815744 for 13312 processes)
      fs.aio-max-nr = 1048576
      net.ipv4.ip_local_port_range = 9000 65500
      net.core.rmem_default = 262144
      net.core.rmem_max = 4194304
      net.core.wmem_default = 262144
      net.core.wmem_max = 1048576

  对于Oracle 11g，Oracle建议如果系统当前设置的值大于上述列出的值，则无需修改，保留原值。
  从上面可知，sysctl.conf包含了所有的内核参数文件，且以kernel开头，下面对这几个重要参数进行逐一描述。

4、参数SHMMAX

  a、参数描述(From meatlink [ID 15566.1])
   Shared memory allocation
   ========================

     Oracle has 3 different possible models for the SGA - one-segment,
     contiguous multi-segment, and non-contiguous multi-segment.
     When attempting to allocate and attach shared memory for the SGA, it
     will attempt each one, in the above order, until one succeeds or raises
     an ORA error. On other, non-fatal, errors, Oracle simply cleans up and
     tries again using the next memory model. The entire SGA must fit into
     shared memory, so the total amount of shared memory allocated under any
     model will be equal to the size of the SGA. This calculated value will
     be referred to below as SGASIZE.

     The one-segment model is the simplest and first model tried. In this
     model, the SGA resides in only one shared memory segment. Oracle attempts
     to allocate and attach one shared memory segement of size equal to total
     size of the SGA. However, if the SGASIZE is larger than the configured
     SHMMAX, this will obviously fail (with EINVAL). In this case, the SGA will
     need to be placed in multiple shared memory segments, and Oracle proceeds
     to the next memory model for the SGA. If an error other than EINVAL occurs
     when allocating the shared memory with shmget(), Oracle will raise an
     ORA-7306. If the segment was received (i.e. if SHMMAX > SGASIZE), Oracle
     attempts to attach it at the start address defined in ksms.o. An error
     on the attach will raise an ORA-7307.

     With multiple segments there are two possibilities. The segments
     can be attached contiguously, so that it appears to be one large
     shared memory segment, or non-contiguously, with gaps between the
     segments. The former wastes less space that could be used for the stack
     or heap, but depending on alignment requirements for shared memory
     (defined by SHMLBA in the kernel), it may not be possible.

     At this point, Oracle needs to determine SHMMAX so it can determine how many
     segments will be required. This is done via a binary search
     algorithm over the range [1...SGASIZE] (since Oracle is trying this
     model and not the one segment model it must be that SHMMAX<SGASIZE)
     The value of SHMMAX calculated will then be rounded to an even page size
     (on some machines, possibly to an even 2 or 4 page block).

     In the contiguous segment model, Oracle simply divides the SGA into
     SGASIZE/SHMMAX (rounded down) segments of size SHMMAX plus another segment
     of size SGASIZE modulo SHMMAX. If more than SS_SEG_MAX segments are
     required total, an ORA-7329 is raised. It then allocates and attaches
     one segment at a time, attaching the first segment at the start address
     defined in "ksms.o". Subsequent segments are attached at an address equal
     to the previous segment's attach address plus the size of the previous
     segment so that they are contiguous in memory.

     For example, if SHMMAX is 2M, SGASIZE is 5M, and the start address is
     0xd0000000, there would be 3 segments, 2 of 2M and 1 of 1M. They would be
     attached at 0xd0000000, 0xd0000800 (0xd0000000+2M), and 0xd0001000
     (0xd0000800+2M). If Oracle receives an error allocating a shared memory
     segment, an ORA-7336 is raised.

     If an error is raised on attaching a shared memory segement, Oracle checks
     the system error returned. If it is EINVAL, the attach address used is most
     likely badly aligned (not a mulitple of SHMLBA). In this case, Oracle tries
     the next model for SGA allocation, non-contiguous segments. Otherwise, an
     ORA-7337 is raised.

     The last model Oracle will try is the non-contiguous model. Here,
     things become a bit more complicated. After calculating SHMMAX, Oracle
     first checks to see if it can put the fixed and variable portion into
     one shared memory segment just large enough to hold the two portions
     If it can, it allocates a segment just big enough to hold both portions.
     If it cannot, it will put them each into their own seperate segment just
     large enough to hold each portion. If the fixed portion is larger than
     SHMMAX, an ORA-7330 will be raised. If the variable portion is larger
     than SHMMAX, an ORA-7331 will be raised. Then Oracle computes the number
     of redo block buffers it can fit in a segment (rounded down to an
     integral number of buffers - buffers cannot overlap segments). An ORA-7332
     is raised is SHMMAX is smaller than the size of a redo block.

     Similarly, the number of db block buffers per segment is calculated, with an
     ORA-7333 raised if SHMMAX is too small to hold one db block. Then Oracle can
     compute the total number of segments required for both the redo and database
     block buffers. This will be buffers/buffers per segment (rounded down) segments
     and one (if necessary) of buffers modulo buffers per segment size, calculated
     seperately for both the redo and db block buffers. These segments will be
     of a size just large enough to hold the buffers (so no space is wasted).

     The total number of segments allocated will then be the number needed for
     the fixed and variable portions (1 or 2) plus the number needed for the
     redo block buffers plus the number of segments needed for the database block
     buffers. If this requires more than SS_SEG_MAX segments, an ORA-7334 is
     raised.

     Once the number of segments and their sizes is determined, Oracle
     then allocates and attaches the segments one at a time; first the fixed
     and variable portion segment(s), then the redo block buffer segment(s),
     then the db block buffer segment(s). They will be attached non-contiguously,
     with the first segment attached at the start address in "ksms.o" and following
     segments being attached at the address equal to the attach address of the
     previous segment plus the size of the previous segment, rounded up to a
     mulitple of SHMBLA.

     If Oracle receives an error allocating a shared memory segment, an ORA-7336 is
     raised. If an error is raised on attaching a shared memory segement, Oracle
     checks the system error returned. If it is EINVAL, normally another model
     would be tried, but as there are no more models to try, an ORA-7310 is raised.
     Other attach errors raise an ORA-7337.

     At this point, we have either attached the entire SGA or returned an
     ORA error. The total size of segments attached is exactly SGASIZE;
     no space is wasted. Once Oracle has the shared memory attached, Oracle
     proceeds to allocating the semaphores it requires.

    该参数定义了一个linux进程能分配虚拟地址空间的单个共享内存段的大小(字节为单位)。
    从上面的描述可知对于Oracle而言，SHMMAX主要用于控制和分配sga，且使用3种不同的模式可供选择来分配sga size
      SHMMAX > SGASIZE : 分配一个单独的共享内存段给sga(首选方式)
      SHMMAX < SGASIZE : 分配多个连续或不连续的共享内存段给sga
    由上可知应当设置为大于当前服务器上运行实例的最大的SGA的大小。
    如该值设置太小或者小于最大sga的大小,则sga无法在一个单独的共享内存段hold住整个sga，而需要分配多个不同的共享内存段来完成。
    对于32-bit操作系统，通常设置为4GB-1byte，而64-bit,则可以设置为大于4GB

  b、查看参数文件值
    # cat /proc/sys/kernel/shmmax
    2147483648

  c、修改参数值
    如果当前最大sga的大小为3g,则至少为shmmax设置下面的值
    # echo 3221225472 > /proc/sys/kernel/shmmax

    # sysctl -w kernel.shmmax=3221225472

    # echo "kernel.shmmax=3221225472" >> /etc/sysctl.conf

5、参数SHMMNI
  a、参数描述
    该参数用于设置共享内存段的总个数。也就是说在内存中总共可以开辟多少个共享内存段。
    对于Oracle 10g，Oracle的对该参数的建议值至少为4096。一般情况下够用。

  b、查看shmmni参数文件的值
    # cat /proc/sys/kernel/shmmni
    4096

  c、修改shmmni参数
    # echo 4096 > /proc/sys/kernel/shmmni

    # sysctl -w kernel.shmmni=4096

    # echo "kernel.shmmni=4096" >> /etc/sysctl.conf

6、参数SHMALL
  a、参数描述
    该参数用于配置系统一次能够使用的最大的共享页面数，该参数的值总是基于ceil(shmmax/PAGE_SIZE)
     Oracle 9i,10g的x86以及x86-64平台推荐以及缺省的大小通常为2097152
     在大多数情况下，该参数是够用的。按照上述计算方式页面内存总大小可以达到8GB(2097152*4096 bytes (shmall*PAGE_SIZE))
     通常情况下，PAGE_SIZE 的大小为4096byte，除非使用了Big Pages 或 Huge Pages
     对于超出8GB系统内存，如共享内存段(shmmax)的最大大小是16G，则所需要共享内存页数(shmall)为：
       16GB/4KB=16777216KB/4KB=4194304(页) 对于32GB，64GB可以依上述方式类推。

  b、查看系统当前的page size大小与SHMALL 参数的值
    $ getconf PAGE_SIZE
    4096

    # cat /proc/sys/kernel/shmall
    2097152

  c、修改SHMALL 参数
    下面是按照16G所需的页面数进行设置
    # echo 4194304 > /proc/sys/kernel/shmall

    # sysctl -w kernel.shmall=4194304

    # echo "kernel.shmall=4194304" >> /etc/sysctl.conf

7、设置信号量
  Semaphore allocation(From meatlink [ID 15566.1])
  ====================

    Semaphore allocation is much simpler than shared memory. Oracle just
    needs to allocate a number of semaphores equal to the processes parameter
    in "init.ora". PROCESSES will be used to refer to this value. Note on
    machines with a post-wait kernel extension, Oracle does not need to allocate
    semaphores (because it doesn't need to implement its own post-wait mechanism).

    Oracle uses semaphores to control concurrency between all the
    background processes (pmon, smon, dbwr, lgwr, and oracle shadows).
    Semaphores are also used to control two-task communication between
    the user process and shadow process if the fast (shared memory)
    driver is used. And in the Unix ports based on MIPS RISC
    processors, Oracle uses a special semaphore to perform basic
    test & set functions that are not provided by the processor

  信号量，有时候也被称为信号灯，是一个非负整数计数器。通常用来协调对资源的访问。
  信号量用于提供进程间的同步或者一个进程内的多个线程来共享资源，比如共享内存。其中信号计数会初始化为可用资源的数目。
  当线程在资源增加时会增加计数，在删除资源时会减小计数，这些操作都以原子方式执行。如果信号计数变为零，则表明已无可用资源。
  计数为零时，尝试减小信号的线程会被阻塞，直到计数大于零为止。
  信号量的数量可以通过系统内核参数SEMMSL来设置。

  a、参数SEMMSL
    该参数定义了每个信号集的最大信号数量
    Oracle 建议将 SEMMSL 设置为Oracle参数文件(用于Linux系统中的所有数据库)中的最大PROCESS实例参数的设置值再加上10 。
    此外， Oracle建议将 SEMMSL 的值设置为不少于100。

  b、参数SEMMNS
    该参数控制整个 Linux 系统中信号(而不是信号集)的最大数。
    Oracle 建议将 SEMMNS 设置为：
      系统中所有数据库实例的PROCESSES参数设置值的总和,加上最大PROCESSES值的两倍,最后根据系统中Oracle数据库的数量,每个加10
    使用计算式来确定在 Linux 系统中可以分配的信号的最大数量。取两者中较小的一个值：SEMMNS 或(SEMMSL * SEMMNI)

    在Oracle 10g和Oracle 11g中，该值被建议设置为SEMMSL * SEMMNI (250*128=32000)

  c、参数SEMOPM
    SEMOPM：该内核参数用于控制一次semop系统调用可以执行的信号操作的数量semopm。semop 系统调用(函数)提供了利用一个 semop
    系统调用完成多项信号操作的功能。一个信号集能够拥有每个信号集中最大数量的SEMMSL 信号，因此建议设置 SEMOPM 等于SEMMSL 。
    Oracle 建议将 SEMOPM 的值设置为不少于 100

  d、参数SEMMNI
    该参数定义整个Linux系统中信号集的最大数量。Oracle 建议将 SEMMNI 的值设置为至少为128 。

  e、查看当前的信号量设置
    # cat /proc/sys/kernel/sem
    250     32000   32      128 (这几个值分别是:SEMMSL, SEMMNS, SEMOPM, and SEMMNI)

    简要描述这四个参数
    SEMMSL: 每个信号集的最大信号数量
    SEMMNS: 系统信号量(非信号集)最大数量
    SEMOPM: 每次semop系统调用可执行的信号操作数
    SEMMNI：系统信号量集最大数量

    也可以通过ipcs -ls来查看当前的信号量设置
    # ipcs -ls

  f、修改信号量相关参数
    # echo 250 32000 100 128 > /proc/sys/kernel/sem

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

    echo "kernel.sem=250 32000 100 128" >> /etc/sysctl.conf

    -- Author : Robinson
    -- Blog   : http://blog.csdn.net/robinson_0612

  h、信号量设置示例
    SEMMSL应该设置为服务器中实例中具有最大的PROCESSES参数+10，例如，当最大的PROCESSES参数为5000时，SEMMSL应设置为5010。
    SEMMNS参数应设置为SEMMSL*SEMMNI，接上例SEMMSL为5010，SEMMNI的值一般为128，则SEMMNS参数应为（5010*128）=641280。
    SEMOPM参数应设置与SEMMSL参数相同，接上例此处应设置为5010
    因此对于信号量建议做如下设置
    sysctl -w kernel.sem="5010 641280 5010 128"

8、参数fs.file-max
  a、参数描述
    该参数指定了当前系统下可打开的最大文件句柄数。也就是可以打开的最大文件数。
    无论何时当一个文件句柄被应用程序请求时，linux内核将动态的分配文件句柄。但是当应用程序释放后，内核并不释放这些文件句柄。
    Linux内核采用循环利用这些句柄方式来取代释放。有点类似于缺省情况下Oracle被删除记录的空闲空间并不释放，下次可继续使用。
    因此系统中已分配的句柄数的值可能很高，而实际真正使用的句柄数值很低。
    注意，该值是一个系统级别的限制，因此该参数的值建议大于等于Oracle用户的limit限制nofile的值

    对于Oracle 9i以及10g，Oracle建议该值至少设置为65536。

  b、查看当前设置的可打开的文件句柄数
    $ cat /proc/sys/fs/file-max

    这里的"fs.file-max = 65536"其实是由"fs.file-max = 512 * PROCESSES"得到的
    我们指定PROCESSES的值为128，即为"fs.file-max =512 *128"。
    6815744 512 x processes (for example 6815744 for 13312 processes)

  c、查看当前文件句柄数的使用情况
    $ cat /proc/sys/fs/file-nr

    The file-nr file displays three parameters:
      - Total allocated file handles
      - Currently number of used file handles (2.4 kernel); Currently number of unused file handles (2.6 kernel)
      - Maximum file handles that can be allocated (see also /proc/sys/fs/file-max)

   在kernel 2.6之前的版本中，file-nr 中的值由三部分组成，分别为：
   1).已经分配的文件句柄数，2).已经分配单没有使用的文件句柄数，3).最大文件句柄数。
   但在kernel 2.6版本中第二项的值总为0，这并不是一个错误，它实际上意味着已经分配的文件句柄无一浪费的都已经被使用了

  d、修改最大文件句柄数
    下面是process为1000个时的设置
    # echo 512000 > /proc/sys/fs/file-max

    # sysctl -w fs.file-max=512000

    # echo "fs.file-max=512000" >> /etc/sysctl.conf

9、网络参数
  a、参数描述
    在Linux下，Oracle使用UDP作为缺省协议用于进程之间的交互以及实例间cache fusion buffer传输。
    Oracle 建议缺省的最大发送以及接收缓冲的大小为256KB。接收缓冲区用于TCP/UDP传输时hold住应用程序接收到的数据直到被读取。
    net.core.rmem_default：表示接收套接字缓冲区大小的缺省值（以字节为单位）。
    net.core.rmem_max ：表示接收套接字缓冲区大小的最大值（以字节为单位）
    net.core.wmem_default：表示发送套接字缓冲区大小的缺省值（以字节为单位）。
    net.core.wmem_max：表示发送套接字缓冲区大小的最大值（以字节为单位）。

  b、设置网络参数
    # sysctl -w net.core.rmem_default=262144
    # sysctl -w net.core.wmem_default=262144
    # sysctl -w net.core.rmem_max=4194304
    # sysctl -w net.core.wmem_max=1048576

    永久修改方式，直接修改sysctl.conf文件
    net.core.rmem_default=262144
    net.core.wmem_default=262144
    net.core.rmem_max=4194304
    net.core.wmem_max=1048576

  c、端口号的设置
    Oracle建议的端口号值为1024 65000
    # sysctl -w net.ipv4.ip_local_port_range="1024 65000"

    net.ipv4.ip_local_port_range=1024 65000
    The first number is the first local port allowed for TCP and UDP traffic, and the second number is the last port number.

  d、其他参数
    对于Oracle rac环境，考虑修改修改下列ip内核参数以提高failover性能
    net.ipv4.tcp_keepalive_time
    net.ipv4.tcp_keepalive_intvl
    net.ipv4.tcp_retries2
    net.ipv4.tcp_syn_retries
    see Metalink Note:249213.1 and Note:265194.1.