ApsaraDB RDS:Use the query governance feature

Limits

• The query governance feature is implemented by analyzing offline data the day after the transaction (T+1). For example, if you add the Optimization Not Required tag to an SQL template, the SQL template is removed from the SQL templates that need to be optimized on the next day.

• Tags added by the system cannot be changed or removed.

• For each database instance, DAS counts and analyzes only the top 200 slow queries that are most frequently executed. This indicates that the maximum number of slow SQL templates of a database instance is 200.

Terms

Procedure

1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

2. In the left-side navigation pane of the page that appears, choose Autonomy Services > Slow Query Logs. On the page that appears, click the Query Governance tab.

3. On the Query Governance tab, view the query governance results.

   • Query Governance: This section displays the query governance results after SQL statements are classified and tags are added to the SQL statements.

     Note

     The Failed SQL Executions parameter is used to collect statistics only if your RDS instance has DAS Professional Edition enabled.

   • Query governance trend: This section displays the trends of the query governance results in a specific time range.

   • Top Rankings: This section displays the Best-performing Instances and Worst-performing Instances charts.

     • Worst-performing Instances: shows the number of executions of SQL statements that cause slow queries on the RDS instance.

     • Best-performing Instances: shows the change in the number of executions of SQL statements that cause slow queries on the RDS instance. A negative value indicates the decrease in the number of executions of the SQL statements. A positive value indicates the increase in the number of executions of the SQL statements.

     We recommend that you handle the instance on which the largest number of slow queries are executed and the instance on which SQL statements require optimization at the earliest opportunity.

   • SQL to Be Optimized: You can specify filter conditions to search for the SQL statements that you want to optimize.

     Note

     You can specify the following filter conditions: database name, SQL keyword, rule tag, and username of a database account. The four filter conditions are evaluated by using the AND operator.

     • Separate multiple database names with commas (,). The database names are evaluated by using the OR operator.

     • Separate multiple SQL keywords with spaces. The SQL keywords are evaluated by using the AND operator.

     • Separate the usernames of multiple database accounts with commas (,). The usernames are evaluated by using the OR operator.

     • You can select multiple rule tags. The selected rules are evaluated by using the OR operator.

     • You can click Suggestions in the Actions column of the required SQL sample to view the suggestions on query governance.

     • You can click Add Tag in the Actions column of the required SQL sample to add a tag. For more information about tags, see the Tag table.

       You can also select multiple SQL statements to add a tag to these SQL statements at a time.

     • You can click Sample in the Actions column of the required SQL sample to view the log details.

     • You can click Trend in the Actions column of the required SQL sample to view the analysis details. For more information about how to analyze and manage slow queries, see Use the slow query log analysis feature for an ApsaraDB RDS for PostgreSQL instance.

     You can export and share the information about the SQL statements that need to be optimized. For more information, see Best practices.

   • Failed SQL: You can specify filter conditions to search for the SQL statements that you want to view.

     Note

     • The Failed SQL parameter is used to collect statistics only if your RDS instance has DAS Professional Edition enabled.

     • You can specify the database name and SQL keyword filter conditions. The filter conditions are evaluated by using the AND operator.

       • Separate multiple database names with commas (,). The database names are evaluated by using the OR operator.

       • Separate multiple SQL keywords with spaces. The SQL keywords are evaluated by using the AND operator.

     You can click Sample in the Actions column of the required SQL sample to view the details.

Best practices

• Use tags to identify the SQL statements that require optimization.

  The query governance feature classifies SQL statements that cause slow queries into the following categories: Optimization Not Required and Optimizable SQL. You can use tags to filter SQL statements and optimize SQL statements based on the severity levels that are specified by the tags. The following table describes the tags supported by DAS.

  ID	Name	Severity level	Immediate attention required	Description
  NEW_SQL	New Slow SQL	Critical	✔️	SQL templates that caused slow queries within the previous seven days.
  DAS_IGNORE	Ignored	Normal	➖	SQL templates to which DAS automatically adds the Optimization Not Required tag. These SQL templates include the SQL templates that start with the SHOW, CREATE, XA, COMMIT, ROLLBACK, SELECT SLEEP, or EXPLAIN statement.
  FUZZY_LIKE	LIKE Query	Normal	➖	SQL templates in which the LIKE operator is used to implement fuzzy matching. Indexes cannot be used in queries that contain the LIKE operator.
  HAS_EXPR	Expressions Contained	Normal	➖	SQL templates that contain calculation expressions. Indexes cannot be used on the involved columns.
  LARGE_IN_LIST	Large List Query	Normal	➖	SQL templates in which more than 200 elements are specified for the IN operator. Such SQL templates may be automatically generated by programs. Indexes cannot be used on the involved columns.
  SELECT_STAR	Full-column Query	Normal	➖	SQL templates in which one or more fields are specified in SELECT clauses, but no conditions are specified. If you do not specify conditions, resources are wasted for querying unnecessary data. This reduces query performance.
  INDEX_ADVISOR	Index Suggestions	Normal	✔️	SQL templates that are used to query data from tables that are not indexed. We recommend that you create and use indexes to accelerate SQL queries.
  COMPLEX_JOIN	Complex JOIN Query	Normal	➖	SQL templates in which the JOIN operator is used to join more than three tables. You cannot use the JOIN operator to join more than three tables. The data type of the specified fields in the tables that you want to join must be the same. Before you join multiple tables, make sure that you create indexes for the fields that you want to query in the tables.
  CROSS_DB	Cross-database Query	Normal	➖	SQL templates that are used to query data in different databases. After databases or tables are migrated from the original database instance to another database instance, cross-database queries may fail.
  SUBQUERY	Subqueries Included	Normal	➖	SQL templates that contain SQL statements for subqueries. We recommend that you rewrite the SQL templates and use the JOIN operator to make queries more efficient.
  DEEP_PAGING	Deep Paging	Critical	➖	SQL templates in which the LIMIT operator is used to implement the deep paging method. We recommend that you rewrite the SQL templates and use the JOIN operator instead of the LIMIT operator.
  WITHOUT_PREDICATE	No Predicates	Critical	✔️	SQL templates that do not contain predicates. Check whether your business requires full table scanning. If your business does not require full table scanning, rewrite the SQL templates.
  NULL_COMPARE	Null Match Error	Critical	➖	SQL templates in which the ISNULL() function is used to check for NULL values. You cannot use the ISNULL() function to find NULL values because NULL is returned if one of the values is NULL.
  COUNT_NOT_STAR	Invalid COUNT Syntax	Critical	➖	SQL templates that contain invalid COUNT syntax. Use COUNT(*) to query the number of data records. Do not use the COUNT (Column name) or COUNT (Constant) syntax. COUNT(*) is the standard syntax that is defined in SQL92 to query the number of rows. The COUNT(*) syntax counts the number of data records that include NULL values and can be executed on all types of databases. The COUNT (Column name) syntax does not count NULL values.
  LARGE_ROWS_EXAMINED	Excessive Scanned Rows	Normal	➖	SQL templates that are used to scan more than 50,000 rows on average. A larger number of rows consume a larger amount of database resources. This causes the SQL queries to execute slowly and affects other SQL queries. Note We recommend that you use more specific filter conditions in SQL statements.
  LARGE_ROWS_SENT	Excessive Returned Rows	Normal	➖	SQL templates that are used in queries for which more than 5,000 rows of data on average are returned. We recommend that you modify the SQL templates to reduce the number of returned rows.
  NO_ADVICE	No suggestion	Normal	➖	No suggestions are provided.
  PERIOD_SQL	Periodic	Normal	➖	SQL templates that are executed at a specific point in time on a daily basis.

  We recommend that you handle the SQL statements that require optimization at the earliest opportunity. Before the system analyzes slow queries, you can add one of the following tags to SQL statements that do not require optimization. This can help reduce the number of SQL statements that require optimization.

  ID	Name	Severity level	Description
  USER_IGNORE	No Need to Optimize	Normal	After you add this tag to an SQL template, the SQL template is not counted in the SQL templates that need to be optimized on the next day.
  DAS_IMPORTANT	High-priority SQL	Normal	Add this tag to SQL templates that are of high priority.
  DAS_NOT_IMPORTANT	Low-priority SQL	Normal	Add this tag to SQL templates that are of low priority.
  DAS_IN_PLAN	Optimize Later	Normal	Add this tag to SQL templates that are to be optimized later.

• Export data.

  • Click Export in the upper-right corner of the SQL Details section to export the data of the filtered SQL templates.

    Note

    The exported data is available for download for three days.

  • You can specify different conditions to filter SQL templates. For example, you can filter SQL templates based on databases or rule tags, create download tasks, and then assign different owners to optimize SQL templates based on databases or rule tags.

  • You can also select multiple SQL templates and export the data of the selected SQL templates.

• Share data.

  You can filter SQL templates and share the filtered SQL templates with the owners of the corresponding SQL templates. You can use one of the following methods to share data:

  • Select SQL templates and click Batch Sharing in the lower part of the section. The system generates a URL. On the page to which the URL directs, the information about the selected SQL templates is displayed. If you are granted the permissions on DAS, you can visit the page to view the information about the selected SQL templates.

  • Specify conditions to filter SQL templates and click Share to the right of Export. The system generates a URL. On the page to which the URL directs, the information about the filtered SQL templates is displayed. If you are granted the permissions on DAS, you can visit the page to view the information about all filtered SQL templates.