行业知识
Oracle-PDB资源限制
前言:
在使用CDB容器数据库时,由于某些PDB出现高并发或者慢SQL,可能会出现PDB资源恶意争用的情况,最终导致一个PDB耗尽全部CDB资源。所以,为了避免PDB出现资源恶意争用,我们需要对PDB的资源,尤其是内存、CPU、IO、并发进行限制,以避免出现一个PDB耗尽全部CDB资源的情况。
资源限制维度:
| 资源 | 限制方法与参数 |
| CPU | 1 CPU_COUNT 2 DBMS_RESOURCE_MANAGER |
| 内存 | 1 SGA(SGA_TARGET+SGA_MIN_SIZE+DB_CACHE_SIZE+SHARED_POOL_SIZE) 2 PGA(PGA_AGGREGATE_LIMIT+PGA_AGGREGATE_TARGET) |
| IO | 1 MAX_IOP 2 SMAX_MBPS |
| 连接数 | pdb目前无法限制单个pdb的连接数,processes无法在pdb里面单独设置 |
| 并行服务进程 | 1 PARALLEL_MAX_SERVERS+PARALLEL_SERVERS_TARGET 2 DBMS_RESOURCE_MANAGER |
CPU资源限制(CPU_COUNT):
1 通过在pdb里面设置cpu_count,限制单个pdb的cpu使用个数,如果pdb里面没有设置,则默认pdb的cpu_count等于根容器CDB$ROOT的cpu_count
2 CDB$ROOT的默认cpu_count为服务器cpu逻辑数量
3 设置pdb1的cpu_count为1,限制最多使用1个逻辑cpu
[oracle@rac19a ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 22 13:30:00 2022Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> show parameter cpu_countNAME TYPE VALUE------------------------------------ ----------- ------------------------------cpu_count integer 2SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB3 READ WRITE NO 4 PDB1 READ WRITE NO 5 PDB2 READ WRITE NO 7 PDB4 READ WRITE NOSQL> alter session set container=pdb1;Session altered.SQL> alter system set cpu_count=1;System altered.SQL> show parameter cpu_countNAME TYPE VALUE------------------------------------ ----------- ------------------------------cpu_count integer 1SQL> SQL> set linesize 400set pagesize 400col name for a50col value for a50col dbname for a10select a.con_id,nvl(b.name,'CDB$ROOT') as dbname,a.name,a.value from v$system_parameter a,v$pdbs b where a.con_id=b.con_id(+) and a.name ='cpu_count'order by a.con_id;SQL> CON_ID DBNAME NAME VALUE---------- ---------- -------------------------------------------------- -------------------------------------------------- 0 CDB$ROOT cpu_count 2 4 PDB1 cpu_count 1
CPU资源限制(DBMS_RESOURCE_MANAGER):
1 通过DBMS_RESOURCE_MANAGER进行限制,DBMS_RESOURCE_MANAGER可以创建不同的资源限制组(利用率、配额、并行不同),通过将不同PDB设置在不到的组里面,从而限制特定 PDB 的 CPU 使用率,并行执行服务器的数量,内存使用量(exadata)
2 DBMS_RESOURCE_MANAGER操作步骤
1. 创建初始化区域In the CDB:exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();2. 创建CDB资源计划BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(plan => 'cdb_plan',comment => 'CDB resource plan for cdb');END;/3. 使用CREATE_CDB_PROFILE_DIRECTIVE创建pdb资源限制策略,这里创建3个等级a,b,c,对不同的pdb进行分组#plan:所属CDB资源计划,填上一步创建的CDB资源计划即可#profile pdb资源策略名称#shares:资源配额,a,b,c的shares分别对应(3,2,1),所以a的资源配额占比为50%[3/(3+2+1)]#cpu限制:最高使用cdb的cpu百分比#并行服务限制:最高使用cdb的并行服务进程百分比---创建A策略BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(plan => 'cdb_plan',profile => 'A',shares => 3,utilization_limit => 100,parallel_server_limit => 100);END;/---创建B策略BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(plan => 'cdb_plan',profile => 'B',shares => 2,utilization_limit => 40,parallel_server_limit => 40);END;/---创建C策略BEGINDBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(plan => 'cdb_plan',profile => 'C',shares => 1,utilization_limit => 20,parallel_server_limit => 20);END;/4 验证初始化区域exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();5 提交初始化区域exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();6 CDB设置资源计划(In the CDB)ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'cdb_plan' scope=both;7 在pdb层面设置不同的资源限制组alter session set container=PDB1;alter system set db_performance_profile='A' scope=spfile;alter session set container=PDB2;alter system set db_performance_profile='B' scope=spfile;alter session set container=PDB3;alter system set db_performance_profile='C' scope=spfile;8 重启pdb生效alter pluggable database all close immediate;alter pluggable database all open;9 检查pdb资源限制配置col name for a30col value for a30 set linesize 400alter session set container=CDB$ROOT;select inst_id, name, con_id, value, ispdb_modifiable from gv$system_parameter2 where name = 'db_performance_profile' order by 1,2,3,4; INST_ID NAME CON_ID VALUE ISPDB---------- ------------------------------ ---------- ------------------------------ ----- 1 db_performance_profile 0 TRUE 1 db_performance_profile 2 TRUE 1 db_performance_profile 3 C TRUE 1 db_performance_profile 4 A TRUE 1 db_performance_profile 5 B TRUE 1 db_performance_profile 7 TRUEalter session set container=CDB$ROOT;select p.name, shares, utilization_limit, profile,INSTANCE_CAGING,CPU_MANAGED,PARALLEL_SERVER_LIMIT from v$rsrc_plan r, v$pdbs p where r.con_id = p.con_id;NAME SHARES UTILIZATION_LIMIT PROFILE INS CPU PARALLEL_SERVER_LIMIT--------------- ---------- ----------------- ------------ --- --- ---------------------PDB$SEED 1 OFF ONPDB3 1 20 C OFF ON 20PDB1 3 A OFF ONPDB2 2 40 B OFF ON 40PDB4 1 OFF ON#PDB1 因为UTILIZATION_LIMIT和PARALLEL_SERVER_LIMIT 都是100%,所以没有限制,查询结果为空
内存资源限制SGA:
1 PDB的SGA限制主要通过参数SGA_MIN_SIZE限制SGA内存下限以及通过参数SGA_TARGET限制SGA内存上限
2 通过sga_target参数限制单个PDB的最大SGA大小,当前root容器的sga设置为1104M
SQL> show parameter sgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------allow_group_access_to_sga boolean FALSElock_sga boolean FALSEpre_page_sga boolean TRUEsga_max_size big integer 1104Msga_min_size big integer 0sga_target big integer 1104Munified_audit_sga_queue_size integer 1048576
3 将pdb1 的sga最大大小限制为800M
---pdb 默认sga_target=0SQL> alter session set container=pdb1;Session altered.SQL> show parameter sgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------sga_max_size big integer 1104Msga_min_size big integer 0sga_target big integer 0---将sga_target设置为800MSQL> alter system set sga_target=800M;System altered.SQL> show parameter sgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------allow_group_access_to_sga boolean FALSElock_sga boolean FALSEpre_page_sga boolean TRUEsga_max_size big integer 1104Msga_min_size big integer 0sga_target big integer 500Munified_audit_sga_queue_size integer 1048576
4 通过sga_min_size参数限制单个PDB的最小SGA大小
5 将pdb1 的sga最小值限制为400M,注意:sga_min_size alter session set container=pdb1;Session altered.SQL> alter system set sga_min_size=400M;System altered.SQL> show parameter sgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------allow_group_access_to_sga boolean FALSElock_sga boolean FALSEpre_page_sga boolean TRUEsga_max_size big integer 1104Msga_min_size big integer 400Msga_target big integer 800Munified_audit_sga_queue_size integer 1048576
6 此外,还可以进一步对pdb的db_cache_size以及shared_pool_size最小值进行限制,注意:db_cache_size or shared_pool_size alter system set db_cache_size=400M;System altered.SQL> alter system set shared_pool_size=200M;System altered.SQL> SQL> show parameter db_cacheNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_cache_advice string ONdb_cache_size big integer 400MSQL> show parameter sharedNAME TYPE VALUE------------------------------------ ----------- ------------------------------hi_shared_memory_address integer 0max_shared_servers integershared_memory_address integer 0shared_pool_reserved_size big integer 18454937shared_pool_size big integer 200Mshared_server_sessions integershared_servers integer 1
内存资源限制PGA:
1 PDB的PGA限制主要通过参数pga_aggregate_target以及pga_aggregate_limit限PGA内存的上限
pga_aggregate_target在PDB的设置需要遵循以下规则:
1 小于或等于CDB级别设置的PGA_AGGREGATE_TARGET
2 小于或等于CDB级别设置的50% PGA_AGGREGATE_LIMIT
3 小于或等于PDB级别设置的50% PGA_AGGREGATE_LIMIT
PGA_AGGREGATE_LIMIT在PDB的设置需要遵循以下规则:
1 小于或等于CDB级别设置的PGA_AGGREGATE_LIMIT
2 大于或等于两倍PDB级别设置的PGA_AGGREGATE_TARGET
2 限制单个PDB的pga大小,当前CDB根容器pga_aggregate_limit为2G,pga_aggregate_target为1G
SQL> alter session set container=pdb1;Session altered.SQL> show parameter pgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------pga_aggregate_limit big integer 2Gpga_aggregate_target big integer 1GSQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 600M SCOPE = BOTH;System altered.SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET =300M SCOPE = BOTH;System altered.SQL> show parameter pgaNAME TYPE VALUE------------------------------------ ----------- ------------------------------pga_aggregate_limit big integer 600Mpga_aggregate_target big integer 300M
3 可以通过检查监控PDB的内存使用情况
COLUMN PDB_NAME FORMAT A10SELECT r.CON_ID, p.PDB_NAME, r.SGA_BYTES/1024/1024, r.PGA_BYTES/1024/1024, r.BUFFER_CACHE_BYTES/1024/1024, r.SHARED_POOL_BYTES/1024/1024 FROM V$RSRCPDBMETRIC r, CDB_PDBS p WHERE r.CON_ID = p.CON_ID;
IO限制:
1 IO限制是Oracle12.2之后推出的新功能,通过参数MAX_IOPS 以及max_mbps限制数据库每秒的IO数量以及吞吐量
2 MAX_IOPS:每秒的IOPS数量,默认为0
3 MAX_MBPS:每秒的IO吞吐量(MB),默认为0
4 通过在PDB里面设置,可以限制PDB的IOPS以及MBPS
SQL> alter session set container=pdb1;Session altered.SQL> ALTER SYSTEM SET MAX_IOPS = 1000 SCOPE = BOTH;System altered.SQL> ALTER SYSTEM SET MAX_MBPS = 50 SCOPE = BOTH;System altered.SQL> show parameter MAX_NAME TYPE VALUE------------------------------------ ----------- ------------------------------max_iops integer 1000max_mbps integer 50
5 PDB监控IO使用情况
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_throttleFROM v$rsrcpdbmetric r,cdb_pdbs pWHERE r.con_id = p.con_idORDER BY p.pdb_name;
并行服务进程限制(PARALLEL参数):
1 并行服务进程通过PARALLEL_MAX_SERVERS与PARALLEL_SERVERS_TARGET进行PDB的限制
2 PARALLEL_MAX_SERVERS:用于控制数据库最大的并行进程处理数量,默认值为PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5
3 PARALLEL_SERVERS_TARGET:用于控制单个执行语句的并行最大数量,默认值为
For a CDB:
Equal to the PARALLEL_MAX_SERVERS value for the CDB.
For a PDB or non-CDB:
PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2
4 在PDB里面并行服务进程数量,当前根容器parallel_max_servers为40,parallel_servers_target为40
SQL> alter session set container=pdb1;Session altered.SQL> alter system set parallel_max_servers=16;System altered.SQL> alter system set parallel_servers_target=8;System altered.SQL> SQL> SQL> show parameter parallel_NAME TYPE VALUE------------------------------------ ----------- ------------------------------parallel_max_servers integer 16parallel_min_degree string 1parallel_min_percent integer 0parallel_min_servers integer 4parallel_min_time_threshold string AUTOparallel_servers_target integer 8parallel_threads_per_cpu integer 1recovery_parallelism integer 0SQL>
并行服务进程限制(DBMS_RESOURCE_MANAGER):
并行服务进程通过DBMS_RESOURCE_MANAGER进行限制的方法参考CPU资源限制(DBMS_RESOURCE_MANAGER)章节的内容。
CREATE_CDB_PROFILE_DIRECTIVE存储过程对parallel_server_limit的设置就是对并行服务的限制;
总结: