Storage areas fact fiction
1 / 32

Storage Areas Fact & Fiction - PowerPoint PPT Presentation

  • Uploaded on

Storage Areas Fact & Fiction. Dan Foreman BravePoint, Inc. Email: [email protected] Introduction. Dan Foreman - Instructor, Consultant Progress User since 1984 (V2.1) Presenter at USA Progress Users Conference 1990-1998 2002-2004 Presenter at QAD User Conference 1998-2002. Introduction.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Storage Areas Fact & Fiction' - keaira

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Storage areas fact fiction
Storage AreasFact & Fiction

Dan Foreman

BravePoint, Inc.

Email: [email protected]


  • Dan Foreman - Instructor, Consultant

  • Progress User since 1984 (V2.1)

  • Presenter at USA Progress Users Conference

    • 1990-1998

    • 2002-2004

  • Presenter at QAD User Conference

    • 1998-2002


  • Author of:

    • Progress Performance Tuning Guide

    • Progress Database Administration Guide

    • Virtual System Tables Guide

    • V9 Database Administration JumpStart

    • ProMonitor - Database monitoring tool

    • Pro D&L - Dump & Load with 1 hour downtime

    • Progress DBA Resource Kit

    • STGEN - V8 to V9 Conversion Tool

    • All Books are Now Available Online

Bravepoint inc
BravePoint, Inc.

  • Formerly United Systems, Inc (Feb 2001)

  • Progress Services Provider

    • Training (Progress, QAD, Linux, and more)

    • Performance Tuning & DB Administration

    • Internet, Java, & WebSpeed Interfaces

    • Custom Programming

  • Progress Experience

    • 125+ Employees

    • 3 members have used Progress since 1984

    • Our most junior instructor has 8 years experience

Who are you
Who Are You?

  • Are you currently using the V9 Database in Production?

    • V9.0*, V9.1A, V9.1B, V9.1C, V9.1D

    • Service Pack Level?

  • If not what version? V4, V5, V6, V7, V8

Storage areas what are they
Storage Areas - What are They

  • A Storage Area is a storage location in a V9 database

  • A Database can have one or more Areas

  • Also similar to Oracle/DB2 table spaces

  • An Area can contain database Objects (i.e. tables and indexes)

  • An Area can consist of one or more Extents on disk

  • Each Area grows independently

Storage areas benefits
Storage Areas - Benefits

  • Records per Block (1 to 256) can be set by Area which means more efficient use of space in the Database (more details later)

  • Multi-database transactions may be subject to logical data corruption if 2 Phase Commit is not enabled; Storage Areas make it easier to keep all data in one DB (no more need for “split schema” which is an obsolete technique)

  • It may be possible to improve performance by using Areas to distribute tables with heavy I/O volume over multiple disks

Storage areas benefits1
Storage Areas - Benefits

  • Areas make it possible to keep static (infrequently changed) and dynamic (heavy create and delete activity and subject to scattering) data separated

  • Doing a dump/load on a particular table (or tables) now has the potential to be valuable; there is NO value to dumping & loading a table in V8

Storage area disadvantages
Storage Area - Disadvantages

  • Areas make DB administration more complicated

    • There are NO Single Volume Databases in V9

    • Even the DLC empty DBs are Multi-Volume

    • probkup, procopy are Area ‘Sensitive’

  • Multiple High Water Marks (HWM) to Monitor (since there can be a variable extent for each Area) - there are 36 Areas in the MFG/PRO eB Database Structure File

Storage area disadvantages1
Storage Area - Disadvantages

  • A V9 DB will probably be larger than V8 because of the additional space allocated for each Area for new growth

  • It is not possible to add new Extents or Areas On-Line

  • promon does not have Area HWM information

Storage areas
Storage Areas

Index E

Table E

Index C-1

Index D-1

Index E-1

Index A-1

Index B-1

Table D

Table B

Table C

Table A

Area 6

Area 7

Area 8

Area 51











Disk Storage - Database Extents

Storage areas1
Storage Areas

  • Control Area (.db File)

    • In V8 (and earlier) contained Extent path names

    • In V9 contains _Area, _AreaExtent Tables

    • These tables can be queried with the 4GL

  • Primary Recovery Area (V8 BI Extents)

    • 2GB BI Size Limit is Gone! (it’s just much higher)

  • After-Image Areas (V8 AI Extents)

  • User Defined Data Areas (no V8 equivalent)

  • Schema Area (No V8 equivalent)

Storage areas2
Storage Areas

  • Area 6 is the Schema Area. It contains:

    • The Database Master block

    • Object to Area mapping information

    • Sequence value block

    • Schema tables, indexes, and SQL views

  • It is the “default” Area

  • May contain user data but not recommended

  • If no other Area is created, Area 6 will contain all data (as is the case after conversion from V8 with proutil conv89)

  • V9.1D - mvsch option on proutil

Storage areas3
Storage Areas

  • The Physical Area layout is described in the Structure (.st) file

    • Areas can have many extents

    • An Extent is a separate disk file just like V8

    • Only the last extent of an Area may be variable size

    • Each Area grows independently

  • Some options use the .st file to create and update DBs (prostrct, prorest, procopy)

  • Area contents (what tables goes to what Area) are defined in the Dictionary and/or Data Definitions (.df) - example on next page

Storage areas4
Storage Areas

  • A Table or Index Object can be placed in any data Area

  • Data Areas can contain indexes and/or tables

  • An Area can be dedicated to one object

  • Objects cannot span Areas

  • Simpler is better - do not go to extremes

  • My recommendation: 19 data Areas maximum; Why?

Records per block
Records per Block

  • Records per Block may be different in each Area:

    • Values: 1, 2, 4, 8, 16, 32, 64, 128, or 256

  • Benefits

    • Improved storage utilization

    • Allows use of large block size (8k) for better performance

  • Disadvantage

    • Higher RPB = Lower Maximum Area Size

Mfg pro storage areas
Mfg/Pro & Storage Areas

  • QAD did a reasonable job of dividing into their database into Storage Areas with these exceptions:

    • The “TRANSACTION” Area (Area #7) has too much ‘stuff’ in it - needs further refinement

    • Records per Block Values are not optimal

Preparing for storage areas
Preparing for Storage Areas

  • V8.3 Table and Index I/O Monitoring

    • -tablebase & -tablelimit

    • -indexbase & -indexlimit

    • _tablestat & _indexstat VSTs

  • Identify Static vs Dynamic vs Historical Data

  • Identify Large Tables

  • Identify tables that are frequently and easily scattered

  • Identify unused indexes

Options to convert to v9
Options to Convert to V9

  • proutil conv89

    • Usually takes less than 5 minutes to run

    • All Data & Indexes are stored in the Schema Area

    • Will NOT take advantage of Storage Areas

    • Moving Tables and Indexes still leaves a Large Schema Area (V9.1D proutil/mvsch)

  • Dump/Load

    • Will take ……longer than conv89

    • Can take full advantage of Areas through easy modifications to the .df file

V9 structure st files
V9 Structure (.st) Files

# Schema Area

# Records/Block:64

# Located in the /db directory

d “schema area”,64 /db

# Data Area

# Area Name: data1 Area Number: 10

# Records/Block:128

d “data1”:10,128 /db1/data_10.d1 f 100000

d “data1”:10,128 /db2/data_10.d2 f 100000

d “data1”:10,128 /db3/data_10.d3

# Index Area

#Area Name:index1Area Number: 20

#Records/Block:Not given because intended for index blocks

d “index1”:20 /db4/ f 100000

d “index1”:20 /db4/

# Variable size BI extent

b /bi/prod.b1

# After Image Extents

a /ai/data.a1 f 100000

a /ai/data.a2 f 100000

a /ai/data.a3 f 100000

V9 utilities table move
V9 Utilities - Table Move

  • proutil <db> -C tablemove <table> <area>

  • Rebuilds the Indexes during the move since Recids are no longer unique to a DB (but are unique to an Area)

  • Off-line as well as on-line

  • On-line: Will Exclusive Lock the entire Table for the duration of the move

  • One Move = One Trx = Bigger BI/AI Files

  • Monitor the process with the _UserStatus VST; the Table Lock can be monitored with promon or the _Lock VST

V9 utilities index move
V9 Utilities - Index Move

  • proutil <db> -C indexmove <Index def> <area>

  • Index def: <owner>.<table>.<index>

  • Run off-line or on-line

  • On-line:

    • Only read operations will be allowed

    • The table will be SHARE locked

  • Benefits: flexibility and availability

  • Monitor the process with the _UserStatus VST

V9 utilities index compact
V9 Utilities - Index Compact

  • proutil <db> -C indexcompact<owner>.<tablename>.<indexname>

  • Combines partially filled adjacent index blocks

  • Runs off-line or on-line

  • Index Fix + Index Compact = Index Rebuild

  • Benefits:

    • Performance - fewer disk i/o’s

    • Reduces index size

    • Availability - index not locked

  • DB Corrupting Bug until V9.1D SP06

Enhancements to utilities
Enhancements to Utilities

  • Most utilities are now ‘Area Aware’

  • Example: proutil idxbuild will only scan the blocks in the Area where the table exists

  • proutil dbanalys/tabanalys/ixanalys can now report individual Areas (this is undocumented)

    • proutil <db> -C dbanalys <area name>

Enhancements to utilities1
Enhancements to Utilities

  • procopy & probkup

    • Target Area Numbers must Match

    • Area Names don’t need to Match

    • Target Records per Block must Match

    • prorest - the # of Areas must match

    • Uses .st File (if one exists) in Target Directory

Enhancements to utilities2
Enhancements to Utilities

  • proutil truncate area

    • If no Area is specified, all Areas that have no Storage Objects have the HWM moved to ‘ground zero’ (AKA empty)

    • If an Area name is specified, the data is in the Area is removed (after confirmation)

    • In each case there is no impact on the physical extents

New virtual system tables
New Virtual System Tables

  • VSTs are Automatically Enabled in all V9 Databases

  • New VSTs

    • _AreaStatus HWM Info

    • _MyConnection Info about my connection

    • _StatBase Reset table/index I/O limits

    • _UserStatus Monitor some online utilities

V9 size limits
V9 Size Limits

  • Database size: BIG! (mb > gb > tb > eb)

  • Primary Recovery Area (BI) size: 16-32TB

  • Maximum Number of Areas: 1,000

  • Data Area size: Varies

    • 16TB (1 row per block, 8k block size)

    • 8GB (256 rows per block, 1k block size)

  • Data Area block size: 8,192 bytes

  • Extent size: Still 2 gigabytes until V9.1C

    • proutil <db> -C EnableLargeFiles

V9 size limits1
V9 Size Limits

  • Record size: 32,000 bytes

  • Records per Area/Table: 2,147,483,647

    • Depends on Block Size & RPB Value

  • Number of Records per block: 256

  • Number of Indexes: 32,000

  • Number Sequences: 2,000 (with 8k blocks)

    • Increased from 100

  • Number of concurrent users: 10,000

  • Number of concurrent transactions: 10,000

For more information
For More Information

  • Dan Foreman’s V9 Database Administration Jumpstart publication

  • Dan Foreman Consulting