Automatic SQL throttling

Applicable scenarios

Procedure

1. Log on to the DAS console.
2. In the left-side navigation pane, click Instance Monitoring.
3. Find the database instance that you want to manage and click the instance ID. The instance details page appears.
4. In the left-side navigation pane of the instance details page, click Autonomy Center.
5. In the upper-right corner of the Autonomy Center page, click Autonomy Service Settings.
   
6. On the Autonomous Function Settings tab of the Autonomous Function Management panel, enable the autonomy service. On the Optimization and Throttling tab, select Automatic Throttling and configure the parameters described in the following table to specify the conditions for triggering automatic throttling.
   

   Parameter	Description
   CPU Utilization	Specify a value that is greater than or equal to 70.
   Number of Active Sessions	Specify an integer that is greater than or equal to 16 when CPU Utilization and Number of Active Sessions are in the OR relationship. Specify an integer that is greater than or equal to 2 when CPU Utilization and Number of Active Sessions are in the AND relationship.
   Duration	Specify a value that is greater than or equal to 2. The value is measured in minutes.

   Note For example, you can specify the following trigger conditions: The CPU utilization exceeds 80%, the number of active sessions is greater than 64, and the abnormal situation lasts for more than 2 minutes. If these conditions are met within a specified throttling window, automatic SQL throttling is triggered, and the system starts to monitor database instance performance.

   • If the issue persists after throttling, the system automatically rolls back the throttling operation.
   • The throttling duration does not exceed the specified maximum throttling duration.
7. Click OK.
   Note After you enable the automatic SQL throttling feature, different types of database services respond differently to SQL statements that contain all SQL keywords.

   • For an ApsaraDB RDS for MySQL instance that runs MySQL 5.6 or MySQL 5.7 or a PolarDB for MySQL cluster that runs MySQL 5.6, the corresponding database system returns error code 1317 and the "query execution was interrupted" error message.
   • For an ApsaraDB RDS for MySQL instance that runs MySQL 8.0 or a PolarDB for MySQL cluster that runs MySQL 5.7 or MySQL 8.0, the SQL statements enter the Concurrency control waiting state. After the number of SQL statements that are in the Concurrency control waiting state exceeds the threshold specified by the ccl_max_waiting_count parameter if the version of your instance supports ccl_max_waiting_count, the corresponding database system returns the "Concurrency control waiting count exceed max waiting count" error message together with the following error code:
     • ERROR 7534 (HY000) for an ApsaraDB RDS for MySQL instance that runs MySQL 8.0
     • ERROR 3277 (HY000) for a PolarDB for MySQL cluster that runs MySQL 5.7
     • ERROR 7533 (HY000) for a PolarDB for MySQL cluster that runs MySQL 8.0

     If the value of ccl_max_waiting_count is the default value 0, all throttled SQL statements are in the Concurrency control waiting state and no error is returned. When DAS performs SQL throttling, DAS sets the ccl_max_waiting_count parameter to the default value that is defined in DAS if the value of ccl_max_waiting_count is 0 or directly uses the value of ccl_max_waiting_count if the value is greater than 0.

Use scenarios

• Anomaly detection

  DAS uses machine learning algorithms to train a model offline based on historical performance data of a database instance. Then, DAS uses this model to detect abnormal changes in metrics in real-time. The 24/7 anomaly detection feature is more efficient for database instances than threshold value-based anomaly detection methods.

• Root cause analysis
  DAS can subscribe to anomaly events in database instances and collect information about sessions that are created when anomalies occur. Then, DAS combines full request analysis and security audit to identify the root causes of the anomalies based on the statistics of performance metrics. In most cases, anomalies occur due to the following causes:

  • Blocking SQL statements

    DAS analyzes real-time sessions, lock waits, and running transactions to detect SQL statements that cause DDL changes, lock waits, and large transactions. DAS also checks the number of sessions that are created to execute SQL statements and the execution time of SQL statements. If an SQL statement causes a large number of sessions or requires a long execution time, DAS terminates the abnormal sessions instead of performing SQL throttling.

  • Resource-intensive SQL statements

    SQL statements of this type do not cause high concurrency. However, SQL statements of this type consume a large amount of CPU resources, I/O resources, or network resources. These statements are submitted on an ongoing basis.

  • Traffic-intensive SQL statements

    SQL statements of this type are executed in a proper manner. However, when a large number of SQL statements are executed at the same time, the SQL statements consume a large amount of database resources. This causes performance bottlenecks and exceptions, such as slow responses to queries of key-value pairs.

  • Others

    SQL statements of other types can also cause anomalies.

• Automatic throttling

  When DAS detects resource-intensive SQL statements and traffic-intensive SQL statements, DAS automatically extracts the characteristics of these statements. If you enable the automatic SQL throttling feature, DAS automatically throttles the abnormal SQL requests.

• Extraction of characteristics
  If DAS detects abnormal SQL statements on which throttling must be implemented, DAS extracts the characteristics of the SQL statements for precise throttling. This prevents the entire service from being compromised due to inaccurate extracted characteristics. In ideal scenarios, the extracted characteristics are unique and only requests that use the identified abnormal SQL statements are throttled. DAS supports two types of SQL throttling:

  • Template-based SQL throttling

    An SQL template is an SQL statement in which the parameters do not have specific values. If a large number of SQL statements that use a specific SQL template are executed at the same time, anomalies can occur regardless of parameter values. Therefore, DAS extracts only the characteristics of the SQL template. This throttling method applies to multiple scenarios. For example, throttling is implemented when traffic spikes occur or when a large number of SQL statements are submitted to query tables that are not indexed.

  • Statement-based SQL throttling

    If some of the SQL statements that use a specific SQL template cause anomalies but others do not, DAS extracts characteristics from the SQL template and the specific parameter values of the SQL statements that cause anomalies. This throttling method is suitable for scenarios in which data skew is detected.

  In template-based SQL throttling, DAS extracts template IDs from SQL statements that cause anomalies and throttles SQL requests based on the template IDs. A template ID can be an ID that is automatically generated by database middleware or an SQL hint that is added by developers to the SQL template. This facilitates precise throttling and ensures that SQL statements that use other templates are not throttled. However, template-based throttling cannot be used to throttle SQL statements that do not have a template ID. This includes SQL statements that are submitted in a CLI. These statements can still be executed.

  If SQL statements do not contain a template ID, you must extract characteristics from the SQL statements. SQL templates are generated based on SQL statements in the analysis process. In the following example, SQL Template 1 is generated based on SQL Statement 1, and SQL Template 2 is generated based on SQL Statement 2. If DAS throttles SQL requests based on SQL Template 1, the characteristics select id, name, age from students where name are extracted. If DAS throttles SQL requests based on these characteristics, SQL requests that use SQL statements with these characteristics are throttled regardless of how the requests are submitted. Even though some SQL requests use SQL statements that are not created based on this template, these requests can also be throttled. In this example, SQL Template 2 has all characteristics of SQL Template 1. In this case, DAS also throttles SQL statements that use SQL Template 2.

  /* SQL Statement 1 */
  select id,name,age from students where name='Bob';
  /* SQL Template 1 */
  select id,name,age from students where name = ?;
  /* SQL Statement 2 */
  select id,name,age from students where name='Bob' and sid='Unique ID';
  /* SQL Template 2 */
  select id,name,age from students where name=? and sid=?

• Automatic SQL optimization

  If DAS detects an abnormal SQL statement that can be optimized during root cause analysis, DAS throttles requests that use this SQL statement as an emergency response and optimizes the SQL statement. To optimize query performance, DAS automatically creates indexes for the tables to query.

• Monitoring and rollback

  After you enable automatic SQL throttling, DAS monitors the performance of a database. If the workloads of the database are not decreased or if the amount of traffic is not reduced as expected, DAS rolls back the throttling operation. Then, DAS performs root cause analysis again.

Related API operations

• EnableSqlConcurrencyControl
• DisableSqlConcurrencyControl
• DisableAllSqlConcurrencyControl
• GetRunningSqlConcurrencyControlRules
• GetSqlConcurrencyControlRulesHistory
• GetSqlConcurrencyControlKeywordsFromSqlText

Best practices

• Troubleshoot slow queries
• Troubleshoot high CPU utilization