Abstract: In relational database, the optimizer is one of the core components of the database, because a series of factors will affect the execution of the statement, the optimizer comprehensively weighs each factor, and selects the best execution plan among many execution plans.
This article is from huawei Cloud community “Huawei Cloud GaussDB(foropenGauss) Special live broadcast Issue 5: SQL Optimization Interpretation”, the author of the original text: Yan Fat.
1, the preface
In relational database, the optimizer is one of the core components of the database. Because a series of factors will affect the execution of statements, the optimizer comprehensively weighs each factor and selects the best execution plan from numerous execution plans. With the advent of the era of big data, such as e-commerce, games, telecommunications and other industries are large-scale applications, a single database node is difficult to cope with the growing scale of data and ensure the performance of the need, the business is facing the problem of “no storage, slow calculation, inaccurate calculation”. GaussDB(for openGauss) uses a horizontally scalable distributed architecture to meet the storage and computing requirements of massive data. Based on the CBO cost of the target SQL execution plan, it selects the execution path with the lowest cost among many execution plans of the target SQL. The cost of each execution path is calculated based on the statistics of tables, indexes, columns and other related objects in the target SQL. The cost is an estimate of the I/O, CPU, and network resources consumed by the target SQL.
- I/O resources: The cost of reading table data from disk into memory
- CPU resources: The cost of processing data for tables in memory
- Network resources: Distributed SQL that requires data interaction between DNS. When the data needed for actual execution is not in the local DN (data needs to be obtained from other DNS), the network resource consumption is converted into equal I/O resource consumption and then estimated.
This paper introduces the distributed parallel execution framework and distributed execution plan based on the content of the fifth live broadcast.
2. Distributed parallel execution framework
2.1 Actuator: PIPELINE model
The executor features of GaussDB(for openGauss) are as follows: The executor is executed from bottom up in the query plan tree and based on the volcano model. That is, each node returns a row of records to its parent node.
The biggest advantage of volcano model is that it can only take out one tuple at a time as required. After processing this tuple, the system will take out the next tuple that meets the conditions until all the tuples that meet the conditions are taken out. As you can see from the way this works, it requires very little system resources at each execution.
2.2 High-performance distributed query engine
GaussDB(for openGauss) takes full advantage of the multi-core feature and executes concurrently through multiple threads to improve system throughput. As we all know, in the traditional distributed MPP database, the cost of data shuffling is very expensive, which limits the range of user scenarios.
GaussDB(for openGauss) can make full use of the current multi-core characteristics, adopt the parallel execution mechanism, has years of precipitation in SQL execution optimization, and provides three streams (broadcast flow, aggregation flow and redistribution flow) to reduce the flow of data between DN nodes. Breaking through the limitation of user usage scenarios caused by costly data shuffle in traditional distributed MPP databases, even complex SQL and transaction analysis hybrid (HTAP) scenarios can be best executed.
General execution process of GaussDB(for openGauss) :
- A service application sends an SQL file to a Coordinator. The SQL file can contain CRUD operations on data.
- A Coordinator uses the optimizer of a database to generate an execution plan. Each DN processes data according to the requirements of the execution plan.
- Data is distributed on each DN based on the consistent Hash algorithm. Therefore, the DN may need to obtain data from other DN during data processing. GaussDB provides three stream streams (broadcast stream, aggregation stream, and redistribution stream) to transfer data between DN. Make the join need not extract to CN to execute;
- DN returns the result set to Coordinate for summary;
- The Coordinator returns the summary result to the service application.
3. Distributed execution plan
CN determines whether the SQL statement can be executed directly on each DN without data exchange according to the distributed column information and associated column information of the table. If so, CN adopts the LIGHT_QUERY or FQS_QUERY process and keeps the attitude of being irrelevant. I will send whatever you send to me. The whole query command is directly sent to DN for execution, and output directly after execution. If data interaction between each DN is required, the STREAM operator will be used. If you find that you cannot use the stream operator, you return to the original PGXC process.
3.1 LIGHT_QUERY
– Scenario: statements can be executed directly in a DN (single shard statements, click the scenario).
create table t1 ( col1 int, col2 varchar ) distribute by hash(col1);
create table t2 ( col1 int, col2 varchar ) distribute by hash(col1);
Copy the code
3.2 FQS_QUERY
– Scenario: When the statement can be completely pushed down to multiple DN and data interaction between the DN is not required.
– Principle: CN generates a RemoteQuery plan directly without the optimizer and delivers it to DN logically through the executor. Each DN generates and executes an execution plan according to the push-down statement, and the execution results are summarized on CN.
create table t1 ( col1 int, col2 varchar ) distribute by hash(col1);
create table t2 ( col1 int, col2 varchar ) distribute by hash(col1);
Copy the code
The biggest difference between LIGHT_QUERY and FQS_QUERY is that although CN directly sends the received query to DN for processing after judgment, LIGHT_QUERY only involves single DN for operation. However, FQS_QUERY involves multiple DNS to operate separately, and none of them involves data interaction between DNS.
3.3 the STREAM GATHER
– Scenario: Data exchange between DNS is required.
– Principle: CN generates the execution plan with stream operator through the optimizer according to the original statement and sends it down to DN for execution. During the execution of DN, there is data interaction (STREAM node), and the STREAM operator establishes connections between DN for data interaction. CN summarizes the execution results and undertakes most calculations.
create table t1 ( col1 int, col2 varchar ) distribute by hash(col1);
create table t2 ( col1 int, col2 varchar ) distribute by hash(col2);
Copy the code
3.4 the STREAM REDISTRIBUTE
– Scenario: Data exchange between DNS is required.
– principle: CN generates an execution plan with stream operator through the optimizer according to the original statement and sends it down to DN for execution. During the execution of each DN, there is data interaction (STREAM node), and the STREAM operator establishes connections between DN for data interaction. CN summarizes the execution results and undertakes most calculations.
create table t1 ( col1 int, col2 varchar ) distribute by hash(col1);
create table t2 ( col1 int, col2 varchar ) distribute by hash(col2);
Copy the code
3.5 the STREAM BROADCAST
– Scenario: Data exchange between DNS is required.
– principle: CN generates an execution plan with stream operator through the optimizer according to the original statement and sends it down to DN for execution. During the execution of each DN, there is data interaction (STREAM node), and the STREAM operator establishes connections between DN for data interaction. CN summarizes the execution results and undertakes most calculations.
create table t1 ( col1 int, col2 varchar ) distribute by hash(col1);
create table t2 ( col1 int, col2 varchar ) distribute by hash(col2);
Copy the code
If the REDISTRIBUTE operator is used, data redistribution can make full use of the computing power of multiple nodes. However, the BROADCAST operator is mainly used when the subplan of a stream generates a small amount of data, and the BROADCAST cost is low.
3.6 PGXC
– Scenario: The performance is poor in extreme scenarios that cannot meet the preceding processing methods.
– Principle: CN generates a RemoteQuery plan from some statements in the original statement through the optimizer, and delivers each RemoteQuery to DN. After the execution of DN, the intermediate result data is sent to CN, and CN collects and calculates the remaining execution plan. CN undertakes most of the calculation.
conclusion
To sum up, GaussDB(for openGauss), as a new generation of financial level distributed relational database independently developed, adopts a horizontally scalable distributed architecture, generates distributed operators and distributed execution plans through SQL optimizer. Three streams (broadcast stream, aggregation stream and redistribution stream) are provided to reduce the flow of data between DN nodes. The execution engine is a distributed parallel execution framework, which supports inter-node parallelism and intra-node parallelism. It makes full use of the current multi-core characteristics, improves system throughput through concurrent execution, and has high performance query capability under big data.
For more highlights, please click the replay link to watch:
Bbs.huaweicloud.com/live/cloud_…
Click to follow, the first time to learn about Huawei cloud fresh technology ~