Download
database management with minimal downtime n.
Skip this Video
Loading SlideShow in 5 Seconds..
Database Management with Minimal Downtime PowerPoint Presentation
Download Presentation
Database Management with Minimal Downtime

Database Management with Minimal Downtime

292 Views Download Presentation
Download Presentation

Database Management with Minimal Downtime

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Database Management with Minimal Downtime Dan Foreman BravePoint, Inc Ben Holmes, Eaton Corp

  2. Introduction - Dan Foreman • Progress User since 1984 • Presenter at USA Progress Users Conference 1990-1998 and 2002-2006

  3. Introduction - Dan Foreman Author of: • Progress Performance Tuning Guide • Progress Database Admin Guide • Progress Virtual System Tables • V9 Database Administration Jumpstart • All Books are Searchable Online Free • ProMonitor - Database Monitoring Tool • Pro Dump & Load - Dump/Load Utility • Progress DBA Resource Kit

  4. Who are You? • Progress Version • Largest Database • Database Operating System

  5. Why Dump/Load (D&L)? • Improve Performance by reducing Table Scatter Factor & Record Fragmentation • Reclaim Space in the Database • Migrate between different Progress Versions (e.g. from V9 to V10) • Migrate between different Platforms (e.g. Windows to HP/UX)

  6. Why D&L? • Change the Database Blocksize (V8.2+) • Change the Records per Block (RPB) • Convert to Multiple Storage Areas (V9+) • Convert to V10 Type 2 Storage Areas • Recover Data from a Corrupted Database

  7. How Often? • Linear method - every X number of months • Subjective method - because it feels like about the right time to do it • Serendipitous method - “When’s the next 3 day weekend?” • Empirical method - predictive - decide when is the best time based upon various statistics • Scatter Factor • Fragmentation Factor • Application and/or Utility Performance

  8. Scatter Factor • How close are the Records in a Table to each other physically • Also known as: Physical Scatter Factor • Obtained from proutil dbanalys/tabanalys • I believe that the Progress recommendations are somewhat low (conservative) for most databases; you will spend many weekends doing D&Ls

  9. Scatter Factor • For Large, Frequently Accessed Tables: • .9 Data overlap problem (bug) • 1.0 Green Flag - Perfect • 1.1-2.0 Probably OK • 2.1-3.0 Yellow Flag - Deteriorating • 3.1-4.0 Performance problems are probably being experienced • 4.1+ Red Flag - take action ASAP

  10. Scatter Factor • Scatter Factor is not as crucial in V9+ if selected Tables are stored in dedicated Storage Areas • But don’t forget there is also a Logical Scatter Factor to consider - does the Physical sequence of the records match the sequence of the keys in the most commonly used index • New tool to measure Logical Scatter Factor (Logical Scatter Analyzer)

  11. Fragmentation Factor • A Fragment is a Record • A Record can consist of one or more Fragments • The more Fragments per Record the more I/O is required to read and update a Record • VSTs (_ActRecord) can tell you how much Excess Fragment I/O is actually occurring but unfortunately not on a table level; also the statistics are somewhat suspicious

  12. Index Statistics • Obtained from proutil dbanalys/ixanalys • Block Utilization Percentage • 50% = 2X more I/O to read all the Keys • 25% = 4X more I/O to read all the Keys • B-Tree Levels • Each level means a possible extra I/O to retrieve a Key • How often an Index is used can be found using the _IndexStat VST

  13. How Long • For a Database that isn’t reorganized very often, the dump is typically the longest part of the process • Identify the table that takes the longest time to dump and that will be one of the limiting factors

  14. Outage Tolerance • Example: Johnson Controls (Battery) • One Database Outage per Year • Duration: usually 24 hours

  15. Outage Tolerance • 300gb Database in Australia • Last Dump & Load in 1999 • Optimized D&L takes a “fortnight”, i.e. two weeks • Unacceptable!

  16. Solution: Pro Dump & Load • Does not make the D&L go faster • But it does reduce the D&L Downtime • Example: • 100gb Mfg/Pro Database in Hungary • Using Pro D&L, Total Downtime under One Hour

  17. How Does It Work Initial Phase • Shutdown the Production DB • Take a Copy of the Production DB • Add Pro D&L Replication to the Production DB • Restart the Production DB • Now all changes made to the production DB are stored in a separate DB • Limiting Factor: How long to make a DB copy

  18. How Does it Work? Dump/Load Phase • Dump and Load the Copy DB • If disk space for the Replication DB is adequate, it doesn’t matter how long the D&L takes or what method is used

  19. How Does it Work? Synchronization Phase • Start Applying Replicated Data to the New (Dumped & Loaded) Database

  20. How Does it Work? Final Phase • Shutdown Production Database • Apply the Remaining Replication Data • Verify that the old and new DBs match • Replace the ‘old’ Production DB with the ‘New’ Production DB • Start the Production Database

  21. Requirements, Warnings, and Limitations • Requires very good Progress DBA skills or BravePoint assistance • Lots of Disk Space is Required • Replication Database • Copy Database • Dump Files • Index Rebuild Sort Files • A Separate Server for the D&L is nice

  22. Requirements, Warnings, and Limitations • Tables with no Unique Indexes Require extra handling; fortunately Mfg/Pro only has a few of these tables • SQL92 can’t be used to Update the Production Database during the time Pro D&L is Active • Some additional Drag on Performance particularly if the D&L occurs on the Server where the Production DB is

  23. Introduction – Ben Holmes • Started Programming with Progress in 1989, version 4.2 • Progress Consultant/DBA for over 12 years. • Worked at QAD as Technical Service Manager. • Worked at Eaton for almost 4 years.

  24. Customer Success StoryEaton Corp

  25. Eaton’s Environment • 250+ Production Progress Databases • Database range from 6gb to 120gb • Multiple Versions of Progress (8 & 9) • Multiple Versions of MFG/PRO(7.4 – eB2) • Nine Different Countries • Many Different Time Zones

  26. Average Run Time • Start after Tuesday Backup • Run DB analyses • Turn Pro D&L Replication on • Restore Backup and Perform a Dump/Load using Binary method • Friday & Saturday Load Transactions • Attach New database to Pro D&L Replication Database and load older transactions (@ 1 million per-hour). • We have had over 20 millions transactions.

  27. Continue • Sunday After Backup • Copy New Database over top of Production • Load remaining transactions • Run DB analyses • Compare table counts • Total amount of Production Downtime • Under 3 hours • Grand Total of time 20-75 hours

  28. Conclusion • Questions? • Thank you for coming • danf@prodb.com • BenWHolmes@eaton.com