DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Oracle 10g, Solaris 10, db_writers, and zfs

  1. #1
    Join Date
    Apr 2008
    Location
    Pennsylvania
    Posts
    15

    Oracle 10g, Solaris 10, db_writers, and zfs

    We recently upgraded from Solaris 8 to Solaris 10, Oracle from 9.2.0.5 to 10.2.0.3, and migrated to a totally new storage array, a Sun 2540 configured with a zfs file system.

    I read a white paper by a Quest Software employee (it was a few years old) that stated the following:

    "Asynchronous I-O works as advertised only on raw devices and does not add any significant performance benefit when implemented on advanced filesystems such as jfs, ufs, vxfs, or xfs."

    Is that also true for zfs? Should we be setting db_writer_processes and dbwr_io_slaves higher instead of specifying disk_asynch_io? Our Unix Ops manager thinks that setting disk_asynch_io = true is not buying us anything. We have a 2.8 TB data warehouse, and it must be up with few exceptions. This parameter is not dynamic, so it would require a bounce to change it.

    I have not been able to find recommendations about setting Oracle disk_io parameters with the zfs file system anywhere.

    Thanks.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What's is xfs? It's new to me. So I do not comment on it.

    Did you do some basic bench mark test on the new file system?

  3. #3
    Join Date
    Apr 2008
    Location
    Pennsylvania
    Posts
    15

    xfs

    XFS is a high-performance journaling file system created by Silicon Graphics for their IRIX operating system.

    ZFS (Zettabyte File System) is a distributed file system from Sun that was added to OpenSolaris in 2005 and Solaris 10 in 2006.

    That is all beside the point. I was quoting someone else who stated that Oracle's disk_asynch_io was redundant if it is being deployed on on xfs or any of the other file systems he mentioned. Sun's zfs is one of the newest of that breed.

    The unix operations group did benchmarks for read and write I/O on the 2540 array, and they were happy with the performance. But they were doing that without Oracle in the picture. When we migrated the Oracle database to the 2540, the performance on the array was satisfactory, compared to the old array it was on.

    The question remains, are we better off using multiple db_writers/db_writer_io_slaves instead of disk_asynch_io?

  4. #4
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Should we be setting db_writer_processes and dbwr_io_slaves higher instead of specifying disk_asynch_io
    You should only use dbwr_io_slaves when your O/S doesnt support async I/O. These slaves will then mimic the async I/O behaviour.

    You can have multiple db_writer_processes in conjunction with having disk_asynch_io=true if you have a write intensive system and a large buffer cache. Each writer then operates on its own bit of the buffer cache

    Oracle's recommendation is to leave async_io set to true so I'd do some serious benchmarking before thinking of just turning it off.

  5. #5
    Join Date
    Apr 2008
    Location
    Pennsylvania
    Posts
    15

    Db_writer_processes value

    In previous versions, the recommended setting for db_writers was 1 if you were setting db_asynch_io=true. One expert observed the following:

    "When asynchronous I-O is used (set to TRUE), DB_WRITERS or DB_WRITER_PROCESSES or DBWR_IO_SLAVES should be set to 1, as the combination of ASYNC_WRITE = TRUE and launching multiple DBWR processes, has caused datafile corruption in prior releases of Oracle. Further, the combined usage of asynchronous I-O and multiple DBWR processes or I-O slaves provides no measurable benefit. Normally, either one of the features are used, not both."

    That was, I believe, as recent as V. 8.1.n

    At some point, I recall Oracle changing that and recommending not setting a value for db_writers in your pfile or spfile with asynch_io enabled, that Oracle would do that automatically. I currently have asynch_io enabled, and Oracle has generated a default value of 2 for db_writer_processes.

    I originally posed the following question to our Unix Ops manager:

    "Is it true that having disk_asynch_io enabled in Oracle does not buy us anything? The alternative is to manually configure multiple db_writers (Oracle default = 2) and db writer io slaves. That would create "n" Oracle background processes that would handle all I/O."

    His answer:

    "We're not in Kansas anymore. With the advent of hardware raid, disk array, virtual file systems. It's not so easy to tell what's a disk and what's not a disk. I would guess it's also true that the physical disk is so far removed from the operating system, that the OS really has no way to tell whether or not the data has been written to the disk. Likewise, I would also conclude that having a asynch_io setting in oracle would be meaningless because all i/o would be asynchronous." (In that last part, he is referring to the array manager cache which enables asynchronous operations).

    So that is why he thinks the Oracle disk_asynch_io=true setting is redundant.

    However, my inclination is that it cannot hurt us to enable asynch_io, but MAY hurt us if we do not. My only remaining question is how I can determine the best value for db_writer processes. My max_sga_size is 1.3TB and sga_target is 1.25TB, and I for now I am letting Oracle manage the SGA completely (have I gone completely mad?), other than the keep pool sizes, which are not very large. Right now the database is reporting a buffer cache of 672M and shared pool of 448M. It is a 2.8TB data warehouse, and it is only write-intensive during overnite and month-end loads, which are extremely intense. We have 8 cpus on the box.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can use truss command to figure out what type of system/lib calls made to read/write on disks.

    I do not know much about zfs. Since you have already moved the database on the new FS, it is worth to figure out the system/lib calls.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width