Transcript Presentation Title

Demystifying Exadata I/O
Resource Management (IORM)
Jim Czuprynski
Zero Defect Computing, Inc.
February 6, 2013
-1-
Copyright 2013 Zero Defect Computing, Inc.
My Credentials
•
•
•
•
•
30+ years of database-centric IT experience
Oracle DBA since 2001
Oracle 9i, 10g, 11g OCP
95+ articles on databasejournal.com and ioug.org
Teach core Oracle DBA courses (G/I+RAC, Exadata,
Performance Tuning, Data Guard)
• 2009: Oracle Education Partner Trainer of the Year
• Speaker at Oracle OpenWorld, IOUG COLLABORATE11,
and IOUG COLLABORATE13
• Oracle-centric blog (Generally, It Depends)
-2-
Copyright 2013 Zero Defect Computing, Inc.
Our Agenda
•
•
•
•
•
DBA 3.0: Consolidate or Perish!
Exadata Database I/O: A Refresher
IORM: The Missing Link in Resource Management
DBRM and IORM: Resource Management Concepts
How Exadata Implements IORM
• IORMPLANs and Directives
• I/O Load Balancing Alternatives
• Monitoring IORM “Pushback”
• IORM: Limits, Caveats, and Conclusions
• Q+A
-3-
Copyright 2013 Zero Defect Computing, Inc.
DBA 3.0: Consolidate or Perish!
You’ll just have to do
more with less.
Less is more.
– Ludwig Mies van der
Rohe, c.1950
- Our CIOs, 2013
Engineered systems are the
new 800-pound gorillas
“Testing in production” is still seen as blasphemous
…but DBAs cannot ignore the potential negative impacts
of I/O throughput & latency on application workloads
Resource consolidation is the new reality
Oracle 11g: Instance caging, DBRM I/O
management, and Exadata IORM
Oracle 12c: Built-in consolidation features
-4-
Copyright 2013 Zero Defect Computing, Inc.
Exadata Database I/O: A Quick Refresher *
User issues query:
SELECT
phone_nbr, cust_city
FROM sh.customers
WHERE cust_last_name
LIKE ‘Czuprynsk%’;
2
SmartScan-enabled
execution plan:
TABLE ACCESS STORAGE FULL
(SH.CUSTOMERS)
iDB command generated
and issued to Exadata
storage cells
3
1
3
MB
6
128
bytes
Result set containing
just rows and columns
needed is FETCHed
-5-
5
4
128
bytes
Result set built from
just rows and columns
needed to satisfy
query
* If it was only this simple!
Storage cells
determine which ASM
AUs answer the query
Copyright 2013 Zero Defect Computing, Inc.
Exadata Database I/O: The Conundrum of I/O Collisions
Production workloads
must take precedence …
PRODn
… while the QA team must test
the next application release …
DSS
OLTP
JCQA
… while application developers need at
least some I/O resources to unit test.
JCTEST
-6-
By the way – DSS workloads must defer to
OLTP workloads during peak business hours!
Copyright 2013 Zero Defect Computing, Inc.
IORM: Resource Management‘s “Missing Link”
• Extends intra-database Database Resource Management
(DBRM) features
• Categorizes and segregates I/O for individual databases as
well as different I/O workloads, e.g. :
• Inter-Database: CRM database needs 70% of all I/O resources during
peak user activity
• I/O Category: QA testing must not consume more than 20% of I/O
resources during peak user activity
• Each Exadata storage cell enforces these limits to prevent
I/O resource starvation
• Enables storage consolidation for multiple databases
regardless of their I/O consumption rates
-7-
Copyright 2013 Zero Defect Computing, Inc.
Database Resource Management (DBRM)
• Enables intra-database resource limitations for specific
application workloads
• Introduced in Oracle 8i (in primitive form)
• Since Oracle 10gR1, DBRM allows coupling at database
service, module, and/or action level
• Resource limits can be set for:
-8-
CPU
Elapsed time per call
Active Sessions
Application idle time
UNDO Pool
Maximum estimated execution time
Parallelism
… and several others
Copyright 2013 Zero Defect Computing, Inc.
DBRM: Intra-Database Example
Step 1: Create CATEGORY objects to expose database
application workloads to IORM resource limits
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_CATEGORY(
category => 'OLTP'
,comment => 'OLTP Operations');
DBMS_RESOURCE_MANAGER.CREATE_CATEGORY(
category => 'DSS'
,comment => 'Decision Support Operations');
. . .
-9-
Copyright 2013 Zero Defect Computing, Inc.
DBRM: Intra-Database Example
Step 2: Create new Resource Consumer Groups (RCGs),
attaching an appropriate CATEGORY
. . .
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
consumer_group => 'OLTP'
,comment => 'Decision Support Applications‘
,category => 'OLTP');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
consumer_group => 'DSS‘
,comment => 'Decision Support Applications‘
,category => 'DSS');
. . .
- 10 -
Copyright 2013 Zero Defect Computing, Inc.
DBRM: Intra-Database Example
Step 3: Build a Resource Plan and Resource Plan Directives to
allocate resource limits to RCGs
. . .
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
plan => ‘PEAKTIME'
,comment => 'Peak Time Resource Plan‘);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'PEAKTIME'
,group_or_subplan => 'DSS'
,comment => 'Peak Time - DSS Applications'
,mgmt_p1 => 30
,max_utilization_limit => 45);
. . .
- 11 -
Copyright 2013 Zero Defect Computing, Inc.
DBRM: Intra-Database Example
Step 4: Validate DBRM plan components, then commit these
changes if successfully validated
. . .
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'PEAKTIME'
,group_or_subplan => 'OLTP'
,comment => 'Peak Time - OLTP Applications'
,mgmt_p1 => 50
,max_utilization_limit => 75
);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
- 12 -
Copyright 2013 Zero Defect Computing, Inc.
IORM Plans: Plan Directives
• IORMPLAN directives offer two different ways to distribute
I/O resources:
• DBPLAN directives specify how to distribute IO workloads between
different databases
• CATPLAN directives specify how to distribute IO workloads between
different I/O categories
• Both directives can be specified
• Category imperatives take precedence over database
imperatives
• If no database plan has been specified, then I/O resources
are divided equally between all incoming database requests
- 13 -
Copyright 2013 Zero Defect Computing, Inc.
IORM Plans: Load Balancing Options
The objective directive determines how I/O load balancing will
be enforced on each cell:
• off: No IORM load balancing employed (default)
• low_latency: Minimizes disk latency, but throughput may
suffer (best for OLTP)
• high_throughput: Maximizes throughput, but disk latency
may suffer (best for DSS and DW)
• balanced: Balances disk latency and throughput
• auto: Automatic balancing of I/O requests based on active
category and database plans as workloads change
- 14 -
Copyright 2013 Zero Defect Computing, Inc.
Implementing IORM Plans: Database-Only vs. Category-Only
ALTER IORMPLAN objective=balanced Database-Only
,catplan=‘’ ,dbplan=(
(name=other, level=1, allocation=96, limit=100) –
,(name=jcqa,
level=1, allocation=3, limit=5) ,(name=jctest, level=1, allocation=1, limit=5) )
ALTER IORMPLAN objective=balanced Category-Only
,catplan=( (name=other, level=1, allocation=94) ,(name=oltp, level=1, allocation=4) ,(name=dss,
level=1, allocation=2) ) ,dbplan=‘’
- 15 -
Copyright 2013 Zero Defect Computing, Inc.
Implementing an IORM Plan: Category Plus Database
ALTER IORMPLAN objective=auto –
Combined
,catplan=( (name=other, level=1, allocation=94) ,(name=oltp, level=1, allocation=4) ,(name=dss,
level=1, allocation=2) ) –
,dbplan=(
(name=other, level=1, allocation=96, limit=100) –
,(name=jcqa,
level=1, allocation=3, limit=5) ,(name=jctest, level=1, allocation=1, limit=5) )
Just remember:
Category beats inter-database, and
inter-database beats intra-database
- 16 -
Copyright 2013 Zero Defect Computing, Inc.
How DBRM and IORM Interact
I
O
R
M
Category
Layer
InterDatabase
Layer
IntraDatabase
Layer
QA
(20%)
PROD
(80%)
60%
75%
25%
50%
80%
40%
I
O
R
M
QA
(20%)
PROD
(80%)
20%
50%
D
B
R
M
The ratio between intra-database allocations determines these!
42.0% 14.0%
IORM
Allocates:
- 17 -
O
L
T
P
D
S
S
8.4%
O
L
T
P
5.6%
19.2%
4.8%
3.0%
3.0%
D
S
S
O
L
T
P
D
S
S
O
L
T
P
D
S
S
I/O
Profile
Copyright 2013 Zero Defect Computing, Inc.
How Exadata Implements IORM
1
Compute
nodes send
I/O requests
to storage
cells
Storage Cell
CELLSRV
PROD
3
IORM
Plan
OLTP
DSS
.. others
IORM decides how
long each request for
physical I/O against
HDDs should be
enqueued based on
IORM plan directives
Background queues
QA
OLTP
2
Each I/O
request is
tagged by DB,
I/O type, and
RCG
- 18 -
4
DSS
.. others
Background queues
IORM
Metrics
IORM also gathers
performance metrics for
enqueued physical I/Os
for later evaluation
and/or alert thresholding
Copyright 2013 Zero Defect Computing, Inc.
Advanced IORM Features: Flash Cache and Flash Logging
Recent releases of Exadata software now permit IORM to
restrict access to Smart Flash Cache as well as Smart Flash
Logging features
ALTER IORMPLAN FlashCache (11.2.2.3)
dbplan=(
(name=other, level=1, allocation=96, limit=100 –
,flashCache=on, flashLog=off) –
,(name=jcqa,
level=1, allocation=3, limit=5
,flashCache=on, flashLog=off) –
,(name=jctest, level=1, allocation=1, limit=5
,flashCache=on, flashLog=on) –
FlashLog (11.2.2.4)
)
- 19 -
Copyright 2013 Zero Defect Computing, Inc.
Advanced IORM Features: Inter-Database Roles
Recent Exadata releases also permit IORM to restrict
databases to different I/O resource limits depending on the
database’s current Data Guard role (i.e., primary or standby)
ALTER IORMPLAN objective=balanced ,catplan=‘’ ,dbplan=( (name=other, level=1, allocation=96,
,(name=jcqa,
level=1, allocation=3,
,(name=jctest, level=1, allocation=1,
,(name=jcqa,
level=2, allocation=10,
,(name=jctest, level=2, allocation=90,
)
- 20 -
Roles
limit=100) –
role=primary)
role=primary)
role=standby)
role=standby)
-
Copyright 2013 Zero Defect Computing, Inc.
Monitoring IORM “Pushback”
Exadata provides metrics that report how hard IORM is pushing
back on I/O requests:
• CellCLI and DCLI scripted commands can interrogate the
pertinent IORM metrics
• CellCLI alerts allow storage cells to raise alerts if IORMspecific thresholds are breached
• Enterprise Manager 11gR1 Grid Control metrics and
thresholds extend these features
- 21 -
Copyright 2013 Zero Defect Computing, Inc.
Interrogating IORM Metrics
Use DCLI to run CellCLI queries across all Exadata storage
cells to capture IORM metrics in real time:
qr01cel01$> dcli -g cells "cellcli -e list metriccurrent attributes
name, metricobjectname, metrictype, metricvalue where name like
\'CT_IO_WT.*\' and metricobjectname NOT LIKE \'_.*\' "
qr01cel01$> dcli -g cells "cellcli -e list metriccurrent attributes
name, metricobjectname, metrictype, metricvalue where name like
\'DB_IO_WT.*\' and metricobjectname LIKE \'JC.*\'“
qr01cel01$> dcli -g cells "cellcli -e list metriccurrent attributes
name, metricobjectname, metrictype, metricvalue where name like
\'CG_IO_WT.*\' and metricobjectname LIKE \'JC.*\'“
- 22 -
Copyright 2013 Zero Defect Computing, Inc.
Interrogating IORM Metrics
Exadata Storage Cell metrics capture I/O activity in two
different sizes:
• Small (<= 128KB)
• Large (over 128KB)
Resource Consumer Group
qr01cel01:
qr01cel01:
qr01cel01:
. . .
qr01cel01:
qr01cel01:
. . .
- 23 -
CG_IO_WT_LG
JCQA.DSS
CG_IO_WT_LG
JCTEST.DSS
CG_IO_WT_LG_RQ JCTEST.DSS
Cumulative
Cumulative
Rate
15,574,128 ms
11,967,858 ms
4,155 ms/request
CG_IO_WT_SM
CG_IO_WT_SM
Cumulative
Cumulative
220,853 ms
350,780 ms
JCQA.DSS
JCTEST.DSS
Copyright 2013 Zero Defect Computing, Inc.
Interrogating IORM Metrics
IORM metrics capture I/O activity at different levels too:
Cumulative (since cell startup) and Rate (ms per I/O request)
Database
qr01cel02:
qr01cel02:
qr01cel02:
qr01cel02:
qr01cel02:
qr01cel02:
qr01cel02:
DB_IO_WT_LG
DB_IO_WT_LG
DB_IO_WT_LG_RQ
DB_IO_WT_LG_RQ
DB_IO_WT_SM
DB_IO_WT_SM
DB_IO_WT_SM_RQ
JCQA
JCTEST
JCQA
JCTEST
JCQA
JCTEST
JCQA
Cumulative
Cumulative
Rate
Rate
Cumulative
Cumulative
Rate
12,571,693 ms
348,621 ms
555 ms/request
193 ms/request
52,351,990 ms
40,712,962 ms
1,633 ms/request
Cumulative
Rate
Cumulative
Cumulative
Rate
17,878,385 ms
3,123 ms/request
302,483 ms
7,744 ms
284 ms/request
Category
qr01cel03:
qr01cel03:
qr01cel03:
qr01cel03:
qr01cel03:
- 24 -
CT_IO_WT_LG
CT_IO_WT_LG_RQ
CT_IO_WT_SM
CT_IO_WT_SM
CT_IO_WT_SM_RQ
DSS
DSS
DSS
OLTP
DSS
Copyright 2013 Zero Defect Computing, Inc.
Defining IORM Alert Thresholds
Metric thresholds are simple to define when monitoring
storage cells for unexpected or unacceptable levels of
IORM performance:
CREATE THRESHOLD ct_io_wt_sm.OLTP warning=100, critical=250, comparison='>=', occurrences=2, observation=1
CREATE THRESHOLD db_io_wt_sm_rq.JCQA –
warning= 750, critical=1500, comparison='>=', occurrences=2, observation=1
CREATE THRESHOLD cg_io_wt_lg_rq.JCTEST.DSS –
warning=250, critical=500, comparison='>=', occurrences=2, observation=1
- 25 -
Copyright 2013 Zero Defect Computing, Inc.
Detecting IORM Performance Alerts
Alerts are raised whenever an IORM threshold is violated:
CellCLI> list alerthistory where metricobjectname like '.*JC.*‘
70_1
2013-02-03T21:30:39-06:00
warning
"The warning
threshold for the following metric has been crossed. Metric Name
:
DB_IO_WT_SM_RQ Metric Description : Average IORM wait time per request for small
IO requests issued by a database Object Name
: JCQA Current Value
:
1,447.2 ms/request Threshold Value
: 750.0 ms/request “
72_1
2013-02-03T21:30:39-06:00
critical
"The critical
threshold for the following metric has been crossed. Metric Name
:
CG_IO_WT_LG_RQ Metric Description : Average IORM wait time per request for large
IO requests issued by a consumer group Object Name
: JCQA.DSS Current
Value
: 7,623.8 ms/request Threshold Value
: 500.0 ms/request “
73_1
2013-02-03T21:30:39-06:00
critical
"The critical
threshold for the following metric has been crossed. Metric Name
:
CG_IO_WT_LG_RQ Metric Description : Average IORM wait time per request for large
IO requests issued by a consumer group Object Name
: JCTEST.DSS Current
Value
: 9,665.5 ms/request Threshold Value
: 500.0 ms/request "
- 26 -
Copyright 2013 Zero Defect Computing, Inc.
Monitoring Via EM 11gR1 Grid Control
Metrics
… …and
by Database
by Category
……
by RCG
- 27 -
Copyright 2013 Zero Defect Computing, Inc.
IORM: Limits, Caveats, & Conclusions
As wonderful as IORM sounds, consider these best practices:
• Background processes and Recovery Manager (RMAN)
operations are exempt from IORM resource limits
• For best results, deploy exactly the same IORM Plan
directives across all storage cells
• Be sure to validate DBRM and IORM plan directives for
expected vs. actual behavior
• At worst, an IORM plan can be disabled (but not destroyed)
by setting its objective directive to OFF
- 28 -
Copyright 2013 Zero Defect Computing, Inc.
Thank You For Your Kind Attention!
If you have any questions or comments, feel free to:
 E-mail me at [email protected]
 Follow my blog (Generally, It Depends):
http://jimczuprynski.wordpress.com
 Follow me on Twitter (@jczuprynski)
 Connect with me on LinkedIn (Jim Czuprynski)
Note: Answers to post-presentation
questions appear on the next two slides.
- 29 -
Copyright 2013 Zero Defect Computing, Inc.
Question
Answer
Have there been any performance studies about the impact of turning on
IORM in a mixed workload Exadata environment? We could not turn it on
without a negative performance impact.
To answer this, we’d first need to know what “mixed workload” and
“negative performance impact” really means. We’d also need to see some
statistics, either from AWR reports or IORM metrics, as to what the
“negative performance impact” was exactly. In other words, simply turning
off IORM to solve a performance problem may actually not have solved the
actual performance problem … unless you can supply metrics to back that
up, of course!
Is IORM available only on Exadata?
Essentially, yes; it has no meaning for other SANs except for the Exadata
storage cells because it’s the CELLSRV process that decides how IORM
allocated HDD resources. I do not believe it is applicable on the Sun ZFS
Storage Appliance as of this release.
In DBRM can we use roles to give a default consumer group to a user like
service, module etc.?
You wouldn’t use roles; rather, you’d tag a user session with a particular
value for MODULE or ACTION attributes via
DBMS_APPLICATION_INFO.SET_MODULE. Service is determined
by the database service used when connecting to the database at the
listener.
Can we have multiple databases across all 8 nodes instead splitting them
into number of nodes like 4 for prod 2 for development and 2 for testing?
Certainly! Of course, it depends if you are isolating databases to particular
nodes because you’re implementing them in a RAC multi-node
environment. I’d need to know a bit more about your computing
environment’s “big picture” before I can accurately answer this question.
- 30 -
Copyright 2013 Zero Defect Computing, Inc.
Question
Answer
Can we have multiple databases across all 8 nodes instead splitting them
into number of nodes like 4 for prod 2 for development and 2 for testing?
Certainly! Of course, it depends if you are isolating databases to particular
nodes because you’re implementing them in a RAC multi-node
environment. I’d need to know a bit more about your computing
environment’s “big picture” before I can accurately answer this question.
Can we control the I/O in a single database, with different services in a
database? Specifically, can we control Number of I/O requests per sec for
particular type of workload in database. I think here we talked about
controlling KB/MB/GB I/O per second.
It is definitely possible to do this on a service-by-service basis for an intradatabase resource plan. The SWITCH_IO_MEGABYTES and
SWITCH_IO_REQS resource plan directives respectively tell DBRM to
switch a user session to a different Resource Consumer Group when the
specified limits for either the total amount of I/O (in megabytes) or the
number of individual I/O requests have been exceeded.
On real production systems, what is your observation with the levels to
which plans go for complex deployments?
When I’ve talked to colleagues and students over the past several years,
I’ve found that DBRM plans that went deeper than two (2) levels of
resource allocation tended to be overkill and much more difficult to test
adequately. This isn’t a recommended best practice … but I’ve found it
works best to keep these plans (and correspondingly, the IORM plans) as
simple as possible until you’ve fully tested all possible scenarios
adequately.
In a full X2-2, suppose RESOURCE_MANAGER_PLAN has not been set
on 2 Compute nodes out of 8. How does it affect IORM plans deployed on
all storage cells?
A.
First, remember that RESOURCE_MANAGER_PLAN is really set at the
database instance level, not on the nodes. That said, if there’s not a DBRM
plan activated for the instance, there’s no way to filter out particular
categories of I/O. As I understand IORM, a database which has no assigned
DBRM “tag” will simply fall into the OTHER category for the IORM
CATPLAN; if there’s no specific designation for the database in the
DBPLAN, then it likewise falls into the OTHER database IORM group and
must fend for itself along with other non-designated application workloads.
- 31 -
Copyright 2013 Zero Defect Computing, Inc.