Large Database Plans

SAM Documents
Large Database Testing Plans

In an effort to predict and plan for behavior of a large database, ods will be creating a large database for test purposes. This database will be appoximately 300 gigabytes, and will reside on d0ora1's raid array. This large project will develop in stages and take several months to complete.

Database

Create a new large instance or use the existing d08i instance. To avoid disruption to the sam development team, I do not believe we should use d0ofdev1.

This instance will be used only for the testing of aspects of a large database. It will be transient - and can be expected to have to be remade frequently during initial tests. It will not be backed up. No data should be relied on. It will have the SAM schema - not other schema - defined. There are times when it will require dedicated use of the MACHINE - ie d0ora1. These will be planned and coordinated with the SAM team, but will require downtime of the sam develepment database services.

This instance will have its use and downtime scheduled. Extra ordinary amounts of downtime will be expected to test backup and recovery procedures.

Requirements

Create database tables that would mimick the size expected in production.It is expected the SAM production database will be 300G in about 1.5 years.

Stripe and utilize as much disk space as possible on d0ora1, without compromising d0ofdev1.

Create and/or use current loading scripts to fill the database.

The activities on the database will be recorded in an effort to produce usage patterns. OEM and plsql will be used to monitor database activity. OEM will also be used for some system monitoring. One or more, as of yet undefined tools may also be used for additional system monitoring.
Data will be entered as close to production as possible, including the use of DbServer.

Goals

Test partitioning

Try to decide which tables need partitioning

Try to find how they should be paritioned if possible

Test row migration with partitioning, specifically to see if it is a slow process.

Develop a script and test rolling off partitions, splitting partitions.

Test backup and recovery

Backup of large databases

Recovery of large tablespaces.

Legato and large databases.

Use of read only mode on tablespaces for backup and recovery.
Tuning for backup and recovery?

Establish a backup strategy for a large database, including, table partitions, incremental backups, etc.

Tuning and Performance

Monitor db for tuning problems during loading times, redos, checkpoints, etc.

Database tuning at the init.ora level

System tuning.

Develop expertise in using OEM for tuning, if possible. This assumes the new version of OEM will be available.

Bitmap testing and the Events Table

Create a large Events table, could start with the existing 50 million row table as base.

Try to establish a usage pattern for events.

Create a bitmap index(s) for the trigger levels. Track space usage.

Test bitmap indexing in both plsql and java for response time, possibly decide on a strategy.

Redundancy & Failover

Phase One Plan

Phase One will operate under 2 assumptions. 1.The additional 300G of disk drives are not currently available and 2.As far as testing backup and recovery with Legato, D.Bonham has done already completed as much can be done until the extra drives and the mammoth tape robot shows up.
D.Bonham will be looking into the feasiblity of increasing the database blocksize to 16k by recreating the control files. Most information on large databases suggests our current 8k is the minimal configuration.
D.Bonham will review and adjust any other init.ora parameters that need adjusting to accomodate a large database. The 24x7 book will be a reference for this. We will also be looking at anything available on the web, ie the Winter Group of Boston, to find additional information.
D.Bonham will install S.White's new version of DbServer that inserts events through varray processing. This is the DbServer the benchmarks will run with.
Meta data will be massively inserted using existing scripts. New scripts will be written as needed. Enough data will be inserted to fill the available space.
OEM along with some other, yet undefined, system utilities will be used to monitor the tests. A utility must be found that recognises the raid drives, as OEM does not.
The first test will be loading a partitioned by run_id event table, without any bitmap indices. The default primary and foreign key indices will be in place. We are trying to establish which column is useful to partition on, run_id being our first guess. We also want to time the inserts.
The second test will be loading the same events, only adding at least 1 bitmap index to each level1, level2 and level3 columns.