Klustron

Key Takeaway

An incorrect execution plan can bring down a database. Early detection of such risk is an important indicator of a database system's self-diagnostic capability.

In this article, we will discuss the execution plan. The execution plan is generated during the optimizer phase of the database and is used to guide the execution of statements during the executor phase.

The principle followed by the optimizer in choosing an execution plan is to select the one with the minimum cost.

There are two crucial decisions in this process: index selection and algorithm selection.

Let's take an example of a query on a single table.

The table is created with the following statement:

create table t(
id int primary key, 
c int, 
...
Other fields,
...
index(c))

This table has a primary key and an index on column "c". Suppose we want to execute the query "select id from t order by id."

Those who have used MySQL can immediately see that in most cases, this query will choose the primary key "id" for execution.

However, for the optimizer, "evaluation" and "selection" are essential actions.

The semantics of this query are straightforward. The cost of executing the statement includes "scanning the data" and "sorting."

When there are two indexes in a table, there are two options available:

Use the primary key index (id). Since InnoDB is a clustered index, selecting the primary key (id) requires reading all columns of each row. As the primary key index is already organized in ascending order based on id, no additional sorting is needed. The id values of each row can be directly retrieved and returned.
Use the index on column c. This index only contains columns c and id, resulting in reading fewer data per row. However, since the data is organized in ascending order based on c, according to the semantics of the "ORDER BY id" clause, the retrieved id values need to be sorted.

The optimizer evaluates the cost of these two options and selects the one with the lowest cost to generate the execution plan for the statement.

In most cases, the cost of sorting is higher than the cost of scanning, so option 1 is preferred. In extreme cases where the size of each row is large (i.e., when there are many "other fields" columns), the index on column c may be chosen due to the high cost of reading row data.

Let's explain the logic of the optimizer in generating execution plans with a simple example. When it comes to table joins, the choice of the driving table and join algorithm needs to be considered, and an overall assessment of the costs is made.

Sometimes, users may express that a particular execution plan is "not optimal." So, what are the criteria for evaluating the quality of an execution plan?

From the perspective of the optimizer, the execution plan with the lowest cost is considered optimal. However, from the user's perspective, the ultimate metric is the execution time of the statement.

If a statement uses execution plan A, but in reality, execution plan B has a shorter execution time, we can say that the optimizer has produced a "poor" execution plan.

The challenge is that evaluating the quality of an execution plan based on a single execution of a single statement is not sufficient. So, the question arises: How do we judge the goodness or badness of an execution plan?

Here are some commonly used methods:

Explain: This command is a built-in feature of MySQL, and experienced DBAs can estimate whether there is optimization potential based on the output of EXPLAIN. However, the issue with EXPLAIN is that it requires manual intervention beforehand and relies on experience to make evaluations. It cannot detect cases where the execution plan goes wrong in advance.
Slowlog: When a statement is logged in the slow query log, it includes information about the number of scanned rows. If a statement has a high number of scanned rows, it may indicate that the indexes are not properly built or that the execution plan is incorrect. The slow query log provides real execution results but is retrospective data. By the time a slow query is logged, the problem may have already occurred in the live system. Is there a method to detect such issues before they impact production?
Full audit log: This refers to a method of recording all SQL behaviors in the system, including queries and updates. Relevant information is recorded during the statement execution process and written to the log at the end of the execution. The recorded information includes execution start and end times (in microseconds), scanned rows, updated rows, etc. Although the audit log is also recorded after the statement execution, it captures the complete log, providing an opportunity to detect abnormal conditions such as high scanned rows before slow queries occur, prompting DBAs to investigate.
Comparative system: In reality, the probability of bugs such as "execution plan errors" is not high, and generally, the optimizer accuracy of PostgreSQL is higher than that of MySQL. Therefore, if there are two systems running the same data and statements, when a statement has different execution plans in the two systems, it can be inferred that one of the execution plans may not be optimal, requiring manual intervention. However, maintaining a comparative system itself incurs significant costs. This approach is feasible in Klustron, as its computing nodes are based on PostgreSQL and storage nodes are based on MySQL. By comparing the execution plans at both layers within a statement execution process, potential optimizer bugs can be identified in advance.

This article introduces some methods for detecting execution plan errors. If you have any other approaches or ideas, we welcome you to discuss them with us.