Transcript Document

Tuning I/O
Copyright © 2006, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• Diagnose database I/O issues
• Describe the Stripe And Mirror Everything (SAME)
concept
• Explain the benefits of asynchronous I/O
• Choose appropriate I/O solutions
• Tune I/O using Automatic Storage Management
(ASM)
12-2
Copyright © 2006, Oracle. All rights reserved.
I/O Architecture
Oracle Database 10g includes three standard storage
options:
• File system
• Raw partitions
• Automatic Storage Management (ASM)
12-3
Copyright © 2006, Oracle. All rights reserved.
File System Characteristics
Certain characteristics are better for database use:
• Write-through-cache ability
• Write acknowledgement
• Security
• Journaling
• High performance
12-4
Copyright © 2006, Oracle. All rights reserved.
Raw Partitions
Raw partitions have been considered to be the highperformance solution.
• Raw reads and writes do not use the OS buffer
cache.
• Raw reads and writes can move larger buffers
than file system I/Os.
• Using raw partitions complicates administration.
Write
Read
Process
12-5
Disk file
Copyright © 2006, Oracle. All rights reserved.
I/O Modes
I/O can be written to disk in several ways by using
different system calls:
• Standard I/O
• Synchronous I/O
• Asynchronous I/O
Write
Process
12-6
Flush
Buffer cache
Copyright © 2006, Oracle. All rights reserved.
Disk file
Bandwidth Versus Size
I/O performance depends on bandwidth.
• Number of disks, not size
• Number of controllers
Disk controllers
Background
process
12-7
Copyright © 2006, Oracle. All rights reserved.
Stripe and Mirror Everything
•
•
•
12-8
All data files to access all available bandwidth
All database files to be on the same logical
devices
Highest performance configuration
Copyright © 2006, Oracle. All rights reserved.
Using RAID
Redundant Array of Inexpensive Devices (RAID) levels:
• Level 0
– Striped for performance
– No redundancy
•
Level 1
– Mirrored for safety
– Little performance benefit
•
Level 5
– Block level redundancy (rebuild algorithm)
– Improved read performance
– Additional write cost
12-9
Copyright © 2006, Oracle. All rights reserved.
RAID Cost Versus Benefits
RAID cost is measured in performance and reliability.
• RAID 0:
– Fast
– Loss of any device damages the array.
•
RAID 1:
– Safe and expensive
– Slight benefit in high-read environments
•
RAID 5
– Fast
– Safe with loss of any one device
– Possible high write cost
12-10
Copyright © 2006, Oracle. All rights reserved.
Should I Use RAID 1 or RAID 5?
RAID 1 (Mirroring)
•
•
RAID 5 (Parity)
Recommended by Oracle
Most demanding applications
Pros
• Best redundancy
• Best performance
• Low recovery overhead
Cons
• Requires higher capacity
12-12
•
DSS and moderate OLTP
Pros
• Requires less capacity
Cons
• Less redundancy
• Less performance
• High recovery overhead
Copyright © 2006, Oracle. All rights reserved.
Diagnostics
Indicators of I/O issues:
• Top waits are reads and writes plus:
–
–
–
–
Buffer busy waits
Write complete waits
DB file parallel writes
Enqueue waits
•
File I/O Statistics section shows high waits and
AVG Buffer Wait time higher than average on
certain files.
Note: On a well-performing system, the top events are
likely to be CPU time, db file scattered read, and db file
sequential read.
12-13
Copyright © 2006, Oracle. All rights reserved.
Database I/O Tuning
•
Configuring storage for a database depends on
many variables:
– Which data to put on which disk; complicated by
vendor-configured logical units (LUNs)
– DB application workloads: OLTP, DSS, batch versus
online
– Trade-offs between available options
– Ongoing tuning: changes in workloads
– Expanding or contracting your database
12-14
Copyright © 2006, Oracle. All rights reserved.
What Is Automatic Storage Management?
Application
ASM:
• Is a portable and highDatabase
performance cluster file system
File
• Manages Oracle database files
system
ASM
• Distributes data across disks
Volume
to balance load
manager
• Provides integrated mirroring
Operating system
across disks
• Solves many storage
management challenges
• Encapsulates the SAME methodology
12-15
Copyright © 2006, Oracle. All rights reserved.
ASM: Key Features and Benefits
•
•
•
•
•
•
•
•
Stripes files rather than logical volumes
Online disk reconfiguration and dynamic
rebalancing
Adjustable rebalancing speed
Provides redundancy on a file basis
Supports only Oracle database files
Database cluster file system with performance of
raw I/O usable on all storage platforms
Automatic database file management
No more hot spots: eliminates manual I/O tuning
.
12-16
..
. ..
............................... ...
.
.................
..
......
......
.. ..
.. .. ..
Copyright © 2006, Oracle. All rights reserved.
..
...
......
....
.. ..
.
.. ..
..
...
......
....
.... ..
.
. ..
How Many Disk Groups per Database
•
Two disk groups are recommended:
– Leverage maximum of LUNs
– Backup for each other
– Lower performance may be
used for FRA (or inner tracks)
•
Data DG
FRA DG
ERP DB
CRM DB
HR DB
Exceptions:
– Additional disk groups for different capacity or
performance characteristics
– Different ILM storage tiers
12-18
Copyright © 2006, Oracle. All rights reserved.
Database Storage Consolidation
•
Shared storage across several databases
– RAC and single-instance can use the same ASM
instance.
•
Benefits:
– Simplified and centralized management
– Higher storage utilization
– Higher performance
Payroll
GL
Payroll and GL
…
…
…
10  50 GB
10  50 GB
10  100 GB
12-19
Copyright © 2006, Oracle. All rights reserved.
Which RAID Configuration
for Best Availability?
A.
B.
C.
D.
ASM mirroring
Hardware RAID 1 (mirroring)
Hardware RAID 5 (Parity Protection)
Both ASM mirroring and hardware RAID
Answer: Depends on business requirement and budget
(cost, availability, performance, and utilization)
ASM leverages hardware RAID.
12-20
Copyright © 2006, Oracle. All rights reserved.
ASM Mirroring Guidelines
•
•
•
12-21
Best choice for low-cost storage
Enables Extended Clustering solutions
No hardware mirroring
Copyright © 2006, Oracle. All rights reserved.
ASM Striping Granularity
COARSE
FINE
1MB
AU
12-22
Copyright © 2006, Oracle. All rights reserved.
What Type of Striping Works Best?
A.
B.
C.
D.
ASM striping only (no RAID 0)
RAID 0 and ASM striping
Use LVM
No striping
Answer: A and B
ASM and RAID striping are complementary.
12-23
Copyright © 2006, Oracle. All rights reserved.
ASM Striping Only
Pros
•
•
•
•
Drives evenly distributed for Data & FRA
Higher bandwidth
Allows small incremental growth (73 GB)
No drive contention
Data DG
Oracle DB size: 1 TB
Storage configuration:
8  arrays with
16  73 GB
12  73 GB disks per array
LUNs
RAID 1
1 TB
Cons
•
Not well balanced across
all disks
•
LUN size limited to disk size
12-24
Copyright © 2006, Oracle. All rights reserved.
FRA DG
2 TB
32  73 GB
LUNs
Hardware RAID Striped LUNs
Pros
•
Fastest region for Data DG
•
Balanced data distribution
•
Fewer LUNs to manage while max
spindles
Data DG
Oracle DB size: 1 TB
Storage configuration:
8  arrays with
4  250 GB
12  73 GB disks per array
LUNs
RAID 0+1
1 TB
Cons
•
Large incremental growth
•
Data and FRA “contention”
12-25
Copyright © 2006, Oracle. All rights reserved.
FRA DG
2 TB
4  500 GB
LUNs
ASM Guidelines
•
•
Use external RAID protection when possible.
Create logical units (LUNs) using:
– Outside half of disk drives for highest performance
– Small disk, high rpm (for example, 73 GB/15k rpm)
•
•
•
12-26
Use LUNs with the same performance
characteristics.
Use LUNs with the same capacity.
Maximize the number of spindles in your disk
group.
Copyright © 2006, Oracle. All rights reserved.
ASM Instance Initialization Parameters
•
•
ASM instances have static memory needs.
Using default SGA sizing parameters should be
enough for most configurations: Add 500 KB to
the shared pool per additional disk group after the
first five.
INSTANCE_TYPE = ASM
DB_UNIQUE_NAME = +ASM
ASM_POWER_LIMIT = 1
ASM_DISKSTRING = '/dev/rdsk/*s2', '/dev/rdsk/c1*'
ASM_DISKGROUPS = dgroupA, dgroupB
PROCESSES = 25 + 15*<#DB inst using ASM for their storage>
12-27
Copyright © 2006, Oracle. All rights reserved.
Dynamic Performance Views
V$ASM_TEMPLATE
V$ASM_CLIENT
V$ASM_DISKGROUP
Disk group A
Disk group B
V$ASM_FILE
V$ASM_ALIAS
Storage system
V$ASM_DISK
V$ASM_OPERATION
12-28
Copyright © 2006, Oracle. All rights reserved.
Monitoring Long-Running Operations by
Using V$ASM_OPERATION
Column
GROUP_NUMBER Disk group
12-30
Description
OPERATION
Type of operation: REBAL
STATE
State of operation: WAIT or RUN
POWER
Power requested for this operation
ACTUAL
Power allocated to this operation
SOFAR
Number of allocation units moved so far
EST_WORK
Estimated number of remaining allocation units
EST_RATE
Estimated number of allocation units moved
per minute
EST_MINUTES
Estimated amount of time (in minutes) for
operation termination
Copyright © 2006, Oracle. All rights reserved.
ASM Instance Performance Diagnostics
SELECT
event, total_waits t_wait,
total_timeouts t_timeout,
time_waited t_waittm,
average_wait a_waittm, wait_class
FROM
V$SYSTEM_EVENT
WHERE
wait_class <> 'Idle' and time_waited > 0
ORDER BY 4 DESC;
EVENT
WAIT TOUT WAITT
AVG CLASS
------------------------------ ------ ----- ----- ------- ------ASM mount : wait for heartbeat
1
1
439 438.85 Admin…
kfk: async disk IO
578
0
377
.65 SystI/O
log write(odd)
7
3
296
42.33 Other
rdbms ipc reply
37
1
259
7.01 Other
log write(even)
8
2
197
24.58 Other
SQL*Net message to client
139249
0
103
0 Network
os thread startup
9
0
79
8.77 Conc…
buffer write wait
1
0
60
60.31 Other
DBFG waiting for reply
16
0
1
.04 Other
12-31
Copyright © 2006, Oracle. All rights reserved.
ASM Performance Page
12-32
Copyright © 2006, Oracle. All rights reserved.
Database Instance Parameter Changes
•
Add the following to SHARED_POOL_SIZE:
(DB_SPACE/100+2)*#_External_Red
(DB_SPACE/50+4)*#_Normal_Red
(DB_SPACE/33+6)*#_High_Red
OR
OR
SELECT d+l+t DB_SPACE
FROM (SELECT SUM(bytes)/(1024*1024*1024) d
FROM V$DATAFILE),
(SELECT SUM(bytes)/(1024*1024*1024) l
FROM V$LOGFILE a, V$LOG b WHERE a.group#=b.group#),
(SELECT SUM(bytes)/(1024*1024*1024) t
FROM V$TEMPFILE WHERE status='ONLINE');
•
12-33
Add at least 16 to PROCESSES.
Copyright © 2006, Oracle. All rights reserved.
ASM Disk Metadata Requirements
•
For empty disk groups:
– For normal and high redundancy:
15 + (2 * #_disks) + (126 * #_ASM_insts)
– For external redundancy:
5 + (2 * #_disks) + (42 * #_ASM_insts)
•
For each file:
– High redundancy: Add 3 MB if file size is greater
than 20 MB plus 3 MB for every additional 42 GB.
– Normal redundancy: Add 3 MB if file size is greater
than 30 MB plus 3 MB for every additional 64 GB.
– External redundancy: Add 1 MB if file size is greater
than 60 MB plus 1 MB for every additional 128 GB.
12-34
Copyright © 2006, Oracle. All rights reserved.
ASM Scalability
ASM imposes the following limits:
• 63 disk groups
• 10,000 ASM disks
• 4 petabyte per ASM disk
• 40 exabyte of storage
• 1 million files per disk group
• Maximum file size:
– External redundancy: 35 TB
– Normal redundancy: 5.8 TB
– High redundancy: 3.9 TB
12-35
Copyright © 2006, Oracle. All rights reserved.
Practice Overview:
Tune I/O: A Demonstration
This practice covers the following topics:
• View the symptoms and waits on a single-disk
system
• View the symptoms and waits on a multidisk
system using SAME
• View the symptoms and waits on a multidisk ASM
system
12-36
Copyright © 2006, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Diagnose database I/O issues
• Describe the Stripe And Mirror Everything (SAME)
concept
• Explain the benefits of asynchronous I/O
• Choose appropriate I/O solutions
• Tune I/O using Automatic Storage Management
(ASM)
12-37
Copyright © 2006, Oracle. All rights reserved.