A method for parallel execution optimization of an openGauss kernel database

Abstract

The application provides a method for optimizing parallel execution of an openGauss kernel database, comprising the following steps: modifying the engine content of the openGauss kernel database, adding a flow number irrelevant to data service to the main table data redistribution in the SMP parallel program, performing Hash calculation on the flow number according to the parallel degree, and executing the redistribution on the main table data; broadcasting each small table; compiling the openGauss kernel binary file; modifying the service logic of the stored procedure to dynamically open or close the SMP parallel feature; installing and deploying the openGauss kernel binary software in the operating system; setting the SMP feature of the database software; importing the data model; importing the service data, and opening the SMP parallel execution of the SQL statement or the stored procedure according to the service scheduling requirement. The application realizes the execution plan irrelevant to data distribution, and improves the application range of the openGauss kernel parallel feature.

Description

A method for parallel execution optimization of openGauss kernel database Technical Field

[0001] This invention relates to the field of computer parallel processing technology, and more specifically, to a method for optimizing the parallel execution of the openGauss kernel database. Background Technology

[0002] Most mainstream computer systems on the market today have the ability to achieve parallel computing using multi-CPU or multi-core CPU technology. This technology is called Symmetrical Multi-Processing (SMP), which refers to a group of processors (multiple CPUs) on a single computer, with each CPU sharing a memory subsystem and bus structure. It is a widely used parallel technology in contrast to asymmetric multi-processing. Gauss DB (for openGauss) is a commercial database product launched by Huawei that supports SMP features and is open-sourced to the openGauss community. SMP improves performance through the parallel execution of operators, but it consumes significant system resources, including CPU, memory, and I / O. Essentially, SMP is a resource-for-time tradeoff, which can achieve good performance improvements in suitable scenarios and when resources are sufficient. Typically, SMP is suitable for analytical queries, which are characterized by long individual query times and low business concurrency. SMP parallel technology can reduce query latency and improve system throughput.

[0003] The core principle of SMP (Simultaneous Multiprocessing) features is to utilize the multi-threaded processing technology of the openGauss kernel storage engine to automatically distribute and parallelize data, thereby achieving faster computation. By default, the following conditions are required for SMP features to be applied:

[0004] (1) Various computing resources are relatively abundant, such as memory and CPU;

[0005] (2) Non-high concurrency scenarios;

[0006] (3) A reasonable data distribution needs to be specified so that the data distributed across different computing threads is relatively balanced; the current openGauss kernel requires the data distribution column to achieve better parallel performance.

[0007] The core idea of ​​using SMP parallelism, as shown in Figure 4, is to split the data and distribute it to multiple worker threads for parallel processing, which can improve processing efficiency. However, if the data splitting is unreasonable, resulting in a large difference in processing time between different threads, then the overall execution time of the multiple worker threads will be determined by the execution time of the Data Node thread containing the most data. A common practice is to analyze the business logic and data situation in advance during the SQL (Structured Query Language) writing or development optimization phase, select appropriate distribution columns for the execution plan to optimize, and obtain a relatively balanced data distribution.

[0008] However, during the operation of a production system, data distribution is often dynamic and unlikely to be evenly distributed according to a certain type of business. For example, the computing resources in a critical business production system in the financial industry are relatively abundant, and concurrency is relatively low. For many complex SQL statements or stored procedures in OLAP analysis scenarios, performance optimization can be achieved by modifying the business code, selecting appropriate distribution columns, and using SMP features during the adaptation development process to the openGauss kernel. However, in some scenarios where the data distribution cannot be accurately known in advance, there is always an inability to reasonably select distribution columns during production use. This results in situations where the data volume is not significantly different, but the job execution time is unstable. It is necessary to repeatedly analyze and optimize the relevant SQL. Therefore, the performance improvement of the openGauss kernel database using SMP parallel features is greatly affected by the data distribution, and the application of SMP parallel features is significantly limited. Summary of the Invention

[0009] Therefore, the purpose of this invention is to optimize some logic of the openGauss kernel to achieve an execution plan independent of data distribution, thereby improving the applicability of the openGauss kernel database to adopt SMP parallelism.

[0010] This invention provides a method for optimizing parallel execution of the openGauss kernel database, comprising the following steps:

[0011] S1. Modify the engine content of the openGauss kernel database, add a serial number unrelated to data business in the SMP parallel program, redistribute the main table data of the database table, perform a hash calculation on the serial number according to the parallelism m to obtain a hash value, and use the hash value to perform a redistribution on the main table data.

[0012] S2. Perform full broadcast replication m times on each of the smaller tables in the database table that are associated with the main table, so that each smaller table contains a copy of the data on each distributed data node.

[0013] This way, when the SQL calculations are executed, there is no need to repeatedly retrieve data from external sources;

[0014] S3. Compile the openGauss kernel binary file that supports SMP features;

[0015] S4. Modify the business logic of the openGauss kernel database stored procedure to dynamically enable or disable the SMP parallel feature during the session.

[0016] S5. Select a computer and operating system that support SMP multi-core parallel processing capabilities, and install and deploy the openGauss kernel binary software compiled in step S3 on the operating system; enable SMP features for the openGauss kernel database software.

[0017] S6. Import data model; import business data, and enable SMP parallel execution of SQL statements or stored procedures according to business scheduling requirements.

[0018] Furthermore, the method for enabling SMP parallel execution of SQL statements or stored procedures in step S6 includes the following steps:

[0019] S61. Call the stored procedure or corresponding SQL code segment via JDBC; enter the SQL code segment; enable SMP parallel features and set the number of concurrent paths;

[0020] S62, Enter the openGauss kernel to execute SQL calculations that require parallel processing;

[0021] S63. Return the parallel SQL calculation results in real time for merging, aggregation, and statistics until all worker threads have returned results;

[0022] S64. Sort and filter the SQL calculation results according to the set conditions, generate a result set and return it.

[0023] Furthermore, the method for entering the openGauss kernel to perform parallel SQL computation in step S62 includes:

[0024] The query optimizer parses the SQL calculation fields passed to the openGauss kernel, obtains part of the logic of the main table data through the SQL, performs Hash calculation on the main table data according to the serial number, and redistributes it to form data shards (DataNodes). The data shards are split and distributed to different execution Workers that support multi-threaded parallelism. After receiving the execution instructions, each execution Worker performs data calculation on the data shards (DataNodes).

[0025] Furthermore, the method for distributing data shards to different execution Workers includes:

[0026] The query optimizer performs a full broadcast operation on each of the smaller tables associated with the main table, distributing copies of the data from each smaller table to different execution workers.

[0027] Furthermore, the method for performing data computation on data shards (Data Nodes) includes:

[0028] Perform a join operation within the data shard to achieve a share-nothing approach and thus optimal parallel performance.

[0029] Furthermore, the process after step S64 includes:

[0030] Restore the concurrent path count to the default of 1; proceed to the next SQL code segment and decide whether to enable SMP parallel features as needed; continue until the session process is completed.

[0031] Furthermore, the data model in step S6 includes:

[0032] Business tables, stored procedures, views, and functions.

[0033] This invention achieves balanced data distribution by adding two processing steps to the execution optimization program of the openGauss kernel, thus eliminating the problem of data distribution imbalance.

[0034] Process 1: Redistribute the main table data using serial numbers that are independent of the data, according to the parallelism degree m;

[0035] Process 2: For the smaller tables associated with the main table, perform full broadcast replication m times according to each data shard. During parallel computing, join operations can be performed within the shards to achieve "Share Nothing" and realize the best parallel performance.

[0036] The present invention also provides a computer-readable storage medium having a computer program stored thereon, wherein the program, when executed by a processor, implements the steps of the above-described method for parallel execution optimization of the openGauss kernel database.

[0037] The present invention also provides a computer device, the computer device including a memory, a processor and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the method for parallel execution optimization of the openGauss kernel database as described above.

[0038] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0039] This invention provides an openGauss kernel database with a method to utilize SMP parallel capabilities that is independent of data business and does not require prior analysis of the data distribution column. By adding a serial number that is independent of the data and performing a hash calculation on the serial number according to the degree of parallelism before data sharding, an absolutely average data distribution can be obtained. This maximizes the utilization of the multi-core parallel capabilities of the underlying resources and brings out the effect of SMP parallelism. In scenarios where the main table is associated with multiple smaller tables, there is no need to worry about performance degradation caused by data skew. Attached Figure Description

[0040] Various other advantages and benefits will become apparent to those skilled in the art upon reading the following detailed description of preferred embodiments. The accompanying drawings are for illustrative purposes only and are not intended to limit the invention.

[0041] In the attached diagram:

[0042] Figure 1 is a flowchart of a method for optimizing parallel execution of an openGauss kernel database according to the present invention;

[0043] Figure 2 is a schematic diagram of the computer device according to an embodiment of the present invention;

[0044] Figure 3 is a flowchart of the method for enabling SMP parallel execution of SQL statements or stored procedures according to an embodiment of the present invention;

[0045] Figure 4 is a schematic diagram of the core principle of existing SMP parallelism;

[0046] Figure 5 is a schematic diagram of the core principle of parallel execution optimization in an embodiment of the present invention. Detailed Implementation

[0047] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this disclosure. Rather, they are merely examples of apparatuses and products consistent with some aspects of this disclosure as detailed in the appended claims.

[0048] The terminology used in this disclosure is for the purpose of describing particular embodiments only and is not intended to be limiting of the disclosure. The singular forms “a,” “the,” and “the” as used in this disclosure and the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used herein refers to and includes any and all possible combinations of one or more of the associated listed items.

[0049] It should be understood that although the terms first, second, third, etc., may be used in this disclosure to describe various information, such information should not be limited to these terms. These terms are used only to distinguish information of the same type from one another. For example, without departing from the scope of this disclosure, first information may also be referred to as second information, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when," "when," or "in response to determination."

[0050] The embodiments of the present invention will be further described in detail below with reference to the accompanying drawings.

[0051] This invention provides a method for optimizing parallel execution of the openGauss kernel database, as shown in Figure 1, including the following steps:

[0052] S1. Modify the engine content of the openGauss kernel database, add a serial number unrelated to data business in the SMP parallel program, redistribute the main table data of the database table, perform a hash calculation on the serial number according to the parallelism m to obtain a hash value, and use the hash value to perform a redistribution on the main table data.

[0053] S2. Perform full broadcast replication m times on each of the smaller tables in the database table that are associated with the main table, so that each smaller table contains a copy of the data on each distributed data node.

[0054] This way, when the SQL calculations are executed, there is no need to repeatedly retrieve data from external sources;

[0055] S3. Compile the openGauss kernel binary file that supports SMP features;

[0056] S4. Modify the business logic of the openGauss kernel database stored procedure to dynamically enable or disable the SMP parallel feature during the session.

[0057] S5. Select a computer and operating system that support SMP multi-core parallel processing capabilities, and install and deploy the openGauss kernel binary software compiled in step S3 on the operating system; enable SMP features for the openGauss kernel database software.

[0058] S6. Import data model; import business data, and enable SMP parallel execution of SQL statements or stored procedures according to business scheduling requirements;

[0059] In this embodiment, the data model includes:

[0060] Business tables, stored procedures, views, and functions;

[0061] The method for enabling SMP parallel execution of SQL statements or stored procedures, as shown in Figure 3, includes the following steps:

[0062] S61. Call the stored procedure or corresponding SQL code segment via JDBC; enter the SQL code segment; enable SMP parallel features and set the number of concurrent paths;

[0063] S62. Enter the openGauss kernel to perform SQL calculations that require parallel processing; including:

[0064] The query optimizer parses the SQL calculation fields passed to the openGauss kernel, obtains part of the logic of the main table data through the SQL, performs Hash calculation on the main table data according to the serial number, and redistributes it to form data shards (DataNodes). The data shards are split and distributed to different execution Workers that support multi-threaded parallelism. After receiving the execution instructions, each execution Worker performs data calculation on the data shards (DataNodes).

[0065] The method for distributing data shards to different execution Workers includes:

[0066] The query optimizer performs a full broadcast operation on each of the smaller tables associated with the main table, distributing copies of the data from each smaller table to different execution workers.

[0067] The method for performing data computation on data shards (Data Nodes) includes:

[0068] Perform a join operation within the data shard to achieve a share-nothing approach and thus optimal parallel performance;

[0069] S63. Return the parallel SQL calculation results in real time for merging, aggregation, and statistics until all worker threads have returned results;

[0070] S64. Sort and filter the SQL calculation results according to the set conditions, generate a result set and return it.

[0071] Figure 5 illustrates the core principle of parallel execution optimization in this embodiment of the invention.

[0072] Restore the concurrent path count to the default of 1; proceed to the next SQL code segment and decide whether to enable SMP parallel features as needed; continue until the session process is completed.

[0073] The main application scenario for this embodiment is large-scale non-real-time business operations.

[0074] This invention achieves balanced data distribution by adding two processing steps to the execution optimization program of the openGauss kernel, thus eliminating the problem of data distribution imbalance.

[0075] Process 1: Redistribute the main table data using serial numbers that are independent of the data, according to the parallelism degree m;

[0076] Process 2: For the smaller tables associated with the main table, perform full broadcast replication m times according to each data shard. During parallel computing, join operations can be performed within the shards to achieve "Share Nothing" and realize the best parallel performance.

[0077] This invention, by adding a serial number unrelated to the data and performing a hash calculation on the serial number according to the degree of parallelism before data sharding, can obtain an absolutely average data distribution. This can maximize the utilization of the multi-core parallel capabilities of the underlying resources and achieve the effect of SMP parallelism. In scenarios where the main table is associated with multiple smaller tables, there is no need to worry about performance degradation caused by data skew.

[0078] This invention also provides a computer device. Figure 2 is a schematic diagram of the structure of a computer device provided in this invention. Referring to Figure 2, the computer device includes: an input device 23, an output device 24, a memory 22, and a processor 21. The memory 22 is used to store one or more programs. When the one or more programs are executed by the one or more processors 21, the one or more processors 21 implement the method for parallel execution optimization of the openGauss kernel database as provided in the above embodiments. The input device 23, the output device 24, the memory 22, and the processor 21 can be connected by a bus or other means. Figure 2 shows an example of connection via a bus.

[0079] The memory 22, as a read / write storage medium for a computing device, can be used to store software programs and computer-executable programs, such as the program instructions corresponding to the method for parallel execution optimization of the openGauss kernel database described in this embodiment of the invention. The memory 22 may primarily include a program storage area and a data storage area. The program storage area may store the operating system and at least one application program required for a function; the data storage area may store data created based on the use of the device. Furthermore, the memory 22 may include high-speed random access memory and non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state storage device. In some instances, the memory 22 may further include memory remotely located relative to the processor 21, and these remote memories can be connected to the device via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0080] Input device 23 can be used to receive input digital or character information, and generate key signal inputs related to user settings and function control of the device; output device 24 may include display devices such as a display screen.

[0081] The processor 21 executes various functional applications and data processing of the device by running software programs, instructions and modules stored in the memory 22, thereby realizing the above-mentioned method of parallel execution optimization of the openGauss kernel database.

[0082] The computer equipment provided above can be used to execute the method for parallel execution optimization of the openGauss kernel database provided in the above embodiments, and has corresponding functions and beneficial effects.

[0083] This invention also provides a storage medium containing computer-executable instructions, which, when executed by a computer processor, are used to perform a method for parallel execution optimization of the openGauss kernel database as provided in the above embodiments. The storage medium can be any type of memory device or storage device, including: mounting media such as CD-ROM, floppy disk, or magnetic tape; computer system memory or random access memory such as DRAM, DDRRAM, SRAM, EDO RAM, Rambus RAM, etc.; non-volatile memory such as flash memory, magnetic media (e.g., hard disk or optical storage); registers or other similar types of memory elements; the storage medium may also include other types of memory or combinations thereof; furthermore, the storage medium may reside in a first computer system in which the program is executed, or it may reside in a different second computer system connected to the first computer system via a network (such as the Internet); the second computer system can provide program instructions to the first computer for execution. The storage medium includes two or more storage media that may reside in different locations (e.g., in different computer systems connected via a network). The storage medium may store program instructions (e.g., specifically implemented as a computer program) executable by one or more processors.

[0084] Of course, the computer-executable instructions provided in the embodiments of the present invention are not limited to the method for parallel execution optimization of the openGauss kernel database as described in the above embodiments, but can also execute related operations in the method for parallel execution optimization of the openGauss kernel database provided in any embodiment of the present invention.

[0085] The technical solution of the present invention has been described above with reference to the preferred embodiments shown in the accompanying drawings. However, it will be readily understood by those skilled in the art that the scope of protection of the present invention is obviously not limited to these specific embodiments. Without departing from the principles of the present invention, those skilled in the art can make equivalent changes or substitutions to the relevant technical features, and the technical solutions after these changes or substitutions will all fall within the scope of protection of the present invention.

[0086] The above description is merely a preferred embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A method for optimizing parallel execution of the OpenGauss kernel database, characterized in that, Includes the following steps: S1. Modify the engine content of the openGauss kernel database, add a serial number unrelated to data business in the SMP parallel program, redistribute the main table data of the database table, perform a hash calculation on the serial number according to the parallelism m to obtain a hash value, and use the hash value to perform a redistribution on the main table data. S2. Perform full broadcast replication m times on each of the smaller tables associated with the main table in the database table, so that each smaller table contains a copy of the data on each distributed data node; S3. Compile an openGauss kernel binary file that supports SMP features. S4. Modify the business logic of the openGauss kernel database stored procedure to dynamically enable or disable SMP parallel features during the session; S5. Select a computer and operating system that support SMP multi-core parallel processing capabilities, and install and deploy the openGauss kernel binary software compiled in step S3 on the operating system; enable SMP features for the openGauss kernel database software; S6. Import the data model; import business data, and enable SMP parallel execution of SQL statements or stored procedures according to business scheduling requirements; the method for enabling SMP parallel execution of SQL statements or stored procedures in step S6 includes the following steps: S61. Call the stored procedure or corresponding SQL code segment via JDBC; enter the S... QL code segment; Enable SMP parallelism and set the number of concurrent paths; S62, Enter the openGauss kernel to execute the SQL computation that needs to be parallelized; S63, Return the parallel SQL computation results in real time for merging, aggregation, and statistics until all worker threads have returned results; S64, Sort and filter the SQL computation results according to set conditions, generate a result set and return it; The method of entering the openGauss kernel to execute the SQL computation that needs to be parallelized in step S62 includes: parsing the SQL computation fields passed to the openGauss kernel through the query optimizer, obtaining part of the main table data through SQL, performing Hash calculation on the main table data according to the serial number to perform redistribution and form data shards. The Node splits the data shards and distributes them to different execution Workers that support multi-threaded parallelism. Each execution Worker, upon receiving the execution instruction, performs data computation on the data shard Data Node. The method for distributing data shards to different execution Workers includes: performing a full broadcast (BROADCAST) operation on each smaller table associated with the main table using the query optimizer, distributing copies of the data from each smaller table to different execution Workers. The method for performing data computation on the data shard Data Node includes: performing a join operation within the data shard to achieve Share Nothing. After step S64, the process further includes: restoring the concurrency path to the default 1 path; entering the next SQL code segment, deciding whether to enable SMP parallelism as needed; until the session process is completed. The data model in step S6 includes: business tables, stored procedures, views, and functions.

2. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the steps of the method for optimizing parallel execution of the openGauss kernel database as described in claim 1.

3. A computer device, the computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the steps of the method for parallel execution optimization of the openGauss kernel database as described in claim 1.

Images