Strength Strategy Stability Progress Performance Monitoring and Tuning
Strength. Strategy. Stability.
Progress Performance Monitoring and Tuning Dan Foreman Progress Expert Brave. Point 2011
Introduction – Dan Foreman Progress User since 1984 Author of: Progress Performance Tuning Guide Progress DB Administration Guide Progress Virtual System Tables Pro. Monitor Pro Dump&Load And other miscellaneous stuff 3 Brave. Point 2011
Introduction - Audience Progress Version? V 8 V 9 V 10? DB Operating System: *ix or Windows Largest Single Database One of Brave. Point’s customer’s has a Six Terabyte database 4 Brave. Point 2011
Monitoring Categories Progress Databases Progress Clients Character Graphical App. Servers & Web. Speed SQL Clients Operating System Application – beyond the scope of this presentation 5 Brave. Point 2011
Monitoring - Database Growth I/O Record Locking After Imaging Before Imaging Resource Contention Latches Semaphores 6 Brave. Point 2011
Monitoring - Database Health Log File Blocks & Chains Pointers (Index Data) Database Structure Database “Demographics” Replication Backups 7 Brave. Point 2011
Monitoring Tools promon Virtual System Tables Other Progress utilities 3 d Party Pro. Monitor (Brave. Point) Open. Edge Management (Progress) Other 8 Brave. Point 2011
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 9 Brave. Point 2011
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 10 Brave. Point 2011
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 11 Brave. Point 2011
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… 12 Brave. Point 2011
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. ) 13 Brave. Point 2011
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) 14 Brave. Point 2011
DB Monitoring – Before Image Long Duration Transactions Goal: 60 minutes maximum Fix: good code BI Size Goal: “reasonable” – typically 1 -4 gb max Fix: good code 15 Brave. Point 2011
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) 16 Brave. Point 2011
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 17 Brave. Point 2011
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 18 Brave. Point 2011
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… 19 Brave. Point 2011
DB Monitoring - Health Pointers (Indexes Data) proutil idxcheck proutil idxfix 20 Brave. Point 2011
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 T 2 Areas 21 Brave. Point 2011
DB Monitoring - Demographics Scatter Factor proutil dbanalys/tabanalys 4+ for large, heavily used tables is EVIL Bug in early V 10 Scatter Factors Continued… 22 Brave. Point 2011
DB Monitoring - Demographics Fragmentation proutil dbanalys/tabanalys Fragmented Records require a minimum of 2 X more I/O to read and write Continued… 23 Brave. Point 2011
DB Monitoring - Demographics Tables getting large enough to warrant migration to a dedicated Storage Area proutil dbanalys/tabanalys Index Utilization Percent proutil dbanalys/ixanalys 24 Brave. Point 2011
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 V 10. 1 C) 25 Brave. Point 2011
SQL Client Monitoring I/O – same as Progress Clients Query Plan UPDATE STATISTICS – not really monitoring but tuning 26 Brave. Point 2011
Monitoring - Other Memory consumption Progress Clients Java App. Servers & Web. Speed Tomcat Does it respond? 27 Brave. Point 2011
Conclusion Questions? If you need further assistance: Dan Foreman Publications Pro. Monitor www. bravepoint. com danf@prodb. com Thank You For Coming! Don’t forget your Conference Evaluations! 28 Brave. Point 2011
- Slides: 28