Transcript How to tell if you`re heading for The Wall

DB-13: Database Health Checks
How to tell if you’re heading for The Wall
Richard Shulman
Principal Support Engineer
If this was about the song…

Outside the Wall (Waters) 1:42
All alone, or in two's,
The ones who really love you
Walk up and down outside the wall.
Some hand in hand
And some gathered together in bands.
The bleeding hearts and artists
Make their stand.
And when they've given you their all
Some stagger and fall, after all it's not easy
Banging your heart against some mad bugger's wall.
“Isn't this where...."
DB-13: Database Health Checks
2
© 2006 Progress Software Corporation
Introduction
How to tell if you’re 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)
DB-13: Database Health Checks
3
© 2006 Progress Software Corporation
Agenda
How to tell if you’re heading for The Wall




What is the Wall?
Business Focused Metrics
• If you can’t measure – you won’t 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?
DB-13: Database Health Checks
4
© 2006 Progress Software Corporation
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
DB-13: Database Health Checks
5
© 2006 Progress Software Corporation
Agenda
How to tell if you’re heading for The Wall




What is the Wall?
Business Focused Metrics
• If you can’t measure – you won’t 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?
DB-13: Database Health Checks
6
© 2006 Progress Software Corporation
Business Focused Metrics
If you can’t measure – you won’t improve

What do you measure?

When should you measure?

Whose time is your money measured by
(customer time or company time)?
DB-13: Database Health Checks
7
© 2006 Progress Software Corporation
Agenda
How to tell if you’re heading for The Wall




What is the Wall?
Business Focused Metrics
• If you can’t measure – you won’t 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?
DB-13: Database Health Checks
8
© 2006 Progress Software Corporation
Pick a Metric
(if you don’t like this one find a metric you like)
 FURPS
•
•
•
•
•
Functionality
Usability
Reliability
Performance
Supportability
DB-13: Database Health Checks
9
© 2006 Progress Software Corporation
Agenda
How to tell if you’re heading for The Wall




What is the Wall?
Business Focused Metrics
• If you can’t measure – you won’t 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?
DB-13: Database Health Checks
10 © 2006 Progress Software Corporation
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
DB-13: Database Health Checks
11 © 2006 Progress Software Corporation
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)
= 524288 blocks
2 billion recids / 1 (record per block)
= 2 billion blocks
VST’s _areastatus._areastatus-rmnum
DB-13: Database Health Checks
12 © 2006 Progress Software Corporation
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 > database blocksize
= fragmented records
DB-13: Database Health Checks
13 © 2006 Progress Software Corporation
2 Billion Record limit continued
 ( Blob or Clob) / 32K = # of recids used
 Can use large numbers of recids quickly
DB-13: Database Health Checks
14 © 2006 Progress Software Corporation
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
DB-13: Database Health Checks
15 © 2006 Progress Software Corporation
Memory
Another Brick in the Wall Part III



32-bit OS’s limit 32-bitly
64-bit OS’s don’t
(but Progress picked an arbitrary limit)
More users = more memory either by client or by
server
DB-13: Database Health Checks
16 © 2006 Progress Software Corporation
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
DB-13: Database Health Checks
17 © 2006 Progress Software Corporation
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
DB-13: Database Health Checks
18 © 2006 Progress Software Corporation
Agenda
How to tell if you’re heading for The Wall




What is the Wall?
Business Focused Metrics
• If you can’t measure – you won’t 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?
DB-13: Database Health Checks
19 © 2006 Progress Software Corporation
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)
 VST’s
(program dictated)
 Top
 Your favorite OS tool
DB-13: Database Health Checks
20 © 2006 Progress Software Corporation
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
DB-13: Database Health Checks
21 © 2006 Progress Software Corporation
Proutil dbanalys
 Number of records
 Mean Size of records
 Scatter Factor
 Fragment Factor
 Number of Index blocks
 Utilization of Index blocks
DB-13: Database Health Checks
22 © 2006 Progress Software Corporation
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
DB-13: Database Health Checks
23 © 2006 Progress Software Corporation
VST’s
 _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
DB-13: Database Health Checks
24 © 2006 Progress Software Corporation
Agenda
How to tell if you’re heading for The Wall



Business Focused Metrics
• If you can’t measure – you won’t 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?
DB-13: Database Health Checks
25 © 2006 Progress Software Corporation
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?
DB-13: Database Health Checks
26 © 2006 Progress Software Corporation
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
DB-13: Database Health Checks
27 © 2006 Progress Software Corporation
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!
DB-13: Database Health Checks
28 © 2006 Progress Software Corporation
Agenda
How to tell if you’re heading for The Wall



Business Focused Metrics
• If you can’t measure – you won’t 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?
DB-13: Database Health Checks
29 © 2006 Progress Software Corporation
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
DB-13: Database Health Checks
30 © 2006 Progress Software Corporation
Demonstration
 Small demonstration of some Progress®
scripts and code
 Sample output generated by our Reports
DB-13: Database Health Checks
31 © 2006 Progress Software Corporation
Questions?
DB-13: Database Health Checks
32 © 2006 Progress Software Corporation
Where to find more information
Progress Tech Support Solution Center
Progress Technical Support
DB-13: Database Health Checks
33 © 2006 Progress Software Corporation
Thank you for
your time
DB-13: Database Health Checks
34 © 2006 Progress Software Corporation
DB-13: Database Health Checks
35 © 2006 Progress Software Corporation