OPS-1: DBA 101 - How Healthy is Your Database Today?

1
OPS-1 DBA 101 - How Healthy is Your Database
Today?
Libor Laubacher
Ruanne Cluer
Principal Tech Support Engineer
Principal Tech Support Engineer
2
Agenda
DBA 101 - How Healthy is Your Database Today?

• Physical Database Limits
• Database Health Check
• Runtime Memory And Block Checking
• The 'recovery' part of DR

3
Physical Database Limits
10.1B physical limit changes

• Maximum extent size 1 TB
• Maximum extents per area 1.024
• 8 Maximum area size 1,024 TB (1 PB)
• Maximum number of areas 32,000
• 8 Maximum database size 32,000 PB (32 EB)
• Example
• 8 KB database blocksize with 64 rpb
• Maximum number of records per area
  8,796,093,022,208

4
Physical Database Limits
10.1B physical limit changes (cont.)

• 2 billion (232) records limit removed
• Still applies for Type I areas
• Errors
• SYSTEM ERROR Attempt to read block 2147472480
  which does not exist. (210)
• The maximum Area Size has been reached for Area
  Please refer to the Progress Database Limit
  chapter in the Database Administration Guide and
  Reference. (9099)
• New warning since 10.0B
• bkxtn WARNING Area ltareaNumbergt extent
  ltextentNamegt has reached the ltpercentagegt percent
  threshold for block usage - current block hiwater
  ltmaxAreaBlockgt. (13435)

5
Physical Database Limits
Extra space reservation

• 10.1B feature
• During crash recovery a database may grow
  exhausting rowids in an area
• Reserve space at the end of each area to
  accommodate growth when the area is close to its
  limit
• 1K or 2K DB block size 1 GB reserved
• 128 or 256 rpb area 1 GB reserved
• Any other combination 5 GB
• Areas are therefore allowed to expand the
  database up to max block limits minus reserved
  space

6
Physical Database Limits
Records per Block Type I area
RPB Maximum of blocks per Storage Area Maximum physical size of Storage Area per DB blockisze (in GB) Maximum physical size of Storage Area per DB blockisze (in GB) Maximum physical size of Storage Area per DB blockisze (in GB)
RPB Maximum of blocks per Storage Area 1 4 8
32 67,108,864 64 256 512
64 33,554,432 32 128 256
128 16,777,216 16 64 128
256 8,388,608 8 32 64
excluding threshold reservation
7
Physical Database Limits
Records per Block type II area (10.1B)
DB BS Maximum of blocks per Storage Area Maximum of recids Maximum of recids Maximum of recids Maximum of recids
DB BS Maximum of blocks per Storage Area 32 rpb 64 rpb 128 rpb 256 rpb
1 1,099,511,627,776 35,184,372,088,832 70,368,744,177,664 140,737,488,355,328 281,474,976,710,656
2 549,755,813,888 17,592,186,044,416 35,184,372,088,832 70,368,744,177,664 140,737,488,355,328
4 274,877,906,944 8,796,093,022,208 17,592,186,044,416 35,184,372,088,832 70,368,744,177,664
8 137,438,953,472 4,398,046,511,104 8,796,093,022,208 17,592,186,044,416 35,184,372,088,832
excluding threshold reservation
8
Physical Database Limits
Records per Block setting

• Optimal space allocation
• Increases areas addressable space
• Reduces record fragmentation

Records per (4K) Block Head(2RB20) Create Limit Overhead Remaining space
1 22 150 172 3924
32 84 150 234 3862
64 148 150 298 3798
128 276 150 426 3670
256 532 150 682 3414
OE10 Type I Storage Area
9
Physical Database Limits
Records per Block type II area (10.1B)

• 256 rpb is not a magic number
• Use casemultiple users creating records for
  same table
• massive fragmentation
• performance problems
• dump and load (still using 256 rpb)
• still problems
• getting rpb right is still important in Type II
• Invest time tuning rpb settings

10
Agenda
DBA 101 - How Healthy is Your Database Today?

• Physical Database Limits
• Database Health Check
• Runtime Memory And Block Checking
• The 'recovery' part of DR

11
Database Health Check
What is it ?

• No such thing as a health check standard
• Continuous process, not an event
• Physical checks
• Logical checks
• Better safe than sorry
• Catch it early and minimize the damage than later
  and suffer costly downtime consequences

12
Database Health Check
What to run, when to run it and what to look for ?

• Log files daily
• Error reporting and trends
• Tabanalys report weekly
• Fragmentation, Scatter, Growth trends
• Dbtool reports weekly
• Physical and logical data integrity
• Idxcheck report weekly
• Index integrity
• Database Statistics Report monthly
• Storage Area High Water Mark, space requirements

13
Database Health Check
tabanalys report Record Fragmentation

• proutil ltdbnamegt -C tabanalys gt tabana.out
• Baseline record fragmentation factor gt 30
• Tables over 80 (Factor 1.80) very strong
  candidates
• Online defragmentation since 10.1A

Block 6
Block 11
200 bytes
Update1
Update2
200 bytes
300 bytes
14
Database Health Check
tabanalys report - Scatter Factor

• How far a tables records are from each other
• i.e. how far are they from contiguous perfection
• value close to 1 indicates a good quality of
  record allocation
• When to DL
• bigger scatter most likely indicates a need for
  DL
• it is not the one and only metric
• need to know more about your data
• ignore scatter of small tables (lt 1000 records)
• Logical scatter

15
Database Health Check
tabanalys report (cont.)

• Record
  Size(B)-Fragments----Scatter
• Table Records Size Min Max Mean Count
  Factor Factor
• PUB.Company 1000000 29.2M 25 40 30 1000060
  1.0 2.0
• PUB.Customer 200000 582.4M 27 20063 3053 350901
  1.2 4.1

Binary load (type I) PUB.Company 1000000
29.2M 25 40 30 1000000 1.0
1.0 PUB.Customer 200000 582.1M 27 20063 3053
299950 1.0 4.8
Binary load (type II) PUB.Company 1000000
29.2M 25 40 30 1000000 1.0
1.0 PUB.Customer 200000 582.1M 27 20063 3053
301922 1.0 1.0
16
Database Health Check
Database Statistics Report

• prostrct statistics ltdbnamegt gt stats.out
• Online 9.1E04, 10.0B05
• Storage Area High Water Mark Active blocks
• prostrct add
• Backup requirements
• Database size Total blocks
• Rowid limits maxblocks
• Diskspace
• Variable extent growth trends

17
Database Health Check
Database Statistics Report (cont.)

• Files in Area Cust_Data
• /db101/dd_9.d1 256,049,152
• /db101/dd_9.d2 256,049,152
• /db101/dd_9.d3 185,204,736
• DB Block Usage Cust_Data
• Active blocks 168,640
• Data blocks 160,129
• Free blocks 8511
• Empty blocks 1600
• Total blocks 170,240
• Extent blocks 3
• Records/Block 64
• Cluster size 1
• 0.51 MAXBLOCKS(33,554,432)

• Database Block Usage Summary
• Active blocks 182,862
• Data blocks 173,883
• Free blocks 8979
• Empty blocks 12,810
• Extent blocks 7
• Total blocks 195,672
• Backup 715 MB

18
Database Health Check
dbtool report

• dbtool dbname
• Online and threaded (9.1D06)
• Option 5. Read or validate database blocks
• 3 levels of validation, increasing in scope
• 0 - reads and validates block header
• 1 - level 0 plus validates record size
• 2 - level 1 plus checks if there is any record
  overlap
• Validates record blocks in one or all areas
• First error found in a block is reported in db.lg
  file then skip to the next record block

19
Database Health Check
dbtool report (cont.)

• Logical data corruption
• Option 3. Validate Records
• SYSTEM ERROR Cannot read field 51 from record,
  not enough fields. (450)
• Dump the record identified
• Option 4. Record Version Validation

20
Database Health Check
dbtool report (cont.)

• Option 6. Record fixup
• Known errors that can be fixed programmatically
• repairing small fragmented records for 10.1B
  64-bit recids
• record versioning vs schema versioning
• Add fixup options as we encounter fixable
  problems
• Not documented, run under advisement

21
Database Health Check
Index checks

• proutil dbname -C idxcheck NL -Bp
• Idxcheck report weekly
• Online (since 10.1B02)
• 4 levels of checks (since 10.1C)
• 1 - Validate physical consistency of index blocks
• 2 - Validate keys for each record
• 3 - Validate record for each key
• 4 - Validate key order (since 10.1C01)
• L - Lock tables during the check
• R - Reset error limit, current 500

22
Database Health Check
Locking in online idxcheck

• A shared schema lock aquired during the check,
  reading schema records with NO-LOCK
• If NL is used
• L - Lock tables not available
• indexes can be altered by other utilities
• L - Lock tables is used
• updates to the tables will be frozen as shared
  table locks on the tables as indexes are checked
• indexes can not be altered by other utilities
  through an admin lock.

23
Database Health Check
Idxcheck

• Status of online idxcheck process _UserStatus
• error limitation default is 500
• R - Reset error limit, current 10
• complete, estimated time remaining status while
• scanning blocks - total blocks below high water
  mark
• validating keys - total number of index blocks
• Schedule index fixup projectSYSTEM ERROR Index
  check found lterror-cntgt errors.(2805)

24
Log File Analysis with LogRead
What is it ?

• Log files (can) produce a lot of data
• Not always easy to analyse
• LogRead is a GUI utility for log files
• No CHUI version available
• View, manipulate, filter, sort, merge translate
• Written in ABL
• Extensible Add custom log handlers
• Not officially supported
• Available via PSDN

25
Log File Analysis with LogRead
What can LogReader do ?

• Loads log files from many sources versions
• Merges multiple logs
• Filtering noise from logs
• Localization
• Date formats, code pages
• Translation of PROMSGS
• Sorting and searching
• Timestamp adjustment
• Handlers

26
Log File Analysis with LogRead
LogReader considerations

• Unload un-needed log files
• otherwise content stays in memory
• Ensure the Source Target promsgs matches the
  log file version
• i.e. not .lg file v10 and promsgs v8
• Utilities are associated with the relevant
  handler
• database uses database handler

27
Demo Logging LogRead
28
Agenda
DBA 101 - How Healthy is Your Database Today?

• Physical Database Limits
• Database Health Check
• Runtime Memory And Block Checking
• The 'recovery' part of DR

29
Runtime Memory And Block Checking
What is it ?

• A means of protecting the database from hardware
  and software bugs
• Earlier detection (in memory) of data
  inconsistencies at a block level before disk
• Enabled through startup parameters
• Enabled/disabled online through
• promon 8 RD 8 Administrative Functions 8 8.
  Adjust consistency checks
• Available since 10.1B

30
Runtime Memory And Block Checking
Five levels of consistency checks

• Database level -DbCheck
• all index and record blocks (except LOB blocks)
• Area level -AreaCheck ltarea namegt
• all index and record blocks in the specified area
  (except LOB blocks)
• Index level -IndexCheck ltindex namegt
• all index blocks of the specified index
• Table level -TableCheck lttable namegt
• all record blocks of the specified table (except
  LOB blocks)
• Memory protection -MemCheck
• any potential memory violations in the buffer
  pool of all index and record blocks

31
Runtime Memory And Block Checking
Applying consistency checks

• Used on broker, single-user, proutil, roll
  forward
• -DbCheck
• Overrides AreaCheck, -IndexCheck, -TableCheck
• Consistency check applied like only -DbCheck
  was used, ignoring others
• Applies to the whole database
• -MemCheck
• Can be used with all the other options

32
Runtime Memory And Block Checking
Applying consistency checks (cont.)

• Each option can only be enabled once
• -AreaCheck ltarea namegt
• -IndexCheck ltindex namegt
• -TableCheck lttable namegt
• -MemCheck
• Four options can be enabled at the same time
• Isolate where the problem is suspected
• Example of startup parameters
• proserve dbname AreaCheck OrderArea
  IndexCheck Customer.CustNum TableCheck Item
  -MemCheck

33
Runtime Memory And Block Checking
When to use it ?

• If corruption is in doubt, enable!
• Current operation will stop when checks fail
• Extra messages written to the db.lg file
• Usr 5 Invalid Record Block Detected
• Usr 5 15 D RL_RMCHG (PL) adbkey 6/21504
  updctr 8 difLen -1 flags recnum 0 logOp 1
  recsz 179 newsz 200
• Usr 6 Invalid Index Block Detected
• Usr 6 268 D RL_CXINS (PL) adbkey 6/20768
  updctr 35 objDbkey 64 root 20768 offset 3 cs
  0 extracs 4 attr 1

34
Runtime Memory And Block Checking
When to use it ? (cont.)

• Performance impact
• Memory Check lt 1
• Physical checks lt 5
• Usage limitations
• None of the physical checks are available during
  crash recovery
• -MemCheck is always available
• None of these parameter can be used on OpenEdge
  Replication target database

35
Agenda
DBA 101 - How Healthy is Your Database Today?

• Physical Database Limits
• Database Health Check
• Runtime Memory And Block Checking
• The 'recovery' part of DR

36
The 'recovery' part of DR
Quotes from cases where going to backup is not
an option ..
"The hotspare host is only used to roll forward
ai's and is not powerful enough to run
production" Restoring 30 GB database to
production server would take about 4 hours due to
remote location of backups" "There was no
database to restore because the rewind tape
device was used after each operation, so only the
last db backup is on tape" "The index areas are
not included in the OS backups .. it takes too
long to idxbuild"
37
The 'recovery' part of DR
Checklist for RECOVERY

• Can our backup can be restored?
• Is time-to-restore acceptable downtime?
• Once restored, do we know where to start with
  application recovery?
• Is the production recovery document still valid?
• How often do we schedule a recovery run-through?

38
In Summary

• Health checks are an iterative process
• physical and logical
• Records per Block (rpb) are important
• even with Type II areas and (almost) no physical
  limits
• provided you care about performance
• Dont assume that if the application is presently
  running well, that a database is therefore
  healthy
• always check

39
For More Information, go to PSDN

• Best Practices for Records-Per-Block Settings
• Database Statistics Tool
• http//www.psdn.com/library/entry!default.jspa?ext
  ernalID51
• LogRead 1.0 Tool Overview (English and Spanish)
• http//www.psdn.com/library/entry.jspa?externalID
  1841
• LogRead Source code
• http//www.psdn.com/library/entry!default.jspa?ext
  ernalID349

40
Relevant Exchange Sessions

• OPS-2 OpenEdge Management in the Real World
• OPS-3 What's New in 10.1 RDBMS?
• OPS-4 Complete Database Disaster Recovery Plan!
• OPS-8 Alerts, Alarms, Pages and Harbingers of
  Trouble
• OPS-14 Effective OpenEdge Database Configuration
• OPS-15 What was Happening with My Database,
  AppServer, Operating System. . . Yesterday, Last
  Month, Last Year?

41
?
Questions
42
Thank You
43
(No Transcript)
44
Database HealthCheck
Database Statistics Report (another view..)
lt backup
HWM
HWM