1 / 38

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 V10 Database Administration Jumpstart

daniel-sloan
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 • V10 Database Administration Jumpstart • Virtual System Tables • Pro Dump & Load • ProMonitor • Balanced Benchmark

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

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

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

  6. 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)

  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

  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

  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. Largest Table

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

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

  15. Server Demographics • RAM • Minimum: 32gb • Maximum: 80gb • CPUs • Minimum: 8 • Maximum: 24

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

  17. Concurrent Database Connections • AHM: 2999 • Broder: 1163 • Wachovia: 389 • VTB24 2000-2100

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

  19. Daily Growth Rates

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

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

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

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

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

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

  26. Database Replication • After Imaging (2) • OE/Fathom Replication (2) • Restore from Snap Copy (2)

  27. Replication Issues • Getting Fathom Replication to integrate smoothly with Veritas Cluster Server • Long Redo Phase Bug (fixed in V10.1C)

  28. Worst Progress Bug VTB 24 Issue Number: OE00177223 DB crashes with error 645 and cxDoSplit - data too long errors

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

  30. Dump/Load • Broder: Pro Dump/Load • AHM: Quarterly Binary D&L • Wachovia: “Are you kidding?” (2007) • Wachovia: Pro Dump/Load (2008) • VTB24 “Never happened”

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

  32. 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!”

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

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

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

  36. Summary • Don’t be afraid of Progress VLDBs especially with V10.1B and later

  37. Vragen?

  38. Conclusion • Thank you for coming! • Thank you for the invitation

More Related