DB13: Database Health Checks

1
DB-13 Database Health Checks

• How to tell if youre heading for The Wall

Richard Shulman
Principal Support Engineer
2
If this was about the song

• Outside the Wall (Waters) 142 All alone, or in
  two's,The ones who really love youWalk up and
  down outside the wall.Some hand in handAnd some
  gathered together in bands.The bleeding hearts
  and artists Make their stand.And when they've
  given you their allSome stagger and fall, after
  all it's not easyBanging your heart against some
  mad bugger's wall.
• Isn't this where...."

3
Introduction
How to tell if youre heading for The Wall

• Introduction
• What is the Wall?
• Business interest in Enterprise Databases
• How OpenEdge fits the bill
• There are real life constraints
• We call these constraints (The Walls)

4
Agenda
How to tell if youre heading for The Wall

• What is the Wall?
• Business Focused Metrics
• If you cant measure you wont improve
• FURPS model for evaluation
• We call these constraints (The Walls)
• Different kinds of Wall?
• What tools can help to see The Wall?
• What steps to take to avoid The Wall?
• What else can you do with the data?

5
What is the Wall?

• The Wall is anything that can cause a database
  or application slowdown or outage for a reason
  which could have been proactively avoided.

Image from www.havingasoftware.nl
6
Agenda
How to tell if youre heading for The Wall

• What is the Wall?
• Business Focused Metrics
• If you cant measure you wont improve
• FURPS model for evaluation
• We call these constraints (The Walls)
• Different kinds of Wall?
• What tools can help to see The Wall?
• What steps to take to avoid The Wall?
• What else can you do with the data?

7
Business Focused Metrics If you cant measure
you wont improve

• What do you measure?
• When should you measure?
• Whose time is your money measured by (customer
  time or company time)?

8
Agenda
How to tell if youre heading for The Wall

• What is the Wall?
• Business Focused Metrics
• If you cant measure you wont improve
• FURPS model for evaluation
• We call these constraints (The Walls)
• Different kinds of Wall?
• What tools can help to see The Wall?
• What steps to take to avoid The Wall?
• What else can you do with the data?

9
Pick a Metric (if you dont like this one find a
metric you like)

• FURPS
• Functionality
• Usability
• Reliability
• Performance
• Supportability

10
Agenda
How to tell if youre heading for The Wall

• What is the Wall?
• Business Focused Metrics
• If you cant measure you wont improve
• FURPS model for evaluation
• We call these constraints (The Walls)
• Different kinds of Wall?
• What tools can help to see The Wall?
• What steps to take to avoid The Wall?
• What else can you do with the data?

11
Constraints AKA The Walls

• 2 billion recid limit - depends on how you slice
  and dice it
• File system limits
• Memory limits
• User load
• Inefficient code

12
How soon will you hit the 2 billion record limit
-- depends on how you slice and dice it.
I have seen the writing on the Wall

• 2 billion recids / 256 (records per block)
• 8192000 blocks
• 2 billion recids / 4 (records per block)
• 524288000 blocks
• 2 billion recids / 1 (record per block)
• 2 billion blocks
• VSTs _areastatus._areastatus-rmnum

13
2 Billion Record limit continued
Fragmentation is a Killer AKA Another Brick in
the Wall Part 1

• Fragments use record slots too.
• Small records time updates to records
• larger records (maybe fragmented records)
• Records gt database blocksize
• fragmented records

14
2 Billion Record limit continued

• ( Blob or Clob) / 32K of recids used
• Can use large numbers of recids quickly

15
File System
Another Brick in the Wall Part II

• OS Support File System Format Progress
  EnableLargeFiles Large File Support
• If you share space with others will they play
  nice with you?
• Only Enterprise license allows large file support
• File Descriptors

16
Memory
Another Brick in the Wall Part III

• 32-bit OSs limit 32-bitly
• 64-bit OSs dont
• (but Progress picked an arbitrary limit)
• More users more memory either by client or by
  server

17
User Load
Another Brick in the Wall Part IV

• More requests for data
• More CPU load
• More disks or different arrangement
• More memory
• More semaphores

18
Inefficient Code
Another Brick in the Wall Part V

• How easy is it to overwhelm your system with bad
  code? So easy it will make you cry.
• Any database (Progress or otherwise) can be
  overwhelmed with bad code.
• More records
• More disk and CPU time
• More network bandwidth

19
Agenda
How to tell if youre heading for The Wall

• What is the Wall?
• Business Focused Metrics
• If you cant measure you wont improve
• FURPS model for evaluation
• We call these constraints (The Walls)
• Different kinds of Wall?
• What tools can help to see The Wall?
• What steps to take to avoid The Wall?
• What else can you do with the data?

20
What tools can help to see The Progress Wall?
Tools to help see the wall

• Tool Frequency
• Prostrct statistics (monthly)
• Prostrct list (as needed)
• Proutil dbanalys (weekly)
• statParse.p (monthly)
• VSTs (program dictated)
• Top
• Your favorite OS tool

21
Prostrct Statistics Prostrct List

• Prostrct Statistics
• To see high water mark of area
• Prostrct List
• To see records per block
• To see area extent information
• Visible output is better than file output

22
Proutil dbanalys

• Number of records
• Mean Size of records
• Scatter Factor
• Fragment Factor
• Number of Index blocks
• Utilization of Index blocks

23
statParse.p

• Per Area listing of highwater mark and maximum
  block number based on RPB
• Notification if area is close to block limit
  based on high water mark

24
VSTs

• _area and _areastatus VST can provide live
  monitoring of some of the important data related
  to Maximum size of each area
• _areaextent can show the per file listing of size

25
Agenda
How to tell if youre heading for The Wall

• Business Focused Metrics
• If you cant measure you wont improve
• FURPS model for evaluation
• We call these constraints (The Walls)
• What is the Wall?
• What tools can help to see The Wall?
• What steps to take to avoid The Wall?
• What else can you do with the data?

26
What steps to take to avoid The Wall?

• An ounce of prevention.
• A pound of cure

Early versus Late - Which price do you want to
pay?
27
If only I had.

• Correct choice of records per block can
• Improve performance
• Improve space utilization on disk
• Improve memory utilization in the buffer pool
• Compaction of indices can save space
• Proutil C MvSch

28
Proper Planning
Avoiding the wall

• Things to Plan For
• New user load
• Batch job duration
• Maintenance windows duration
• Backup and Restore times
• No tool can totally replace proper planning!

29
Agenda
How to tell if youre heading for The Wall

• Business Focused Metrics
• If you cant measure you wont improve
• FURPS model for evaluation
• We call these constraints (The Walls)
• What is the Wall?
• What tools can help to see The Wall?
• What steps to take to avoid The Wall?
• What else can you do with the data?

30
What else can you do with the data?
Preventing the foundation for the wall

• Growth trending
• Monitor high scatter per table
• Monitor high fragmentation per table
• When is a variable extent growing

31
Demonstration

• Small demonstration of some Progress scripts and
  code
• Sample output generated by our Reports

32
Questions?
33
Where to find more information Progress Tech
Support Solution Center Progress Technical Support
34
Thank you foryour time
35
(No Transcript)