Accumulated request queue of a tenant

OceanBase Database supports multi-tenant clusters and uses threads and coroutines to allocate and schedule CPU resources across tenants. If the CPU resources of a cluster become insufficient when the number of tenant requests increases or complex queries are executed, the request queue is accumulated, which results in prolonged SQL response time and slow execution. This topic describes the emergency procedure to eliminate the exception.

Emergency procedure

To relieve the accumulated request queue of a tenant in the cluster, you can perform the following operations:

1. Scale up the CPU resources of the tenant or switch the leader role to a lightly loaded server.

   We recommend that you scale up the CPU resources of the tenant by modifying the UNIT_CONFIG and UNIT_NUM parameters in the OceanBase Cloud Platform (OCP) console. If you use the CLI tool, perform the following steps:

   i. Execute the following SQL statement to confirm the unit configurations of the tenant.``

   Notice

   Replicas may use different resource pools. You need to separately modify the configurations of each replica.``

   SELECT a.tenant_name,a.tenant_id,b.name unit_config,c.name pool_name,b.max_cpu,b.min_cpu,
   round((b.max_memory/1024/1024/1024)) max_memory,round((b.min_memory/1024/1024/1024)) min_memory
   FROM
   __all_tenant a,
   __all_unit_config b,
   __all_resource_pool c
   WHERE a.tenant_id=c.tenant_id
   AND b.unit_config_id = c.unit_config_id
   ORDER BY a.tenant_id desc;
   

   ii. Execute the following SQL statement based on the obtained configurations.

   Notice

   As of OceanBase Database V3.2, you can modify only the CPU and memory configurations of a resource unit. The modifications to other I/O configurations, such as the disk and input/output operations per second (IOPS), do not take effect, and the default values are used.

   ALTER RESOURCE UNIT unit_config_name min_cpu=2,max_cpu=2, max_memory='4G',min_memory='4G', max_disk_size='500G',max_iops=10000,min_iops=10000,max_session_num=10000;
   

2. If the tenant has multiple units, you can scale out the cluster by adding OBServer nodes to the zone, and then modify the unit_num parameter. For more information, see Scaling in Basic database management.

3. If you find execution spikes for an SQL statement, consult with business staff and throttle the execution.

   1. Locate the SQL statement with execution spikes.

      You can use the SQL diagnosis feature in the OCP console. Filter SQL statements by the number of execution times to view the execution history of an SQL statement in a specified period.

   2. Perform SQL throttling.

      Insert the max_concurrent hint to the execution plan that is bound to the SQL statement to limit the SQL execution concurrency. Example:

      CREATE OUTLINE outline_name ON sql_id USING HINT /*+max_concurrent(1)*/;
      

      For more information, see "SQL statement" in SQL Reference (MySQL Mode) and "SQL statement" in SQL Reference (Oracle Mode).