Transcript Progress Database Management Best Practices

How Healthy is Your
Progress System?
(Progess DB Best Practices)
Dan Foreman
BravePoint, Inc.
[email protected]
1
Introduction- Dan Foreman
Progress User since 1984
Guest Speaker at USA & European
Progress Users Conferences since 1988
2
Introduction- Dan Foreman
Author of:
Progress Performance Tuning Guide
Progress Database Admin Guide
Progress System Tables Guide
V10 Database Admin Jumpstart
Online Access (free with paper book)
ProMonitor - Performance Monitoring Tool
Pro D&L - Dump/Load with very short
downtime regardless of DB size
Balanced Benchmark – Load Testing Tool
3
Introduction - BravePoint
The Largest(?) Progress consulting
group in the world (managing one of
the world’s largest databases)
Three have used Progress since 1984
Database Group:
Managed DBA Services
Performance Tuning
Database Repair (and proactive protection)
Load Testing
Much more
4
Introduction – Who Are You?
Largest Single DB?
Largest Concurrent DB Connections?
Progress Version?
Database Operating System?
5
Best Practices - Recovery
Test your Entire Recovery Plan at
least once a year
Verify Progress backups with prorest
and –vp or –vf
Log all activities related to backups,
AI maintenance, and other automated
activities
Generate an Alert (i.e. email, SMS,
etc.) if any activity related to
backup/AI fails
6
Best Practices – After Imaging
Enable After Imaging (AI)
Verify After Image Logs with rfutil
aiverify
Minimum: Copy AI Logs to a nonproduction server frequently (every
few minutes)
Best: Use the AI logs to Replication
production DB on another server as a
Hot or Warm Standby
7
Best Practices – After Imaging
Keep archived AI logs in a separate
location from the backups
Keep archived AI logs as long as you
keep the backups
Keep the live AI extents extents as far
“away” from the DB/BI files as possible
Separate physical disk
Separate LUN (SAN
Separate Volume Group
Separate Logical Volume/File System
8
Best Practices – Unix/Linux
Unix/Linux: DO NOT logon as root
unless you really need to
Use sudo
Use a root equivalent account
Use O/S security to protect the DB, BI,
and AI files from
accidental/casual/intentional deletion
proutil EnableLargeFiles on each
database and make sure all file
systems support large files
9
Best Practices – Unix/Linux
Don’t use kill -9 to terminate a Self
Service Progress session; You might
bring the database DOWN! if you
happen to kill a session that is
holding a Latch
10
Best Practices – DB Maint
Always have an up-to-date Structure
(.st) file available
Run proutil dbanalys periodically
Can find certain errors such as #1124
Scatter and Fragmentation Information
indicates if a Dump&Load is needed
Monitor Table growth rates
Elapsed time to run the utility is a
performance indicator
11
Best Practices – DB Monitoring
Check the Database log (.lg) file for
errors DAILY. Look for words such as:
kill* drastic warn* error system dead fatal
abnormal exceed* fail* wrong unexpected* invalid
died damage* overflow* violation insufficient
missing disappear* corrupt* allow* attempt* cannot
enough illegal beyond impossible increase
unknown unable stop* (and many more)
Use OpenEdge Management or
ProMonitor to assist with log file
monitoring or write your own (not so
easy)
12
Best Practices – DB Monitoring
Important because promon & Virtual
System Tables don’t show history &
trends
ProMonitor
ProTop
OpenEdge Management
Build your own
13
Best Practices – DB Safety
Use the -bithold parameter as an extra
safeguard; Set to 50% of available BI
Disk Space
Crash recovery causes the BI file to grow
Crash recovery causes the AI files to grow
AI extents cannot be emptied during crash
recovery
bigrow size < BI Size Alert Threshold <
(-bithold value = (available BI disk space / 2))
14
Best Practices – DB Tuning
Spin Locks (-spin) between 1000 and
100000
Why such a wide range?
BI Buffers (-bibufs) 32-64
AI Buffers (-aibufs) exactly equal to BI
Buffers
BI Block Size (-biblocksize) 16
AI Block Size (-aiblocksize) exactly
equal to BI Block Size
15
Best Practices – DB Tuning
Page Writers
DB Writers (APWs): 2-4
BI Writer (BIW): 1
AI Writer (AIW): 1
Before Image Cluster Size: 16-32mb
Pre-Formatting BI Clusters if BI
truncated
proutil bigrow
16
Best Practices – DB Tuning
Database Buffers (-B) - lots
Don’t use the promon ‘Buffer Hits %’
to monitor – Prior to V10.1B it is
buggy and frequently wrong
V10.2B SP04 Alternate Buffer Cache
-B2
For heavy read-mostly tables (and
associated indexes) that fit completely in
the memory allocated
17
Best Practices – DB Tuning
Use Buffer Hit Ratio
Ratio of: DB Requests / DB Reads
3 digits:1 is usually excellent
Higher than that usually indicates bad
code
Lower than 20:1 is usually poor
performance
A Ratio is a better indicator especially
if the percentage is approaching 100%
18
Best Practices – DB Tuning
Possible Reasons for a Poor Hit Ratio
One report looking at ‘old’ data (i.e. YTD
data) can kill a good Hit Ratio although
the “dip” usually temporary
Database needs a dump/load (poor
Scatter and/or Fragmentation Factors)
See more on the next slide
19
Best Practices – DB Tuning
Possible Reasons for a Poor Hit Ratio
-B is too small
Online utilities (dbanalys, probkup, etc.);
use –Bp to reduce the impact
Reports with indexing problems
Reports run “wide open”
The Hit Ratio was checked soon after the
DB Broker started
20
Best Practices – DB Tuning
Lots of misinformation &opinions
about Direct I/O (-directio)
Added in V6 but only applied to Data
General and Sequent Platforms
Starting in V8 applies to all platforms
but the Progress Documentation
wasn’t updated right away
Database Startup Option
21
Best Practices – DB Tuning
But -directio isn’t a good idea for all
platforms
Don’t use on:
Windows
Linux
22
Best Practices – DB Structure
Database Block Size 4k-8k
General: Match DB Block Size to File
System Block Size
Set the File System Block Size is as
large as possible
Increase in DB Block Size may mean a
reduction in –B
Dump/Load is required to change the
Block Size
23
Best Practices – DB Structure
Fixed Size Data Extents
Don’t grow into the Variable Extent
Not as crucial as it was in the 90’s
when Storage was slower
Large Extents (as large as triple digit
gigabytes) are not a performance
problem if you are using a “modern”
OS and Storage Device
24
Best Practices – DB Structure
V10
Type 2 (AKA T2) Storage Areas
General recommendations:
T2 for ALL Areas
Large Cluster Size (512) for Tables with a
large number of records
Smallest Cluster Size (8) for Tables with few
records
25
Best Practices – Dump & Load
To Fix Scatter Factor
Not as big an issue with T2 Areas
To Fix Fragmentation
To Change T2 DCS, RPB, DB Blk Size
To verify no DB Corruption Exists
So that if you need to do one in an
Emergency, it won’t be your first time
Usually much more effective than
idxbuild or idxcompact
26
Best Practices - Disk
Disks are the Slowest Server
Component
We recommend Lots of Striped
Database Disks
1999: 9gb & 9-14ms Average Access
2009: 144gb & 6-9ms Average Access
2012: SSD are < .2ms Average Access
27
Best Practices - Disk
Separation of After Image, Before
Image, and Database Disks
Mainly for Integrity (especially AI)
Secondarily for Performance (maybe)
Try to not Stripe DB/BI on the same
Volume
28
Worst Practice – RAID 5 (and Variants)
RAID 5 is (almost) always EVIL!
RAID Levels are not precisely crafted
standards (like USB 3.0, etc.)
SANs are very complex devices
RAID 10 requires more disk space
than RAID 5
Hybrids may be acceptable (RAID 5
for DB, RAID 10 for AI/BI)
YMMV
29
Best Practices - Disk
Stripe Size for RAID 0, 5, 6, or 10
The Largest Stripe Size usually
produces the best Performance
YMMV (or YKMV for some of our
international audience)
30
Best Practices – What you don’t
know can hurt you
Have a third party look at your system
once a year
Doesn’t need to be me - competition
is good
You may be surprised at what you’ve
missed or has slipped through the
cracks
It’s like car insurance…
31
Conclusion
If you need further assistance:
Progress Performance Tuning Guide
Progress Database Administration Guide
Progress System Tables
V10 Database Administration Jumpstart
ProMonitor - performance monitoring tool
Pro Dump/Load
Balanced Benchmark
[email protected] or [email protected]
Thank You for Coming!
32