Apply throttling to an OceanBase cluster

This topic describes how to apply throttling to an OceanBase cluster. The traffic of an OceanBase cluster is mainly generated by SQL statement execution in tenants. You can throttle the traffic of a cluster by controlling the SQL execution speed.

Background

The traffic of an OceanBase cluster is mainly generated by SQL statement execution in tenants. The excessively fast SQL processing may cause the following problems:

• The data in the active memory cannot be promptly frozen.

• Fast write operations overwhelm the memory in a short period.

You can limit the number of concurrent SQL executions to reduce the speed of SQL processing, thereby throttling the traffic of the OceanBase cluster. This avoids the preceding problems when resources are insufficient or the business traffic is large.

Procedure

1. Find the SQL statements that are frequently executed in the OceanBase cluster.

   1. Log on to the OCP console.

   2. On the Tenants page, find the tenant of the cluster in the Tenants list and click the tenant name.

      The Overview page automatically appears.

   3. Click SQL Diagnosis , and then click the TopSQL tab.

   4. Specify a time range as needed, find the most executed SQL statements and record their text and IDs.

2. Add the concurrency limitation hint to these SQL statements.

   1. Log on to the database where the SQL statement is executed.

   2. Add the hint index /*+max_concurrent(1)*/ to the SQL statement. This hint index can limit the number of concurrent execution of the SQL statement to 1, thereby restricting the SQL execution speed. For more information, see Bind an index. You can also execute the following statement to apply throttling:

      # otl_idx indicates the index name that you must specify. SQLID indicates the ID of the SQL statement. 
      CREATE OUTLINE otl_idx_c2 ON "SQLID" 
           USING HINT  
      /*+max_concurrent(1)*/ 
       ;
      
      # Example:
      create outline  otl_name  on "ED570339F2C856BA96008A29EDF04C74" using hint  
      /*+max_concurrent(1)*/  
      ;
      

      The preceding statement means that the number of concurrent executions of the SQL statement whose ID is "ED570339F2C856BA96008A29EDF04C74" on a single OBServer is limited to 1.

   3. Repeat steps 2.a - 2.b to limit the concurrent execution of SQL statements recorded in Step 1.