Database Autonomy Service:[Notice] Optimization of the template algorithm for slow queries

Background information

Database Autonomy Service (DAS) provides the template algorithm for slow queries. The template algorithm first formulates an SQL template by replacing variable values in an original SQL statement with placeholders. Then, the template algorithm encrypts the SQL template to generate an SQL hash. For example, the original SQL statement is: select name, age, score from study where age > 20 and score > 10.

1. After variable values are replaced, the following SQL template is generated: SQL_TEMPLATE = select name, age, score from study where age > $0 and score > $1.

2. Then, the SQL template is encrypted to generate the following SQL hash: 03d4d020ddcdfbda9399bf068934aa54.

3. After minute-level aggregation is performed on the SQL hash, the performance statistics on this type of SQL statement can be obtained.

Benefits

Optimization of the template algorithm for slow queries provides the following benefits:

• Improves efficiency and reduces computing resource consumption.

• Optimizes the template algorithm for SQL statements of the same type. Only one SQL template is generated for SQL statements of the same type.

  • For example, the following original SQL statements are used:

    INSERT INTO study (name, age, score) VALUES ('jam', 10, 90)
    INSERT INTO study (name, age, score) VALUES ('jam', 10, 90),('sam', 11, 80)

  • Before optimization, the following SQL templates are generated:

    INSERT INTO study (name, age, score) VALUES ($0, $1, $2)
    INSERT INTO study (name, age, score) VALUES ($0, $1, $2),($3, $4, $5)

  • After optimization, the following SQL template is generated. The optimization prevents a large number of duplicate SQL templates. In this example, the two SQL templates obtained before optimization are essentially INSERT statements of the same type.

    INSERT INTO study (name, age, score) VALUES (?, ?, ?)

• Generates SQL templates for DDL statements in a coarse-grained manner. For example, SQL templates generated for DDL statements in MySQL are classified into the following categories:

  "CREATE DATABASE ?", "CREATE TABLE ?", "CREATE INDEX ?", "CREATE VIEW ?", "ALTER DATABASE ?", "ALTER TABLE ?", "DROP DATABASE ?", "DROP TABLE ?", "DROP INDEX ?", "DROP VIEW ?", "TRUNCATE TABLE ?", "RENAME TABLE ?", "RENAME DATABASE ?"

• Provides adequate slow query samples. Before optimization, the system samples at least one original slow SQL statement within 10 minutes. After optimization, the system samples each original slow SQL statement for each template. This facilitates troubleshooting.

Effective date

Starting December 13, 2023, the optimized template algorithm is used in Alibaba Cloud regions in phases.

Effective scope and impact

Effective scope

The following database engines support the optimization: ApsaraDB RDS for MySQL, ApsaraDB RDS for PostgreSQL, ApsaraDB RDS for SQL Server, ApsaraDB RDS for MariaDB, and PolarDB for MySQL.

Impact

When you call the DescribeSlowLogs or DescribeSlowLogRecords operation after the optimization, the value of the SQLHash parameter corresponding to the SQLText parameter changes. For example, the value of the SQLHash parameter corresponding to the select * from study statement changes from 1ea57614450625240886f90fd99f90b8 to 19d4d020ddcdfbda9399bf068934aa54 after the optimization. The SQLHash parameter indicates the unique identifier of an SQL statement, and the SQLText parameter indicates the specified SQL statement.

Related API operations