db-13: database health checks how to tell if you’re heading for the wall richard shulman principal...
TRANSCRIPT
DB-13: Database Health Checks
How to tell if you’re heading for The Wall
Richard ShulmanPrincipal Support Engineer
© 2006 Progress Software Corporation2DB-13: Database Health Checks
If this was about the song…
Outside the Wall (Waters) 1:42
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...."
© 2006 Progress Software Corporation3DB-13: Database Health Checks
Introduction
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)
How to tell if you’re heading for The Wall
© 2006 Progress Software Corporation4DB-13: Database Health Checks
Agenda
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?
How to tell if you’re heading for The Wall
© 2006 Progress Software Corporation5DB-13: Database Health Checks
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
© 2006 Progress Software Corporation6DB-13: Database Health Checks
Agenda
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?
How to tell if you’re heading for The Wall
© 2006 Progress Software Corporation7DB-13: Database Health Checks
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)?
© 2006 Progress Software Corporation8DB-13: Database Health Checks
Agenda
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?
How to tell if you’re heading for The Wall
© 2006 Progress Software Corporation9DB-13: Database Health Checks
Pick a Metric (if you don’t like this one find a metric you like)
FURPS • Functionality
• Usability
• Reliability
• Performance
• Supportability
© 2006 Progress Software Corporation10DB-13: Database Health Checks
Agenda
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?
How to tell if you’re heading for The Wall
© 2006 Progress Software Corporation11DB-13: Database Health Checks
2 billion recid limit - depends on how you slice and dice it
File system limits Memory limits User load Inefficient code
Constraints – AKA The Walls
© 2006 Progress Software Corporation12DB-13: Database Health Checks
How soon will you hit the 2 billion record limit -- depends on how you slice and dice it.
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
I have seen the writing on the Wall
© 2006 Progress Software Corporation13DB-13: Database Health Checks
2 Billion Record limit continued
Fragments use record slots too.
Small records + time + updates to records
= larger records (maybe fragmented records)
Records > database blocksize
= fragmented records
Fragmentation is a Killer – AKA Another Brick in the Wall Part 1
© 2006 Progress Software Corporation14DB-13: Database Health Checks
2 Billion Record limit continued
( Blob or Clob) / 32K = # of recids used Can use large numbers of recids quickly
© 2006 Progress Software Corporation15DB-13: Database Health Checks
File System
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
Another Brick in the Wall Part II
© 2006 Progress Software Corporation16DB-13: Database Health Checks
Memory
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
Another Brick in the Wall Part III
© 2006 Progress Software Corporation17DB-13: Database Health Checks
User Load
More requests for data More CPU load More disks or different arrangement More memory More semaphores
Another Brick in the Wall Part IV
© 2006 Progress Software Corporation18DB-13: Database Health Checks
Inefficient Code
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
Another Brick in the Wall Part V
© 2006 Progress Software Corporation19DB-13: Database Health Checks
Agenda
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?
How to tell if you’re heading for The Wall
© 2006 Progress Software Corporation20DB-13: Database Health Checks
What tools can help to see The Progress 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
Tools to help see the wall
© 2006 Progress Software Corporation21DB-13: Database Health Checks
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
© 2006 Progress Software Corporation22DB-13: Database Health Checks
Proutil dbanalys
Number of records Mean Size of records Scatter Factor Fragment Factor Number of Index blocks Utilization of Index blocks
© 2006 Progress Software Corporation23DB-13: Database Health Checks
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
© 2006 Progress Software Corporation24DB-13: Database Health Checks
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
© 2006 Progress Software Corporation25DB-13: Database Health Checks
Agenda
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?
How to tell if you’re heading for The Wall
© 2006 Progress Software Corporation26DB-13: Database Health Checks
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?
© 2006 Progress Software Corporation27DB-13: Database Health Checks
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
© 2006 Progress Software Corporation28DB-13: Database Health Checks
Proper Planning
Things to “Plan For”: New user load Batch job duration Maintenance windows duration Backup and Restore times
No tool can totally replace proper planning!
Avoiding the wall
© 2006 Progress Software Corporation29DB-13: Database Health Checks
Agenda
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?
How to tell if you’re heading for The Wall
© 2006 Progress Software Corporation30DB-13: Database Health Checks
What else can you do with the data?
Growth trending
Monitor high scatter per table
Monitor high fragmentation per table
When is a variable extent growing
Preventing the foundation for the wall
© 2006 Progress Software Corporation31DB-13: Database Health Checks
Demonstration
Small demonstration of some Progress® scripts and code
Sample output generated by our Reports
© 2006 Progress Software Corporation32DB-13: Database Health Checks
Questions?
© 2006 Progress Software Corporation33DB-13: Database Health Checks
Where to find more information
Progress Tech Support Solution Center
Progress Technical Support
© 2006 Progress Software Corporation34DB-13: Database Health Checks
Thank you foryour time
© 2006 Progress Software Corporation35DB-13: Database Health Checks