cc: Tom Tiplady <[EMAIL PROTECTED]> (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: - Understand the disk I/O characteristics related to OLTP applications - Tune existing RAID parameters for optimum performance in these applications - Identify the effect of using various RAID levels for the DBMS files - Identify future features that LSI Logic Storage Systems could provide for optimum performance - Look for opportunities for partnering with Oracler to continually provide high performance products that will take advantage of state-of-art storage subsystems features. 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: - Pentiumr 200 MHz with 128 MB DRAM, (upgraded from 64MB during early testing) - LSI Logic Storage System 6299 Storage Subsystem, Dual 3601 Controllers, 20 drives, total capacity of 40 GB - LSI Logic SYMplicityT Storage Manager (SYMsm 6.0) - Windows NT Enterprise Server Edition, 4.0.3 SP3 - Oracle 8.0.3 (Part of Partner Alliance Package) - Application: TPC-C (under special agreement) 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: - Spread the files (called Data Files in this report) belonging to the tablespaces (user tables and indexes) and the rollback segments over as many disks as possible. For best performance create a RAID-1 LUN for these files if disk space is available. Use the default segment size of 16K bytes to create this LUN if in RAID-1. If disk space is not available for RAID-1, then consider a RAID-5 LUN if performance is not critical to the operation. If RAID-5 LUNs are created for the Data Files, use a segment size of 16K bytes for each. If performance is critical and disks are not available for RAID-1, then use RAID-0 for the Data Files and use a segment size of 16K bytes. Also, employ good backup/restore procedures for the preservation of the database in all cases, but especially if this file is assigned to RAID-0 type of LUNs. - When creating the Data Files LUN in RAID-5 mode on a dual controller system, the following comparison should be made: a) the write cache enabled feature and mirror cache write feature should both be specified, vs. b) neither of these features should be used. Choose the feature that will provide optimum performance in your application. - Do not share the LUNs that are allocated to Data Files with the files allocated to the Online Redo Log Files or the Archive Log File. - Create a RAID-1 LUN for the Online Redo Log Files. The same LUN can be used for the two minimum ORL files if space is not available to create a RAID-1 LUN for each. Except in very high bandwidth installations, one LUN will perform as well as two since this LUN will not typically be the most critical resource relative to performance. A segment size of 64K bytes should be used for this RAID- 1 LUN. There appears to be no reason for Oracle to also perform mirroring of the Online Redo Log Files if the this file is on a RAID- 1 LUN in the RAID subsystem. - Create a RAID-0 LUN or a RAID-1 LUN for the Archive Log File. Performance will be about the same in each case for most applications. The choice depends on the length of time the DBA wants to let the data be at risk before it may be moved to removable media. Obviously, if the length is an extended time, the DBA will feel more comfortable with RAID-1, if space is available. RAID-3 could be used since the file is sequentially written if space is available on a RAID-3 type of LUN. Typically, the file has not been mirrored in most applications since the data is copied to a removable media soon after creation. - Do not use the read-ahead features on any of the files in this application. - Allocate as many buffers (db_block_buffers parameter) in the Oracle SGA as possible to enjoy Oracle's caching efficiencies. - Use a block size value of 4K bytes for Oracle (db_block_buffers=4096). A block size of 8K may also be used but was not tested in this series of test. - Allocate the Windows/NT paging file to a RAID-0 type of LUN. Perhaps this file can be allocated to a RAID-3 LUN if there is a RAID- 3 LUN available and the segment size is set to guarantee RAID-3 type of access (this RAID-3 test has not yet been performed). 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
