280 likes | 418 Views
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
E N D
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 • Progress Virtual System Tables • ProMonitor • Pro Dump&Load • And other miscellaneous stuff
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
Monitoring Categories • Progress Databases • Progress Clients • Character • Graphical • AppServers & WebSpeed • SQL Clients • Operating System • Application – beyond the scope of this presentation
Monitoring - Database • Growth • I/O • Record Locking • After Imaging • Before Imaging • Resource Contention • Latches • Semaphores
Monitoring - Database • Database Health • Log File • Blocks & Chains • Pointers (Index Data) • Database Structure • Database “Demographics” • Replication • Backups
Monitoring Tools • promon • Virtual System Tables • Other Progress utilities • 3d Party • ProMonitor (BravePoint) • OpenEdge Management (Progress) • Other
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
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
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
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…
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.)
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)
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
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)
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
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
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…
DB Monitoring - Health • Pointers (Indexes Data) • proutilidxcheck • proutilidxfix
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
DB Monitoring - Demographics • Scatter Factor • proutildbanalys/tabanalys • 4+ for large, heavily used tables is EVIL • Bug in early V10 Scatter Factors • Continued…
DB Monitoring - Demographics • Fragmentation • proutildbanalys/tabanalys • Fragmented Records require a minimum of 2X more I/O to read and write • Continued…
DB Monitoring - Demographics • Tables getting large enough to warrant migration to a dedicated Storage Area • proutildbanalys/tabanalys • Index Utilization Percent • proutildbanalys/ixanalys
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)
SQL Client Monitoring I/O – same as Progress Clients Query Plan UPDATE STATISTICS – not really monitoring but tuning
Monitoring - Other • Memory consumption • Progress Clients • Java • AppServers & WebSpeed • Tomcat • Does it respond?
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!