Leaving Time-Consuming and Troublesome SQL Optimization to DAS

1) Challenges

When it comes to the diagnosis and optimization capabilities, we naturally think of two questions:

  • 2) Are the capabilities comprehensive?

Challenge 1 — How can we select a reliable optimization recommendation algorithm to generate reliable suggestions?

In the field of SQL diagnosis and optimization, two types of optimization algorithms are commonly used: the rule-based and the cost model-based algorithms. Because of its simplicity and ease of implementation, rule-based recommendation is widely used in many products and services nowadays. It is perfect for databases that lack the WHAT-IF kernel capability, for example, MySQL databases. However, this recommendation algorithm is inflexible and thus cannot ensure the recommendation quality. Let’s have a look at the example about recommending indexes for the following SQL query:

SELECT * 
FROM t1
WHERE time_created >= '2017-11-25'
AND consuming_time > 1000
ORDER BY consuming_time DESC
IX1(time_created)
IX2(time_created, consuming_time)
IX3(consuming_time)
IX4(consuming_time, time_created)
  • Lack of statistical information: Evaluating candidate optimization plan cost is essentially calculating the execution plan cost, which cannot be done without statistical information.

Challenge 2 — How can we ensure sufficient SQL compatibility?

The method that the SQL diagnosis and optimization service uses to ensure SQL compatibility (which involves SQL parsing and SQL semantic verification) directly affects the comprehensiveness of the service and the success rate of diagnosis. Either incompatibility or insufficient compatibility is an issue.

Challenge 3 — How can we build a capability-based test set with sufficient coverage?

Building SQL diagnosis and optimization capabilities have long been a challenging topic. The challenges lie not only in how to incorporate expert knowledge about database optimization but also how to build a comprehensive test-case library to verify the core capabilities. The test-case library is like a ruler that measures the capabilities and the driving force that accelerates the capacity building process. Therefore, an accurate test-case library with sufficient coverage is essential for capability building throughout the process.

a)schema设计:如表、已有索引、约束等;
b)各类统计信息数据;
c)环境参数等等。

Challenge 4 — How can we build diagnosis and optimization capabilities for a large number of SQL statements?

The SQL diagnosis and optimization service is expected to serve millions of database instances in the cloud. Its online service capabilities also face great challenges, such as service-oriented division of complex computing services, horizontal scaling of computing services, maximum parallelism, concurrency control in a distributed resource access environment, effective scheduling based on different priorities, and peak traffic buffering.

2) Capability Building

To address the aforementioned challenges, we built an SQL diagnosis and optimization engine and a capability test set for SQL diagnosis optimization. This section describes the core technical architecture of this engine, and how we built the test set.

2.1 Core Technical Architecture

Figure 1. Core technical architecture of the SQL diagnosis and optimization engine
  • Candidate index generation: The engine generates multiple candidate index combinations based on the syntax tree formed during SQL parsing and verification.
  • Cost evaluation: The engine uses a built-in optimizer independent of the database kernel to retrieve database statistics and stores the statistics in the engine’s cache. The built-in optimizer calculates the costs, based on statistics. It evaluates the cost of each index and SQL rewriting method, then selects the optimal index or SQL rewriting method based on the costs.
  • Index merging and selection: The input of the engine can be one or more query statements or all query statements of the entire database instance. During index recommendation for multiple query statements, indexes recommended for different query statements may be identical or similar to each other or have the same prefixes. In some cases, physical indexes identical or similar to the recommended ones may already exist or share the same prefixes with them.

2.2 Building a Capability-based Test Set

As mentioned earlier, our objective is to build a capability-based test set with sufficient coverage. We would use it as a ruler to measure the capabilities, and a driving force to accelerate the capability building process. In this process, we have built a development model centered on the test case system, as shown in Figure 2.

Figure 2. Test case system

3) Practices

Before the official release of DAS, the DAS SQL diagnosis and optimization service had been running stably in Alibaba Group for more than three years, diagnosing about 50,000 SQL statements on average every day. It effectively optimizes SQL statements for Alibaba Group’s business applications and mainly applies to the following scenarios:

4) Conclusion

We will post a series of articles on a weekly basis to introduce features of Alibaba Cloud DAS in detail, such as AutoScale, exception detection, auto-SQL optimization, workload-based SQL review, intelligent stress testing, and intelligent parameter tuning. Please stay tuned.

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