430 likes | 621 Views
Progress VLDB (Very Large DataBases ). Dan Foreman BravePoint danf@prodb.com. Progress VLDB. Introduction - Dan Foreman. Progress User since 1984 Author of: Progress Database Administration Guide Progress Performance Tuning Guide Progress System Tables (VST, SQL, Meta ) Pro Dump & Load
E N D
Progress VLDB(Very Large DataBases) Dan Foreman BravePoint danf@prodb.com Progress VLDB
Introduction - Dan Foreman • Progress User since 1984 • Author of: • Progress Database Administration Guide • Progress Performance Tuning Guide • Progress System Tables (VST, SQL, Meta) • Pro Dump & Load • ProMonitor • Balanced Benchmark
Audience Survey • Progress Database Version • V8 or earlier • V9 • V10.0* • V10.1* • V10.2A • V10.2B • V11.0 • V11.1
Audience Survey • Single Largest Database Size • > 1 TB • > 500 GB • > 250 GB • > 100 GB • Everyone else can leave the room because: • It will not be interesting to you • Progress VLDB is an “exclusive club”
Agenda • Definition of VLDB • Common Characteristics of VLDB • Growth Rates and Capacity Planning • Top Challenges for VLDB Customers • Wish List • Questions • Conclusion
Definition of VLDB for this Survey • Minimum of 250gb (100gb in 2007) • Single Database (not a set) • Progress only (no Oracle allowed)
Note about Single DB Requirement • Two sites had much larger DBs but split their VLDB into multiple, smaller DBs for performance reasons • Reads per Second beyond a certain point would not improve for a single DB regardless of –spin or –B values • This problem was prior to the extensive latch changes made in V10.1C and LRU parameters in V10.2B06
Progress History – DB Size LImits • V8 • 64gb 1k DB Block Size • 256gb 8k DB Block Size • V9 • Maximum Areas: 1,000 (some are reserved) • Area Size: 1k Blk Size & 256 RPB = 8gb • Area Size: 8k Blk Size & 1 RPB = 16tb • 995 Areas * 16tb = 15,920tb = 16 Exabytes
Progress History – DB Size LImits • V10 • 32,000 Areas in V10.1A • 64-bit DBKEYs in V10.1B • V11 • No changes to my knowledge
Progress Limits - Fragments • A record can potentially be split into two or more pieces called fragments • Each fragment has a ROWID address • V10.1A and earlier • Maximum of 2 billion fragments per Area • V10.1B and later • 9,223,372,036,854,775,807 (9 quintillion)
Biggest Table in a Progress DB? -Record Size (B)- ---Fragments--- Scatter Table Records Size Min Max Mean Count Factor Factor PUB.iegrecord 718932941 1.2T 205 3283 1760 890066584 1.0 2.2 Another Large Table (BMRB) 2009 & 2011 PUB.DataLine 7040294464 690.6G 38 16494 105 -1546825661 1.0 1.0 PUB.DataLine13687002345 1.3T 38 21498 102 806235942 1.0 1.0
Server Demographics • Sun (ML, BMRB) – For some reason I see more Sun Servers outside of the USA • IBM (ADP, Broder, NFCU, Anonymous) • HP/UX > Windows (BP; Aargh!) • BP <> BravePoint or British Petroleum
Server Demographics • RAM • Minimum: 8GB NFCU • Maximum: 360GB BMRB • CPUs (includes Cores) • Minimum: 8 • Maximum: 32 CPUs, 160 Cores
Monitoring Tools • ProMonitor • OE/Fathom Management • Homegrown • ProTop
Virtual & Hardware Partions VMware LPAR or similar
Capacity Planning Tools - CPU • sar • nmon (AIX) • Adrian Performance Monitor (Solaris) • “User’s Scream”
Capacity Planning Tools - RAM • vmstat • nmon (AIX)
Capacity Planning Tools - Disk • iostat • SAN Vendor’s tools
Capacity Planning Tools – DB Growth • Area Status (_areastatus) Reports • dbanalys + Excel • ProMonitor • OE Management
Number of Dedicated DBAs • Lowest: .3 (i.e. 30% of 1 person) • Highest: 3
Backup Method • probkup online to disk • proquiet + SAN Snap Copy • OS backup of Warm Spare DB • Shutdown, SAN Snap Copy, Restart • Customer was uncomfortable with ‘hot’ backup
Database Replication Options • After Imaging • OE/Fathom Replication • Restore from Snap Copy • Note: BravePoint has a customer (medium size bank) that was using SAN Replication but it failed at an inconvenient time
Maintenance Windows • Anonymous • Every 3 months; 12 hours maximum • Broder • 5 minutes every night • BMRB • Twice a month during weekend night Length of the window is not more than 6 hours
Dump/Load • Broder: Pro Dump & Load • Wachovia: 2007 - “Are you kidding?” • Wachovia: 2008 - Pro Dump & Load • BMRB: “Never happened” • Anon: 10 years ago; now do selective table level D&L & idxcompact
Top Challenges • “24 hours is not enough” • Anon: “Never attempt to do high volume reporting or data extracts on Progress”
Non-Technical Challenges “My biggest challenge and also a wish is for PSC to stay in business! With current rate of attrition of Progress market I think that is a valid wish.”
Wish List - 2009 • What program is a Client running (#1) • Online SQL Permissions Changes • Backup by Area • Table Partitioning (AKA Horizontal Partitions) • Online dump/load • Only the first two items have been added by PSC
Wish List - Anonymous “Performance enhancements like bulk operations and partitioning with partition level operations” “Stop the DB from being so fragile; Sessions dying should never bring down a DB”
Wish List - Anonymous “Massive performance enhancements for the basic utilities like idxbuild; Hard to justify 20 hours for an index rebuild when Oracle will do that in 14 minutes on smaller hardware” “True online schema changes (adding columns to existing tables, etc.)”
Wish List – BMRB • Partitioning Options: • It is required for separation of historical and/or rarely used data. • Split based on key and/or index • Possibility for distributed execution of table's partitions on different servers • Online-movement of data between logical partitions
Summary • Progress can handle VLDBs especially with V10.1B and later • A high quality Server & SAN are essential components • I have never personally seen a Terabyte sized Progress DB on Windows; that doesn’t mean they don’t exist…maybe just ashamed
Conclusion • Questions? • Vragen? • Shitsumon?
Conclusion • Thank you for coming! • Thank you for the invitation • Contact Info: • danf@prodb.com • +1 541 908 3437