Progress VLDB Very Large Data Bases Dan Foreman

Progress VLDB (Very Large Data. Bases) Dan Foreman Brave. Point danf@prodb. com Progress VLDB

Introduction - Dan Foreman Progress User since 1984 Author of: Progress Database Administration Guide Progress Performance Tuning Guide V 10 Database Administration Jumpstart Virtual System Tables Pro Dump & Load Pro. Monitor Balanced Benchmark 2 Progress VLDB

Audience Survey Progress Version V 8 or earlier V 9 V 10. 0* V 10. 1 A V 10. 1 B V 10. 1 C V 10. 2 A 3 Progress VLDB

Audience Survey Single Largest Database Size > 1 TB > 500 GB > 250 GB > 100 GB Everyone else can leave the room 4 Progress VLDB

Agenda Definition of VLDB Common Characteristics of VLDB Growth Rates and Capacity Planning Top Challenges for VLDB Customers Wish List Questions Conclusion 5 Progress VLDB

My Definition of VLDB Minimum of 250 gb (100 gb in 2007) Single Database (not a set) I didn’t care about allocated space versus High Water Mark Progress only (no Oracle allowed) 6 Progress VLDB

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 V 10. 1 C 7 Progress VLDB

Progress History – DB Size LImits V 8 64 gb 256 gb 1 k DB Block Size 8 k DB Block Size V 9 Maximum Areas: 1, 000 (some are reserved) Area Size: 1 k Blk Size & 256 RPB = 8 gb Area Size: 8 k Blk Size & 1 RPB = 16 tb 995 Areas * 16 tb = 15, 920 tb = 16 exabytes 8 Progress VLDB

Progress History – DB Size LImits V 10 32, 000 Areas in V 10. 1 A 64 -bit DBKEYs in V 10. 1 B 9 Progress VLDB

Progress Limits - Fragments A record can potentially be split into two or more pieces called fragments Each fragment has a ROWID address V 10. 1 A and earlier Maximum of 2 billion fragments per Area V 10. 1 B and later 9, 223, 372, 036, 854, 775, 807 (9 quintillion) 10 Progress VLDB

Database Sizes Site HWM Allocated Anonymous 9. 1 E 1. 7 TB 2. 0 TB AHM 9. 1 E 0412 743 GB 941 GB Wachovia 9. 1 E 0409 359 GB 400 GB Broder 2007 10. 1 A 0205 290 GB 339 GB Broder 2009 10. 1 C 02 554 GB 560 GB 10. 1 C 0306 1. 48 TB VTB 24 Version 11 Progress VLDB

Largest Table Site Records Size Same Table? Wachovia 202 million 39 gb yes AHM 431 million 67 gb no Broder VTB 24 ADP 125 million 53 gb Yes 7. 04 billion 691 gb Yes 719 million 1 tb Yes See next Slide 12 Progress VLDB

Biggest Table in a Progress DB? Table PUB. iegrecord Records 718932941 Size 1. 2 T -Record Size (B)- ---Fragments--- Scatter Min Max Mean Count Factor 205 3283 1760 890066584 1. 0 2. 2 Another Large Table PUB. Data. Line 7040294464 690. 6 G 38 16494 105 -1546825661 1. 0 13 Progress VLDB

Server Demographics Sun (3) Fujitsu (1) – running Solaris 10 IBM (1) HP Tru-64 (1) HP/UX (1) Linux (1) Windows (2) 14 Progress VLDB

Server Demographics RAM Minimum: 32 gb Maximum: 80 gb CPUs Minimum: 8 Maximum: 24 15 Progress VLDB

Disk Array Demographics Broder: IBM DS 4800; 8 tb; 100 disks; RAID 10 EMC DMX; 1 tb; 128 disks Wachovia: EMC CX 700 – unknown ( controlled by corporate administrators) HP: XP 1024 (OEM’d Hitachi) VTB 24: Hitachi USP V 16 Progress VLDB

Concurrent Database Connections AHM: Broder: Wachovia: VTB 24 2999 1163 389 2000 -2100 17 Progress VLDB

Monitoring Tools Pro. Monitor OE/Fathom Management Homegrown Pro. Top 18 Progress VLDB

Daily Growth Rates Site Approximate Growth per Day AHM 600 mb Wachovia 250 mb Broder 500 mb VTB 24 1. 5 GB to 5 GB 19 Progress VLDB

Capacity Planning Tools - CPU sar nmon (AIX) Adrian Performance Monitor (Solaris) “User’s Scream” 20 Progress VLDB

Capacity Planning Tools - RAM vmstat nmon (AIX) 21 Progress VLDB

Capacity Planning Tools - Disk iostat SAN Vendor’s tools 22 Progress VLDB

Capacity Planning Tools – DB Growth Area Status (_areastatus) Reports dbanalys + Excel Pro. Monitor OE/Fathom Management 23 Progress VLDB

Number of Dedicated DBAs Lowest: Highest: . 3 (i. e. 30% of 1 person) 2 24 Progress VLDB

Backup Method probkup online proquiet – Snap Copy Shutdown, Snap Copy, Restart Customer was uncomfortable with ‘hot’ backup 25 Progress VLDB

Database Replication After Imaging (2) OE/Fathom Replication (2) Restore from Snap Copy (2) 26 Progress VLDB

Replication Issues Getting Fathom Replication to integrate smoothly with Veritas Cluster Server Long Redo Phase Bug (fixed in V 10. 1 C) 27 Progress VLDB

Worst Progress Bug VTB 24 Issue Number: OE 00177223 DB crashes with error 645 and cx. Do. Split - data too long errors 28 Progress VLDB

Maintenance Windows Wachovia Weekdays 1700 -2300 Weekends by negotiation Broder 5 minutes every night VTB 24 Twice a month during weekend night. Length of the window is not more than 8 hrs 29 Progress VLDB

Dump/Load Broder: AHM: Wachovia: VTB 24 Pro Dump/Load Quarterly Binary D&L “Are you kidding? ” (2007) Pro Dump/Load (2008) “Never happened” 30 Progress VLDB

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” 31 Progress VLDB

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!” 32 Progress VLDB

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. ” 33 Progress VLDB

Wish List What program is a Client running (#1) Backup by Area Table Partitioning (AKA Horizontal Partitions) Online SQL Permissions Changes Online dump/load 34 Progress VLDB

Wish List – VTB 24 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 35 Progress VLDB

Summary Don’t be afraid of Progress VLDBs especially with V 10. 1 B and later 36 Progress VLDB

Vragen? 37 Progress VLDB

Conclusion Thank you for coming! Thank you for the invitation 38 Progress VLDB