Transcript BravePoint 2011 - Midwest User Group

Strength.
Strategy.
Stability.
Progress Performance
Monitoring and Tuning
Dan Foreman
Progress Expert
BravePoint
BravePoint 2011
2
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
3
BravePoint 2011
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
4
BravePoint 2011
Monitoring Categories
Progress Databases
Progress Clients
Character
Graphical
AppServers & WebSpeed
SQL Clients
Operating System
Application – beyond the scope of
this presentation
5
BravePoint 2011
Monitoring - Database
Growth
I/O
Record Locking
After Imaging
Before Imaging
Resource Contention
Latches
Semaphores
6
BravePoint 2011
Monitoring - Database
Database Health
Log File
Blocks & Chains
Pointers (Index  Data)
Database Structure
Database “Demographics”
Replication
Backups
7
BravePoint 2011
Monitoring Tools
promon
Virtual System Tables
Other Progress utilities
3d Party
ProMonitor (BravePoint)
OpenEdge Management (Progress)
Other
8
BravePoint 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
BravePoint 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
BravePoint 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
BravePoint 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
BravePoint 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
BravePoint 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
BravePoint 2011
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
15
BravePoint 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
BravePoint 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
BravePoint 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
BravePoint 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
BravePoint 2011
DB Monitoring - Health
Pointers (Indexes  Data)
proutil idxcheck
proutil idxfix
20
BravePoint 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 T2 Areas
21
BravePoint 2011
DB Monitoring - Demographics
Scatter Factor
proutil dbanalys/tabanalys
4+ for large, heavily used tables is EVIL
Bug in early V10 Scatter Factors
Continued…
22
BravePoint 2011
DB Monitoring - Demographics
Fragmentation
proutil dbanalys/tabanalys
Fragmented Records require a minimum
of 2X more I/O to read and write
Continued…
23
BravePoint 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
BravePoint 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 V10.1C)
25
BravePoint 2011
SQL Client Monitoring
I/O – same as Progress Clients
Query Plan
UPDATE STATISTICS – not really
monitoring but tuning
26
BravePoint 2011
Monitoring - Other
Memory consumption
Progress Clients
Java
AppServers & WebSpeed
Tomcat
Does it respond?
27
BravePoint 2011
Conclusion
Questions?
If you need further assistance:
Dan Foreman Publications
ProMonitor
www.BravePoint.com
[email protected]
Thank You For Coming! Don’t forget
your Conference Evaluations!
28
BravePoint 2011