12cR2多租户下隔离PDB磁盘I/O

最新推荐文章于 2022-04-07 22:07:22 发布

地_衣

最新推荐文章于 2022-04-07 22:07:22 发布

阅读量1k

点赞数

分类专栏： oracle

oracle 专栏收录该内容

46 篇文章 0 订阅

订阅专栏

Multitenant : Disk I/O (IOPS, MBPS) Resource Management for PDBs in Oracle Database 12c Release 2 (12.2)

In the previous release there was no easy way to control the amount of disk I/O used by an individual PDB. As a result a "noisy neighbour" could use up lots of disk I/O and impact the performance of other PDBs in the same instance. Oracle Database 12c Release 2 (12.2) allows you to control the amount of disk I/O used by a PDB, making consolidation more reliable.

在12.2之前很难控制单个PDB的磁盘I/O。这样在同一个实例下由于一个糟糕的PDB使用了大量的磁盘I/O而影响其他的PDB。在12.2中允许控制单个PDB最大可使用的磁盘I/O。

Multitenant : Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)

I/O Parameters (MAX_IOPS, MAX_MBPS)

I/O参数（ MAX_IOPS, MAX_MBPS ）

The following parameters can be set at the CDB or PDB level to throttle I/O at the PDB level.

下面两个参数可以在CDB或者PDB级别设置控制PDB级别的I/O阈值

MAX_IOPS : The maximum I/O operations per second for the PDB. Default "0". Values less that 100 IOPS are not recommended.
MAX_MBPS : The maximum megabytes of I/O per second for the PDB. Default "0". Values less that 25 MBPS are not recommended.

MAX_IOPS ：PDB每秒最大的I/O操作次数。默认是0，不推荐设置MAX_IOPS的值小于100.

MAX_MBPS ：PDB每秒最大的I/O带宽（MB）。默认是0，不推荐设置MAX_MBPS的值小于 25 MBPS。

Some things to consider about their usage are listed below.

The parameters are independent. You can use none, one or both.
When the parameters are set at the CDB level they become the default values used by all PDBs.
When they are set at the PDB level they override any default values.
If the values are "0" at both the CDB and PDB level there is no I/O throttling.
Critical I/Os necessary for normal function of the instance are not limited, but do count towards the total I/O as far as the limit is concerned, so it is possible for the I/O to temporarily exceed the limit.
The parameters are only available for the multitenant architecture.
This feature is not available for Exadata.
Throttling will result in a resource manager wait event called resmgr: I/O rate limit.

使用这两个参数，考虑如下情况：

1、这两个参数是独立的，可以设置一个、两个或者都不设置；

2、当在CDB级别设置时将变为所有PDBs的默认值；

3、当在PDB设置时将覆盖默认值；

4、在CDB和PDB中这两个值都是0，没有做I/O阈值限制；

5、某些常规函数的关键IO不会受到限制，但依然会计算到总IO中，所以，实际IO瞬时超过设定的阈值是有可能的

6、这两个参数只能在多租户环境下使用；

7、 Exadata无法使用该特性；

8、设置阈值会引起一个等待事件： resmgr: I/O rate limit

Setting I/O Parameters

设置I/O参数

The example below sets the MAX_IOPS and MAX_MBPS parameters at the CDB level, the default values for all PDBs.

该示例在CDB级别设置MAX_IOPS 和 MAX_MBPS，作为所有PDBs的默认值

CONN / AS SYSDBA

-- Set defaults.
ALTER SYSTEM SET max_iops=100 SCOPE=BOTH;
ALTER SYSTEM SET max_mbps=400 SCOPE=BOTH;

-- Remove defaults.
ALTER SYSTEM SET max_iops=0 SCOPE=BOTH;
ALTER SYSTEM SET max_mbps=0 SCOPE=BOTH;

The example below sets the MAX_IOPS and MAX_MBPS parameters at the PDB level

该示例在PDB级别设置MAX_IOPS 和 MAX_MBPS。

CONN / AS SYSDBA

ALTER SESSION SET CONTAINER = pdb1;

-- Set PDB-specific values.
ALTER SYSTEM SET max_iops=100 SCOPE=BOTH;
ALTER SYSTEM SET max_mbps=400 SCOPE=BOTH;

-- Remove PDB-specific values.
ALTER SYSTEM SET max_iops=0 SCOPE=BOTH;
ALTER SYSTEM SET max_mbps=0 SCOPE=BOTH;

Monitoring I/O Usage for PDBs

监控PDBs的I/O使用情况

Oracle now provides views to monitor the resource (CPU, I/O, parallel execution, memory) usage of PDBs. Each view contains similar information, but for different retention periods.

oracle提供了一些视图来监控PDB的资源使用（CPU,I/O,并行执行，内存），每个视图包含相同的信息，只是保留时间不同。

V$RSRCPDBMETRIC : A single row per PDB, holding the last of the 1 minute samples.
V$RSRCPDBMETRIC :一个PDB对应一行记录，只保留最近一分钟数据。
V$RSRCPDBMETRIC_HISTORY : 61 rows per PDB, holding the last 60 minutes worth of samples from the V$RSRCPDBMETRIC view.
V$RSRCPDBMETRIC_HISTORY :一个PDB对应61行记录，保留最近60分钟的数据
DBA_HIST_RSRC_PDB_METRIC : AWR snaphots, retained based on the AWR retention period.
DBA_HIST_RSRC_PDB_METRIC :AWR快照，保留时间基于AWR的保留时间

The following queries are examples of their usage.

如下示例：

CONN / AS SYSDBA

SET LINESIZE 180
COLUMN pdb_name FORMAT A10
COLUMN begin_time FORMAT A26
COLUMN end_time FORMAT A26
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF'; 

-- Last sample per PDB.
SELECT r.con_id,
       p.pdb_name,
       r.begin_time,
       r.end_time,
       r.iops,
       r.iombps,
       r.iops_throttle_exempt,
       r.iombps_throttle_exempt,
       r.avg_io_throttle 
FROM   v$rsrcpdbmetric r,
       cdb_pdbs p
WHERE  r.con_id = p.con_id
ORDER BY p.pdb_name;

-- Last hours samples for PDB1
SELECT r.con_id,
       p.pdb_name,
       r.begin_time,
       r.end_time,
       r.iops,
       r.iombps,
       r.iops_throttle_exempt,
       r.iombps_throttle_exempt,
       r.avg_io_throttle 
FROM   v$rsrcpdbmetric_history r,
       cdb_pdbs p
WHERE  r.con_id = p.con_id
AND    p.pdb_name = 'PDB1'
ORDER BY r.begin_time;

-- All AWR snapshot information for PDB1.
SELECT r.snap_id,
       r.con_id,
       p.pdb_name,
       r.begin_time,
       r.end_time,
       r.iops,
       r.iombps,
       r.iops_throttle_exempt,
       r.iombps_throttle_exempt,
       r.avg_io_throttle 
FROM   dba_hist_rsrc_pdb_metric r,
       cdb_pdbs p
WHERE  r.con_id = p.con_id
AND    p.pdb_name = 'PDB1'
ORDER BY r.begin_time;

来源： <https://oracle-base.com/articles/12c/multitenant-disk-iops-mdps-resource-management-for-pdbs-12cr2#monitoring-io-usage-for-pdbs>