380 likes | 550 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 V10 Database Administration Jumpstart
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 • V10 Database Administration Jumpstart • Virtual System Tables • Pro Dump & Load • ProMonitor • Balanced Benchmark
Audience Survey • Progress Version • V8 or earlier • V9 • V10.0* • V10.1A • V10.1B • V10.1C • V10.2A
Audience Survey • Single Largest Database Size • > 1 TB • > 500 GB • > 250 GB • > 100 GB • Everyone else can leave the room
Agenda • Definition of VLDB • Common Characteristics of VLDB • Growth Rates and Capacity Planning • Top Challenges for VLDB Customers • Wish List • Questions • Conclusion
My Definition of VLDB • Minimum of 250gb (100gb in 2007) • Single Database (not a set) • I didn’t care about allocated space versus High Water Mark • 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
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
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 PUB.DataLine 7040294464 690.6G 38 16494 105 -1546825661 1.0 1.0
Server Demographics • Sun (3) • Fujitsu (1) – running Solaris 10 • IBM (1) • HP Tru-64 (1) • HP/UX (1) • Linux (1) • Windows (2)
Server Demographics • RAM • Minimum: 32gb • Maximum: 80gb • CPUs • Minimum: 8 • Maximum: 24
Disk Array Demographics • Broder: IBM DS4800; 8tb; 100 disks; RAID 10 • EMC DMX; 1tb; 128 disks • Wachovia: EMC CX700 – unknown ( controlled by corporate administrators) • HP: XP1024 (OEM’d Hitachi) • VTB24: Hitachi USP V
Concurrent Database Connections • AHM: 2999 • Broder: 1163 • Wachovia: 389 • VTB24 2000-2100
Monitoring Tools • ProMonitor • OE/Fathom Management • Homegrown • ProTop
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/Fathom Management
Number of Dedicated DBAs • Lowest: .3 (i.e. 30% of 1 person) • Highest: 2
Backup Method • probkup online • proquiet – Snap Copy • Shutdown, Snap Copy, Restart • Customer was uncomfortable with ‘hot’ backup
Database Replication • After Imaging (2) • OE/Fathom Replication (2) • Restore from Snap Copy (2)
Replication Issues • Getting Fathom Replication to integrate smoothly with Veritas Cluster Server • Long Redo Phase Bug (fixed in V10.1C)
Worst Progress Bug VTB 24 Issue Number: OE00177223 DB crashes with error 645 and cxDoSplit - data too long errors
Maintenance Windows • Wachovia • Weekdays 1700-2300 • Weekends by negotiation • Broder • 5 minutes every night • VTB24 • Twice a month during weekend night. Length of the window is not more than 8 hrs
Dump/Load • Broder: Pro Dump/Load • AHM: Quarterly Binary D&L • Wachovia: “Are you kidding?” (2007) • Wachovia: Pro Dump/Load (2008) • VTB24 “Never happened”
Top Challenges • “24 hours is not enough” • “Physical Redo Phase in AI roll forward. It could take seconds, minutes or several hours.” • Client Performance visibility/tracking • “It's impossible to add indexes to some tables”
Top Challenges • “Knowing more about what a program is doing would be HUGE help. The last X number of DB statements executed would be nice. Like the SQL query plan, but hopefully more comprehensible!”
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 • What program is a Client running (#1) • Backup by Area • Table Partitioning (AKA Horizontal Partitions) • Online SQL Permissions Changes • Online dump/load
Wish List – VTB24 • 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 • Don’t be afraid of Progress VLDBs especially with V10.1B and later
Conclusion • Thank you for coming! • Thank you for the invitation