Transcript Presentation - Progress Software

OPS-1: DBA 101 - How Healthy is Your
Database Today?
Ruanne Cluer
Libor Laubacher
Principal Tech Support Engineer
Principal Tech Support Engineer
Agenda
DBA 101 - How Healthy is Your Database Today?




2
Physical Database Limits
Database Health Check
Runtime Memory And Block Checking
The 'recovery' part of DR
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
3
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Physical Database Limits
10.1B+ physical limit changes (cont.)
 2 billion (2^32) 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: <areaNumber> extent <extentName> has
reached the <percentage> percent threshold for block
usage - current block hiwater <maxAreaBlock>. (13435)
4
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
5
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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)
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
6
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Physical Database Limits
Records per Block – type II area (10.1B+)
DB Maximum # of
BS
blocks
per
Storage Area
**
1
Maximum # of recids
32 rpb
64 rpb
128 rpb
256 rpb
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
17,592,186,044,416
35,184,372,088,832
8,796,093,022,208
** excluding threshold reservation
7
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Physical Database Limits
Records per Block setting
 Optimal space allocation
• Increases areas addressable space
• Reduces record fragmentation
Records per
(4K) Block
Head
(2*RB+20)
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
8
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Physical Database Limits
Records per Block – type II area (10.1B+)
 256 rpb is not a magic number
• Use case:
multiple 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
9
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Agenda
DBA 101 - How Healthy is Your Database Today?




10
Physical Database Limits
Database Health Check
Runtime Memory And Block Checking
The 'recovery' part of DR
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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 …
11
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
12
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Database Health Check
tabanalys report – Record Fragmentation
$
proutil <dbname> -C tabanalys > tabana.out
 Baseline record fragmentation factor > 30%
 Tables over 80% (Factor 1.80) very strong candidates
 Online defragmentation since 10.1A
Block 5
100 bytes
13
Block 6
Update
#1
+200
bytes
OPS-1: DBA 101 - How Healthy is Your Database Today?
Block 11
Update
#2
+200
bytes
+300
bytes
© 2008 Progress Software Corporation
Database Health Check
tabanalys report - Scatter Factor
 How far a table’s 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 D&L
• bigger scatter most likely indicates a need for D&L
– it is not the one and only metric
– need to know more about your data
• ignore scatter of small tables (< 1000 records)
 “Logical” scatter
14
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Database Health Check
tabanalys report (cont.)
Table
Records
Record Size(B)-Fragments----Scatter
Size Min Max Mean
Count Factor Factor
PUB.Company 1000000 29.2M 25
40
30 1000060
PUB.Customer 200000 582.4M 27 20063 3053 350901
1.0
1.2
2.0
4.1
Binary load: (type I)
PUB.Company 1000000 29.2M 25
40
30 1000000
PUB.Customer 200000 582.1M 27 20063 3053 299950
1.0
1.0
1.0
4.8
Binary load: (type II)
PUB.Company 1000000 29.2M 25
40
30 1000000
PUB.Customer 200000 582.1M 27 20063 3053 301922
1.0
1.0
1.0
1.0
15
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Database Health Check
Database Statistics Report
$
prostrct statistics <dbname> > 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
16
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Database Health Check
Database Statistics Report (cont.)
Files in Area:
/db101/dd_9.d1
/db101/dd_9.d2
/db101/dd_9.d3
Cust_Data
256,049,152
256,049,152
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
Database Block Usage Summary
Active
Data
Free
Empty
Extent
Total
blocks:
blocks:
blocks:
blocks:
blocks:
blocks:
182,862
173,883
8979
12,810
7
195,672
Backup: ~715 MB
0.51% MAXBLOCKS(33,554,432)
17
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
18
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
19
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
20
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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+)
•
•
•
•
•
•
21
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
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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.
22
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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 project
SYSTEM ERROR: Index check found <error-cnt>
errors.(2805)
23
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
24
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
25
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
26
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Demo: Logging & LogRead
27
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Agenda
DBA 101 - How Healthy is Your Database Today?




28
Physical Database Limits
Database Health Check
Runtime Memory And Block Checking
The 'recovery' part of DR
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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 R&D 8 Administrative Functions 8
“8. Adjust consistency checks”
 Available since 10.1B+
29
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Runtime Memory And Block Checking
Five levels of consistency checks:
 Database level “-DbCheck”
• all index and record blocks (except LOB blocks)
 Area level “-AreaCheck <area name>”
• all index and record blocks in the specified area (except LOB
blocks)
 Index level “-IndexCheck <index name>”
• all index blocks of the specified index
 Table level “-TableCheck <table name>”
• 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
30
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
31
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Runtime Memory And Block Checking
Applying consistency checks: (cont.)
 Each option can only be enabled once
•
•
•
•
“-AreaCheck <area name>”
“-IndexCheck <index name>”
“-TableCheck <table name>”
“-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
32
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
33
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Runtime Memory And Block Checking
When to use it ? (cont.)
 Performance impact
• Memory Check < 1%
• Physical checks < 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
34
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Agenda
DBA 101 - How Healthy is Your Database Today?




35
Physical Database Limits
Database Health Check
Runtime Memory And Block Checking
The 'recovery' part of DR
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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"
36
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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 runthrough?
37
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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
 Don’t assume that if the application is presently
running well, that a database is therefore healthy
• always check
38
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
For More Information, go to PSDN…
 Best Practices for Records-Per-Block Settings
 Database Statistics Tool
• http://www.psdn.com/library/entry!default.jspa?externalID=51
 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?externalID=349
39
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
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?
40
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Questions
41
OPS-1: DBA 101 - How Healthy is Your Database Today?
?
© 2008 Progress Software Corporation
Thank You
42
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
43
OPS-1: DBA 101 - How Healthy is Your Database Today?
© 2008 Progress Software Corporation
Database HealthCheck
Database Statistics Report (another view..)
Database
6
12
81
173883
Cust_Index
00
11
19
13492
Cust_Data
20%
16
160,129
0%
44
0
< backup
60%
40%
80%
Cust_Data
Cust_Index
Database
Empty
1600
11196
12810
Free
8511
336
8979
Data
160,129
13492
173883
OPS-1: DBA 101 - How Healthy is Your Database Today?
100%
© 2008 Progress Software Corporation