Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> NT/RAID/Oracle

NT/RAID/Oracle

From: Eric D. Pierce <PierceED_at_csus.edu>
Date: Thu, 09 Aug 2001 16:29:52 -0700
Message-ID: <F001.003667EC.20010809163530@fatcity.com>

cc: Tom Tiplady <TTiplady_at_specialtysystems.com>

(from a user group site?)


[converted from .pdf]

Deploying, Managing, and Administering the Oracle Internet Platform

Paper #302 / Page 1

Oracle8 and RAID Levels: Case Results of OLTP on NT

J. W. Kirby, LSI Logic Storage Systems, Inc.

INTRODUCTION

[....................................................................]

The demand for disk storage continues to grow as more businesses depend on relational database management systems (DBMS) to serve their customers in an online transaction processing (OLTP) mode of operation. It is in the best interests of the providers of RAID subsystems, such as LSI Logic Storage Systems, Inc., to become intimately familiar with how these DBMS are using servers and storage. Not only is the amount of storage continuing to grow, the performance expectations of the storage components continue to rise. To maintain any competitive edge the architects of these intelligent subsystems must understand the tunable parameters provided by the DBMS providers and if and how those parameters may be reconciled for optimum performance to the tunable parameters provided by the RAID system and its associated storage management software.

The purpose of this paper is to describe a project that consisted of evaluating the LSI Logic RAID Storage products when used in a transaction processing type of application. The specific purposes of the project itself were to:

WHAT IS RAID? The reader can refer to http://www.raid-advisory.com

...

DBMS/STORAGE RELATIONSHIPS (OVERVIEW) ...

?

Deploying, Managing, and Administering the Oracle Internet Platform Paper #302 / Page 2

Database application programs require other DBMS functions to support the transaction-processing model (see Gray 1993). The model requires that a transaction log be kept that enables a record of all changes to the database be maintained such that in the event a transaction does not complete (unable to COMMIT), the database can revert back to the state in which it was before the transaction started. Thus, we have the requirement for different types of files in a DBMS such as rollback segments, redo log files as well as the files that represent the actual user tables.

TEST CONFIGURATION The platform chosen for the first phase of this evaluation was Windows NTr and the DBMS was Oracle. The application chosen was the TPC-C benchmark program because it is one of the most quoted benchmark programs used by the industry to assess servers and storage performance.

...

The test configuration consisted of several components:

The TPC-C benchmark was installed on a single NT server after reviewing and editing the scripts provided in the TPC-C Kit.

...

RESOURCES OF INTEREST Five major types of files are read and written by Oracle:

Database Control Files,
User Table Spaces,
System Rollback Segments,
Online Redo Logs, and
Archive Logs.

Due to the fact that the [***]Control Files[***] are accessed less frequently and the fact that they are very, very critical to the integrity of the database, these files were allocated to the same logical unit (LUN) as the Oracle System Software files. And this LUN was always protected with a RAID-5 configuration.

The User Table Space files and Rollback Segments go into a common area (called the database buffer area) within Oracle's memory space and their performance characteristics are similar in many aspects. So, these two types are combined as a single type in this report and will be called the Data Files. The behavior characteristics of this type of file (Data Files) and the other two types of files (Online Redo Log Files and Archive Log Files) were observed and analyzed during the running of TPC-C. Each of these will be described.

?

Deploying, Managing, and Administering the Oracle Internet Platform Paper #302 / Page 3

DATA FILES The Data Files represent the users tables, indexes and for the sake of this discussion rollback segments. These files are the most heavily used among all of the Oracle files in the TPC-C application, once the database has been loaded. These files are accessed randomly with respect to location within the file. The block size of these files is an Oracle parameter (can be set when the database is initially constructed) and is usually set to 2K, 4K or 8K bytes. A size of 4K was chosen for these tests after some preliminary 2K Byte testing revealed that 4K was more consistent with Windows NT blocks. A block size of 8K will be tested in the future. The access pattern Oracle uses to read and write to these data files seemed to align nicely on the beginning byte of the 4K block.

Since these files are heavily used by the TPC-C Benchmark and since the Benchmark is a stress-type of application, these types of files should be spread across as many different disks as possible. In RAID terms, this means a very wide LUN is recommended for these files, regardless of the particular RAID level being used by the LUN. The testing bore this out as can be seen in Appendix 1.

...

These data files are typically not mirrored in many OLTP shops because of the robustness and other data protection schemes, procedures and redundancy features provided and/or recommended by the DBMS providers. These activities include regularly scheduled backup and restore procedures for the data files and the built-in recovery mechanisms provided by the online log files, rollback segments and the archive log files. These files are discussed in the sections that follow. There are other reasons these data files are not usually mirrored. To do so would increase the disk requirements as well as the performance concern associated with having to issue two physical write I/O requests for each write function.

However, with RAID subsystems available to provide data protection at different levels (RAID-0, RAID-1 or RAID-5), the customer should be provided more information to enable him or her to make a more informed decision on

?

Deploying, Managing, and Administering the Oracle Internet Platform Paper #302 / Page 4

whether to provide dynamic data-protection capabilities to the data files. In this case, there remain the two primary factors to consider.

First, the increased space requirements and the associated costs. ...

The second factor is the performance factor associated with these non-RAID-0 configurations.
...

ONLINE REDO LOG FILES The Online Redo Log Files are used by Oracle to record every change made to a table for every transaction. The reason for this is to be able to restore the database to the state in which it was before the transaction began in the event that an instance failure occurs causing the need to restore the database to some previous known and consistent state. This is obviously a lot of activity. However, these series of tests indicated that the I/O rate to these files is generally less than the data files described in the previous section. The characteristics for these Online Redo Log Files are described in this section.

These files are sequentially accessed. These files are written in various block sizes (generally 4K to 20K bytes). The block size is not fixed and varies from one request to the next. The I/O requests to these files do not necessarily align on the block size boundary. These files are 100% Writes except when being read for copying in "Archive Log Mode".

Oracle requires at least two Online Redo Log files. They should always be mirrored due to their criticality to the integrity of the database. Oracle provides a convenient means to do the mirroring within Oracle if chosen by the DBA, but Oracle does not enforce mirroring to be employed. So, the DBA has four choices for mirroring of these Online Redo Log Files: None; to be done by Oracle; to be done by the operating system if supported; or to be done within a RAID subsystem.

The better choice is for the RAID subsystem to always be operating in RAID-1 (mirroring) mode for this very critical type of file. ...

?

Deploying, Managing, and Administering the Oracle Internet Platform Paper #302 / Page 5

...

ARCHIVE LOG FILE ...

THE ORACLE SYSTEM GLOBAL AREA (SGA) MEMORY An effort was made to adjust parameters in the standard Oracle init.ora file such that NT paging of this area would be kept to a minimum. However, some runs did require paging and therefore the paging file was always placed on a non-RAID type of disk. This was done to minimize the introduction of another variable as test results were compared with each other.

I/O OBSERVATIONS ...

?

Deploying, Managing, and Administering the Oracle Internet Platform Paper #302 / Page 6

...

OTHER FINDINGS ...

Oracle provides the DBA with the option of running in a mode called Archive Log Mode. When running in Archive Log Mode, the tpmC rate generally declined about 7% in this series of tests. ... To insure the highest form of data protection, Oracle should always be running in Archive Log Mode.

...

The paging file of Windows/NT should [***]never[***] be on a RAID-5 LUN.
...

?

Deploying, Managing, and Administering the Oracle Internet Platform Paper #302 / Page 7

SUMMARY AND RECOMMENDATIONS Customers who are running OLTP applications, particularly on the Windows/NT platform using Oracle should be able to make a more informed decision regarding RAID file allocations by using the results and conclusions of the experiment described in this report:

REFERENCES Gray 1993 - "Transaction Processing: Concepts and Techniques", Jim Gray and A. Reuter; Morgan Kaufmann
Publishers, 1993.

Massiglia 1997 - "The RAIDbook - A Storage System Handbook", Sixth Edition, Paul Massiglia, Published by The RAID Advisory Board, 1997.

TRADEMARKS SYMplicity is a trademark of LSI Logic Corporation. Oracle is a registered trademark of the Oracle Corporation. Pentium is a registered trademark of Intel Corporation. TPC, tpmC, and TPC-C Benchmark are trademarks or

?

Deploying, Managing, and Administering the Oracle Internet Platform Paper #302 / Page 8

registered trademarks of the Transaction Performance Council. Windows NT is a registered trademark of Microsoft Corporation. All MKS products are trademarks or registered trademarks of Mortice Kern Systems, Inc. All other
brand or product names may be trademarks or registered trademarks of their respective companies.

The information contained in this document is subject to change without notice. LSI Logic does not assume any liability arising out of the applications or use of any product or service described herein. This document neither states nor implies any kind of warranty, including, but not limited to implied warranties of merchantability or fitness for a particular use.

?

Deploying, Managing, and Administering the Oracle Internet Platform Paper #302 / Page 9

Appendix II

...

---end---

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eric D. Pierce
  INET: PierceED_at_csus.edu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Aug 09 2001 - 18:29:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US