Common memory issues

Issue 1: What do I do when the tenant memory limit is exceeded?

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

When you encounter the preceding error, first check whether the MemStore memory limit is exceeded. If the MemStore memory limit is exceeded, check whether data is written excessively or throttling is not enabled. This error is reported when a large amount of data is written and the data minor compaction cannot keep up with the write speed. Run the following statement to view the current memory usage:

obclient> SELECT /*+ READ_CONSISTENCY(WEAK),query_timeout(100000000) */ TENANT_ID,SVR_IP,
           round(ACTIVE_SPAN/1024/1024/1024,2) ACTIVE_GB,
           round(MEMSTORE_USED/1024/1024/1024,2) TOTAL_GB,
           round(FREEZE_TRIGGER/1024/1024/1024,2) FREEZE_TRIGGER_GB,
           round(MEMSTORE_USED/FREEZE_TRIGGER*100,2) percent_trigger,
           round(MEMSTORE_LIMIT/1024/1024/1024,2) MEM_LIMIT_GB 
FROM oceanbase.GV$OB_MEMSTORE
WHERE tenant_id >1000 OR TENANT_ID=1
ORDER BY tenant_id,TOTAL_GB DESC;

For this issue, you can increase the tenant memory. For example, contact technical support to increase the memory. After the issue is resolved, analyze the cause. If throttling is not enabled, take corresponding measures and then roll back the tenant memory increase. If the tenant memory is insufficient for the business scale, set a reasonable tenant memory size based on the minor compaction frequency. If the MemStore memory limit is not exceeded, run the following statement to check which memory module is exceeded:

obclient> SELECT tenant_id, svr_ip, hold module_sum
FROM oceanbase.GV$OB_MEMORY
WHERE tenant_id>1000 AND hold<>0 AND
             CTX_NAME NOT IN ('KVSTORE_CACHE_ID','MEMSTORE_CTX_ID')
ORDER BY tenant_id, module_sum DESC;

The judgment standard for exceeding the memory module limit is module_sum > (tenant memory_size - tenant MemStore memory). If a module memory limit is exceeded, you may need to adjust the memory of the individual module. For example, you can modify the value of ob_sql_work_area_percentage. If the tenant memory is small, you can also increase the tenant memory.

Issue 2: What do I do when the plan cache hit rate is lower than 90%?

For OLTP systems, the plan cache hit rate should be no less than 90%. Run the following statement to view the plan cache hit rate:

obclient> SELECT hit_count,executions,(hit_count/executions) as hit_ratio
FROM V$OB_PLAN_CACHE_PLAN_STAT
WHERE (hit_count/executions) < 0.9;

obclient> SELECT hit_count,executions,(hit_count/executions) AS hit_ratio
FROM V$OB_PLAN_CACHE_PLAN_STAT
WHERE (hit_count/executions) < 0.9 AND executions > 1000;

Check whether there are similar statements, such as in or not in, followed by a random number of parameters, which leads to a large waste of memory; if not, memory shortage may be caused by a surge in business volume or the number of sessions. In this case, you can adjust the tenant memory size.

Issue 3: What do I do when the log contains fail to allocate memory or allocate memory fail information?

The log contains tenant_id (tenant ID) and context (memory module) information. You can run the following statement to view the specific memory module information:

obclient> SELECT * FROM oceanbase.GV$OB_MEMORY WHERE CTX_NAME=xxx AND tenant_id = xxx

As described in Issue 1, if a memory module limit is exceeded, you can first adjust the memory of the individual module. If the tenant memory is small, you can increase the tenant memory. For this issue, contact technical support for assistance.

Issue 4: What do I do when the memory limit for tenant 500 is exceeded?

tenant_id =500 indicates the internal tenant of OceanBase Database, which is referred to as Tenant 500. Generally, this error is frequently reported, indicating that the system memory is exhausted or the remaining memory on the server is fully preallocated, leaving no available memory for expansion.

To resolve this issue, check the memory usage. The memory usage of Tenant 500 is not statistics in the V$OB_MEMORY view, but you can query the oceanbase.GV$OB_MEMORY table for details. At the same time, check the memory usage in the system. If the system memory is exhausted, determine whether it is an occasional system issue. If not, and if it does not affect business operations, the system can release the memory on its own. If large-scale business operations or system process activities are using or preallocating memory, you can consider increasing the memory. For this operation, contact technical support for assistance.

SELECT * FROM oceanbase.GV$OB_MEMORY WHERE tenant_id=500;
//Command for checking system memory usage