Tenant request queue backlog

In an OceanBase cluster, threads and coroutines are used to allocate and schedule CPU resources across multiple tenants. When the request volume from tenants increases or complex query processing occurs, insufficient CPU resources can result in a backlog of queued requests, ultimately leading to longer SQL response time and slower processing speed. This topic provides information on how to handle this issue in emergency situations.

Emergency handling procedure

1. Expand CPU resources for the tenants or switch the leader role to a server with lower load.

   To expand CPU resources for a tenant, you are advised to modify the UNIT_CONFIG and UNIT_NUM parameters through OCP. If you use the command line, perform the following sub-steps:

   i. Execute the following SQL statement to confirm the unit_config used by the current tenant.

   Notice

   Multiple replicas may use different resource pools, so you need to modify the configurations of each replica separately.

   SELECT a.tenant_name,a.tenant_id,b.name unit_config,c.name pool_name,b.max_cpu,b.min_cpu
   FROM
   OCEANBASE.DBA_OB_TENANTS a,
   OCEANBASE.DBA_OB_UNIT_CONFIGS b,
   OCEANBASE.DBA_OB_RESOURCE_POOLS 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 disk and IOPS, are temporarily ineffective and can be left as default.

   ALTER RESOURCE UNIT unit_config_name min_cpu=2,max_cpu=2,memory_size ='2G',max_iops=10000,min_iops=10000;
   

2. For tenants with multiple units, you can scale out the cluster by adding OBServer nodes to the zone and increasing unit_num.

3. If you find execution time spikes for an SQL statement, perform throttling after confirming with business staff.

   1. Identify SQL statements with execution time spikes.

      You can use the SQL Diagnostics feature in OCP. 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.

      Add the max_concurrent hint to the SQL statement's execution plan to limit the SQL query concurrency. Here is an example:

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