Common memory issues

ERROR 4030 (HY000): OB-4030:Over tenant memory limits

When you receive the foregoing error message, first check whether the memory occupied by the MemStore exceeds the specified threshold. If yes, check whether an excessive amount of data is being written into the MemStore and whether throttling is disabled. The error message is returned when a large amount of data is being written into the memstore and the minor compaction speed does not keep up with the data write speed. Run the following statement to check the memory status:

obclient> SELECT /*+ READ_CONSISTENCY(WEAK),query_timeout(100000000) */ TENANT_ID,IP,
           round(ACTIVE/1024/1024/1024,2)ACTIVE_GB,
           round(TOTAL/1024/1024/1024,2) TOTAL_GB,
           round(FREEZE_TRIGGER/1024/1024/1024,2) FREEZE_TRIGGER_GB,
           round(TOTAL/FREEZE_TRIGGER*100,2) percent_trigger,
           round(MEM_LIMIT/1024/1024/1024,2) MEM_LIMIT_GB 
FROM gv$memstore
WHERE tenant_id >1000 OR TENANT_ID=1
ORDER BY tenant_id,TOTAL_GB DESC;

The emergency workaround to this issue is to scale out the tenant memory. After the issue is temporarily solved, analyze the root cause. If the issue is caused because throttling is not implemented, enable throttling and roll back the tenant memory scale-out operation. If the issue is caused because the memory of the tenant failed to support the growth of business, configure a proper memory size for the tenant based on the minor compaction frequency. If this issue is not caused by a memstore overrun, run the following statement to determine the memory module on which an overrun occurs:

obclient> SELECT tenant_id,svr_ip,sum(hold) module_sum
FROM __all_virtual_memory_info
WHERE tenant_id>1000 AND hold<>0 AND
             mod_name NOT IN ( 'OB_KVSTORE_CACHE','OB_MEMSTORE')
GROUP BY tenant_id,svr_ip;

If the value of module_sum of a memory module is greater than the value of min_memory of the tenant, which is greater than the size of the MemStore of the tenant, it is considered an overrun has occurred on the memory module. If an overrun has occurred on a memory module, the memory for the individual memory modules needs to be adjusted by modifying, for example, ob_sql_work_area_percentage. If the memory size of the tenant is too small, scale out the memory size.

The hit rate of the plan cache is less than 90%.

The hit rate of the plan cache must reach at least 90% for an online transaction processing (OLTP) system. You can run the following statement to check the hit rate of the plan cache:

obclient> SELECT hit_count,executions,(hit_count/executions) as hit_ratio
FROM v$plan_cache_plan_stat
where (hit_count/executions) < 0.9;

SELECT hit_count,executions,(hit_count/executions) AS hit_ratio
FROM v$plan_cache_plan_stat
WHERE (hit_count/executions) < 0.9 AND executions > 1000;

Check for similar statements, for example, statements with a random number of parameters following in or not in, which consume a lot of memory unnecessarily. If such statements do not exist, the issue may be caused by insufficient memory resulted from a sudden increase in traffic or sessions. In this case, you need to scale out the memory of the tenant.

Error messages such as fail to alloc memory or allocate memory fail exist in the log.

The error message contains the tenant_id(tenant ID) and mod_id (memory module ID) information, with which you can obtain the information about the specific memory module by running the following statement:

obclient> SELECT * FROM __all_virtual_memory_info WHERE mod_id=xxx AND tenant_id = xxx

If a memory overrun occurs on the memory module, you may first need to adjust the memory size for the module, same as the solution to the first issue. If the memory of the tenant is too small, scale out the memory of the tenant.