JP2004213680A - Database management system and query processing method - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide a query processing method for speeding up query processing. <P>SOLUTION: Distribution nodes 1 to 8 are provided with a table T1 or a table T2 for distributing and storing databases for query, retrieve information from these databases and distribute the retrieved information to other nodes. Connecting nodes 9 to 11 sort the distributed information, and when there are a plurality of sorted pieces of information, merge these pieces of information and perform matching for the query based on the merged information. A determination management node 12 receives and analyzes the query, and determines a distribution node and a connecting node for performing execution processing based on the result of analysis. The determination management node 12 also outputs the result for the query sent out from the connecting node. <P>COPYRIGHT: (C)2004,JPO&NCIPI

Description

  The present invention relates to a database processing apparatus and, more particularly, to a query processing method suitable for parallel processing of queries suitable for a relational database management system.

  A database management system (hereinafter abbreviated as DBMS), particularly a relational DBMS, processes a query expressed in a non-procedural language, determines an internal processing procedure, and executes it according to the internal processing procedure. SQL is used as the database language (Database Language SQL ISO 9075: 1989). The main method of the conventional query processing is to determine a single internal processing procedure based on a preset rule and from a plurality of candidate processing procedures selected using various statistical information, by cost evaluation, Some determine what seems optimal. In the former, although the load for creating the processing procedure is small, there is a problem in the validity of uniformly set rules, and also in the optimumness of the selected internal processing procedure. The latter manages various kinds of statistical information, creates a plurality of candidate processing procedures, calculates a load for cost evaluation thereof, and gives an optimal processing procedure. For example, Satoh, K., et.al. "Local and Global Optimization Mechanisms for Relational Database", Proc. VLDB, 1985. (Non-Patent Document 1). In the related art, a processing procedure is determined by estimating a data amount from an inquiry condition.

  In many DBMSs, query processing is realized through two-phase processing of query analysis processing and query execution processing. When a query language is incorporated into a host language (COBOL, PL / I, etc.), a query is analyzed and analyzed in advance before executing this application program, and one internal processing procedure as an execution format is created. In this query expression, in many cases, variables in the host language are described in the search condition expression. The constant is assigned to this variable when the internal processing procedure of the result of the query analysis processing is executed, that is, when the query is executed. The problem in this case is that a plurality of optimal processing procedures can be considered according to the values substituted for the variables. In order to solve this problem, there is a method in which a plurality of processing procedures are created at the time of query execution processing, and the processing procedure is selected according to a value assigned to a variable at the time of query execution. Japanese Patent Application Laid-Open No. 1-194028 (Patent Document 1) and Graefe, G., et.al. "Dynamic Query Evaluation Plans", Proc. ACM-SIGMOD, 1989. There is a technique described in reference 2).

  Further, in response to an increase in the amount of transactions and an increase in the amount of databases that exceed the increase in CPU performance and disk capacity, there is a demand from users for providing a scalable parallel database system. User performance requirements for a database system include handling more than tens of thousands of concurrent users, the appearance of search transactions in terabytes, and guaranteeing response times that are not proportional to table size. Parallel database systems have received attention in recent years, coupled with a reduction in hardware costs. The parallel database system is described in DeWitt, D., et.al .: "Parallel Database Systems: The Future of High Performance Database Systems", CACM, Vol. 35, No. 6, 1992. Technology. In such a system, it is necessary to connect processors tightly or loosely, and to allocate and schedule database processing statically / dynamically to a plurality of processors. Although the response performance is improved by increasing the degree of parallelism, excessive degree of parallelism adversely affects the increase of overhead and the extension of the response time of other transactions. Therefore, setting an appropriate degree of parallelism is important.

  In the database processing, data to be processed exists in a secondary storage device, and it is necessary to read and transfer a large amount of data for each database operation. Even in a parallel database system, when the amount of data to be transferred is large, the data transfer time becomes a performance bottleneck of the database system. Therefore, a method of effectively utilizing the time for transferring data from the secondary storage device can be considered. This overlaps the data transfer time and the time required for database processing on the data, and is well known in the prior art. This method is also applicable to data transfer between processors connected by an interconnection network.

JP-A-1-194028

Satoh, K., et.al. "Local and Global Optimization Mechanisms for Relational Database", Proc. VLDB, 1985. Graefe, G., et.al. "Dynamic Query Evaluation Plans", Proc. ACM-SIGMOD, 1989. DeWitt, D., et.al .: "Parallel Database Systems: The Future of High Performance Database Systems", CACM, Vol. 35, No. 6, 1992.

  In the above prior art, the query optimization process is a process in which the DBMS automatically determines the most efficient processing procedure based on various statistical information of the database system from a query input by a user. Furthermore, when a variable is embedded in the selection condition expression of the query, a plurality of processing procedures are developed at the time of query analysis, and the processing procedure is selected according to the value assigned to the variable at the time of executing the query. The optimal processing procedure is selected.

  In the parallel database processing, a database operation is divided into each node (processor or a pair of a processor and a disk device), and each database operation operates in parallel or in a pipeline at each node. According to the above prior art, even in this parallel processing mode, a method of selecting a processing procedure at each node is applicable.

  However, in the processing that operates in parallel, the respective nodes perform parallel processing at the same time, but there is a problem that the number of each node cannot be determined corresponding to the database operation executed in each node. That is, since the criterion for determining the number of nodes is not clear, excessive parallelization adversely affects the increase of overhead, and it is difficult to optimally distribute the load.

  In addition, in the process of performing the pipeline operation, the database operation is divided and stored in each node. However, when there is a variation in data division, a method of equally dividing each node is not clear.

  Further, when a plurality of processes are performed within the time, for example, when there is a restriction on the processing time, each database operation executed at each node is parameterized, and time adjustment (tuning) is performed based on the expected processing time. It is not clear how to do it.

  An object of the present invention is to provide a query processing method and a database system that speed up query processing.

  In order to solve the above problem, the present invention includes a plurality of nodes that execute database processing, the plurality of nodes being a database management system connected to another node via a network, and Storage means for distributing and storing a database; a distribution node including distribution means for extracting information from the storage means and distributing the extracted information to another node; and reordering for rearranging the information distributed from the distribution node. Means for combining a plurality of pieces of the rearranged information, a merger for merging the plurality of pieces of information when there is a plurality of pieces of information, a matching node for performing a match to an inquiry based on the merged information; Analyzing means for analyzing the inquiry and creating a processing procedure for the inquiry; Comprising a determining means for determining a distribution node and join node performing the execution process based on the analysis result of the inquiry, said obtained from join node, and a decision management node and output means for outputting the result to the inquiry.

  The determination unit may determine the distribution node based on an analysis result of the inquiry of the analysis unit, calculate an expected processing time in the distribution node, and determine a connection node based on the processing time. .

  The determining means is configured to allocate the distribution of the extracted information to the connection nodes equally to each of the connection nodes based on the determined amount of information to be extracted at the distribution node.

  The decision management node may include a storage unit that stores optimization information regarding information in a storage unit of each of the nodes so that the determination unit uniformly allocates the extracted information to the connection node.

  The decision management node uses a predetermined hash function in order for the decision means to equally allocate the extracted information to the joining node.

  In addition, the plurality of nodes independently perform processing, and the connection node sequentially inputs information distributed from the distribution node, and performs processing for each input information.

  Further, the distribution node may include a reordering unit that rearranges information distributed by the distribution node.

  The determining means determines from the calculation result of the expected processing time in the distribution node that the distribution node rearranging means rearranges the distribution node after the distribution processing to the distribution node which finishes the processing earlier. be able to.

  The determining means determines to increase the number of the connection nodes determined based on the processing time by a predetermined number.

  The unit for rearranging the connection nodes may have a function of performing a merge process after the rearrangement process is completed. The matching means may have a function of performing the merge processing.

  The determining means calculates an expected processing time in the matching means and the output means, and based on the calculation result, when the processing time of the output means is larger than the processing time of the matching means, It is determined that the matching means is to perform the merge processing.

  According to another aspect of the present invention, there is provided a database management system including a plurality of nodes interconnected via a network, wherein the database management system includes a storage unit that stores data constituting the database in a distributed manner. A plurality of first nodes for extracting data stored in the storage means when instructed, and sending the extracted data to the network for distributing the extracted data to another node; Receiving a query from a user and a plurality of second nodes for sending the result to the network, analyzing the query, and performing a database operation on the received data. Based on the result, the data is transmitted to at least one first node of the plurality of first nodes. , Instruct at least one of the second nodes to execute the database operation, receive the operation result sent over the network, and output the result for the inquiry And a third node.

  The third node determines a first node that instructs the retrieval of the data based on an analysis result of the received query, calculates an expected processing time in the first node, and calculates the processing time. The second node instructing execution of the database operation can be determined based on

  The third node determines a distribution of the extracted data to the determined second node based on an expected amount of extracted data at the determined first node. Make sure that nodes are evenly allocated.

  The third node may include a storage unit that stores optimization information on data in a storage unit of each of the nodes for uniformly assigning the extracted data to the determined second node.

  The third node uses a predetermined hash function to evenly allocate the extracted data to the determined second node.

  Further, the plurality of nodes independently perform processing, and the determined second node sequentially inputs the distributed data from the determined first node, and for each of the input data, Perform processing.

  Further, each of the plurality of first nodes may further rearrange data distributed by the first nodes.

  The third node is arranged in the first node after the distribution processing for the first node whose processing ends earlier based on the calculation result of the expected processing time in the determined first node. You can decide to make a replacement.

  The third node determines to increase the number of the second nodes determined based on the processing time by a predetermined number.

  The determined second node may perform a merging process after the rearrangement of the distributed data in the first node is completed.

  The second node performs a merge process after the rearrangement of the distributed data in the first node, and performs a matching process for an inquiry based on the merged data.

  The third node calculates an expected processing time in the matching process and a process of outputting a result with respect to the inquiry, and calculates a processing time of the output process based on the calculation result. If the time is longer than the processing time, the merge processing is performed during the matching processing.

(Action)
The decision management node determines the distribution node based on an analysis result of the inquiry of the analysis unit, calculates an expected processing time in the distribution node, and determines a connection node based on the processing time. The determining unit is configured to allocate the distribution of the extracted information to the connection nodes equally to each of the connection nodes based on the determined amount of information to be extracted at the distribution node.

  Each of the determined distribution nodes retrieves information from the storage unit based on an analysis result of the inquiry, and distributes the retrieved information to other nodes. The distribution node and the connection node perform processing independently of each other, and the connection node sequentially inputs information distributed from the distribution node and performs processing for each input information. Each of the determined connection nodes rearranges the information distributed from the distribution node, merges the rearranged information when there are a plurality of pieces of information, and matches a query to the query based on the merged information. And outputs the result for the query obtained from the join node.

  In addition, the determining unit determines from the calculation result of the expected processing time in the distribution node that the distribution node rearrangement unit rearranges the distribution node after the distribution process for the distribution node that finishes processing earlier. I do. The rearrangement means of the determined distribution node rearranges information distributed by the distribution node.

  Further, the determining unit calculates an expected processing time in the matching unit and the output unit, and based on the calculation result, when a processing time of the output unit is larger than a processing time of the matching unit, It is determined that the matching means is to perform the merge processing. The determined matching means performs a merging process.

  Further, when the processing time is predetermined, in order to perform the processing within the processing time, the determination unit determines the number of the connection nodes determined based on the expected processing time in the distribution node by a predetermined number. Decide to increase. As a result, the number of connection nodes increases, and the rearrangement unit of the connection nodes can perform the rearrangement processing in a short time, and thus performs the merge processing after the rearrangement processing ends.

According to the query processing method of the present invention, the number of each node can be determined corresponding to the database operation executed at each node. In addition, if there is a variation in data division, the data is equally divided into each node, and each database operation executed in each node is parameterized and the expected processing time is equalized. And smooth pipeline operation is possible.
Further, according to another configuration of the present invention, the third node receives an inquiry from the user, analyzes the inquiry, and, based on the analysis result, at least one of the plurality of first nodes. And instructing the first node to retrieve the data and instructing at least one of the second nodes to execute the database operation. And a third node configured to distribute the extracted data to the determined second node based on the expected amount of extracted data at the determined first node. To be evenly distributed.

  Each of the determined first nodes, when instructed, retrieves the data stored in the storage means and sends the retrieved data to the network for distribution to other nodes. The first node and the second node perform processing independently of each other, and the determined second node sequentially inputs the distributed data from the determined first node, and receives the input data. The processing is performed every time. Each of the determined second nodes, when instructed, receives the data transmitted to the network, performs a database operation on the received data, and transmits the result to the network. The third node receives the operation result transmitted on the network and outputs a result for the inquiry.

  Further, the third node, after calculating the expected processing time at the determined first node, distributes the first processing to the first node whose processing ends earlier, and then executes the processing at the first node. Decide to sort the data. The determined first node rearranges the data after the distribution processing.

  Further, the third node calculates an expected processing time in the matching processing and the processing of outputting the result with respect to the inquiry, and based on the calculation result, calculates a processing time of the output processing in the matching. If the time is longer than the processing time, the merge processing is performed during the matching processing.

  Further, when the processing time is predetermined, the third node increases the number of the second nodes determined based on the processing time by a predetermined number in order to perform the processing within the processing time. To decide. As a result, the number of second nodes increases, and the sorting process can be performed in a shorter time. After the rearrangement process is completed, a merge process can be performed.

  According to the present invention, it is possible to determine a node that extracts and distributes data and a node that executes a database operation. Also, if there is a variation in the data division, the data is equally divided into each node, each database operation executed in each node is parameterized, and the expected processing time is equalized. And the pipeline operation can be performed smoothly.

  The number of each node is determined according to the database operation executed in each node, the data is divided equally into each node, and the processing time executed in each node is equalized, so that the processing time is not biased among the nodes. In addition, a high-speed query process can be realized.

  Hereinafter, embodiments of the present invention will be described in detail with reference to the drawings.

  FIG. 2 shows a conceptual diagram of the database system of the present embodiment. In FIG. 2, a database system includes a plurality of application programs (hereinafter abbreviated as AP) 10 and 11 created by a user, and a database for query processing and resource management. An operating system (hereinafter, referred to as an operating system) that manages the entire computer system and a DBMS 20 that manages the entire computer system by reading and writing data to be subjected to input / output processing in database processing. Operating system), a database 40 for storing data to be subjected to database processing, and a dictionary 50 for managing database definition information. The DBMS 20 is connected to another database management system. The dictionary 50 also stores, for example, optimization information on the join columns used in the present embodiment.

  The DBMS 20 includes a system control unit 21 that performs input / output management and the like in addition to management and control of the entire system, a logical processing unit 22 that performs logical processing related to an inquiry, and a physical processing unit 23 that performs physical processing of a database. And a database buffer 24 for storing data to be processed by the DBMS 20. The logic processing unit 22 includes a query analysis 220 for performing syntax analysis and semantic analysis of the query, a static optimization process 221 for generating an appropriate processing procedure, and a code generation 222 for generating a code corresponding to the processing procedure. , A dynamic optimization process 223 for selecting an optimal one from the processing procedure candidates generated in the static optimization process 221, and a code interpretation execution unit 224 for interpreting and executing the code. Further, the physical processing unit 23 includes a data access process 230 for realizing condition determination, editing, record addition, and the like of the accessed data, a database buffer control 231 for controlling reading and writing of database records, and an input. A mapping process 232 for managing the storage location of data to be output and an exclusive control 233 for implementing exclusive control of resources shared by the system are provided.

  FIG. 3 shows an example of a hardware configuration to which the present invention is applied. Specifically, FIG. 3 shows an example of an application configuration of a parallel processor system in which a processor and a disk device constitute one node and a plurality of nodes are provided. In FIG. 3, processors 60 to 65 and disk devices 70 to 75 are connected by an interconnection network 80. The hardware configuration shown in FIG. 3 is a configuration for parallel processing of the database system shown in FIG. 2 by a plurality of processors, and the processing is distributed to each node.

  FIG. 1 shows a configuration in the case where functions are distributed for each of the nodes. FIG. 1 shows a schematic diagram of a database system to which the present embodiment is applied. Hereinafter, a processing example of the parallel database system will be described with reference to FIG. In this example, parallel processing is applied to a search request for a database. In FIG. 1, each node is assigned to each node a sort function for extracting and distributing data and a merge function for combining and processing data sorted by a plurality of nodes. Some nodes have only a sorting function, and some nodes have a sorting function and a merging function. The database consists of a table which can be viewed from the user in a two-dimensional table format, and the table has data for each row or row. A row is composed of one or more attributes (this is called a “column”). In FIG. 1, there are T1 and T2 as tables in the database. Table 1 is stored from node 1 (90) to node 4 (91), and table T2 is stored from node 5 (92) to node 8 (93). Each of these nodes is a distribution node, and the data extraction processing and the data distribution processing are executed based on the table stored in the distribution node. Nodes 9 (94) to 11 (96) are connection nodes that receive data output from nodes 1 to 4 and 5 to 8 and perform partial column sorting and merging to perform complete column sorting. Execute creation. Further, the node 12 (97) is a decision node that receives a query, analyzes the query, and determines the number of distribution nodes and connection nodes that execute processing for the query. The node 12 (97) receives and outputs data output from the nodes 9 to 11. These nodes are connected by an interconnection network 80. Nodes 1 to 4 and nodes 5 to 8 and nodes 9 to 11 operate in parallel, and are processed by nodes 1 to 4 and nodes 5 to 8, respectively. The result thus obtained operates in a pipeline such that the processing is immediately performed in the nodes 9 to 11 (hereinafter, referred to as a parallel pipeline operation). The nodes 9 to 11 and the node 12 also operate in a pipeline manner. Hereinafter, the partial column sorting process in the nodes 9 to 11 is called a slot sorting process, and the complete column creating process is called an N-way merge process. The slot sort process refers to a sort process within a page in which data is stored. If the data is read in the slot order, the rows can be accessed in ascending order. The N-way merge process uses an N-way buffer and inputs N sort runs at each merge stage to finally create one sort run.

  The query for the database search process is, for example, as follows.

SELECT T1. C3, T2. C3
FROM T1, T2
WHERE T1. C1 = T2. C1
AND T1. C2 =?
When such an inquiry is received at the node 12, the node 12 selects an optimal distribution processing method, and instructs each node via the network. In the above inquiry, the table T1 is stored from the node 1 (90) to the node 4 (91), and the table T2 is stored from the node 5 (92) to the node 8 (93). And a data distribution process is performed. Further, the nodes 9 (94) to 11 (96) sequentially receive the data output from the nodes 1 to 4 and the nodes 5 to 8, and execute the sorting process and the combining process. The node 12 (97) receives and outputs the data output from the nodes 9 to 11. This terminates the database search.

  Next, the relationship between the processing times of the above nodes will be described with reference to FIG. FIG. 4 is a schematic diagram for explaining the parallel pipeline operation. In FIG. 4, reference numerals 100 and 101 correspond to processing from node 1 (90) to node 4 (91) and from node 5 (92) to node 8 (93) in FIG. Execute 110 and 111 correspond to the processing from the node 9 (94) to the node 11 (96), and the slot sort processing, the N-way merge processing, and the matching processing are executed. Reference numeral 120 corresponds to the processing in the node 12 (97), and the request data output processing is executed. Along the time axis, the data processed in the data extraction processing and the data distribution processing 100 and 101 are sequentially transferred to the slot sort processing 110 and 111 and executed in a pipeline manner. The process from the data fetch process to the slot sort process is called a fetch phase. Also, the N-way merge processes 110 and 111 are simply executed in parallel at each node. This N-way merge processing period is called a merge phase. Further, the result of the matching process is sequentially transferred to the request data output process 120 and executed in a pipeline. The period from the matching to the output of the requested data is called a combining phase. The time chart shown in FIG. 4 shows the processing contents when the inquiry example shown in FIG. 1 is applied. In the extraction phase, the processing time from the node 1 (90) to the node 4 (91) is indicated as T1 data extraction / data distribution processing time 130. The processing time from the node 5 (92) to the node 8 (93) is indicated by a T2 data extraction / data distribution processing time 131, the transfer time in the interconnection network 80 is indicated by a data distribution transfer time 140, and the node 9 (94) ) To node 11 (96) are executed as shown in T1 / T2 slot sort processing time 150. In FIG. 4, the take-out phase ends up to the point in time of slot sort processing completion wait 180. In the merge phase, the processing time from the node 9 (94) to the node 11 (96) is executed at the time indicated by the T1 / T2N way merge processing time 151. This merge phase ends up to the T1 / T2N way merge process waiting 181. In the connection phase, the processing time from the node 9 (94) to the node 11 (96) is indicated by a matching processing time 152, the processing time in the interconnected network 80 is indicated by a connection result transfer time 160, and the processing time in the node 12 (97) is indicated. The processing time is indicated by a request data output processing time 170, and each is executed within that time.

  Next, a method of distributing the processing of the node 12 to each node group in FIG. 1 will be described with reference to FIG. FIG. 5 is an explanatory diagram showing a method of assigning data to each node group in the data distribution processing. As a premise, a node group that performs data extraction / data distribution processing includes ten nodes 1 to 10 each including processors 200 to 230 and disk devices 201 to 231. Further, it is assumed that the node group that performs the combining process includes five nodes 11 to 15 each including processors 240 to 250 and disk devices 241 to 251. The dictionary 50 stores optimization information 51 on the join column. The optimization information 51 is information for equally dividing the data of the database. For example, since the number of data items for the join column is not usually uniform, the optimization information 51 may be divided by the join column so that the number of data items becomes uniform. To do. As shown in FIG. 5, the data stored in the nodes 1 to 10 can be equally divided in the respective divided ranges from v1 to v10. In this case, in order to equally divide the data into the nodes 11 to 15, the node numbers 11, 12, 13, 14, and 5 are divided into five sections v1 to v2, v3 to v4, v5 to v6, v7 to v8, and v9 to v10. It suffices to provide a distribution processing means for associating 15 with the distribution processing means. When the above-mentioned optimization information does not exist, an appropriate hash function may be set to perform data distribution. In this way, the node 12 in FIG. 1 includes the distribution processing means, and distributes the processing to each node group when performing the N-way merge processing. As a result, in the case described above, the data can be equally divided into the nodes 11 to 15, and the processing time becomes equal.

  Next, a method of determining the number of connected nodes when performing the N-way merge process will be described with reference to FIG. FIG. 6 is a schematic diagram illustrating a method for determining the number of connected nodes.

  Each phase of the parallel connection processing in FIG. 1 and the processing time of each processing are graphed and laid out in accordance with the outline of the parallel pipeline operation shown in FIG. In FIG. 6, it is assumed that data fetching / data distribution processing is executed in nodes 1 to 8 and that processing times of 300 to 305 are required. Here, it is assumed that the processing time 304 of the node 5 is the maximum processing time. The slot sort processing time can be derived from the number N of coupling processing nodes, predetermined system characteristics (CPU performance, disk device performance, and the like), and a database operation method. It can be obtained by the following equation.

パイプライン処理を行う際の効果を最大にするために、スロットソート処理の性能特性と最大処理時間３０４との交点となる結合ノード割当て数３５０をノード数として求めることができる。結合ノード割当て数３５０が決まると、Ｎウェイマージ処理時間３２０および突き合わせ処理時間３３０が、Ｎウェイマージ処理の性能特性と突き合わせ処理の性能特性とから同様に推定できる。これらの処理時間の合計が問い合わせに対する全体の処理時間となる。このように結合ノード数を決定し、データ取り出し／データ分配処理において分配されたデータを逐次マージして同時に処理することにより、全体の処理時間（問い合わせをしてから出力されるまでの応答時間）を短縮することができる。

In order to maximize the effect of performing the pipeline processing, it is possible to determine the number of nodes 350 to be assigned, which is the intersection of the performance characteristic of the slot sort processing and the maximum processing time 304, as the number of nodes. When the number of connection nodes 350 is determined, the N-way merge processing time 320 and the matching processing time 330 can be similarly estimated from the performance characteristics of the N-way merge processing and the matching processing. The sum of these processing times is the total processing time for the inquiry. In this manner, the number of connection nodes is determined, and the data distributed in the data fetching / data distribution processing are sequentially merged and simultaneously processed, so that the entire processing time (response time from inquiry to output). Can be shortened.

  Specific examples of the performance characteristics used in determining the number of connection nodes will be described below. For example, assume that the number of rows is 10,000,000 in both table T1 and table T2, the number of conditions is T1-1 (the total number of rows is reduced to 1%), and the number of distribution nodes for data extraction / data distribution processing is Each of Tables T1 and T2 is equally divided into 16 nodes, the number of connection nodes is 8, the processor performance is 50 MIPS (50 million instructions executed per second), and the network transfer rate is 20 Mbytes / sec. . The result of processing by the actual database management system under such conditions or the result calculated from the performance model is as follows.

  The processing times of the distribution nodes in Table T1 and Table T2 are respectively 180 seconds, the T1 / T2 slot sort processing time is 80 seconds, the N-way merge processing time is 380 seconds, the matching processing time is 110 seconds, and the requested data output time is 10 seconds. It becomes. The processing time for the inquiry is estimated based on the processing performance of these results.

  Next, a processing time adjustment method (tuning method) for further reducing the response time based on the connection node number determination method shown in FIG. 6 will be described with reference to FIGS. 7, 8, and 9. . In the method described below, the distribution processing means of the node 12 is calculated in advance when determining the distribution of the processing to each node group, and determines the distribution from the result.

  FIG. 7 shows a schematic diagram of the slot sorting preprocessing. It is assumed that the data fetching / data distribution processing is executed by the nodes 1 to 8 and that the processing time of each of the nodes 300 to 305 is required. The processing time for each node varies depending on the number of data in each table. Further, the slot sorting process is set to be executed in the combination processing node group. If there is a variation in the processing time for each node, consider a processing procedure for transferring the slot sorting processing to the data extraction / data distribution processing node group. As shown in FIG. 7 as the preprocessing of the slot sort, the slot sort processing is performed at the node where the data extraction / data distribution processing ends earlier. According to the processing, the slot sort processing time at the node having the number of assigned joint nodes 350 can be reduced from 310 to 312. At the processing time difference 311, the N-way merge processing is shifted. This is nothing short of extending the run length of the slot sort process. As a result, the N-way merge processing time can be reduced, and as a result, the response time can be reduced.

  FIG. 8 shows a schematic diagram of slot sort run length tuning. For example, when a plurality of processes are performed within that time period, such as when there is a restriction on the processing time, each database operation executed by each node is parameterized, and time adjustment (tuning) is performed based on the expected processing time. The method of doing is explained. The number of connection processing nodes is increased to a minimum from the number of connection node assignments 350 obtained in FIG. 6 to shorten the response time. In this case, it is assumed that the number of connected nodes is 351. Assuming that the number of connected nodes is 351, the slot sort processing time is reduced from 310 to 312. In order to maximize the pipeline effect, the N-way merge processing is shifted to the slot sort processing in the processing time 311. As a result, the number of merges in the N-way merge process is reduced, and the processing time can be reduced to 320, and as a result, the response time can be reduced.

  FIG. 9 shows a schematic diagram of N-way merge count tuning. If the matching processing time 330 determined by the number of connection node assignments 350 is shorter than the required data output processing time 340, the last merge processing of the N-way merge processing can be shifted to the matching processing. If the sum of the last merge processing time 331 and the matching processing time 330 of the N-way merge processing does not exceed the required data output processing time 340, the final merge processing is shifted to the matching processing. Thereby, the response time can be reduced.

  Next, an operation flow of the database management system according to the present embodiment will be described. FIGS. 10, 11, 12, 13, 14 and 15 show flowcharts of the processing of the DBMS in this embodiment. In FIG. 10, the DBMS includes a query analysis process 400 for performing a query analysis process (step 220), a query optimization process (step 221), and a code generation process (step 222), which are performed before a query is executed. And a query execution process 410 for executing a process for a query by executing a code interpretation of the query (step 224).

  Hereinafter, an outline of each processing unit will be described.

(A) Query analysis processing 400
10A and 10C, in the query analysis (step 220), syntax analysis and semantic analysis of the query sentence input by the application program in the node 12 are executed (step 2200). In FIG. 10A, in the static optimization processing (step 221), the ratio of data satisfying the condition is estimated from the conditional expression appearing in the query at the node 12, and based on a rule set in advance. Then, a valid access path candidate (particularly, an index is selected) is created, and a candidate for a processing procedure is created. In the code generation (step 222), the processing procedure candidates in the node 12 are developed into an executable form.

(B) Query execution processing 410
In FIG. 10B, in the dynamic runtime optimization (step 223), a processing procedure to be executed in each node group is determined based on the constant substituted in the node 12. In the code interpretation execution (step 224), the processing procedure is interpreted and executed in each node.

  Next, a detailed processing flow of each processing unit will be described.

  In FIG. 10D, in the dynamic optimization processing (step 221), a predicate selection rate of a conditional expression appearing in a query is estimated (step 2210), and an access path including an index or the like is pruned (step 2211). A processing procedure candidate combining these access paths is generated (step 2212).

  In FIG. 10E, in the predicate selection rate estimation (step 2210), it is checked whether or not a variable appears in the query conditional expression (step 22101). If a variable appears, it is checked whether the conditional expression has column value distribution information (step 22104). If present, terminate. If not, a default value is set according to the type of the conditional expression (step 22105), and the process ends. If the variable does not appear, it is checked whether the conditional expression has column value distribution information (step 22104). If not, a default value is set according to the type of the conditional expression (step 22105), and the process ends. If there is, the selectivity is calculated using the column value distribution information (step 22103).

  In FIG. 11, in access path pruning 2212, the index of the column appearing in the query conditional expression is registered as an access path candidate (step 22120). Next, it is checked whether the table to be accessed by the query is divided and stored in a plurality of nodes (step 22121). If divided and stored, the parallel table scan is registered as an access path candidate (step 22123). If not, the table scan is registered as an access path candidate (step 22123). It is checked whether or not the selectivity of each conditional expression has already been set (step 22124). If set, the index of the conditional expression that minimizes the selectivity for each table is set as the highest priority of the access path (step 22125). If not set, the maximum / minimum value of the selectivity of each conditional expression is obtained (step 22126). Finally, the selection criterion of each access path is calculated from system characteristics such as CPU performance and IO performance (step 22127), and only the selection criterion of the access path combining single or plural indexes is lower than the above selection criterion. It is registered as an access path candidate (step 22128).

  In FIG. 12, the processing procedure candidate generation 2213 checks whether the table to be accessed by the inquiry is divided and stored in a plurality of nodes (step 22130). If divided and stored, the process proceeds to step 22135. If it is not divided and stored, it is checked whether or not the sorting procedure is included in the processing procedure candidate (step 22131). If it is included, the process moves to step 22135. If not, it is checked whether the access path of the table to be accessed by the query is unique (step 22132). If it is unique, a single processing procedure is created (step 22133). Is created (step 22134), and the process ends. In step 22135, the query is decomposed into two-way joins that can be joined. The data reading / data distribution processing procedure is registered as a candidate corresponding to the storage node group of the table to be divided and stored. Also, the slot sorting procedure is registered as a candidate (step 22136). A slot sort procedure, an N-way merge procedure, and a matching procedure are registered as candidates corresponding to the join processing node group, and the slot sort run length and the number of merge processes are parameterized (step 22137). The request data output procedure is registered in the request data output node (step 22138). Finally, when all the evaluations for the decomposition result have been completed (step 22139), the process ends.

  In FIG. 13, the code generation 222 checks whether or not the processing procedure candidate is unique (step 2220). If it is unique, the process proceeds to step 2223. If it is not unique, optimization information including column value distribution information and the like is embedded in the processing procedure (Step 2221), and a data structure for selecting the processing procedure based on the constant assigned at the time of executing the query is created (Step 2222). Finally, the processing procedure is developed into an executable form (step 2223).

  In FIG. 14, the dynamic optimization processing 223 checks whether or not the created processing procedure is single (step 22300). If it is single, it ends. If not, the selectivity is calculated based on the substituted constant (step 22301). It is checked whether or not the candidate processing procedure includes a parallel processing procedure (step 22302). If not included, the processing procedure is selected according to the access path selection criteria (step 22313), and the process ends. If it is included, the optimization information (column value distribution information of the join column, the number of rows and the number of pages of the table to be accessed, etc.) is input from the dictionary (step 22303), and the processing for data retrieval / data distribution is performed. The time is calculated in consideration of each system characteristic as described above (step 22304). The number p of nodes to be assigned to the combining process is determined from the processing time, and the processing procedure a1 is determined (step 22305). It is checked whether there is a variation in the data retrieval / data distribution processing time (step 22306). If there is a variation, a processing procedure a2 for executing the slot sort processing in the data extraction / data distribution processing node group is set (step 22307). Next, a processing procedure a3 in which the number p of connected nodes is increased by α is set (step 22308). If the required data processing time is larger than the sum of the matching processing time and the one-way N-way merge processing time (step 22309), the processing procedure a4 in which the one-way N-way merge processing is shifted to the matching processing is set ( Step 22310). The optimum processing procedure is selected from the processing procedures a1 to a4 from the viewpoints of the minimum response time, the minimum amount of load on each node, and the small effect on other transaction response performance (Step 22311). Data distribution information is created based on the optimization information (step 22312). If there is no optimization information, data distribution information is created according to the join column evaluation value of the hash function. The processing procedure is selected according to the access path selection criteria (step 22313), and the process ends.

  In FIG. 15, in a code interpretation execution process 224, a process is performed in each set node according to a corresponding processing procedure.

  First, each node determines whether data extraction / data distribution processing is set (step 22400). If the data extraction / data distribution processing is set, the database accessing the storage device of each node is accessed, and the conditional expression is evaluated (step 22401). Data is extracted based on the data distribution information created based on the optimization information, and the data is sequentially distributed to the buffers of the connection nodes (step 22402). It is determined whether or not the buffer of each connection node is full, and if full, the data is transferred to the corresponding connection node in a page format. When all the data corresponding to the inquiry is taken out and distributed, the process ends (step 22404).

  In addition, each node determines whether or not the slot sorting process is set (step 22405). If the slot sort processing is set, page format data is received from the data extraction / data distribution processing node (step 22406), and the received data is sequentially subjected to slot sort processing (step 22407). The processed slot sort result is temporarily stored, and the slot sort process ends (step 22408).

  In addition, it is determined whether or not the N-way merge process is set (step 22409). If the N-way merge process is set, the N-way merge process is executed based on the slot sort result (step 22410), and the N-way merge process result is temporarily stored in a buffer or the like (step 22411). To end.

  Further, it is determined whether or not the matching process has been set (step 22412). If the matching process has been set, the sorted list of the N-way merge process result is matched, and data is set in the output buffer (step 22413). If the output buffer is full, it is transferred to the request data output node in a page format (step 22415).

  In addition, it is determined whether or not the request data output process has been set (step 22416). If the request data output process has been set, it is determined whether or not there is transfer of page format data from the connection node (step 22417). If there is a page format data transfer, the page format data is received (step 22418), and the query processing result is output to the application program. If there is no page format data transfer, the query processing result is directly received. The data is output (step 22419).

  In the above-described code interpretation execution processing, when slot sorting is performed in the data extraction / data distribution processing node group when there is a variation in processing time, the code interpretation is performed after the data extraction / data distribution processing is completed. The execution process 224 is executed again to perform the slot sort process.

  Further, if the result of the N-way merge processing is not a complete sort sequence in step 22413, the merge and matching processing of the final stage are performed.

  By performing the processing as described above, the inquiry response time of the database management system can be reduced.

  The joint node assignment method shown in FIG. 6 and the tuning methods shown in FIGS. 7, 8 and 9 may be applied independently, or may be applied in any combination. That is, in the dynamic optimization process 223, it is assumed that all combinations can be applied. Further, in the data retrieval processing, application of a parallel input / output access method including a plurality of disk devices, application of a batch input / output method / read-ahead input / output method, and optimization of information or a data distribution method using a hash function in data distribution processing are performed. Application, application of a parallel sorting method to N-way merge processing, application of a matching processing method between nodes to the matching processing, application of a parallel reception processing method by allocating a plurality of nodes to the request data output processing, and the like are also conceivable. In steps 22309 and 22310, one N-way merge process is assumed. However, n times (n ≧ 1) may be generally used.

  With respect to the parallel pipeline operation shown in FIG. 4, when the joint node assignment method shown in FIG. 6 and the tuning methods shown in FIGS. 7, 8 and 9 are applied, among the extraction phase, merge phase, and join phase, In some cases, the merge phase can be omitted. That is, it becomes possible by extending the slot sort run length and moving the N-way merge process. In this case, the merge phase process is omitted from the query execution process.

  The query processing method of the present invention is not limited to the use of the rule using the statistical information and the cost evaluation, but may be applied to any method that can provide a processing procedure for providing appropriate database reference characteristic information. For example, the present invention can also be applied to a DBMS that performs optimization processing such as cost evaluation only, rule use only, and cost evaluation and rule use together.

  The present invention can be realized through a software system of a tightly-coupled / loosely-coupled multiprocessor system large-scale computer, or through a tightly-coupled / loosely-coupled multiprocessor system in which a dedicated processor is prepared for each processing unit. It is also possible to realize. Further, the present invention is applicable to a single processor system as long as parallel processes are allocated for each processing procedure.

  According to the present embodiment, the number of each node is determined according to the database operation executed at each node, and if there is a variation in the division of data, the data is equally divided into each node, Since each database operation to be executed is parameterized and the expected processing time is equalized, the processing time is not biased among the nodes, the pipeline operation can be performed smoothly, and high-speed query processing can be realized.

Schematic diagram of parallel join processing Configuration diagram of database system Hardware configuration diagram Schematic diagram of parallel pipeline operation Overview of data distribution processing Schematic diagram of binding node assignment Overview of slot sort preprocessing Slot sort run length tuning overview Schematic of N-way merge count tuning Flowchart of database management system Flowchart of database management system Flowchart of database management system Flowchart of database management system Flowchart of database management system Flowchart of database management system

Explanation of reference numerals

10, 11 application program, 20 database management system, 22 logical processing unit, 220 query analysis, 221 static optimization process, 222 code generation, 223 dynamic optimization process, 224 code execution , 30 operating system, 40 database, 50 dictionary, 80 interconnection network, 90, 91, 92, 93, 94, 95, 96, 97 nodes.

Claims (2)

Storage means for storing the data constituting the database in a distributed manner,
A plurality of first nodes for retrieving and sending stored data according to the retrieval request;
A plurality of second nodes that execute database processing on the data sent from the first node according to the input database processing request and output the database processing result;
The input query request is analyzed, a plurality of database processing requests are generated based on the join columns of the database, and distributed to the second nodes. Generating a retrieval request to the first node, receiving a database processing result of the database processing request from the second node, and outputting a processing result of the inquiry request. A database management system comprising: a node; A database management device that issues a database calculation request to a plurality of database calculation devices,
Analyzing the input query request, generating a plurality of database calculation requests based on the binding column of the database calculation key of the query request, distributing the generated plurality of database calculation requests to the database calculation device, A database management device comprising: a query processing unit that receives a processing result output from each of the database operation devices and outputs a processing result of the inquiry request.

Images