1 / 24

Database Growth: Problems & Solutions

Database Growth: Problems & Solutions. Thomas Boyd Hewlett-Packard. Agenda. The Data Explosion Effects of Database Explosion End User IT Operations Lab Tests Real-world experiences Solutions Q & A. The Data Explosion. “Data Explosion”: 166,000 Google Hits

metta
Download Presentation

Database Growth: Problems & Solutions

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. Database Growth: Problems & Solutions Thomas Boyd Hewlett-Packard

  2. Agenda • The Data Explosion • Effects of Database Explosion • End User • IT Operations • Lab Tests • Real-world experiences • Solutions • Q & A

  3. The Data Explosion • “Data Explosion”: 166,000 Google Hits • “Information Life Cycle Management”: 1.3M Google Hits • Numerous analyst papers • Archiving vendors

  4. Data Explosion: Largest OLTP on Unix Databases • Source: Winter Corporation; Top 10 Survey (www.wintercorp.com) • 2003: Max: 5.4 TB, Average 2.5 TB • 2005: Max: 16.4 TB, Average 6.8 TB Max: 200% Increase Average: 175% Increase

  5. Data Explosion: Extremes • India: 6.6 newmobile subscribers for month of October, 2006 • USA: 25-35% growth in 2006 holiday online retails sales, industry-wide • Europe: BASEL II • Hewlett-Packard: Consolidating 85 data centers into 6

  6. Messaging (Semi-structured) Files (Unstructured) Data Explosion: Types of Data • Structured, semi-structured, and unstructured Business Applications Enterprise Information Database (Structured)

  7. Data has a Lifecycle Sales order booked High Change order Partial shipments Complete shipment Customer invoices generated Access / SLA Requirements Sales order closed Customer inquiry Internal audit Retrieval Activity Low Corporate data retention period Regulatory data retention period ends Time

  8. Inactive Data Sales order booked Large Change order Partial shipments Complete shipment Customer invoices generated Sales order closed Database Size Inactive Data Customer inquiry Internal audit Retrieval Activity Active Data Small Time

  9. Agenda • The Data Explosion • Effects of Database Explosion • End User • IT Operations • Lab Tests • Real-world experiences • Solutions • Q & A

  10. Effects of Database Growth • End-User • Declining application performance • Decreased application availability • Information overload • Is your application presenting irrelevant information to end-user, causing extra work?

  11. Effects of Database Growth • IT Operations • Storage requirements • Server requirements • Backup and recovery windows • Maintenance and upgrades windows • Cloning operations • Database and Application Tuning

  12. Lab Tests • Single Table Query, non-unique index range scan (good selectivity) Not Verified

  13. Lab Tests: Future • Single Statement Testing • Track all relevant statistics • Insert and delete statements • Complex joins • Full scale stress testing • Simulate Oracle EBS load • Varying machine and storage configurations • Redo, Rollback analysis • Backup (RMAN) analysis

  14. 1300 Total Batch Run Time (Hrs/Month) Data Growth Impact 1200 Application Data 1100 Application Data (GB) 1000 900 800 700 600 Batch Run Time 500 400 Real World: Data Growth vs. Performance Phase I Tuning

  15. Real World: Data Growth versus Performance Total Batch Run Time (Hrs/Month) Data Growth Impact Application Data (GB) Phase II Tuning Sustained Predictable Performance Archiving

  16. Agenda • The Data Explosion • Effects of Database Explosion • End User • IT Operations • Lab Tests • Real-world experiences • Solutions • Q & A

  17. Solutions: Control Data Generation • Look for setups or configurations that are contributing to data growth • Persistent failures in interfaces • Persistent failures/notifications from Workflow • Excess use of Oracle Alerts • Inappropriate inventory tracking levels • Monitor

  18. Solutions: Purging • Some data is not subject to regulatory requirements and has no ongoing business value • Candidates: • Concurrent request data • MRP runs • Workflows • Temporary and Interface Tables • Others

  19. Solutions: Hardware Upgrades • Storage • Server • Scale up • Scale out (RAC) • Network

  20. Solutions: No Consolidation • Sometimes managing two medium sized databases is easier than managing one large database • Gives up on all the advantages of consolidation • IT Operations • Consolidating reporting, etc…

  21. Solutions: Partitioning • Powerful Oracle feature for managing large tables • Improves performance if queries are able to use partition keys • “Life cycle” of data may leave active data in same partition as inactive data

  22. Solutions: Archiving • Remove data from OLTP Databases while retaining ability to access • Database Backups/Snapshots • Exports, CSV files • XML Files or other self-describing format • Relocation to secondary database • Access via reporting tools or business analytics • Access via native application

  23. Agenda • The Data Explosion • Effects of Database Explosion • End User • IT Operations • Lab Tests • Real-world experiences • Solutions • Q & A

  24. Conclusion • Plan for it! • Understand its effects • COLLABORATE 07: Updated Presentation and White Paper • Full lab results

More Related