1 / 28

Strength. Strategy. Stability.

Strength. Strategy. Stability. Progress Performance Monitoring and Tuning. Dan Foreman Progress Expert BravePoint. Introduction – Dan Foreman. Progress User since 1984 Author of: Progress Performance Tuning Guide Progress DB Administration Guide

garson
Download Presentation

Strength. Strategy. Stability.

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. Strength. Strategy. Stability.

  2. Progress Performance Monitoring and Tuning Dan Foreman Progress Expert BravePoint

  3. Introduction – Dan Foreman • Progress User since 1984 • Author of: • Progress Performance Tuning Guide • Progress DB Administration Guide • Progress Virtual System Tables • ProMonitor • Pro Dump&Load • And other miscellaneous stuff

  4. Introduction - Audience • Progress Version? • V8 • V9 • V10? • DB Operating System: *ix or Windows • Largest Single Database • One of BravePoint’s customer’s has a Six Terabyte database

  5. Monitoring Categories • Progress Databases • Progress Clients • Character • Graphical • AppServers & WebSpeed • SQL Clients • Operating System • Application – beyond the scope of this presentation

  6. Monitoring - Database • Growth • I/O • Record Locking • After Imaging • Before Imaging • Resource Contention • Latches • Semaphores

  7. Monitoring - Database • Database Health • Log File • Blocks & Chains • Pointers (Index  Data) • Database Structure • Database “Demographics” • Replication • Backups

  8. Monitoring Tools • promon • Virtual System Tables • Other Progress utilities • 3d Party • ProMonitor (BravePoint) • OpenEdge Management (Progress) • Other

  9. Monitoring Tools - promon • Old faithful • Character interface • Doesn’t record any history • No Alerting • Somewhat poorly documented • See Dan Foreman’s Performance Tuning Guide • Takes a ‘snapshop’ of DB Shared Memory

  10. Monitoring Tools – VSTs • Access Shared Memory using System Tables • Doesn’t have everything promon does • Poorly documented • Dan Foreman’s VST Guide • Must write the code yourself • Some tables are hazardous to good performance: _lock

  11. DB Monitoring - Growth • DB Extends • Goal: zero – i.e. all growth occurs in fixed size extents • Not as important as it used to be IF there is a very high quality disk storage system

  12. DB Monitoring – I/O DB Requests - # of block accesses DB Reads - # of reads from DB on disk required to satisfy DB Requests DB Level Individual Client Level Continued…

  13. DB Monitoring – I/O • Hit Ratio – Ratio of DB Requests:DB Reads • Goal: find as many blocks in Buffer Cache (-B) as possible; i.e. High Hit Ratio • Fix: Dump&Load • Fix: Increase Buffer Cache • Fix: Avoid ‘stupid’ queries (no good index, ODBC reporting, etc.)

  14. DB Monitoring – Before Image • Buffers Flushed • Goal: Zero (or near zero) • Fix: BI Cluster Size • Fix: Async Page Writers (APWs) • Empty Buffer Waits • Goal: Zero • Fix: BI Buffers and/or BI Writer (BIW)

  15. DB Monitoring – Before Image • Long Duration Transactions • Goal: 60 minutes maximum • Fix: good code • BI Size • Goal: “reasonable” – typically 1-4gb max • Fix: good code

  16. DB Monitoring – After Image • Empty Buffer Waits • Not running After Imaging – don’t be a fool • Goal: Zero • Fix: AI Buffers and/or AI Writer (AIW)

  17. DB Monitoring – Record Locking • Lock Table High Water Mark (HWM) • Goal: don’t hit the HWM • Fix: larger Lock Table (-L) – be careful!!! • Fix: fix the code • Record Waits • Goal: Zero • Fix: fix the code

  18. DB Monitoring - Contention • Latch Contention – Latch Timeouts • Goal: Low (zero is impossible) • Fix: adjust –spin parameter • Semaphore Contention – Semaphore Waits • Goal: Low (zero is impossible) • Fix: adjust –semsets parameter

  19. DB Monitoring - Health • Log (.lg) File • Monitoring for Warnings & Errors • Blocks & Chains • proutil <db> -C dbanalys • Avoid running during production hours • Run against a copy of production DB • Several other health checking options exist • Continued…

  20. DB Monitoring - Health • Pointers (Indexes  Data) • proutilidxcheck • proutilidxfix

  21. DB Monitoring - Structure • Tables or Indexes in the Schema Area • Indexes in Table Areas • Tables in Index Areas • Type 2 Areas!!! • Sorry but some QAD staff are just plain wrong about T2 Areas

  22. DB Monitoring - Demographics • Scatter Factor • proutildbanalys/tabanalys • 4+ for large, heavily used tables is EVIL • Bug in early V10 Scatter Factors • Continued…

  23. DB Monitoring - Demographics • Fragmentation • proutildbanalys/tabanalys • Fragmented Records require a minimum of 2X more I/O to read and write • Continued…

  24. DB Monitoring - Demographics • Tables getting large enough to warrant migration to a dedicated Storage Area • proutildbanalys/tabanalys • Index Utilization Percent • proutildbanalys/ixanalys

  25. Progress Client Monitoring • I/O • _tablestat & _indexstat VSTs • _usertablestat & _userindexstat VSTs • Code Efficiency • Progress Application Profiler – undocmented • What Program is being executed • _connect VST (since V10.1C)

  26. SQL Client Monitoring I/O – same as Progress Clients Query Plan UPDATE STATISTICS – not really monitoring but tuning

  27. Monitoring - Other • Memory consumption • Progress Clients • Java • AppServers & WebSpeed • Tomcat • Does it respond?

  28. Conclusion • Questions? • If you need further assistance: • Dan Foreman Publications • ProMonitor • www.BravePoint.com • danf@prodb.com • Thank You For Coming! Don’t forget your Conference Evaluations!

More Related