Oceanbase V4诊断No memory or reach tenant memory limit

by

在之前的文档《故障诊断 Oceanbase “ORA-00600: internal error code, arguments: -4013, No memory or reach tenant memory limit” 特烦恼》记录过一个Oceanbase V3时的内存异常增加限制的案例，我们知道Oceanbase V4对一些View相比V3做了重构，这里记录几个V4中常用的SQL和案例。

1， 分析内存 列每个租户的TOP MEMUSED 

# sys 租户

obclient(root@sys)[oceanbase]> select mem.*,tenant_name,tenant_type,STATUS,TENANT_ROLE
 from (select * from (SELECT tenant_id,svr_ip,svr_port,ctx_name,mod_name,format(hold,0),used, row_number() over(partition by tenant_id,svr_ip order by used desc) rn  FROM v$ob_memory) where rn<=10) mem,dba_ob_tenants ots 
where mem.tenant_id=ots.tenant_id;
+-----------+---------------+----------+-------------------+-----------------+----------------+-----------+------+-------------+-------------+--------+-------------+
| tenant_id | svr_ip        | svr_port | ctx_name          | mod_name        | format(hold,0) | used      | rn   | tenant_name | tenant_type | STATUS | TENANT_ROLE |
+-----------+---------------+----------+-------------------+-----------------+----------------+-----------+------+-------------+-------------+--------+-------------+
|         1 | 172.20.22.215 |     2882 | PLAN_CACHE_CTX_ID | SqlPhyPlan      | 314,406,784    | 280744162 |    1 | sys         | SYS         | NORMAL | PRIMARY     |
|         1 | 172.20.22.215 |     2882 | MEMSTORE_CTX_ID   | Memstore        | 278,822,912    | 278695344 |    2 | sys         | SYS         | NORMAL | PRIMARY     |
|         1 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | MysqlRequesReco | 146,243,584    | 146019200 |    3 | sys         | SYS         | NORMAL | PRIMARY     |
|         1 | 172.20.22.215 |     2882 | CO_STACK          | CoStack         | 129,540,096    | 129299136 |    4 | sys         | SYS         | NORMAL | PRIMARY     |
|         1 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | CACHE_MAP_NODE  | 54,099,968     |  54073344 |    5 | sys         | SYS         | NORMAL | PRIMARY     |
|         1 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | LogGroupBuffer  | 41,963,520     |  41943040 |    6 | sys         | SYS         | NORMAL | PRIMARY     |
|         1 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | ASHListBuffer   | 31,477,760     |  31458304 |    7 | sys         | SYS         | NORMAL | PRIMARY     |
|         1 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | SqlPlanMon      | 31,580,160     |  29766912 |    8 | sys         | SYS         | NORMAL | PRIMARY     |
|         1 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | MinorMergeMgr   | 32,366,592     |  28770304 |    9 | sys         | SYS         | NORMAL | PRIMARY     |
|         1 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | ServerObjecPool | 22,532,096     |  22462464 |   10 | sys         | SYS         | NORMAL | PRIMARY     |
|      1033 | 172.20.22.215 |     2882 | CO_STACK          | CoStack         | 91,865,088     |  91694208 |    1 | META$1034   | META        | NORMAL | PRIMARY     |
|      1033 | 172.20.22.215 |     2882 | MEMSTORE_CTX_ID   | Memstore        | 54,099,968     |  54075216 |    2 | META$1034   | META        | NORMAL | PRIMARY     |
|      1033 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | LogGroupBuffer  | 41,963,520     |  41943040 |    3 | META$1034   | META        | NORMAL | PRIMARY     |
|      1033 | 172.20.22.215 |     2882 | PLAN_CACHE_CTX_ID | SqlPhyPlan      | 32,723,008     |  29733121 |    4 | META$1034   | META        | NORMAL | PRIMARY     |
|      1033 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | MysqlRequesReco | 20,324,352     |  20177472 |    5 | META$1034   | META        | NORMAL | PRIMARY     |
|      1033 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | OmtTenant       | 15,005,440     |  14876065 |    6 | META$1034   | META        | NORMAL | PRIMARY     |
|      1033 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | CACHE_MAP_NODE  | 14,565,376     |  14558208 |    7 | META$1034   | META        | NORMAL | PRIMARY     |
|      1033 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | MinorMergeMgr   | 15,851,520     |  14090240 |    8 | META$1034   | META        | NORMAL | PRIMARY     |
|      1033 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | FreeTbltStream  | 8,777,728      |   8760064 |    9 | META$1034   | META        | NORMAL | PRIMARY     |
|      1033 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | SqlDtlBuf       | 9,510,912      |   8523159 |   10 | META$1034   | META        | NORMAL | PRIMARY     |
|      1034 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | LogGroupBuffer  | 167,854,080    | 167772160 |    1 | orcldb      | USER        | NORMAL | PRIMARY     |
|      1034 | 172.20.22.215 |     2882 | CO_STACK          | CoStack         | 105,799,680    | 105602880 |    2 | orcldb      | USER        | NORMAL | PRIMARY     |
|      1034 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | MysqlRequesReco | 102,547,456    | 102344576 |    3 | orcldb      | USER        | NORMAL | PRIMARY     |
|      1034 | 172.20.22.215 |     2882 | PLAN_CACHE_CTX_ID | SqlPhyPlan      | 92,865,024     |  82623912 |    4 | orcldb      | USER        | NORMAL | PRIMARY     |
|      1034 | 172.20.22.215 |     2882 | TX_DATA_TABLE     | MEMTABLE_ARENA  | 55,709,440     |  55606528 |    5 | orcldb      | USER        | NORMAL | PRIMARY     |
|      1034 | 172.20.22.215 |     2882 | MEMSTORE_CTX_ID   | Memstore        | 47,857,664     |  47835768 |    6 | orcldb      | USER        | NORMAL | PRIMARY     |
|      1034 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | CACHE_MAP_NODE  | 31,211,520     |  31196160 |    7 | orcldb      | USER        | NORMAL | PRIMARY     |
|      1034 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | MinorMergeMgr   | 20,201,472     |  17956864 |    8 | orcldb      | USER        | NORMAL | PRIMARY     |
|      1034 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | SqlDtlBuf       | 18,874,368     |  16914176 |    9 | orcldb      | USER        | NORMAL | PRIMARY     |
|      1034 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | OmtTenant       | 15,005,440     |  14876065 |   10 | orcldb      | USER        | NORMAL | PRIMARY     |
|      1037 | 172.20.22.215 |     2882 | CO_STACK          | CoStack         | 95,477,760     |  95300160 |    1 | META$1038   | META        | NORMAL | PRIMARY     |
|      1037 | 172.20.22.215 |     2882 | MEMSTORE_CTX_ID   | Memstore        | 56,180,736     |  56155032 |    2 | META$1038   | META        | NORMAL | PRIMARY     |
|      1037 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | LogGroupBuffer  | 41,963,520     |  41943040 |    3 | META$1038   | META        | NORMAL | PRIMARY     |
|      1037 | 172.20.22.215 |     2882 | PLAN_CACHE_CTX_ID | SqlPhyPlan      | 38,865,664     |  35504847 |    4 | META$1038   | META        | NORMAL | PRIMARY     |
|      1037 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | MysqlRequesReco | 24,485,888     |  24336960 |    5 | META$1038   | META        | NORMAL | PRIMARY     |
|      1037 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | OmtTenant       | 15,005,440     |  14876065 |    6 | META$1038   | META        | NORMAL | PRIMARY     |
|      1037 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | CACHE_MAP_NODE  | 14,565,376     |  14558208 |    7 | META$1038   | META        | NORMAL | PRIMARY     |
|      1037 | 172.20.22.215 |     2882 | DEFAULT_CTX_ID    | MinorMergeMgr   | 15,335,424     |  13631488 |    8 | META$1038   | META        | NORMAL | PRIMARY     |

...

2, 分清租户

报错里一般会提示租户信息，如果是在业务租户中的DML SQL或query通常是在业务租户呢，但是如果是DDL或BACKUP系统操作，可能是SYS租户或业务对应的 meta租户。

登录系统租户，查询 DBA_OB_TENANTS 视图即可查看所有的租户。TENANT_TYPE 表示租户类型：SYS 为系统租户，META 为 Meta 租户，USER 为用户租户。租户 ID 为 1 的是系统租户。租户 ID 大于 1000 的租户中，偶数的是用户租户，奇数的是 Meta 租户，并且用户租户的租户 ID 比其对应 Meta 租户大 1。

3, 日志分析

oceanbase的日志较多，对于这个报错的查询关键字，如1002是租户ID

grep ‘malloc_allocator.*tenant: 1002’ observer.log*

4， 如果是SQL执行报错

查询相关的SQL运行时信息，如案例2，gv$ob_sql_audit中SQL执行耗时GET_PLAN_TIME的值较大，接近ELAPSED_TIME的时间，REQUEST_MEMORY_USED的内存值达到了90G。

select * from gv$ob_sql_audit

5, 如果是META 租户

登录META租户查询

select a.zone, a.SVR_IP,a.SVR_PORT, b.status,cpu_capacity,cpu_assigned_max,cpu_capacity-cpu_assigned_max as cpu_free,round(memory_limit /1024/1024/1024 ,2) as memory_total_gb,round((memory_limit-mem_capacity) /1024/1024/1024 ,2) as system_memory_gb,round(mem_assigned /1024/1024/1024 ,2) as mem_assigned_gb,round((mem_capacity-mem_assigned) /1024/1024/1024 ,2) as memory_free_gb,round(log_disk_capacity /1024/1024/1024 ,2) as log_disk_capacity_gb,round(log_disk_assigned /1024/1024/1024 ,2) as log_disk_assigned_gb,round((log_disk_capacity-log_disk_assigned) /1024/1024/1024 ,2) as log_disk_free_gb,round((data_disk_capacity /1024/1024/1024 ),2) as data_disk_gb,round((data_disk_in_use /1024/1024/1024 ),2) as data_disk_used_gb,round((data_disk_capacity-data_disk_in_use) /1024/1024/1024 ,2) as data_disk_free_gb from gv$ob_servers a join oceanbase.DBA_OB_SERVERS b on a.zone=b.zone\G;

已知问题

  • 1， SQL中有大量的in list

现象： SqlExecutor优化器模块占用内存大

原因： 内核bug

当前规避方式： 将in 优化关闭: alter system set _enable_in_range_optimization = 0;
风险：有些大in的场景可能会内存开销增加。

2. insert all into values 内存膨胀

现象： OBServer日志内存信息如下，DEFAULT_CTX_ID的内存占用高达近200G。

insert all into values 类型 SQL 后跟大量多行数据时，在硬解析时，内存膨胀，另外行数不固定，可能导致不能命中 plan_cache，出现并发硬解析时，同时占用内存就很大，导致租户内存被挤占。

规避方案

  1. 改写为insert into values 多行，控制500-1000条提交一次，避免一次插入条数调大。

影响版本

数据库版本：OceanBase 4.2.1.10

3， 备份失败或DDL失败

现象： DDL挂起，备份挂起， 《案例 Oceanbase备份失败 status一直处理”PENDING”》

原因：SYS租户配置的资源过低

规避方案： 增加SYS租户的配置，建议32G以上。

持续更新…

Leave a Comment