New RAID Design for DB ?

From: Stan Novinsky <stan_novinsky_at_jhuapl.edu>
Date: Tue, 09 Mar 1999 13:53:22 -0500
Message-ID: <36E56E22.EAEAA220@jhuapl.edu>

Below is a description of a system we have and one that we will be going to. This was posted earlier as a dcument but may have been unreadable.

I was thinking about reorganizing our DB for maximum performance but am not to sure about RAID 7000.

Any input appreciated.

Thanks

---

We currently have a Database running on an Alpha 8400 with OpenVMS 6.2. We will be upgrading to Oracle8.0.5, Digital UNIX, and upgrading the hardware. We are considering an option to reorganize the DB files so that it will operate at maximum. The following is a description of current and what we want we will have to work with for the next system.

Current

1.

The current DB is defined with 14 RZ28 drive using RAID 1 and RAID 0+1 – a total of 5 mirrored drives. We chose this configuration for the disk recoverability capability because the system MUST be up at all times and down time is a critical issue. We are not using ARCHIVING and at the present time it is not being considered for the new configuration. The current backup strategy is to run daily exports to save critical data. If recovery is necessary, a procedure is executed that will recover the DB to the state it was in at the last daily export.

We may look into using Oracle8 Recovery Manager as a DB a backup strategy.

2.

INIT.ORA parameter of importance:

• DB_BLOCK_BUFFERS = 8k (32000)
• BLOCK_SIZE = 8192
• DB_FILE_MULTIBLOCK_READ_COUNT = 8
• SHARED_POOL_SIZE = 40971520
• LOG_BUFFER = 655360
• LOG_CHECKPOINT_INTERVAL = 17000
• PROCESSES = 100
• DML_LOCKS = 400

3.

Given that the Table Space files and tables (179) + views, packages, etc. are currently defined as:

• 4 Gig Tablespace Datafile on a DISK1 (RAID 0+1) containing all data tables (179). Of importance is that 2 tables are clustered. Table1 can contain up to 2.5 million rows of data. All DB data will be accessed from NT Workstations via C++ generated GUI’s. This data is required to be retrieved at an optimal rate (less than 10 seconds).
  - Cluster for Table1 - Storage of 350m INITIAL, 80 PCTUSED, 5 PCTFREE
  
  • Cluster for Table2 - Storage of 20m INITIAL, 80 PCTUSED, 5 PCTFREE
• 3 Gig Tablespace Datafile on a DISK2 (RAID 0+1) containing the indexes for the tables. Two indexes are clustered (with tables above).
  - 60 Meg Tablespace Datafile for System on DISK1 (RAID 0+1)
  
  • (4) 8 Meg Redo Logs on DISK3 (RAID 1)
  • 800 Meg Tablespace Datafile for Rollback Seg1 on DISK3 (RAID 1)
  • 800 Meg Tablespace Datafile for Rollback Seg2 on DISK3 (RAID 1)
  • 550 Meg Tablespace Datafile for Temporary Segments on DISK1 (RAID 0+1)
  • 25 Meg Tablespace Datafile for Tools on DISK1 (RAID 0+1)
  • Control File 1 on DISK3 (RAID 1)
  • Control File 2 on DISK4 (RAID 1)

Future

The following is a description of the new hardware and how it will be used.

A.

Alpha 8400 Sever running Digital UNIX 4.0E and containing an Oracle8.0.5 DB will consist of:
- (2) 5/625 CPU's

• 18 Storage Works 9.1 GB 10000 RPM Disks
• 2 HSZ70 controllers
• TZ89 DLT tape drive (APL ONLY)
• 3.5 GB memory
• RAID 7000
• TCP/IP
• (2) 100 MB ethernet cards (1 Fiber, 1 Base-T)

B.

The DB on the Alpha 8400 will be populated with data from up to 10 Sub-Systems running on an Alpha 1200 Server Running OpenVMS 7.1. The Sub-Systems will use SQLnet over TCP/IP on a 100 Megabit Per Second ethernet to connect to the Oracle8.0.5 DB.

In addition to the two tables currently clustered, up to 15 additional tables will be clustered as well as the indexes in order to gain additional better performance on retrieval of the data. These tables hold historic data and may contain up to 2 million rows. So, out of the 179 tables, the 15 will be the ones that are accessed the most by the NT’s.

C.

The data in the Oracle8.0.5 DB will be accessed from NT 4.0 Workstations (450 Mhz Dual Processor Pentium II ??) configured with Oracle Client Software. The front end to the database will use Borland C++ Builder generated GUI’s and the Oracle ODBC Driver connected over TCP/IP on a 100 Megabit Per Second ethernet.

Questions

Based on the above current description and what we have to work with:

A.

Should we change or modify the TableSpace (TS) Roll Back, Redo layout since we have 2 more drives that are on a faster controller (i.e. Separate TS’s for Cluster Tables, use more than 1 data file for the DATA TS, use multiple TS’s for data, etc) ?

If so, what could be a possible RAID and TableSpace, Rollback, Redo configuration to support fast access and at the same time recoverability with RAID mirroring ?

B.

What would be considered a reliable backup strategy that is not complicated and would not involve extended down time (1 + hour). Recovery Manager ? Received on Tue Mar 09 1999 - 12:53:22 CST