How Healthy is Your Progress System? (Progess DB Best Practices)

1
How Healthy is Your Progress System?(Progess DB
Best Practices)

• Dan Foreman
• BravePoint, Inc.
• danf_at_prodb.com

2
Introduction- Dan Foreman

• Progress User since 1984
• Guest Speaker at USA European Progress Users
  Conferences since 1988

3
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 DL - Dump/Load with very short downtime
  regardless of DB size
• Balanced Benchmark Load Testing Tool

4
Introduction - BravePoint

• The Largest(?) Progress consulting group in the
  world (managing one of the worlds largest
  databases)
• Three have used Progress since 1984
• Database Group
• Managed DBA Services
• Performance Tuning
• Database Repair (and proactive protection)
• Load Testing
• Much more

5
Introduction Who Are You?

• Largest Single DB?
• Largest Concurrent DB Connections?
• Progress Version?
• Database Operating System?

6
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

7
Best Practices After Imaging

• Enable After Imaging (AI)
• Verify After Image Logs with rfutil aiverify
• Minimum Copy AI Logs to a non-production server
  frequently (every few minutes)
• Best Use the AI logs to Replication production
  DB on another server as a Hot or Warm Standby

8
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

9
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

10
Best Practices Unix/Linux

• Dont 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

11
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 DumpLoad is needed
• Monitor Table growth rates
• Elapsed time to run the utility is a performance
  indicator

12
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)

13
Best Practices DB Monitoring

• Important because promon Virtual System Tables
  dont show history trends
• ProMonitor
• ProTop
• OpenEdge Management
• Build your own

14
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 lt BI Size Alert Threshold lt
• (-bithold value (available BI disk space / 2))

15
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

16
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

17
Best Practices DB Tuning

• Database Buffers (-B) - lots
• Dont 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

18
Best Practices DB Tuning

• Use Buffer Hit Ratio
• Ratio of DB Requests / DB Reads
• 3 digits1 is usually excellent
• Higher than that usually indicates bad code
• Lower than 201 is usually poor performance
• A Ratio is a better indicator especially if the
  percentage is approaching 100

19
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

20
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

21
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 wasnt updated right away
• Database Startup Option

22
Best Practices DB Tuning

• But -directio isnt a good idea for all platforms
• Dont use on
• Windows
• Linux

23
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

24
Best Practices DB Structure

• Fixed Size Data Extents
• Dont grow into the Variable Extent
• Not as crucial as it was in the 90s 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

25
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

26
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
  wont be your first time
• Usually much more effective than idxbuild or
  idxcompact

27
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 lt .2ms Average Access

28
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

29
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

30
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)

31
Best Practices What you dont know can hurt you

• Have a third party look at your system once a
  year
• Doesnt need to be me - competition is good
• You may be surprised at what youve missed or has
  slipped through the cracks
• Its like car insurance

32
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
• danf_at_prodb.com or dforeman_at_bravepoint.com
• Thank You for Coming!