280 likes | 409 Views
DBSonar - Slice and Dice Performance Tuning and Management for IDS 7.x, 9.x and 10.x. Eric Lam CTO/Cobrasonic. Difficulties in database performance tuning. Massive data with unknown definition No relationship between DATA Lack of Interpretation knowledge
E N D
DBSonar -Slice and Dice Performance Tuning and Management for IDS 7.x, 9.x and 10.x • Eric Lam • CTO/Cobrasonic
Difficulties in database performance tuning • Massive data with unknown definition • No relationship between DATA • Lack of Interpretation knowledge • No historical evidence information is kept • It happened, but when? Why? What’s the Impact? • Fighting among AP, DB, and HW teams • Human is not designed for routine checking…
Mission: A non-stop expertise providing tight linked information for browsing and discovering Collect Integrate Narrow Down Root Cause • Provides analysis and suggestion about real-time/historical resource consumption • Cross-linking data from Net, OS, DB and applications • Performs slice and dice analysis as OLAP tool to bring out a clear picture of bottleneck • Tells what happened inside the user application itself • Continuously monitors database systems for potential problems and issues precautious alerts for you to solve these problems before they impact end-users. • Allowing system administrators to make informed decisions in handling exceptions or planning resources. • Make you an expert to work productively instead of routinely.
OS AP DBSonar Server Collecting, Integrating, Interpreting OS AP DBSonar Server Collecting, Integrating, Interpreting DBSonar —System Architecture DBSonar Client Interactive Analysis Tool Repository Alert Deliver Service
Server Switch Panel SQL Fatal Error Monitoring and Alert OS Performance Indicator Database Server Performance Trend DB Profile Network
Integrated Performance Analysis • Integrates OS, DB and Application • Top down analysis
The power of cross-linking • What if System Busy now???
Tracking Individual Busy Connection • A language independent DB application inspector • Easily pin-point the bottleneck instead of tracing the source code I don't want to trace someone's code!!!
SQL Statement Normalization • seleCt * from CUSTOMER where fname=‘Eric’ and age = 36; • Select * from cuStomer WHERE Fname=‘Brian’ AND age = 32 • select * from customer where fname=‘Spark’ and age =35 The ONLY way to do meaningful SQL tracing It does matter whatever “condition” have been changed in the where claust, or formatting, all will be normalized to be an identical one!
Cost-evaluation of problematic SQL statements Statements are not executed (even on IDS 7.x)
Check SQL against busy table • Evaluate the criteria, whether it choose the right path? • Whether additional index need to added or restructured? • Drill down to see SQL Analysis Information
Customizable Multilayer Trend-chart Display Events are connected based on point in time SQL at point in time can also be retrieved to check where the bottleneck came!
Real-Time & Historical Trend Chart • Real-Time System Monitoring Trend Chart • Disk I/O、Memory 、CPU 、SQL • Zoom in for Detail Utilization Trend Chart • Historical Utilization Trend Chart • OS – Disk I/O、Memory 、CPU 、SQL • Database – DBSpace I/O 、Buffer read/write 、Checkpoint … • Both for Hourly and Daily Trend Chart • The time-series based chart analysis brings the relationship between event and resource utilization • Service as source for capacity planning
Expert system for onconfig parameter optimization Effective Value Original Value Parameters Suggestion
Maintain a continuous service environment • Event Based Alert Subsystem (EMail, SMS) • When/Why/Who causes the issue? • Fast drill down approach to narrow down to the causing to ensure service continuous and recover fast • Log event and associated info for long term issue elimination
Instability causing Table Failure Dead Lock Database resource overflow Assertion Fail Chunk down Long Transaction Disk Full Backup Aborted Logical Log Full Unable to allocate memory
Facility for problem resolving Problem Resolve Quickly
Display of the IDS Online Logs • Important Messages -> highlighted • Unimportant Messages -> filteredout
Database Health Check Reports • Analysis Action Guide – System Tuning iteratively • Outstanding issues list both system and AP • Time series based historical trend charts integrate and form a clear picture among OS, DB and AP • Hardware Capacity Plan for Future
Speed-up Your Business with Intelligent Administration DBSonar™ offers • Higher Overall System Availability • Optimized Hardware Utilization • Faster Application Response Time • Reduced System Administration Effort • Reduced Software Development Costs
DBSonar - Product Development Focus Performance Reliability IDS 7.x/9.x/10.x DisasterRecovery Security
Session TitleSession: ##### Eric Lam Cobrasonic Inc. eric.lam@cobrasonic.com
Product Plan • Deliver new Rule and Drill Down Tree • SNMP support • GUI enhancement, optimize in content aspect • SQL optimizer rewriter • Access shared memory instead of sysmaster • Report in PDF formant, support report customization • Support Save Directives • Generate update statistics SQL command • Tuning on Data Dictionary