Oracle® Solaris 11.2 Tunable Parameters Reference Manual

Exit Print View

 
Updated: December 2014
 
 

Tuning ZFS for an Oracle Database

ZFS is recommended for any Oracle database version in single instance mode. ZFS can be used with an Oracle RAC database when it is available as a NFS-shared file system.

Review the following recommendations below for tuning ZFS for an Oracle database:

  • Verify that you are running the latest Oracle Solaris release

    Start with the latest Oracle Solaris 10 or Oracle Solaris 11 release, with the Solaris 10 9/10 release as a minimum starting point.

  • Create LUNs for your ZFS storage pools, if needed

    Use your storage array tools to create LUNs that will be presented to the ZFS storage pool. Or, consider using whole disks for your mirrored ZFS storage pools. For more information, see Chapter 3, Managing Oracle Solaris ZFS Storage Pools, in Managing ZFS File Systems in Oracle Solaris 11.2 .

  • Create a storage pool for data files for tables, index, undo and temp data

    Consider creating a mirrored storage pool to provide a higher level of data redundancy. For example:

    # zpool status dbpool
pool: dbpool
state: ONLINE
scan: none requested
config:

NAME                     STATE     READ WRITE CKSUM
dbpool                   ONLINE       0     0     0
mirror-0                 ONLINE       0     0     0
c0t5000C500335F95E3d0    ONLINE       0     0     0
c0t5000C500335F907Fd0    ONLINE       0     0     0
mirror-1                 ONLINE       0     0     0
c0t5000C500335BD117d0    ONLINE       0     0     0
c0t5000C500335DC60Fd0    ONLINE       0     0     0

errors: No known data errors

    For databases with high redo log activity, such as a typical OLTP database with many commits, use a separate LUN for a separate log device.

  • Create a storage pool for the archivelog

    If available, a system's internal disk can handle this type of load. The archivelog file system can also be a file system in the dbpool.

    # zpool create archivepool c0t5000C500335E106Bd0

  • Create the ZFS file systems and set the specific file system properties by using the following guidelines

    Create separate file systems for redo, archive, undo, and temp database components using the following recordsize:

    • Oracle Solaris 11 and earlier releases - 128K

    • Oracle Solaris 11.1 and later releases - 1M

    The general rule is to set the file system recordsize = db_block_size for the file systems that contains Oracle data files. For table data and index components, create a file system with an 8 KB record size. Also consider providing metadata caching hints for your database file systems by using the primarycache property. For more information about ZFS file system properties, see Introducing ZFS Properties in Managing ZFS File Systems in Oracle Solaris 11.2 .

    • Create file systems for the table data files and index data files with an 8 KB recordsize. Use the default value for primarycache.

      # zfs create -o recordsize=8k -o mountpoint=/my_db_path/index dbpool/index
# zfs set logbias=throughput dbpool/index
# zfs get primarycache,recordsize,logbias dbpool/index
NAME            PROPERTY      VALUE         SOURCE
dbpool/index    primarycache  all           default
dbpool/index    recordsize    8K            local
dbpool/index    logbias       throughput    local

    • Create file systems for temporary and undo table spaces

      For Oracle Solaris 11 and earlier releases, use the default recordsize and primarycache values.

      # zfs create -o mountpoint=/my_db_path/temp dbpool/temp
# zfs set logbias=throughput dbpool/temp
# zfs create -o mountpoint=/my_db_path/undo dbpool/undo
# zfs set logbias=throughput dbpool/undo

      For Oracle Solaris 11.1 and later releases, use the following recordsize and default primarycache values. 

      # zfs create -o recordsize=1m -o mountpoint=/my_db_path/temp dbpool/temp
# zfs set logbias=throughput dbpool/temp
# zfs create -o recordsize=1m -o mountpoint=/my_db_path/undo dbpool/undo
# zfs set logbias=throughput dbpool/undo

    • Create a storage pool for redo logs with a separate log device. For databases with high redo log activity, such as a typical OLTP database with many commits, use a separate log device LUN.

      Partition the disk into two slices, a small slice, s0, in the 64 to 150 MB range, for the separate log device. The s1 slice contains the remaining disk space for the redo log.

      # zpool create redopool c0t50015179594B6F11d0s1 log c0t50015179594B6F11d0s0
# zpool status redopool
pool: redopool
state: ONLINE
scan: none requested
config:

NAME                       STATE     READ WRITE CKSUM
redopool                   ONLINE       0     0     0
c0t50015179594B6F11d0s1    ONLINE       0     0     0
logs
c0t50015179594B6F11d0s0    ONLINE       0     0     0

errors: No known data errors

    • Create a file system for redo logs in the redo pool.

      For Oracle Solaris 11 and earlier releases, use the default file system values for recordsize and primarycache.

      # zfs create -o mountpoint=/my_db_path/redo redopool/redo
# zfs set logbias=latency redopool/redo

      For Solaris 11.1 and later releases, use the following recordsize and default primarycache values.

      # zfs create -o recordsize=1m -o mountpoint=/my_db_path/redo redopool/redo
# zfs set logbias=latency redopool/redo

    • Create a file system for archivelog files in the archive pool.

      For Oracle Solaris 11 and earlier releases, enable compression using the default value for recordsize and set primarycache to metadata

      # zfs create -o compression=on -o primarycache=metadata -o mountpoint=
/my_db_admin_path/archive archivepool/archive
# zfs get primarycache,recordsize,compressratio,compression,available,
used,quota archivepool/archive
NAME                 PROPERTY       VALUE         SOURCE
archivepool/archive  primarycache   metadata      local
archivepool/archive  recordsize     128K          default
archivepool/archive  compressratio  1.32x         -
archivepool/archive  compression    on            local
archivepool/archive  available      40.0G         -
archivepool/archive  used           10.0G         -
archivepool/archive  quota          50G           local

      For Solaris 11.1 and later releases - Enable compression, set primarycache to metadata and use the following recordsize value:

      # zfs create -o compression=on –o recordsize=1M \
        -o mountpoint=/my_db_admin_path/archive archivepool/archive
# zfs get primarycache,recordsize,compressratio,compression,\
        available,used,quota archivepool/archive
NAME                 PROPERTY       VALUE        SOURCE
archivepool/archive  primarycache   all          local
archivepool/archive  recordsize     1M           local
archivepool/archive  compressratio  1.32x        -
archivepool/archive  compression    on           local
archivepool/archive  available      40.0G        -
archivepool/archive  used           10.0G        - 
archivepool/archive  quota          50G          local

  • Consider setting quotas so that your database file systems have sufficient disk space to operate and taking snapshots of your database file systems. In addition, set a reservation on a dummy file system to reserve 10-20% of pool space to maintain pool performance.

    # zfs set reservation=20gb dbpool/freespace

  • For additional information about tuning storage arrays and memory resources, see the white paper at http://www.oracle.com/technetwork/server-storage/solaris/config-solaris-zfs-wp-167894.pdf.

  • Additional Oracle database configuration recommendations

Copyright © 2000, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices
Previous
Next