Solving Database Crashes Due to Service Exceptions — Auto-SQL Throttling with Alibaba Cloud DAS

1) Background

2) Issues

2.1 Traffic Issues — Sudden Traffic Bursts

2.2 Data Issues — SQL Statements on Skewed Data

2.3 SQL Issues — Poor SQL Statements

3) Implementation

3.1 Overall Process

3.2 Performance Metric Collection

3.3 Exception Detection

3.4 Root Cause Analysis

  • Blocking SQL Statements: DAS analyzes database instance information, such as real-time sessions, lock waits, and ongoing transactions, to determine whether DDL changes, large transactions, or lock waits exist. Then DAS calculates the number of affected sessions and the execution time of these sessions. If the exception affects a relatively large number of sessions, or the affected sessions require a rather long time to execute, DAS kills abnormal sessions without throttling.
  • Resource-consuming SQL Statements or Poor SQL Statements: These SQL statements have a low degree of parallelism but consume massive amounts of CPU, I/O, or network resources, and are constantly committed.
  • Traffic-intensive SQL Statements: A large number of normal SQL statements execute at the same time in the database, causing a resource bottleneck in the database and causing the slow response to SQL queries on data stored in key-value format.
  • Others: This category includes all the other cases that are not covered by the preceding three types.

3.5 Auto-throttling

3.6 Keyword Selection

  • SQL Template-based Throttling: An SQL template contains text extracted from specific parameters of SQL statements. These SQL statements will certainly cause exceptions when a large number of them are executed in parallel, regardless of the specific parameter values. SQL template-based throttling is suitable for scenarios with traffic bursts and unindexed tables. In this case, DAS automatically throttles SQL statements that match keywords in the template.
  • SQL Text-based Throttling: This mode is suitable for scenarios with skewed data, where some SQL statements that match the template are executed normally, but some are not. In this case, the keywords must contain not only the information in the template but also specific parameter values.
/* SQL 文本1 */
select id,name,age from students where name='张三';
/* SQL 模板 1 */
select id,name,age from students where name = ?
/* SQL 文本2 */
select id,name,age from students where name='张三' and sid='唯一ID';
/*SQL 模板 2*/
select id,name,age from students where name=? and sid=?

3.7 Auto-optimization

3.8 Tracking and Rollback

4) Use

Original Source:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com