1 / 43

Progress VLDB (Very Large DataBases )

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

lorie
Download Presentation

Progress VLDB (Very Large DataBases )

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. Progress VLDB(Very Large DataBases) Dan Foreman BravePoint danf@prodb.com 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

  3. Audience Survey • Progress Database Version • V8 or earlier • V9 • V10.0* • V10.1* • V10.2A • V10.2B • V11.0 • V11.1

  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”

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

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

  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

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

  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

  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)

  11. Database Sizes

  12. Database Sizes

  13. Database Sizes

  14. Largest Table

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

  16. Largest Variable Extent Size - 1TB

  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

  18. Server Demographics • RAM • Minimum: 8GB NFCU • Maximum: 360GB BMRB • CPUs (includes Cores) • Minimum: 8 • Maximum: 32 CPUs, 160 Cores

  19. Buffer Cache (-B)

  20. Spin Locks (-spin)

  21. Storage

  22. Concurrent Database Connections

  23. Monitoring Tools • ProMonitor • OE/Fathom Management • Homegrown • ProTop

  24. Virtual & Hardware Partions VMware LPAR or similar

  25. Daily Growth Rates

  26. Capacity Planning Tools - CPU • sar • nmon (AIX) • Adrian Performance Monitor (Solaris) • “User’s Scream”

  27. Capacity Planning Tools - RAM • vmstat • nmon (AIX)

  28. Capacity Planning Tools - Disk • iostat • SAN Vendor’s tools

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

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

  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 

  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

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

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

  35. Top Challenges • “24 hours is not enough” • Anon: “Never attempt to do high volume reporting or data extracts on Progress”

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

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

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

  39. 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.)”

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

  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 doesn’t mean they don’t exist…maybe just ashamed

  42. Conclusion • Questions? • Vragen? • Shitsumon?

  43. Conclusion • Thank you for coming! • Thank you for the invitation • Contact Info: • danf@prodb.com • +1 541 908 3437

More Related