1 / 37

Caché Performance Troubleshooting Part I The Application

Caché Performance Troubleshooting Part I The Application. Vik Nagjee Product Manager, Kernel Technologies. What are the organs of my system?. The Vitruvian man. Homage. Right, back to systems…. CPU. Memory. I/ O Disk Network. Caché-level Metrics. CPU. Memory. Non DB I/O

mansour-jalil
Download Presentation

Caché Performance Troubleshooting Part I The Application

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. Caché Performance TroubleshootingPart IThe Application Vik Nagjee Product Manager, Kernel Technologies

  2. What are the organs of my system? The Vitruvian man

  3. Homage

  4. Right, back to systems… CPU Memory I/O Disk Network

  5. Caché-level Metrics CPU Memory Non DB I/O Files, Network DB I/O CACHE.DAT, WIJ, Journals Physical Block Reads Routine Commands Global References Physical Block Writes Journal Writes Time-to-run or other Application Specific Metrics

  6. Key Performance Metrics: Caché • Global References • Single access to global (get, set, kill) • Roughly translates to ‘database references per second’ • Routine Commands • Single Caché Object Script (COS) instruction • Roughly reflects CPU load • Application Execution Time • Overall measure of performance • The “does the user want to throw their PC out the window?” metric

  7. If you’re going to do one thing, do this… The Keys to Success

  8. Application Metrics • First step towards controlling yourapplication performance • Reflects amount of work and/or “user-feel” speed of the application • Time to open an account screen • Time to build a report • # of widgets in minutes/seconds/microarcseconds

  9. Application Metrics – Collect in your code • For every operation record the following into a table (or global, if you’re old-school like me) • Time - $zhorolog • Global References, Routine Commands, etc… • Use %Monitor.Process • Analyze the collected data • With DeepSee, or • With Excel, or • Whatever…

  10. Case Study – Ontario Systems • At the Beginning • For every Web Service call. Recorded: • Method name • Start Time • End Time

  11. DeepSee – Activity by day

  12. DeepSee – Slowest methods

  13. Case Study – Ontario Systems • Now • For every Web Service call. Recorded: • Method Name • $zh (time with maximum precision possible) • Global References, Routine Commands • UnitTests – run after every build • Same as above data recorded for every test and compared with previous results • Degrade in performance metrics back to development as a performance problem

  14. Мicrobenchmark • Time • Might be subject to external influence • Is data in buffers already? • Are there other processes running? • Is someone else sharing the SAN?? • Global References, Routine Commands • Constant for Code+Data combination • Easy to measure

  15. Tips and Tricks – Мicrobenchmark • Global References, Routine Commands • Record before and after code change • Add to your unit tests • Double the test database size • Metrics (GloRef, RtnCom) should not increase 2x • If increase is two fold – you might not have the right index • Decrease size of the global buffers • Easy way to emulate a “large database”

  16. Identifying the bottlenecks

  17. ^PERFMON • Command-line tool for collecting performance statistics arranged by globals and routines • Robust API available • Can answer questions such as: • What routine’s responsible for the most lines of code executed? • What global was accessed the most? • Directory block writes per routine… • Etc…

  18. The ^PERFMON Report • Most Important Metrics • Global References • Routine Commands • Important Metrics • Data Block Read/Write • Journal Entries • Block Allocation • Lock/Lock Fail

  19. After ^PERFMON Top of the PERFMON report SQL Query? Yes No ^%SYS.MONLBL Analyze Query Plan

  20. ^%SYS.MONLBL • Trivia: MONLBL == MonitorLineByLine • Similar in UI and metrics to ^PERFMON, but collects data on a line-by-line basis Time – Time spend executing that line of code Total Time – Executing line of code and all calls

  21. Query Plan Analyzer

  22. Identifying Bottlenecks - Summary • Identifying bottlenecks? • Look for Routines/Queries generating the most: • Global References • Routine Commands • Block Reads • There are apps tools for that: • ^PERFMON • ^%SYS.MONLBL • Query Plan Analyzer • …

  23. Is That All? • Not quite… • Lock conflicts • Block Collisions • Resource Seizes

  24. More tools for your toolkit bag Tools, tools, tools…

  25. Monitoring – Other Tools • %Monitor.Process – low footprint in-process monitoring tool • Collects same set of metrics as ^PERFMON & ^%SYS.MONLBL • Negligible performance impact (safe for production) • Data accessible only within process (need to use API) • ^PROFILE • Unix’s ‘top’ – like UI • “Hybrid” of ^PERFMON and ^%SYS.MONLBL • Good for brief first look

  26. ^PROFILE

  27. Advanced Monitoring New in 2012.2 • ^GLOBUFF • Utility to identify Global Bufferusage per-global • ^BLKCOL • Utility to detect block collisions • Same machinery as cstat –Dx,y

  28. ^GLOBUFF New in 2012.2

  29. ^BLKCOL New in 2012.2

  30. SQL Monitoring - %SYS.PTools • SQL Monitoring tool • Attached to cached queries (requires queries purge) • Works with Class Queries and Embedded SQL (Requires recompile) • Collects data about executed queries (time, glorefs etc.) • SQL interface to results (%SYS_PToolspackage) • Starting 2013.1 exposed in Management Portal (SQL Runtime Statistics)

  31. SQL Runtime Statistics New in 2013.1

  32. %SYS.PTools Example SELECT RoutineName, QueryText, COUNT(*) as CountTotal, SUM(TotalTime) as TimeTotal FROM %SYS_PTools.SQLStatsView WHERE namespace='PERF' GROUP BY RoutineName ORDER BY CountTotal

  33. SQL Index Analyzer New in 2013.1

  34. Caché and OS Tools

  35. See Also • CachéMonitoring Guide • http://developer.intersystems.com • WRC Performance Team

  36. Upgrade!!! • Recent free-of-charge improvements • Per-process routine-vector caching • Faster object dispatch • BOL Elimination • C level code for common object methods • New, faster!!! local arrays • Multicore, Virtualized environments optimization

  37. You can reach me at: vik@intersystems.com Thanks for attending! Q&A

More Related