Shulou(Shulou.com)05/31 Report--

How to understand the GaussDB explain distributed execution plan? for this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Preface

The execution plan (also known as the interpretation plan) is the concrete step for the database to execute SQL statements, such as accessing the data in the table through index or full table scan, the implementation of join query and the order of join, and so on. If the performance of the SQL statement is not satisfactory, we should first look at its execution plan. This article mainly introduces how to interpret the distributed execution plan generated by GaussDB (DWS) in detail and find the advantages of performance tuning from the plan.

1. Introduction of execution operator

To understand the execution plan, you must first know the concept of the database execution operator:

The following focuses on the STREAM operator, which is the most important operator in sharing nothing-based distributed planning.

Three types of stream operators

1) Gather Stream (NRV 1)-each source node sends its data to the destination node

2) Redistribute Stream (NRV N)-each source node calculates the hash value according to the connection conditions, distributes it according to the recalculated hash value, and sends it to the corresponding target node

3) Broadcast Stream (1 N)-one source node sends its data to N target nodes

1) it is mainly used for data exchange between CN and DN, 2) and 3) it is mainly used for data exchange between DN

2. EXPLAIN usage

The SQL execution plan is the number of nodes that shows the detailed steps when a SQL statement is executed. Each step is a database operator, also known as an execution operator. Use the explain command to view the specific execution plan generated by the optimizer for each query.

1) the syntax of EXPLAIN

Where the default value of COSTS and NODES in option is ON, and the other parameters default to OFF.

Description:

A) EXPLAIN + QUERY will not be actually executed, only the plan will be printed out, and the ANALYZE in the specified option can be actually executed.

B) the PERFORMANCE option sets all options to ON by default, that is, all execution information is displayed.

C) the CPU/BUFFER/DETAIL option depends on ANALYZE, and can only be used if ANALYZE is set to ON.

D) the DETAIL option is used to control the output. When DETAIL is set to ON, the specific execution information on each DN is displayed; when DATAIL is set to OFF, the summary information of all DN is displayed, that is, the maximum and minimum information.

2) EXPLAIN display format

Two display formats (normal/pretty) are provided in GaussDB, which are controlled by setting the parameter explain_perf_mode. Where normal format is the default display format.

The normal format is as follows:

The pretty format is as follows:

The improved display format has a clear hierarchy, and the plan includes plan node id, so the performance analysis will be more simple and direct.

Before using it, you can use show explain_perf_mode; to view the display style used by the current database.

You can also use set explain_perf_mode=pretty/normal; to format the output.

3. Sample plan interpretation (resource consumption per operator, time consuming, etc.)

1) four common types of plans

Build a table sentence:

A) FQS plan, complete push down, release query

Two tables JOIN, and the connection condition is the distributed columns of each table. When the stream operator is turned off, CN will send the statement directly to each DN for execution, and the final result will be summarized in CN.

B) non-FQS plan, partial statement push down

Two tables JOIN, and the join condition contains non-distributed columns. When the stream operator is turned off, CN will send the base table scan statement to each DN, and then JOIN on the CN.

C) Stream plan, no data exchange between DN

The two tables are JOIN, and the join condition is the distributed columns of each table, so each DN does not need to exchange data. After the stream plan is generated by CN, the plan except Gather Stream is sent to DN for execution, the base table is scanned on each DN, and hashed, and then sent to CN.

D) Stream plan, where there is data exchange between DN

Two tables JOIN, and the join condition contains non-distributed columns. When the stream operator is enabled, the stream plan is generated, and there is data exchange between the DN. At this time, for the tt02 table, the base table will be scanned in each DN. After the scan, it will be hashed according to the tt02.c1 in the JOIN condition and then re-sent to each DN. Then JOIN will be done on each DN, and finally summarized to the CN.

2) detailed explanation of explain performance

A) implement the plan

Id: executes the operator node number.

Operation: the name of the specific execution node operator.

A-time: the corresponding operator execution time of each DN. In [], the left side is the minimum value and the right side is the maximum value, including the lower layer operator execution time.

A-rows: the total number of global rows output by the corresponding operator.

E-rows: the number of output lines estimated by each operator.

Peak Memory: the peak memory consumption of each DN operator, with the minimum on the left side and the maximum on the right side in [].

The estimated memory usage for each operator on E-memory:DN is displayed only by operators executed on DN. Some scenarios use parentheses after the estimated memory usage to show the upper limit of memory that can be automatically extended by the operator if the memory source is sufficient.

E-width: the estimated width of each operator's output tuple.

E-costs: the execution cost estimated by each operator.

B) predicate filtering

Display the filter conditions for the corresponding execution operator node

C) memory usage

It mainly shows the maximum memory consumption of CN, the maximum memory consumption of DN, the maximum memory consumption of each operator, the estimated memory consumption of each operator, the start-up of Stream thread and the sending and receiving time.

D) Targetlist Information

The output target column information corresponding to each operator.

E) DN information

Execution time, Buffer, CPU information of each operator

F) Custom information

The connection information between CN and DN, the connection information between DN and DN.

G) Summary information

DN actuator start time, [min_node_name, max_node_name]: [min_time, max_time]

End time of DN actuator, [min_node_name, max_node_name]: [min_time, max_time]

Remote query poll time: the time spent waiting for poll when receiving the result

CN actuator start, run and end time

Network traffic, amount of data sent by stream operator

Optimizer execution time

Query ID

Total execution time

H) introduction of execution time

The execution information of each operator consists of three parts:

Where:

Dn_6001_6002/dn_6003_6004 indicates the node information of the specific execution, and the information in parentheses is the actual execution information.

Actual time=0.013..2290.971 indicates the actual execution time

The first number represents the time it takes to enter the current operator to output the first piece of data during execution

The second number is the total execution time of all the data output

Note: in the whole plan, except that the execution time of the leaf node is the execution time of the operator itself, the execution time of the other operators includes the execution time of the child nodes.

In this plan, Node 7 and Node 9 are leaf nodes, and the other nodes are non-leaf profiles. No.1 is the top-level node, so the execution time of that node can be used as the execution time of the entire query.

Rows=2001550 indicates that the output data of the current operator is 2001550 rows

Loops=1 indicates that the current operator was executed only once, while for a scan of the partition table (Node 7):

The loops of this layer scan operator is 7. For a partition table, the scan of each partition table is a complete scan operation. When you switch to the next partition, it is a new query operation. The table is defined as follows:

The Inventory table has seven partitions, so seven table scans are performed, so loops=7.

I) introduction of CPU information

The process executed by each operator has CPU information, in which cyc represents the number of cycles of the CPU, ex cyc represents the number of cycles of the current operator, excluding its child nodes, inc cyc is the number of cycles containing child nodes, ex row is the number of rows of data output by the current operator, and ex cimpulse r is the average number of cycles used for each piece of data obtained by ex cyc/ex row.

J) introduction to Buffer information

Buffers displays buffer information, including read and write of shared and temporary blocks.

Shared blocks contain tables and indexes, and temporary blocks are disk blocks used in sorting and materialization. The block data displayed by the upper node contains the number of blocks used by all its child nodes.

There are two kinds of parameters involved in Buffers: shared and temp, and shared hit/read/dirtied/written and temp read/write.

Hit blocks: represents the number of blocks read from disk

Dirtied blocks: represents the number of blocks in the current query that have been modified and have not previously been modified

Written blocks: the number of blocks that represent the current thread to write the modified data in the shared bufer back to disk

K) execution memory

Where:

Peak Memory:5KB represents the peak memory used by the current operator when actually executing

Estimate Memory:1024MB represents the estimated memory, which is given to the optimizer.

L) additional implementation information

(1) sort operator, which displays sorting information

Sort Method stands for sorting methods, including quicksort (fast row) and disksort (outer row). Fast scheduling means that when there is enough memory, all sorting operations are completed in memory. Outflow indicates that the current available memory is insufficient and the disk needs to be lowered.

(2) hashjoin operator

Buckets: represents the number of buckets actually used in the hash table

Batches: represents the actual number of chunks in the hashjoin. If Batches=1, it means that all the data is in memory and there is no lower disk operation; otherwise, it means that there is a lower disk operation, and Batches-1 represents the number of temporary files.

Memory Usage: memory usage in hashjoin

(3) hashagg operator

If a data disk occurs, there will be a File Num:512 message showing the number of temporary files.

(4) stream operator

The stream operator counts the number of bytes of data processed by the current operator, the time it takes to obtain data from child threads (poll time), and the time it takes to process data (Deserialize Time).

The child nodes of the stream operator count the time information of the sender, as follows:

Sending time Send time, queuing time Wait Quota time, OS sending time and data processing time.

3) explain tuning example

A query statement takes several operator steps to output the final result. It is common that the overall query performance is degraded due to the excessive time-consuming of individual operators. These operators are the bottleneck operators of the whole query. The general optimization method is the EXPLAIN ANALYZE/PERFORMANCE command to view the bottleneck operator of the execution process, and then optimize it specifically.

When scanning the base table, for queries that filter a large amount of data, such as point search or range scanning, it will be time-consuming to use SeqScan to scan the whole table. You can build an index on the condition column and select IndexScan for index scanning to improve the scanning efficiency. The following is an example:

In the above example, the full table scan returned 3360 pieces of data, filtered out a large amount of data, and after indexing on the sssolddate_sk column, the efficiency of using IndexScan scanning was significantly improved from 960ms to 8ms.

This is the answer to the question on how to understand the GaussDB explain distributed execution plan. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.