1 / 32

Storage Areas Fact & Fiction

Storage Areas Fact & Fiction. Dan Foreman BravePoint, Inc. Email: danf@prodb.com. 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.

keaira
Download Presentation

Storage Areas Fact & Fiction

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Storage AreasFact & Fiction Dan Foreman BravePoint, Inc. Email: danf@prodb.com

  2. 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

  3. Introduction • 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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 Extent Extent Extent Extent Extent Extent Extent Extent Extent Extent Disk Storage - Database Extents

  12. 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)

  13. 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

  14. 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

  15. Storage Areas (.df File)

  16. 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?

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

  24. 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

  25. 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>

  26. 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

  27. 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

  28. 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

  29. 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

  30. 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

  31. For More Information • Dan Foreman’s V9 Database Administration Jumpstart publication • Dan Foreman Consulting • danf@prodb.com

More Related