JP2016095561A - Control device, distributed database system, method, and program - Google Patents

Abstract

PROBLEM TO BE SOLVED: To reduce a total processing time for a work load that accompanies issuance of a query.SOLUTION: A control device according to the present invention comprises: query set acceptance means for accepting a query set including one or more queries for an object data group that is a prescribed data group stored separately in a plurality of data storage means; processing cost estimation means for estimating, on the basis of the meta-data of the object data group, a first processing cost that is a processing cost when a query session is executed without redividing/allocating the object data group and a second processing cost that is a processing cost when the query session is executed after redividing/allocating the object data group by a prescribed method; redivision/allocation determination means for determining, on the basis of the estimation result by the processing cost estimation means, whether or not to redivide/allocate the object data group, and, when redividing/allocating the object data group, what method should be used to redivide/allocate the object data group.SELECTED DRAWING: Figure 14

Description

  The present invention relates to a distributed database system, a control device that controls data partitioning, a partition placement control method, and a partition placement control program.

  As preprocessing of analysis processing that analyzes a large amount of data using machine learning, etc., the data group to be analyzed is extracted, aggregated, shaped, etc., so that the analysis processing is easy to process (for example, matrix, planning matrix) , Any data type with attribute data, etc.) may be performed. Such work is generally referred to as attribute generation processing (feature engineering).

  The attribute generation process is often performed using a database system. When using the database system, for example, the user or the means for performing attribute generation processing stores the data group to be analyzed as original data in the storage area of the database system, and then executes SQL for the database system. A query language generally called a query is issued to perform desired data manipulation. When the original data is enormous, a distributed database system is used.

  A distributed database system divides a data group into fine data blocks and stores them in a plurality of data nodes, so-called partitioning (partition), and data operation based on a query in parallel for each of the partitioned data blocks It is a system that performs. As open source software for data manipulation implemented in a distributed database system, Hive, Impala, Presto, and the like are widely known.

  In the attribute generation process, a large number of queries are issued at a time in order to obtain a form of data that can be easily processed by the analysis process. Therefore, it can be said that the attribute generation process is a time-consuming process in the analysis task. In order to improve the efficiency of the entire analysis task, it is required to improve the efficiency of attribute generation processing.

  As a technique for improving the efficiency of data operation in a distributed database system, there is a technique for adding meta information to each divided data block.

  An example of this technique will be described using ORCFFile, which is one of the data formats supported by Hive, as an example. In the ORC file, a record group (original data) handled by Hive is divided into a group composed of a plurality of records (row data) and held as a data block. That is, in the ORC file, a plurality of records are stored in each data block. Each data block holds statistical information such as the maximum value, the minimum value, the sum, and the count value of the records for each column in the data block as metadata. Hereinafter, the metadata indicating the statistical amount of data in the data block, which is given to the data blocks arranged in a divided manner, may be referred to as block statistical information.

  Block statistics information is efficient when performing certain types of data operations. For example, consider the following query:

Query example 1:
select * from Table1 where attribute01 <= 100;

  Query example 1 is a query for performing a data operation of acquiring all records having a column (attribute) name column value “attribute01” of 100 or less from a table “Table1”. The table is also called a database table or a relationship model object.

  When the distributed database system using Hive receives such a query, first, based on the condition indicated by the predicate “attribute01 <= 100” in the query, the block statistical information of each data block is referred to, and the column “ Check the minimum value of “attribute01”. Here, the predicate is a predicate in the predicate logic, and corresponds to, for example, a part of a logical expression specified in the where clause in the query. Among the predicates used in a query, there are a selection predicate and a join predicate that cause a change in processing cost depending on the way of divided arrangement. The predicate used in the above query example 1 is a selection predicate.

  Now, suppose that the minimum value of the “attribute01” column indicated by the block statistical information of a certain data block is 110. In that case, only the record whose value of the “attribute01” column is 110 or more exists in the data block. Therefore, such a data block does not need to be read in the process of the query example 1.

  In the distributed database system using Hive, when ORFFile is used as the data format, first, block statistical information of each data block is referred to based on the predicate in the issued query. Then, when it is found that there is no record in the data block that matches the predicate, more specifically, a record that satisfies the logical expression indicated by the predicate, the distributed database system avoids reading the data block. In this way, the generation of I / O (Input / Output) for the storage area is suppressed, and higher-speed data operation is realized.

  As a technique related to the divided arrangement of data in the distributed database system, for example, there is a technique described in Non-Patent Document 1. In Non-Patent Document 1, when a database system stores additional data, how to divide and arrange the additional data based on the history of queries issued in the past with respect to the data already stored. A data division arrangement method for determining the data is described. Specifically, the database system extracts from the query history a set of selection predicates that frequently appear in previously issued queries, and matches any one or more selection predicates included in the extracted selection predicate set. Sort the lines that are not in the same group. By performing such a divided arrangement, a data block corresponding to a group that does not match one or more selection predicates can be skipped when a query including any of the corresponding selection predicates is issued. Further, it is possible to improve the efficiency of data operation by reducing unnecessary I / O to the storage area.

  Also, in Patent Document 1, each of a plurality of table areas is determined based on the access frequency and the record hit rate for each of a plurality of divided tables calculated from an access history at each designated timing. Is described to change the arrangement of the partition table to be stored.

  Further, in Patent Document 2, when a query is issued, when the transaction for processing the query does not match the order of dimensions in the sort state in the current block and the output order of the query results, It is described that the block is sorted.

  Patent Document 3 discloses that a query is converted into a processing tree composed of tasks having a relational algebra such as a selection operation or a join operation as a contact point, and the relational algebra is converted into a result of another relational algebra from the converted processing tree. It describes that a group of tasks that can be executed without depending on the database is taken out and grouped for each access area to the database. Further, in Patent Document 3, when performing multi-processing and processing for grouped tasks, tasks are processed in ascending order of processing costs, and access to a common block is performed at once. Etc. are described.

JP 2009-288879 A JP-A-11-003260 JP 2008-165622 A

L. Sun, M. J. Franklin, S. Krishnan, and R. S. Xin, "Fine-grained partitioning for aggressive data skipping.", SIGMOD 2014, June 2014, p.1115-1126.

  In order to obtain the effect of suppressing unnecessary I / O by the block statistical information, the data block must be appropriately divided and arranged. That is, in order to improve the efficiency of data operation in the distributed database system, it is important that the original data is divided into data blocks that can suppress unnecessary I / O.

  For example, if it is known in advance that a query including a predicate related to the “attribute01” column as in Query Example 1 is frequently issued, each record of “Table1” is divided and arranged based on the value of the “attribute01” column Thus, unnecessary I / O to the storage area can be effectively suppressed.

  However, in an analysis task in which an analyst performs an analysis process while performing trial and error, attributes to be generated in the attribute generation process are also different each time. For this reason, it is difficult to grasp in advance the tendency of the columns used in the query issued in the attribute generation process.

  For example, as in the method described in Patent Document 1, if a past access history is used, a partition table having a low hit rate despite a high access frequency may be further divided into two or more partition tables. Is possible. However, when a newly issued query set does not follow the access history, the method based on such access frequency and hit rate cannot improve the efficiency of data operation.

  In other words, the access frequency and record hit rate indicated by the access history were suitable as the criteria for data division in the review of the data division arrangement periodically performed in database systems that issue queries with similar trends. However, it is not suitable as a criterion for asking whether or not to re-divide data before executing a new query set.

  Note that, as in the method described in Non-Patent Document 1, any method that sets a partition key based on the frequency of predicates in a query may be applicable to a new query set.

  However, the method described in Non-Patent Document 1 has the following problems. First, the method described in Non-Patent Document 1 considers only the frequency of predicates in the query set, and does not consider the processing cost that the predicates themselves give to data operations. For this reason, when a predicate that has a low processing cost and does not cause a problem frequently appears in the query history, the predicate that is truly a bottleneck of the processing cost may be ignored. If a predicate that is truly a bottleneck in processing costs is ignored, the efficiency improvement effect may not be expected.

  Secondly, the method described in Non-Patent Document 1 considers only the selection predicate and ignores the combined predicate. A join predicate is a predicate that requires a join operation between a plurality of tables. The selection predicate is a predicate that does not require a join operation between a plurality of tables and requires a selection operation for extracting a row that matches a condition from one table.

Query example 2:
select * from T, R where T.name = R.name ∧ T.age>30;

  For example, in the above query example 2, a logical product of two predicates of a join predicate “T.name = R.name” and a selection predicate “T.age> 30” is used. Since it is a logical product, “T.name = R.name∧T.age> 30” may be collectively regarded as one join predicate.

  There are several algorithms with different computational complexity in the join operation. For example, in the case of the above query example 2, the required operation is a combination of the selection operation and the join operation, but the data is appropriately sorted in advance and divided. The more efficient algorithm can be selected in the join operation.

  However, the method described in Non-Patent Document 1 does not disclose any join predicates, and only targets selection predicates. Therefore, when a join predicate is used, an appropriate division arrangement can be performed. Can not. In general, since the processing cost of the join operation tends to be larger than the selection operation, the method described in Non-Patent Document 1 that targets only the selection predicate causes a large opportunity loss in processing efficiency. Yes.

  Thirdly, the method described in Non-Patent Document 1 assumes that the newly added data is divided for the first time, and is suitable for data already existing in the database. It is not assumed that data is divided and arranged again. In attribute generation processing, a large number of queries are often issued at a time. When such a large number of queries are viewed as a query set that is a series of processing requests, it takes a lot of time to execute the query set. For this reason, when a query set including a large number of queries is accepted, it may be possible to reduce the total execution time by re-splitting the data according to an appropriate standard corresponding to the query set before the execution. is there. However, since it may naturally take extra time to redo the data divisional arrangement, a mechanism for determining whether to perform the data divisional arrangement is necessary. However, Non-Patent Document 1 does not describe any such mechanism. Not considered. In general, the process of redoing and re-dividing data, that is, the process of dividing and re-allocating data using a different division method when the data is divided and arranged in a plurality of data nodes, This is called re-partition.

  Note that the method described in Patent Document 2 is intended to prevent the same sort process from being repeated for each block by holding information indicating the sort state in the block for each block. Therefore, no consideration is given to the optimization of the divided arrangement of the entire data.

  In addition, the method described in Patent Document 3 is a method for grouping tasks corresponding to arithmetic processing in a query for each data area of an access destination to improve processing efficiency. No consideration is given to the optimization of.

  Therefore, the present invention provides a control device, a distributed database system, a divided arrangement control method, and a distribution that can reduce the total processing time of a workload that involves issuing multiple queries such as attribute generation processing and queries that require time for predicate operations. An object is to provide a program for arrangement control.

  A control device according to the present invention includes a query set receiving unit that receives a query set including one or more queries for a target data group that is a predetermined data group that is divided and stored in a plurality of data storage units, and target data Based on the group metadata, the first processing cost, which is the processing cost when the query set is executed without subdividing the target data group, and the query after subdividing the target data group by a predetermined method Based on the processing cost estimation means for estimating the second processing cost, which is the processing cost when the set is executed, and the estimation result by the processing cost estimation means, the target data group is subdivided before executing the query set Re-division arrangement determining means for determining whether or not to arrange, and in the case of sub-division arrangement, how to re-divide arrangement. To.

  The distributed database system according to the present invention includes a plurality of data storage means for dividing a predetermined data group into a plurality of data blocks and storing the data, and one or more queries for the target data group that is the predetermined data group. Query set receiving means for receiving a query set, metadata storage means for storing metadata of the target data group, and processing cost when executing the query set without subdividing the target data group based on the metadata A processing cost estimation unit that estimates the first processing cost and the second processing cost that is the processing cost when the query set is executed after the target data group is subdivided and arranged, and the processing cost estimation unit Based on the estimation result, whether or not to subdivide the target data group and execute subdivision arrangement before executing the query set Is a subdivision arrangement determination unit that determines how to perform subdivision arrangement, and when the target data group is determined to be subdivision arrangement by the subdivision arrangement determination unit, before executing the query set, And a subdivision arrangement executing means for executing subdivision arrangement of the data group.

  Further, in the data division arrangement control method according to the present invention, the control device includes a query set including one or more queries for a target data group that is a predetermined data group that is divided and stored in a plurality of data storage means. Receiving and processing device when the control device executes the query set without subdividing the target data group based on the metadata of the target data group, and the target data group are determined in advance before executing the query set The processing cost is estimated when the query set is executed after subdivision placement by the above method, and before the controller executes the query set, the target data before executing the query set is executed based on the estimation result. It is characterized in that it is determined whether or not a group is to be subdivided and in what manner the group is to be subdivided.

  In addition, the divided arrangement control program according to the present invention accepts a query set including one or more queries for a target data group, which is a predetermined data group that is divided and stored in a plurality of data storage means, in a computer. Based on the processing and metadata of the target data group, the processing cost when executing the query set without subdividing the target data group, and executing the query set after subdividing the target data group by a predetermined method Whether to re-divide the target data group based on the estimation result before executing the query set and the processing cost, and what method to re-divide It is characterized in that a process for determining whether to divide and arrange is executed.

  According to the present invention, it is possible to reduce the total processing time of a workload that involves issuing a query such as attribute generation processing.

It is a block diagram which shows the structural example of the distributed database system concerning embodiment of this invention. 3 is a block diagram illustrating a configuration example of a processing cost estimation unit 12. FIG. It is a flowchart which shows the operation example of the distributed database system concerning embodiment of this invention. It is a flowchart which shows the operation example of the process cost estimation means 12 and the subdivision arrangement | positioning judgment means 13. It is explanatory drawing which shows the example of a query set. It is explanatory drawing which shows the example of the extraction result of a predicate. It is explanatory drawing which shows the example of the extraction result of a column set. Is an explanatory diagram showing an example of a subset Q _{i 'queries.} It is explanatory drawing which shows the example of a division | segmentation arrangement | positioning of an object data group. It is explanatory drawing which shows the other division | segmentation arrangement | positioning example of a target data group. It is explanatory drawing which shows the other division | segmentation arrangement | positioning example of a target data group. It is explanatory drawing which shows the example of a query execution plan. It is explanatory drawing which shows an example of metadata. It is a block diagram which shows the outline | summary of this invention.

  Hereinafter, embodiments of the present invention will be described with reference to the drawings. FIG. 1 is a block diagram showing a configuration example of a distributed database system according to an embodiment of the present invention.

  The distributed database system shown in FIG. 1 includes a query set receiving unit 11, a processing cost estimating unit 12, a subdivision arrangement determining unit 13, a subdivision arrangement execution unit 14, a query execution unit 15, and a plurality of data storage units. 21 and metadata storage means 22. In FIG. 1, each component is connected to be communicable with each other via a network, a bus, or the like.

  Each of the data storage means 21 stores a data block obtained by dividing a data group to be processed by a query. Hereinafter, a data group to be processed by a query is simply referred to as a target data group.

  In the present embodiment, the target data group is table data composed of one or more row data including values assigned to each of one or more columns. In the present invention, the table data is used in the sense of a table entity. The target data group is divided into a plurality of data blocks each having one or more row data using a column set, which is a set of one or more arbitrary columns in the table data, as a partition key, and the data storage means 21 Stored in More specifically, the target data group is divided into a plurality of data blocks and stored in the data storage means 21 based on the value of the column set designated by the partition key included in each row data. The partition key is a key used as a reference when dividing table data. Further, the distributed database system holds information indicating to which data block each row data belongs.

  The metadata storage unit 22 stores the metadata of the target data group. The metadata of the target data group only needs to include information that allows the processing cost estimation unit 12 described later to calculate the processing cost of the query set. The metadata of the target data group includes, for example, basic information such as the data size (number of rows, byte size, etc.) of the target data group and the data size (number of rows, byte size, etc.) of each data block, Statistics related to various categories (columns, rows, collections, etc.), especially column values in the table data of the target data group such as cardinality, selectivity, and histogram of the value of each column of the target data group Statistics may be included.

  In this embodiment, the calculation method of the processing cost of the query set is not particularly limited, but the metadata of the target data group is a statistic regarding the value of the column in the table data set as the target data group, and the occupation in the column It is preferable to include a statistic regarding the degree and the degree of selection (ease of selection or difficulty of selection).

  The query set accepting unit 11 accepts a query set for the target data group from the outside. The query set only needs to include one or more queries. For example, the query set publisher may input one or more queries to be executed as a series of processes as a query set.

  The processing cost estimation unit 12 is the first processing cost when the query set is executed without subdividing the target data group based on the metadata of the target data group stored in the metadata storage unit 22. And a second processing cost that is a processing cost when the query set is executed after the target data group is re-divided and arranged by a predetermined method. For example, the processing cost estimation unit 12 may estimate the first processing cost and the second processing cost in response to a request from the subdivision arrangement determination unit 13.

  In the present embodiment, the processing cost is an index related to the processing time, which is the time taken to complete the specified processing, and can be compared with the processing time by comparing the processing costs. It doesn't matter.

  The processing cost estimation means 12 is not only the metadata of the target data group, but also the components of the distributed database system such as the disk bandwidth of the computer that controls each data storage means 21 and the network bandwidth that connects the computers. The first processing cost and the second processing cost may be estimated based on the information regarding the processing performance. Here, the bandwidth is used to mean the data transfer capacity per unit time.

  FIG. 2 is a block diagram illustrating a configuration example of the processing cost estimation unit 12. As shown in FIG. 2, the processing cost estimation unit 12 includes a column set extraction unit 121, a first processing cost calculation unit 122, a divided arrangement method storage unit 123, and a second processing cost calculation unit 124. You may go out.

  The column set extraction unit 121 extracts a column set that appears in a query predicate (more specifically, a selection predicate and a join predicate) included in the query set. The column set extracted by the column set extraction unit 121 is used as a partition key candidate in the subdivision arrangement, and is used to limit queries for which processing costs are to be calculated. The column set extraction unit 121 extracts column sets that appear at a predetermined frequency or higher from column sets that appear in the selection predicates or join predicates of the queries included in the query set in order to further reduce the processing cost calculation time. Also good.

  The first processing cost calculation unit 122 calculates a first processing cost.

  The divided arrangement method storage unit 123 stores information for specifying a divided arrangement method used when calculating the second processing cost. The divided arrangement method storage unit 123 may store a plurality of pieces of information specifying the divided arrangement method. Examples of partitioning methods include partitioning methods that divide the target data group so that many row data with the same column set value as the partition key are included in the same data block, and partition keys that span all data blocks. For example, there is a division method that divides the target data group so that the row data is sorted by the value of the column set. Even when the same division method is used, when a different number of divisions is designated, it may be handled as a different division arrangement method. The information stored in the divided arrangement method storage unit 123 is not particularly limited as long as it is information specifying a divided arrangement method that can be handled by the subdivision arrangement execution unit. If the division arrangement method for calculating the second processing cost is fixed, for example, because there is only one division arrangement method that the subdivision arrangement execution unit 14 can handle, the division arrangement method storage unit 123 It may be omitted. Hereinafter, the division arrangement method specified by the information stored in the division arrangement method storage unit 123 may be simply referred to as a designated division arrangement method.

  The second processing cost calculation unit 124 calculates a second processing cost. The second processing cost calculation unit 124 identifies each column set extracted by the column set extraction unit 121 as a partition key candidate, and is specified for each candidate by information stored in the divided arrangement method storage unit 123. A second processing cost is calculated when the target data group is subdivided and arranged by the division arrangement method. At this time, the second processing cost calculation unit 124 may calculate the second processing cost only for queries that use the candidate column set. In this case, the first processing cost calculation unit 122 also calculates the first processing cost for only the query that uses the candidate column set for the candidate.

  The first processing cost calculation unit 122 and the second processing cost calculation unit 124 calculate the processing cost on the assumption that a predetermined optimization algorithm is applied as the query execution algorithm when calculating the processing cost. May be. The predetermined optimization algorithm may be, for example, an execution algorithm that optimizes the order of operations of a query including a join operation.

  Although not shown, the distributed database system includes an algorithm storage unit that stores information indicating what algorithm the query execution unit 15 described later corresponds to, and a query set storage unit that stores an accepted query set. You may have.

  Note that the first processing cost calculation unit 122 and the second processing cost calculation unit 124 calculate these processing costs before executing the query set.

  The subdivision arrangement determining unit 13 determines whether to subdivide the target data group before executing the query set based on the first processing cost and the second processing cost estimated by the processing cost estimation unit 12. In the case of subdivision arrangement, it is determined how to perform subdivision arrangement.

  For example, the re-division arrangement determination unit 13 determines the partition key candidate and the division arrangement method for which the smallest second processing cost is calculated when any of the second processing costs is smaller than the first processing cost. May be used to determine that the target data group is to be subdivided.

  The subdivision arrangement execution unit 14 executes subdivision arrangement of the target data group before executing the query set when the subdivision arrangement determination unit 13 determines that the target data group is subdivided.

  For example, in response to a request from the subdivision arrangement determination unit 13, the subdivision arrangement execution unit 14 may execute subdivision arrangement of the target data group using a designated partition key and division arrangement method. More specifically, the subdivision arrangement execution unit 14 allocates each row data included in the table data to one of the plurality of data blocks based on a set of values related to the column set designated as the partition key in the table data. May be. At this time, for example, the subdivision arrangement execution unit 14 may generate partition information indicating which data block the row data belongs to for each row data. Then, the subdivision arrangement execution means 14 stores each row data in the data block to which the row data belongs by issuing a query instructing the movement of the row data based on the generated partition information. Data storage means 21 to be moved. The re-division arrangement executing unit 14 generates metadata (block statistical information) indicating the statistical amount of data in each data block in the new division arrangement after executing the sub-division arrangement, and stores the metadata. 22 may be stored.

  The query execution means 15 executes the designated query.

  In the present embodiment, the query set accepting unit 11 includes, for example, a data input / output device that inputs and outputs data such as a mouse, a keyboard, and a network card, and an information processing device such as a CPU that operates according to a program stored in the storage unit. And realized by. Further, the processing cost estimation unit 12, the subdivision arrangement determination unit 13, the subdivision arrangement execution unit 14, and the query execution unit 15 are realized by an information processing apparatus such as a CPU that operates according to a program stored in the storage unit, for example. Is done. In addition, the data storage unit 21, the metadata storage unit 22, and other storage units (the algorithm information storage unit, the query set storage unit, and the like) are realized by a storage device, for example.

  Next, the operation of this embodiment will be described. FIG. 3 is a flowchart showing an operation example of the distributed database system of the present embodiment. In the example shown in FIG. 3, first, the query set receiving unit 11 receives a query set from the outside (step S11).

  Next, the processing cost estimation means 12 calculates the first processing cost and the second processing cost of the accepted query set (step S12). The processing cost calculation method will be described later.

  Next, the subdivision arrangement determination unit 13 determines whether or not to redivide the target data group based on the calculated first processing cost and second calculation cost, and when subdivision arrangement is performed. It is determined which method is used for re-division arrangement (step S13).

  In step S13, the re-division arrangement determining unit 13 compares the calculated first processing cost with the second processing cost, and if any of the second processing costs is smaller than the first processing cost. (Yes in step S13), it is determined that the subdivision arrangement is performed using the method (partition key candidate and division arrangement method) in which the smallest second processing cost is calculated. Further, the subdivision arrangement determination unit 13 compares the calculated first processing cost with the second processing cost, and if neither of the second processing costs is smaller than the first processing cost (step No in S13), it is determined that the subdivision arrangement is not performed (proceeds to step S15).

  When the subdivision arrangement determination unit 13 determines that subdivision arrangement is to be performed (Yes in step S13), the subdivision arrangement execution unit 14 executes subdivision arrangement by a designated method (step S14).

  Finally, the query execution means 15 executes the accepted query set and outputs the execution result (step S15, step S16).

  On the other hand, when the subdivision arrangement determination unit 13 determines not to perform subdivision arrangement (No in step S13), the query execution unit 15 executes the accepted query set with the current division arrangement, and the execution result. Is output (step S15, step S16).

  Next, the operation of this embodiment will be described using a specific example. The flowchart shown in FIG. 4 is an example of a more detailed processing flow of the above-described steps S12 to S14 that are operations of the processing cost estimation unit 12 and the subdivision arrangement determination unit 13.

In the example illustrated in FIG. 4, first, the first processing cost calculation unit 122 of the processing cost estimation unit 12 uses each query q _j εQ of the query set (hereinafter referred to as query set Q) received by the query set reception unit 11. Query_cost_no _j is calculated as the first query execution cost, which is the processing cost for executing the query q _j when the subdivision arrangement is not performed (step S101).

FIG. 5 is an explanatory diagram showing an example of the query set Q. For example, it is assumed that the query set accepting unit 11 accepts a query set Q including five queries (q1 to q5) as shown in FIG. In such a case, the first processing cost calculation unit 122 calculates query_cost_no _j for each of q _j = q1 to q4. In this example, j is an integer of 1 to 4.

Next, the column set extraction unit 121 of the processing cost estimation unit 12 extracts all column sets C _i that appear at a predetermined frequency or higher in either the selection predicate or the join predicate from the query q _j of the query set Q. set set of columns _{C i {C 1, C 2} ,. . . , CI} is obtained (step S102). Note that I is the number (number of types) of the extracted column set C _i . Incidentally, the column set extraction unit 121, if the number of the extracted set of columns C _i is not less than the predetermined number may be extracted a predetermined number of column sets C _i from the most emerging set of columns C _i in order. Further, the column set extraction means 121, when the number of extracted column sets C _i is zero, calculates the second processing cost when using a different divided arrangement method as it is with the partition key in the current divided arrangement. The second processing cost calculation unit 124 may calculate it.

Below, include a simple extraction method set of columns C _i as an example. The extraction method enumerates all the column sets that appear in either the selection predicate or the join predicate of the query in the query set Q with a query set Q and a frequency threshold θ. Any method or algorithm can be used.

  In a simple extraction method, the column set extraction unit 121 first scans the query set Q and extracts predicates from the where clause. The column set extraction unit 121 extracts all column combinations used in the extracted predicates, and generates a single column set whenever the extracted column combinations are different. The example by the query set Q shown in FIG. 5 is shown. First, the column set extraction unit 121 scans each query (q1 to q4) in the query set Q and extracts a predicate from the where clause. At this time, if there is a predicate connected by logical sum (∨) as in the query q3, the column set extraction unit 121 divides the predicate and handles it as separate predicates.

  FIG. 6 is an explanatory diagram illustrating an example of a predicate extraction result. FIG. 6 shows an example of predicates extracted from the query set Q shown in FIG. According to the above method, five predicates (p1 to p5) are extracted from the query set Q shown in FIG.

  Subsequently, the column set extraction unit 121 extracts a column set including a combination of columns appearing in each of the extracted predicates, and calculates the frequency at which the extracted column set appears in all the extracted predicates. At this time, when a plurality of columns existing in the same table co-occur in one predicate, the column set extraction unit 121 extracts all combinations created by the plurality of columns as a column set, Calculate the frequency for each of the extracted column sets. For example, in the predicate p1, three columns “T.name”, “R.name”, and “T.age” appear. “T.name” and “T.age” are columns existing in the same table. There are three possible combinations of columns created by multiple columns in the same table: {T.name}, {T.age}, {T.name, T.age}. Each combination is extracted as one set of columns. From the predicate p1, a different column {R.name} of the table is further added, and {T.name}, {T.age}, {T.name, T.age}, {R.name} A total of four column sets are extracted. Since all of these column sets appear in the predicate p1, the number of appearance predicates is incremented by 1 for each of these four column sets. The column set extraction unit 121 performs the same processing on the extracted predicates p1 to p5. If the column set extracted from the next predicate completely matches the already extracted column set, the number of appearance predicates may be incremented by 1 with respect to the completely matched column set instead of a new column set. .

FIG. 7 is an explanatory diagram illustrating an example of column set extraction results. FIG. 7 shows a column set extracted from the predicate set shown in FIG. 6 and its frequency. As shown in FIG. 7, in this example, five column sets C _i = C _{1 to} C ₅ are extracted, and the frequency (number of appearance predicates) is calculated for each of the extracted column sets C _i . Now, assuming that θ = 3, the column set extraction unit 121 extracts column sets C ₁ and C ₂ as column sets that frequently appear in the query set Q. Note that the column set extraction unit 121 excludes the same column set as the partition key in the current division arrangement from the extraction target when the subdivision arrangement determination unit 13 designates only the partition key as a method for the subdivision arrangement. May be.

  In the example shown in FIG. 7, the frequency is defined as the number of predicates in which the target column set appears, that is, the number of appearance predicates, but the definition of the frequency is not limited to this. For example, the ratio of the number of appearance predicates to the total number of extracted predicates may be the frequency, the number of queries in which the target column set appears in the predicate instead of the number of predicates may be the frequency, or any other definition The measured index may be used.

Next, the column set extraction unit 121, for each of the extracted set of columns C _i, from the query q _j included in the query set Q, it extracts the query q _j that covers the column set C _i Thus, a subset Q _i ′ consisting of the extracted query q _j is obtained (step S103). Here, the query covering the column set C _i is specifically a query including a selection predicate or a join predicate, and a column that is an element of the column set C _i is included in the one query. It is a query that appears in all. Hereinafter may _{be 'each} query q _j included in the query q _j' subset Q _i referred to. Here, j ′ represents that which satisfies the condition of covering the designated column set C _i among the identifiers _j of the queries q _j included in the query set Q.

FIG. 8 is an explanatory diagram illustrating an example of a subset Q _i ′ of a query. FIG. 8 shows an example of the subsets Q ₁ ′ and Q ₂ ′ of the query extracted from the query set Q for the column sets C ₁ and C ₂ that are the column set C _i extracted from the query set Q shown in FIG. It is shown.

For example, when the column sets C ₁ and C ₂ are extracted as frequent column sets, the column set extraction unit 121 scans the query set Q again, and the column set C ₁ or the column set C ₂ is the where clause. The query appearing in the predicate is specified, and the identifier j of the specified query may be associated with the identifier i of the corresponding column set.

In this example, the column set extraction unit 121 obtains Q ₁ ′ = {q1, q2, q3} as a subset Q ₁ ′ of queries in which all column sets C ₁ = {T.age} appear. Further, the column set extraction unit 121 obtains Q ₂ ′ = {q1, q2, q5} as a subset Q ₂ ′ of queries in which all column sets C ₂ = {T.name} appear.

  Next, the second processing cost calculation unit 124 of the processing cost estimation unit 12 uses the metadata of the target data group stored in the metadata storage unit 22 and the information stored in the divided arrangement method storage unit 123. Based on this, a second processing cost is calculated when the column set extracted in step S102 is re-divided and arranged as a partition key (step S104).

In step S104, the second processing cost calculating unit 124 calculates partition_cost _i as a divided arrangement cost that is a processing cost for executing the re-divided arrangement when each of the extracted column sets C _i is a partition key. At the same time, query_cost_part _{ij ′} is calculated as the second query execution cost that is the processing cost for executing each of the queries q _{j ′} ∈Q _i ′ in the state after the subdivision arrangement.

In the present example, the second processing cost is obtained by adding partition_cost _i and query_cost_part _{ij ′} for all j ′ of the query q _{j ′} ∈Q _i ′ to one column set C _i . Also, the sum of query_cost_no _j for all j with j = j ′ in the query q _j εQ is the first processing cost compared with the second processing cost of the column set C _i . .

  When the first processing cost and the second processing cost are calculated, the subdivision arrangement determination unit 13 compares these, and determines whether or not subdivision arrangement is performed. A partition key used at times is determined (steps S105 to S112).

In this example, the subdivision arrangement determining unit 13 determines whether there is a column set C _i whose total processing cost is smaller than that when the subdivision arrangement is not performed, and cost_cut in which the following conditional expression (1) is true Judgment is made based on whether _i exists. Note that cost_cut _i in the conditional expression (1) corresponds to an estimation of the amount of cost reduction by subdivision arrangement in the designated column set C _i .

In the example shown in FIG. 4, subdivided rearrangement determining means 13 first is initialized identifier i set of columns C _i, conditional expressions (1) initializes the buffer B for storing a C _i became true (Step S105). In step S105, i = 1 and B = Φ. Here, Φ is a symbol representing an empty set. In this example, the value of the identifier i is set to 1 for easy understanding, but the format of the identifier is not particularly limited.

Next, the subdivision arrangement determination unit 13 determines the conditional expression (1) for the column set C _i indicated by the identifier i (step S106). Specifically, the subdivision arrangement determination unit 13 calculates cost_cut _i according to the conditional expression (1), and determines whether the calculated cost_cut _i is 0 or more. If conditional expression (1) is true (Yes in step S106), the buffer B, and the column set _{C i,} or the identifier i used to calculate the Cost_cut _i, is stored with the calculated value of the Cost_cut _i ( Step S107). On the other hand, if false (No in step S106), the process directly proceeds to step S108.

In step S108, i is incremented. Next, the subdivision arrangement determination means 13 determines whether or not _i exceeds I, which is the maximum number of column sets C _i in step S109, that is, all column sets C _i extracted in step S102. It is determined whether or not the processing of S107 is completed.

Subdividing the rearrangement determining means 13 has completed the processing of step S106~ step S107 for all columns set _{C i} (Yes in step S109), determines whether the buffer B is not empty (step S110). If the determination formula in step S110 is true, that is, if the buffer B is not empty, it is determined that subdivision arrangement is performed, and the process proceeds to step S111. On the other hand, if the determination formula in step S110 is false, that is, if the buffer B is empty, it is determined that the subdivision arrangement is not performed (step S112). For example, when it is determined that the subdivision arrangement is not performed, the subdivision arrangement determination unit 13 may pass the query set Q to the query execution unit 15 and issue an instruction for executing the query set Q.

In step S111, when the subdivision arrangement determination unit 13 determines that subdivision arrangement is to be performed, the column set C _i having the maximum value of cost_cut _i is selected with reference to the buffer B, thereby selecting the selected column set C _i . It is decided to repartition using the partition key.

  The subdivision arrangement determination unit 13 may issue, for example, an instruction for executing subdivision arrangement together with column set information as a partition key to the subdivision arrangement execution unit 14.

The example shown in FIG. 4 is an example in which there is one designated divided arrangement method, but there may be a plurality of designated divided arrangement methods. In such a case, in step S104, the second processing cost calculation unit 124 uses the column set C _i as a partition key for each set of the column set C _i and the divided arrangement method M _h and uses the divided arrangement method. Partition_cost _ih , which is the divisional arrangement cost when using M _h, and the second query execution cost for each of the queries q _{j ′} ∈Q _i ′ in the state after subdivision arrangement using such a method query_cost_part _{ihj ′} may be calculated.

In that case, the subdivision arrangement determination means 13 determines whether there is a set of the column set C _i and the subdivision arrangement method M _h that reduces the total processing cost by performing the subdivision arrangement as compared with the case where it is not. The determination may be made based on whether or not cost_cut _{ih in} which 2) is true exists. Cost_cut _ih the conditional expression (1) corresponds to estimation of cost reductions by subdivision located in the specified set of columns _{C i} and the division arrangement method _{M h.}

The subdivision rearrangement determining means 13, when the Cost_cut _ih the conditional expression (2) is true is present, the Cost_cut _ih selects the set of column sets _{C i} and split arrangement method _{M h} having the maximum value As a result, it is determined that the selected column set C _i is used as the partition key and re-divided using the selected divided arrangement method M _h .

  In addition to the above method, the second processing cost calculation unit 124 calculates the second query execution cost by fixing in advance with a predetermined division method, for example, and the subdivision arrangement determination unit 13 reduces the cost. Based on the amount, it is determined whether or not subdivision placement is to be performed, and in the case of subdivision placement, the type of predicate operation that has become expensive from the calculated second query execution cost and within the query The partition key and the division arrangement method may be determined based on the appearance frequency of those operations.

  For example, the subdivision arrangement determination unit 13 is used when a query that is expensive when performing a selection operation on a set of columns in a query in a query set with a matching condition (= or ≠) performs another operation. When there are a large number of queries compared to a high-cost query, the partition set and a partitioning method in which many row data having the same column value are arranged in the same block may be selected. Further, for example, the subdivision arrangement determining unit 13 performs another operation when a query that is expensive when performing a selection operation with a range condition (> or <) on a column set in a query in a query set. When the number of queries is high compared to a high-cost query, the column set may be selected as a partition key and a partitioning method in which all row data is sorted by column values over all blocks. Further, for example, the subdivision arrangement determination unit 13 compares the query that is expensive to perform the join operation on the column set in the query in the query set with the cost that is expensive when performing other operations. In many cases, the column set and a partitioning method that sorts all row data by column values over all blocks may be selected as a partition key.

  In the above example, the column set extraction unit 121 extracts a column set that is a partition key candidate (step S102), and the appearance frequency of the predicate (selection predicate or join predicate) in the query set is as follows. One of the requirements is that the frequency is equal to or higher than a predetermined frequency, but all column sets that appear in the selection predicates or join predicates in the query set are extracted without looking at the frequency of appearance in the predicates in the query set. Is also possible. In such a case, θ = 0 may be set.

  However, when the total number of column sets is large, the calculation time becomes very long, so it is preferable to set an appropriate threshold value. However, it should be noted that if the threshold value of the appearance frequency is set too high, a low-frequency column set having a large cost may be overlooked.

  In this embodiment, rather than extracting a predicate having a predetermined frequency or higher from a query set to obtain a partition key candidate, a column set having a predetermined frequency or higher is extracted from the query set to obtain a partition key candidate. . For this reason, it is considered that the possibility of overlooking a low-frequency column set having a large cost is lower than the method based on the predicate frequency. In general, because columns and predicates have a coarser granularity concept, the frequency of columns tends to be relatively large even if the frequency of predicates that include a column in a query set is extremely low. is there. In many query sets, the number of types of column sets used for predicates is smaller than the number of types of predicates. For this reason, it can be expected that the number of column sets that are overlooked is smaller than the number of predicates that are overlooked.

  Next, a method for calculating the processing cost will be described using a specific example. In this example, when the entire target data group is sorted by the value of the column set specified by the partition key and then equally divided into the specified number of blocks (sort partition method) This is an example of a method for calculating the processing cost. Also in this example, the query set Q shown in FIG. 5 is used as an example.

  FIG. 9 is an explanatory diagram illustrating an example of a divided arrangement of the target data group. FIG. 9 shows an example in which row data belonging to the table “T” in the target data group is divided and arranged into two data blocks. The table “T” has three columns, “name” column, “age” column, and “sex” column, as columns corresponding to the data attribute.

  Now, the first data block TB1 includes three row data whose values of the column “name” are “suzuki”, “yamada”, and “aida”, respectively. The second data block TB2 includes three row data whose column “name” values are “kuroda”, “hirata”, and “satou”. The metadata storage unit 22 stores at least the minimum value “min” and the maximum value “max” of each column in the data block as block statistical information of each data block. In the example illustrated in FIG. 9, the table “T” is arranged, for example, by being equally divided into two data blocks (TB1, TB2) in the order in which the row data is registered.

  FIGS. 10 and 11 are explanatory diagrams illustrating other examples of divided arrangement of the target data group. FIG. 10 shows an example in which {T.age} is used as the partition key and the table “T” is divided and arranged into two data blocks (TB1, TB2) using the sort division method. FIG. 11 shows an example in which {T.sex, T.name} is used as the partition key and the table “T” is divided and arranged into two data blocks (TB1, TB2) using the sort partition method. Has been.

  It should be noted that the division method for performing the divided arrangement is not limited to the sort division method. For example, a general method is a method in which a hash value is generated from a column set value designated as a partition key and row data is allocated to a block having the same hash value (hash partitioning method). The division arrangement cost (partition_cost) is different for each division arrangement algorithm. For example, when a typical sort algorithm is used in the sort partition method, a partition arrangement cost of O (NlogN) + O (N) is required for the number of rows N of the table data. In the hash partition method, O (N) It becomes the division arrangement cost.

  Next, an example of calculating the query execution cost is shown. Cost calculation for each query is equivalent to performing cost-based query optimization in the database technology field. In general, cost-based query optimization enumerates all possible query execution plans for a given query.

  FIG. 12 shows three types of query execution plan trees as examples of possible query execution plans for the query q1 shown in FIG. Each query execution plan tree has a table at a leaf node. Further, the intermediate node of each query execution plan tree corresponds to the operation (selection, combination, projection) of the target query. In the query execution plan tree, this means that processing is executed in order from the left leaf node, and the result is calculated at the root node. The symbol σ in the figure indicates that the operation is a selection operation. A ribbon-like symbol indicates that the operation is a join operation. The subscripts of each operation symbol represent the selection condition and the combination condition, respectively.

  The first processing cost calculation unit 122 and the second processing cost calculation unit 124 calculate the query execution cost for executing each query execution plan based on such a query execution plan tree, and obtain the most processing cost. The query execution cost of a small query execution plan may be adopted as the query execution cost of the query.

  The query execution cost of each query can be calculated from metadata such as a value selection rate and a histogram stored for each column stored in the metadata storage unit 22.

For example, it is assumed that column sets C ₁ and C ₂ are given as frequent column sets C _i in the query set Q. Note that the query q _{ij ′} covering the column set C ₁ = {T.age} is q1, q2, and q3.

Now, the second processing cost calculation means 124, consider calculating the Query_cost_part ₁₁ is a second query execution cost query q1 in the state after the subdivision placed using a column set _{C 1} in the partition key. In the example shown in FIG. 12, the third plan has the lowest query execution cost. This is because when the combination operation and the selection operation are mixed, it is more efficient to perform the selection operation first, so that the second plan and the third plan are less expensive than the first plan.

In this example, the table “T” is re-divided and arranged by the sort division method with the column set C ₁ = {T.age} as the partition key. That is, it is in the state shown in FIG. Then, unnecessary reading into the first data block can be prevented by referring to the block statistical information in advance for the selection operation “T.age> 30”. Accordingly, the processing time is shorter in the third plan than in the first plan.

In this example, the third plan is selected, and the second query execution cost based on the third plan is used as the query_cost_part ₁₁ . Similarly, query_cost_part ₁₂ and query_cost_part ₁₃ are also obtained for the queries q2 and q3.

Note that the method for calculating the query execution cost when the subdivision arrangement is not performed is basically the same. That is, the first processing cost calculation means 122, based on the query plan tree of the target query q _j, the query execution cost of the most processing cost small query plan for the current re club arrangement, the target query q _j This is adopted as query_cost_no _j which is the first query execution cost.

However, the first processing cost calculation unit 122 calculates the query execution cost in the current divided arrangement state when calculating the query execution cost when executing each query execution plan. In the case of this example, the first processing cost calculation unit 122 calculates the query execution cost of the query execution plan tree assuming that the table “T” is in the state shown in FIG. The state shown in FIG. 10 is an example in which the table “T” is divided without being sorted by {T.age}. Therefore, for example, the query q1 is a query execution plan in which two types of plans, the first plan and the second plan, shown in FIG. 12 are considered. Therefore, the second plan is selected as the most efficient, and the query execution cost of the second plan in the current split arrangement is query_cost_no ₁ . The first processing cost calculation unit 122 calculates query_cost_no _j in the same manner for other queries q _j . In this way, various processing costs for calculating cost_cut _i and cost_cut _ih corresponding to the column set C ₁ and the column set C ₂ are obtained.

The subdivision arrangement determination unit 13 substitutes the various processing costs obtained in this way into, for example, the conditional expression (1) to obtain cost_cut _i for each column set C _i .

  An example of calculating the query execution cost is shown below. This example is a calculation example of the query execution cost for the third plan of the query q1 described above. Since the query q1 is a query including a selection operation and a join operation, the query execution cost is the sum of the processing cost of the selection operation and the processing cost of the join operation. There are a plurality of indexes representing the processing cost, such as CPU cost and I / O cost, but only I / O cost is considered here. There are two types of I / O: disk I / O and network I / O. Here, it is assumed that these loads are the same regardless of the storage location of the data block. Only O cost is calculated.

  In this example, the processing cost of the selection operation and the join operation is represented by the sum of the number of rows included in the data block accessed by the operation. Accessing the data block means that disk I / O is performed. In this example, it is assumed that no index exists.

  In the third plan of query q1, a selection operation is first executed as the first operation. More specifically, the selection calculation specifying the condition “age> 30” is executed for the table “T”. When there is no index, it is necessary to access all data blocks in a selection operation on a table that is not sorted by the column set used in the condition (in this example, {T.age}). In other words, the processing cost of the selection calculation when the subdivision arrangement is not performed is 6.

  On the other hand, the processing cost of the selection operation when the column set used for the condition is repartitioned by the sort partition method with the partition key as follows is as follows. When the data is divided after being sorted in the order of the values of the column set used in the condition, access to unnecessary data blocks can be avoided by referring to the block statistical information. For this reason, in the calculation of the processing cost of the selection calculation, first, the number of data blocks that will be accessed in the selection calculation (hereinafter referred to as the number of access blocks) is estimated.

  For example, the second processing cost calculation unit 124 calculates the total number of row data having column values that match the conditions based on a histogram of column values stored in the metadata storage unit 22 as metadata. % May be calculated.

  FIG. 13 shows a histogram of the column “age” in the table “T” of this example. In this example, it is assumed that the histogram shown in FIG. 13 is obtained from the metadata storage unit 22 as the histogram of the column “age” in the table “T”. According to the histogram shown in FIG. 13, it can be known in advance (before the subdivision arrangement) that 50% of the row data having a value of the column “age” larger than 30 exists.

  Hereinafter, this 50% is referred to as selectivity in this example, and is represented by π. Thereby, the number of access blocks by the selection calculation is obtained by the following calculation.

Number of access blocks = ceil (total number of rows x selection rate π) ÷ floor (total number of rows ÷ number of data blocks)
... (3)

  Here, ceil () represents a function that rounds up the decimal point. Further, floor () represents a function for truncating after the decimal point.

  According to Expression (3), it is estimated that the number of access blocks by the selection operation that is the first operation in the third plan of the query q1 = 1.

  Also, at this time, it can be seen that the number of rows selected by the selection calculation is the total number of rows × selectivity π = 3.

  Next, in the third plan, the join operation is executed as the second operation. It should be noted that the processing cost of the join operation does not change depending on whether the sorting is performed with {T.age} or not. Now, the target data group includes 6 row data having the column “age” as the row data of the table “R”, and 2 data blocks (data block RB1, data block RB2) every 3 rows. It is assumed that the data is divided and stored.

  An algorithm that is well-known for join operations in a distributed database system is called Map-side-join, and is an algorithm that performs processing in the following flow.

  Assume that there are n nodes that perform the join operation of the query in parallel. In general, each data storage means is associated with a node (computer) that collectively performs data operations on the data blocks stored in the data storage means. First, in a first step, the database management system copies a smaller table “T” to all n nodes. In the second step, n nodes read the data block including the row data of the larger table “R” so that there is no duplication between the nodes, and the data block of the read table “R” Join table “T” copied in step. In the third step, the database management system merges and outputs the join results of the nodes.

  As the second operation of the third plan, when the selection result for the table “T” by the first operation is combined with the table “R”, if n = 2, the table by the first operation is first set. A table “T ′” consisting of three rows as a selection result for “T” is read into each node (node n1 and node n2). Next, the node n1 reads the data block RB1 which is a data block of the table “R” and combines it with the table “T ′”, and the node n2 reads the data block RB2 which is a data block of the table “R”. Combine with “T '”. At this time, the processing cost of the join operation is the processing cost for scanning the table “T ′” and the processing cost for scanning the data block having the maximum number of rows in the table “R”.

  Therefore, the processing cost by the join operation, which is the second operation in the third plan of the query q1, is estimated as 3 + ceil (6 ÷ 2) = 6.

From the above, query_cost_no ₁ which is the query execution cost (first query execution cost) of the query q1 in the state where the table “T” is not divided and arranged in the current divided arrangement, that is, the column “age” is 6 + 6 = 12. Further, the query_cost_no ₁₁ that is the query execution cost (second query execution cost) of the query q1 in a state where the table “T” is rearranged in the column “age” is 3 + 6 = 9. Query execution costs can be calculated for other queries in the same way.

  In the above example, it is assumed that the I / O load on the disk and the I / O load on the network are the same regardless of the storage location of the data block, but these loads depend on the storage location of the data block. Data blocks based on information on the processing performance of the constituent elements of the distributed database, such as the disk bandwidth of each computer that controls each data storage means and the network bandwidth that connects these computers. The query execution cost may be calculated by setting a weight according to the storage location.

  As described above, according to the present embodiment, firstly, it is possible to perform the data division arrangement considering the processing cost given to the data operation by the query predicate, and the predicate in which the processing cost of the operation does not become a problem frequently. It can be avoided that predicates that are truly bottlenecks are ignored when they appear in the query. Secondly, it is possible to set a partition key for data division arrangement that makes the cost of not only the selection predicate but also the join predicate a target and makes the predicate with a truly large cost efficient. Third, by calculating the probability that the total processing time required to execute the query set when the data division arrangement is redone will be avoided, avoiding the case where the total processing time increases conversely due to the subdivision arrangement it can. Therefore, it is possible to improve the efficiency of a workload that involves issuing a query, such as an attribute generation process in which one work is completed only after executing a query that takes a long time to calculate a plurality of queries and predicates.

  In particular, when the distributed database system possesses block statistical information such as ORFFile for the divided data blocks, it can be expected that the total processing time required for executing a series of queries will be reduced.

  In addition, according to the present embodiment, for example, when a high-cost selection operation or join operation for one or more columns appears in a large amount of queries, data subdivision arrangement for the one or more columns If there is a possibility that the total processing time will be reduced when a large number of queries are executed after performing the query, compared to the case where the re-partitioning is not performed, the data is subdivided from the column set that is expected to reduce the most cost among the one or more columns. Since a partition key can be generated for placement, the total processing time required to execute a series of queries can be expected to be minimized.

  This is because not only the column set that frequently appears in the query predicate but also the cost of the query that includes the column set in the predicate, and the subdivision arrangement that minimizes the total processing cost of the query set is performed. is there. Such subdivision arrangement can be performed by determining the partition key and the division arrangement method based on the cost reduction amount calculated by the equation (1) or the like. Since the partition key and the partition placement method for subdivision placement are determined based on the amount of cost reduction, if a predicate that does not pose a problem in computation cost frequently appears in the query, it is truly a bottleneck for computation. The problem that predicates are ignored can be avoided. In addition, by focusing on the column set that appears in the predicate rather than the query predicate, it is possible to target not only the selection predicate but also the cost of the join predicate. The key can be extracted. Moreover, the risk of executing unnecessary subdivision arrangement can be avoided by providing subdivision arrangement judgment means.

  In the above embodiment, an example has been described in which the distributed database system determines whether or not to repartition and arrange the data for the accepted query set. The means or device for determining whether or not the distributed database system does not have to be a control device. For example, before issuing a query set to a distributed database system, a user issues a query set to a separately prepared data subdivision arrangement control device and distributes the query set received by the data subdivision arrangement control device. Determine whether and how to re-partition the data group stored in the database system, and issue a split placement instruction to the distributed database system, It may be transferred.

  That is, the query set receiving means, processing cost estimation means, subdivision arrangement means, query execution means, subdivision arrangement means, and metadata storage means described in the above embodiment may be realized as separate units. . Also, the column set extraction means, the first processing cost calculation means, the divided arrangement method storage means, and the second processing cost calculation means that are included in the processing cost estimation means may be realized as separate units. .

  Next, the outline of the present invention will be described. FIG. 14 is a block diagram showing an outline of the present invention. As shown in FIG. 14, the control device of the present invention includes a query set receiving unit 51, a processing cost estimating unit 52, and a subdivision arrangement determining unit 53.

  The query set accepting unit 51 (for example, the query set accepting unit 11) selects a query set including one or more queries for a target data group that is a predetermined data group that is divided and stored in a plurality of data storage units. Accept.

  The processing cost estimation unit 52 (for example, the processing cost estimation unit 12) is a first processing cost that is a processing cost when a query set is executed without subdividing the target data group based on the metadata of the target data group. A processing cost and a second processing cost that is a processing cost when the query set is executed after the target data group is re-divided and arranged by a predetermined method are estimated.

  Based on the estimation result by the processing cost estimation unit 52, the subdivision arrangement determination unit 53 (for example, the subdivision arrangement determination unit 13) determines whether to subdivide the target data group before executing the query set. In the case of rearrangement, it is determined how to perform the rearrangement.

  By having such a configuration, it is possible to reduce the total processing time of a workload that involves issuing a query.

  The target data group is table data including one or more row data including values assigned to each of one or more columns, and the target data group is a set of one or more arbitrary columns in the table data. Is divided into a plurality of data blocks each having one or more row data based on the value of the partition key included in each row data, and the metadata of the target data group is a column The processing cost estimation means includes a column set appearing in the query predicate included in the query set as a second key, and the target data group is subdivided and arranged as a partition key. The processing cost of the query set is estimated based on the metadata, and the re-division arrangement determining unit determines whether any of the second processing costs is higher than the first processing cost. If again, it may be determined that subdivide arranged subject data group using a set of columns in which the smallest second processing cost obtained partitioning key.

  In addition, a column set extraction unit that extracts a column set that appears at a predetermined frequency or more in a query predicate included in the accepted query set is provided, and the processing cost estimation unit includes the column set extracted by the column set extraction unit. As the partition key candidates, the first processing cost and the second processing cost for executing a query including a predicate that uses a column included in the candidate of the query set for each candidate may be estimated. Good.

  Further, the processing cost estimation means estimates a plurality of second processing costs corresponding to a plurality of division arrangement methods for one partition key candidate, and the subdivision arrangement determination means determines which of the second processing costs. If is smaller than the first processing cost, it may be determined that the target data group is subdivided and arranged using the partition key candidate and the division arrangement method that provide the smallest second processing cost.

  The plurality of division arrangement methods include a first division method that divides the target data group so that many row data having the same column set values as partition keys are included in the same data block, and all data There may be included a second division method for dividing the target data group so that the row data is sorted by the value of the column set used as the partition key over the block.

  The processing cost estimation means may estimate the first processing cost and the second processing cost by applying a predetermined optimization algorithm as a query execution algorithm.

  The processing cost estimation means further estimates the first processing cost and the second processing cost based on the disk bandwidth of the computer that controls each data storage means or the bandwidth of the network that connects the computers. May be.

  Although the present invention has been described with reference to the embodiments and examples, the present invention is not limited to the above embodiments and examples. Various changes that can be understood by those skilled in the art can be made to the configuration and details of the present invention within the scope of the present invention.

  The present invention can be suitably applied not only to analysis applications, but also to applications such as improving the efficiency of business systems that handle large amounts of data and improving the efficiency of computer systems that perform complex calculations.

DESCRIPTION OF SYMBOLS 11 Query set reception means 12 Processing cost estimation means 121 Column set extraction means 122 First processing cost calculation means 123 Division arrangement method storage means 124 Second processing cost calculation means 13 Subdivision arrangement judgment means 14 Subdivision arrangement execution means 15 Query execution means 21 Data storage means 22 Metadata storage means 51 Query set acceptance means 52 Processing cost estimation means 53 Subdivision arrangement judgment means

Claims (10)

Query set accepting means for accepting a query set including one or more queries for a target data group that is a predetermined data group divided and stored in a plurality of data storage means;
Based on the metadata of the target data group, a first processing cost that is a processing cost when the query set is executed without subdividing the target data group, and the target data group in a predetermined method Processing cost estimation means for estimating a second processing cost that is a processing cost when the query set is executed after the subdivision arrangement;
Based on the estimation result by the processing cost estimation means, whether or not to re-divide the target data group before executing the query set, and in what way to re-division arrangement And a subdivision arrangement determining means for determining whether or not to perform the control. The target data group is table data composed of one or more row data including a value assigned to each of one or more columns.
The target data group includes a column set that is a set of one or more arbitrary columns in the table data as a partition key, and each set of one or more row data based on the value of the partition key included in each row data. Divided into a plurality of data blocks,
The metadata of the target data group includes a statistic regarding the value of the column,
The processing cost estimation means uses, as a second processing cost, a processing cost of the query set when the target data group is subdivided and arranged using a column set appearing in a query predicate included in the query set as a partition key. , Based on the metadata,
The subdivision arrangement determining means uses the column set from which the smallest second processing cost is obtained as a partition key when any of the second processing costs is smaller than the first processing cost. The control device according to claim 1, wherein the control unit determines that the group is to be rearranged. Column set extraction means for extracting a column set that appears at a predetermined frequency or more in a query predicate included in the accepted query set,
The processing cost estimation means executes each of the column sets extracted by the column set extraction means as a partition key candidate, and executes a query including a predicate that uses a column included in the candidate in the query set for each candidate. The control device according to claim 2, wherein the first processing cost and the second processing cost are estimated. The processing cost estimation means estimates a plurality of second processing costs corresponding to a plurality of divided arrangement methods for a partition key candidate,
The re-division arrangement determining unit uses the partition key candidate and the division arrangement method that provide the smallest second processing cost when any of the second processing costs is smaller than the first processing cost. The control device according to claim 2, wherein the target data group is determined to be subdivided. The plurality of division arrangement methods include a first division method that divides a target data group so that many row data having the same column set value as a partition key are included in the same data block, and all data blocks. 5. The control device according to claim 4, further comprising: a second division method that divides the target data group so that the row data is sorted by the value of the column set that is set as the transition partition key. The processing cost estimation means estimates a first processing cost and a second processing cost by applying a predetermined optimization algorithm as an execution algorithm of a query. The control device described. The processing cost estimation means further estimates the first processing cost and the second processing cost based on the disk bandwidth of each computer that controls each data storage means or the bandwidth of the network that connects the computers. The control device according to any one of claims 1 to 6. A plurality of data storage means for dividing and storing a predetermined data group into a plurality of data blocks;
Query set accepting means for accepting a query set including one or more queries for the target data group which is the predetermined data group;
Metadata storage means for storing metadata of the target data group;
Based on the metadata, a first processing cost that is a processing cost when the query set is executed without subdividing the target data group, and the query after subdividing the target data group A processing cost estimation means for estimating a second processing cost that is a processing cost when the set is executed;
Based on the estimation result by the processing cost estimation means, whether or not to re-divide the target data group before executing the query set, and in what way to re-division arrangement Subdivision arrangement judging means for judging whether to do,
Re-division arrangement execution means for executing re-division arrangement of the target data group before executing the query set when it is determined by the sub-division arrangement determination means that the target data group is re-division arrangement. A distributed database system characterized by this. The control device accepts a query set including one or more queries for a target data group that is a predetermined data group that is divided and stored in a plurality of data storage means,
Before the execution of the query set, the control device executes the query set without subdividing the target data group based on the metadata of the target data group, and the target Estimating the processing cost when the query set is executed after the data group is re-partitioned by a predetermined method,
Whether the target data group is to be subdivided and arranged before executing the query set based on the estimation result before executing the query set, and Is a divisional placement control method characterized by determining how to re-partition placement. On the computer,
A process of receiving a query set including one or more queries for a target data group that is a predetermined data group that is divided and stored in a plurality of data storage means;
Based on the metadata of the target data group, the processing cost when the query set is executed without subdividing the target data group, and the query after the target data group is subdivided by a predetermined method A process for estimating the processing cost when the set is executed, and whether or not the target data group is subdivided and arranged based on the estimation result before the query set is executed. Is a split placement control program for executing the process of determining how to re-partition placement.

Images