Progress VLDB (Very Large DataBases) Dan Foreman Progress VLDB

2 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

Progress VLDB 3 Audience Survey Progress Database Version V8 or earlier V9V10.0*V10.1*V10.2AV10.2BV11.0V11.1

Progress VLDB 4 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

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

Progress VLDB 6 Definition of VLDB for this Survey Minimum of 250gb (100gb in 2007) Single Database (not a set) Progress only (no Oracle allowed)

Progress VLDB 7 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 VLDB 8 Progress History – DB Size LImits V8 64gb1k DB Block Size 256gb8k 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 VLDB 9 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 VLDB 10 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)

Progress VLDB 11 Database Sizes SiteVersionHWMAllocated Anonymous10.2B2.2TB2.2TB** Mortgage Lender (ML) 10.2B04410GB369GB520GB499GB NFCU10.0B05287GB409GB Broder A GB339GB Broder C02554GB560GB Broder C02760GB992GB

Progress VLDB 12 Database Sizes SiteVersionHWMAllocated ADP C034.7TB6.8TB ADP C035.5TB6.0TB

Database Sizes DB-13: Progress VLDB 13 SiteVersionHWMAllocated Big Modern Retail Bank (BMRB) C TB1.48TB BMRB B TB2.8TB BMRB has 7 Total DBs at Headquarters Site; 256G, 296G, 304G, 306G, 461G, 2.6TB, 2.8TB

Progress VLDB 14 Largest Table SiteRecordsSize Same Table? NFCU 85 million 120gbNo ML 178 million 306 million 30gb74gbNoYes Broder 249 million 49gbYes BMRB Billion 691gb Yes >>> BMRB Billion 1.3 TB Yes >>> ADP 719 million 1.2 TB Yes >>>

Biggest Table in a Progress DB? Progress VLDB 15 -Record Size (B)- ---Fragments--- Scatter Table Records Size Min Max Mean Count Factor Factor PUB.iegrecord T Another Large Table (BMRB) 2009 & 2011 PUB.DataLine G PUB.DataLine T

Largest Variable Extent Size - 1TB DB-13: Progress VLDB 16

Progress VLDB 17 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

Progress VLDB 18 Server Demographics RAM Minimum:8GBNFCU Maximum:360GBBMRB CPUs (includes Cores) Minimum:8 Maximum:32 CPUs, 160 Cores

Buffer Cache (-B) Site-B DB Block Size Total Broder k2gb NFCU k1gb ML k16gb Anon k12gb BMRB k10.4gb Progress VLDB 19

Spin Locks (-spin) Site-spinRemarks Broder > > 5000 NFCU50000 ML50000 Recently wanted to increase to * cores or !! Anon20000 BMRB10000 Remember they have 160 Cores Progress VLDB 20

Storage Progress VLDB 21 SiteDetails Broder IBM DS8100 – RAID 10 Anon EMC VMAX - RAID 10 (200+ drives) BMRB Hitachi USP V – RAID 10

Progress VLDB 22 Concurrent Database Connections SiteConnections Broder1100 NFCU820 ML1290 ADP325 – mostly AppServers BMRB2100 Anon674 – AppServers for 4600 humanoid users

Progress VLDB 23 Monitoring Tools ProMonitor OE/Fathom Management HomegrownProTop

Virtual & Hardware Partions VMware LPAR or similar Progress VLDB 24

Progress VLDB 25 Daily Growth Rates Site Approximate Growth per Day ML850MB Broder500MB BMRB 1.5GB to 5GB Anon1GB

Progress VLDB 26 Capacity Planning Tools - CPU sar nmon (AIX) Adrian Performance Monitor (Solaris) Users Scream

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

Progress VLDB 28 Capacity Planning Tools - Disk iostat SAN Vendors tools

Progress VLDB 29 Capacity Planning Tools – DB Growth Area Status (_areastatus) Reports dbanalys + Excel ProMonitor OE Management

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

Progress VLDB 31 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 Customer was uncomfortable with hot backup

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

Progress VLDB 33 Maintenance Windows Anonymous Every 3 months; 12 hours maximumBroder 5 minutes every nightBMRB Twice a month during weekend night Length of the window is not more than 6 hours

Progress VLDB 34 Dump/Load Broder:Pro Dump & Load Wachovia: Are you kidding? Wachovia: Pro Dump & Load BMRB:Never happened Anon:10 years ago; now do selective table level D&L & idxcompact

Progress VLDB 35 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. Progress VLDB 36

Progress VLDB 37 Wish List 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 Progress VLDB 38

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.) Progress VLDB 39

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 Progress VLDB 40

Progress VLDB 41 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 doesnt mean they dont exist…maybe just ashamed

Progress VLDB 42 Conclusion Questions?Vragen?Shitsumon?

Progress VLDB 43 Conclusion Thank you for coming! Thank you for the invitation Contact Info: