Data placement model generation system

Abstract

To provide a data arrangement model generation system that obtains more appropriate physical data arrangement.SOLUTION: A data arrangement model generation system generates node constitution and a data arrangement key in a distributed parallel database by reinforcement learning, acquires sample data from data stored in the distributed parallel database, estimates data arrangement using node constitution and a data arrangement key having information on a sort order of columns constituting the data and information on a distributing system between nodes as states in the reinforcement learning, calculates reward in the reinforcement learning on the basis of a result, the node constitution, and processing cost for queries executed to the distributed parallel database, includes a reinforcement learning section that outputs the node constitution and data arrangement key on which the reinforcement learning is performed according to the calculated reward as action, and updates the node constitution and data arrangement key, which are output as action, as new states.SELECTED DRAWING: Figure 11

Description

[Technical field]

[0001] The present invention relates to a technique for generating a machine learning model that automatically calculates an appropriate data placement between nodes in a database system, particularly in a distributed parallel database system. [Background technology]

[0002] As the amount of data handled by databases increases, the need for distributed parallel databases is growing. A distributed parallel database distributes and stores data across multiple nodes, and processes the data on each node. In this system, the design of physical data placement between nodes is very important. In particular, it is known that the execution speed of a query varies greatly depending on which data in which table is placed on which node in what form. Specifically, designs are made such as placing data to be joined on the same node, and sorting data in advance according to filter conditions. That is, since a wealth of knowledge about databases is required to design the physical placement of data, this design is often done by database administrators. However, due to reasons such as the increase in the number of queries and tables, the increase in the amount of data handled, and the fact that the optimal data placement differs for each query, it is becoming more difficult for database administrators to perform physical data placement. Therefore, technology that supports physical data placement design through a system is required. If physical data placement can be implemented appropriately, it will be possible to achieve the target performance with a smaller number of nodes, and it will also be possible to improve cost performance. [Prior art documents] [Patent documents]

[0003] [Patent Document 1] US 8,326,825 B2 [Non-patent literature]

[0004] [Non-Patent Document 1] V. Leis, A. Gubichev, A. Mirchev, P. Boncz, A. Kemper, and T. Neumann. How good are query optimizers, really? PVLDB, 9(3), 2015. Summary of the Invention [Problem to be solved by the invention]

[0005] In Patent Document 1, the system extracts optimal candidates for which data should be placed on which node. However, in this patent, all possibilities for physical data placement on nodes are enumerated, and an exhaustive search is performed using a branch-and-bound method to derive the optimal physical data placement. Therefore, in order to select the optimal configuration from multiple types of node configurations, it is necessary to list all node configurations as candidates and perform the same processing on each candidate. On the other hand, considering which cloud service will be used and other factors, there are hundreds to thousands of possible node configuration patterns, which results in a very long processing time. In addition, since there are a great many possibilities for physical data placement, a query cost is predicted at high speed using a query optimizer. That is, in this known example, a data placement predicted to be optimal by the query optimizer is derived. On the other hand, as shown in Non-Patent Document 1, it is known that the query cost prediction performance of the query optimizer is not high, and the optimal physical data placement is not necessarily obtained by this method.

[0006] An object of the present invention is to provide a technique that makes it possible to obtain a more appropriate physical data placement than in the past. [Means for solving the problem]

[0007] a data placement model generation system for generating a node configuration and a data placement key in a distributed parallel database by reinforcement learning, the data placement model generation system comprising: a sample acquisition processing unit that acquires sample data from data stored in the distributed parallel database based on a predetermined acquisition method; a data placement estimation unit that estimates the placement of the data based on the state and the sample data, using the node configuration and the data placement key having information on the sort order of the columns that make up the data and information on the distribution method between nodes as states in the reinforcement learning; a reward calculation unit that calculates a reward in the reinforcement learning based on a result of the estimation of the data placement, the node configuration, and a processing cost of a query executed on the distributed parallel database; a reinforcement learning unit that outputs the node configuration and the data placement key obtained by reinforcement learning in accordance with the calculated reward as actions; and a state update unit that updates the node configuration and the data placement key output as the action as a new state.

[0008] and a state update unit that updates the node configuration and the data placement key outputted as the action as a new state in the distributed parallel database. The data placement model generation system according to one aspect of the present invention is a data placement model generation system that generates a node configuration and a data placement key in a distributed parallel database by reinforcement learning, and is characterized in that it has: a data distribution acquisition unit that acquires a data distribution of each table and a data distribution of a table after joining joined by the query based on data stored in the distributed parallel database and a query executed on the distributed parallel database; a data placement estimation unit that estimates the data placement based on the state and the data distribution, and sets the node configuration and the data placement key having information on the sort order of the columns that make up the data and information on the distribution method between nodes as states in the reinforcement learning; a reward calculation unit that calculates a reward in the reinforcement learning based on a result of the estimated data placement, the node configuration, and a processing cost of a query executed on the distributed parallel database; a reinforcement learning unit that outputs the node configuration and the data placement key obtained by reinforcement learning in accordance with the calculated reward as an action; and a state update unit that updates the node configuration and the data placement key outputted as the action as a new state. Effect of the Invention

[0009] According to the present invention, it is possible to obtain a more appropriate physical data placement than in the past. [Brief description of the drawings]

[0010] [Figure 1] Overall configuration diagram of the model learning configuration of the reinforcement learning system in the first embodiment [Diagram 2] Overall configuration diagram of the estimation configuration of the reinforcement learning system in the first embodiment [Diagram 3] Target performance [Figure 4] Price Constraints [Diagram 5] Node Information [Figure 6] Number of episodes [Figure 7] Query Information [Figure 8] Sample Acquisition Method [Figure 9] Node Configuration [Figure 10] Data Alignment Key [Figure 11] Overall process flow diagram during model learning in Example 1 [Figure 12] Rewards [Figure 13] action [Figure 14] State Update Process [Figure 15] Episode end determination process [Figure 16] Learning completion determination process [Figure 17] State initialization process [Figure 18] Sample data acquisition process [Figure 19] Data placement estimation process [Figure 20] Reward calculation process (node ​​placement capacity estimation) [Figure 21] Reward calculation process (calculation of overall processing cost) [Figure 22] Reward calculation process (total price calculation) [Figure 23] Reward calculation process (reward calculation) [Figure 24] Overall processing flow diagram of the estimation process in the first embodiment [Diagram 25] Overall configuration diagram of the model learning configuration of the reinforcement learning system in Example 2 [Figure 26] Overall configuration diagram of the estimation configuration of the reinforcement learning system in Example 2 [Figure 27] Overall process flow diagram during model learning in Example 2 [Figure 28] Data distribution calculation process [Figure 29] Data distribution estimation process [Diagram 30] Reward Calculation Process in Example 2 (Calculation of Overall Processing Cost) [Diagram 31] Overall process flow diagram of estimation process in Example 2 [Diagram 32] FIG. 1 is a diagram showing an example of a schematic configuration of a computer. DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

[0011] Hereinafter, an embodiment of the present invention will be described with reference to the drawings. The following description and drawings are examples for explaining the present invention, and appropriate omissions and simplifications have been made for clarity of explanation. The present invention can be implemented in various other forms. Unless otherwise specified, each component may be singular or plural.

[0012] In order to facilitate understanding of the invention, the position, size, shape, range, etc. of each component shown in the drawings may not represent the actual position, size, shape, range, etc. Therefore, the present invention is not necessarily limited to the position, size, shape, range, etc. disclosed in the drawings.

[0013] In the following explanation, various information may be described using expressions such as "database," "table," and "list," but the various information may be expressed in other data structures. To indicate independence of data structure, "XX table," "XX list," and the like may be referred to as "XX information." When describing identification information, when expressions such as "identification information," "identifier," "name," "ID," and "number" are used, these are interchangeable.

[0014] When there are multiple components having the same or similar functions, they may be described by using the same reference numerals with different subscripts. However, when there is no need to distinguish between these multiple components, the subscripts may be omitted.

[0015] In addition, in the following description, processing performed by executing a program may be described, but the program is executed by a processor (e.g., a CPU (Central Processing Unit), a GPU (Graphics Processing Unit)) to perform a defined process using storage resources (e.g., a memory) and / or an interface device (e.g., a communication port) as appropriate, so the subject of the processing may be the processor. Similarly, the subject of the processing performed by executing a program may be a controller, device, system, computer, or node having a processor. The subject of the processing performed by executing a program may be a calculation unit, and may include a dedicated circuit that performs specific processing (e.g., an FPGA (Field-Programmable Gate Array) or an ASIC (Application Specific Integrated Circuit)).

[0016] A program may be installed in a device such as a computer from a program source. The program source may be, for example, a program distribution server or a computer-readable storage medium. When the program source is a program distribution server, the program distribution server may include a processor and a storage resource for storing a program to be distributed, and the processor of the program distribution server may distribute the program to be distributed to other computers. In the following description, two or more programs may be realized as one program, and one program may be realized as two or more programs.

[0017] The functions and processing in each of the following embodiments can be realized, for example, by a general computer 1600 as shown in FIG. 32 (schematic computer diagram) that includes a CPU 1601, a memory 1602, an external storage device 1603 such as an HDD (Hard Disk Drive), a reading / writing device 1607 that reads and writes information from a portable storage medium 1608 such as a CD (Compact Disk) or a USB memory, an input device 1606 such as a keyboard or a mouse, an output device 1605 such as a display, a communication device 1604 such as a NIC (Network Interface Card) for connecting to a communication network, and an internal communication line (referred to as a system bus) 1609 such as a system bus that connects these together.

[0018] Various data stored in this system or used for processing can be realized by the CPU 1601 reading it from the memory 1602 or the external storage device 1603 and using it. Also, each functional unit of this system (for example, the reinforcement learning learning module 101, the reinforcement learning estimation module 201, and the sample acquisition processing unit 109) can be realized by the CPU 1601 loading a predetermined program stored in the external storage device 1603 into the memory 1602 and executing it.

[0019] The above-mentioned predetermined program may be stored (downloaded) in the external storage device 1603 from the storage medium 1608 via the reading / writing device 1607 or from the network via the communication device 1604, and then loaded onto the memory 1602 and executed by the CPU 1601. Alternatively, the program may be directly loaded onto the memory 1602 from the storage medium 1608 via the reading / writing device 1607 or from the network via the communication device 1604, and executed by the CPU 1601.

[0020] In the following, we will explain an example in which the system is composed of a single computer, but similar functions may be realized by distributing all or part of these functions across one or more computers, such as a cloud, and communicating with each other via a network. EXAMPLES

[0021] Hereinafter, a data placement model generation system, which is a physical data placement and node configuration automatic optimization system according to a first embodiment, will be described with reference to Fig. 1 to Fig. 24. The data placement model generation system is a system for automatically calculating an appropriate data placement between nodes in a distributed parallel database system. In the first embodiment, a reinforcement learning system first learns a machine learning model based on inputs such as target performance, price constraints, node information, query information, and a database. After that, the created reinforcement learning model is used to estimate an optimal physical data placement and node configuration.

[0022] 1 is a diagram showing the overall configuration of a model learning process during learning in Example 1. A reinforcement learning module 101 receives target performance 102, price constraints 103, node information 104, episode count 105, query information 106, and sample data 110 as inputs, and outputs a reinforcement learning model 111. The sample data 110 is calculated by a sample acquisition processing unit 109 performing processing using a sample acquisition method 107 from a DB table 108.

[0023] FIG. 2 is a diagram showing the overall configuration of the estimation process of the first embodiment. This system uses a common input that is almost the same as that of the system during reinforcement learning shown in FIG. Estimation Module 201 receives target performance 102, price constraint 103, node information 104, query information 106, and sample data 110 as input. Sample data 110 is calculated by sample acquisition processing unit 109 processing using sample acquisition method 107 from DB table 108. Furthermore, reinforcement learning estimation module 201 outputs node configuration 202 and data allocation key 203. Node configuration 202 is configuration information of nodes that make up a distributed parallel database system. Data allocation key 203 is key information for calculating appropriate data allocation between nodes in a distributed parallel database system.

[0024] Fig. 3 shows an example of the target performance 102. In Fig. 3, the target performance of the distributed parallel database is the average time for query execution.

[0025] Fig. 4 shows an example of the price constraint 103. In Fig. 4, the price per hour is shown as the price constraint of the node.

[0026] Fig. 5 shows an example of the node information 104. In Fig. 5, the node information includes a list of node types, and for each type, the number of CPUs, memory capacity, disk capacity, network bandwidth, and price. This information is used when estimating the execution time of each query and when estimating the price according to the node configuration.

[0027] 6 shows an example of the episode count 105. The episode count 105 means the number of times an episode is repeated during reinforcement learning.

[0028] An example of the query information 106 is shown in FIG. 7. The query information 106 is information related to queries executed in the distributed parallel database system. The query information 106 has query numbers, specific query contents for each number, and the overall execution ratio of queries. The query information 106 can use a query execution log instead. In this case, the executed query type and the query execution ratio are calculated from the query log. Basically, since there is no query log when the system is constructed, it is considered good to use the query information 106, and since a query log exists when the system is operated, the query log is used.

[0029] FIG. 8 shows the sample acquisition method 107. The sample acquisition method 107 includes elements of a sampling method, a sampling ratio, and a minimum number of items. The sampling method indicates how to acquire table sample data. For example, a method of acquiring data randomly is conceivable. The ratio is the sample number acquisition ratio, and the minimum number is the minimum number of data items to be acquired. In other words, if the number of data items * ratio > the minimum number of items, the number of data items * ratio number of data items is extracted, and if the number of data items * ratio < the minimum number of items, the minimum number of data items is extracted.

[0030] 9 shows an example of node configuration 202, and Fig. 10 shows an example of data allocation key 203. These are the outputs of reinforcement learning estimation module 201, and are also the internal states of reinforcement learning module 101 and reinforcement learning estimation module 201.

[0031] The node configuration 202 includes the type and the number of nodes. The type indicates the type in the node information 104 shown in Fig. 5. In other words, the node configuration 202 indicates how many nodes of what type are connected to configure a distributed parallel database system.

[0032] The data placement key 203 has a sort key and a distribution key for each table. The sort key indicates which column each table is based on. The sort key can be expressed as an ordered list of the column names of the table. For example, if the customer table has three columns, c_id, c_name, and c_region, a list of c_id and c_name can be set as the sort key. In this case, the data of the customer table is first sorted by c_id on each node, and then sorted by c_name and stored. This makes it possible to execute a query at high speed, since it is not necessary to read all the data when filtering by a part of the c_name region, for example. Note that if the sort key is blank, it means that the table is not sorted by a specific key.

[0033] The distribution key has one column name or all element of each table. If a column name is set in the distribution key, for example, the hash of the value of the column is obtained, and the node to place it on is determined according to the hash value. If the distribution key is c_region, the hash of c_region is obtained, and the node to place each column on can be determined according to the hash value. When joining tables, if the nodes can be distributed using the same join key, it is possible to join without transferring data between nodes, and the processing performance of the distributed parallel database can be improved. If the distribution key is all, the table is placed on all nodes. In particular, if the size of the table is small, it is possible to eliminate the network load when joining tables by placing it on all nodes with only a small increase in disk capacity. If the distribution key is blank, each column is placed, for example, in a round robin manner. This allows the data of the table to be distributed evenly to all nodes, and the query processing load for each node to be distributed between nodes.

[0034] FIG. 11 is an overall processing flow diagram of the reinforcement learning module 101. The reinforcement learning module 101 is composed of a reinforcement learning environment 1011 and a reinforcement learning agent 1012. The reinforcement learning environment 1011 has a state inside, and calculates a reward 1013 according to this state. The state is composed of a node configuration 202 and a data allocation key 203 (the node configuration 202 and the data allocation key 203 shown in FIG. 2 are the state output to the outside). The reward 1013 is input to the reinforcement learning agent 1012, and the reinforcement learning agent 1012 calculates an action 1014. The action 1014 is input to the reinforcement learning environment 1011, and the reinforcement learning environment 1011 updates the internal state according to the input action 1014, and calculates a new reward 1013. A general reinforcement learning module can be used as the reinforcement learning agent 1012. For example, it is possible to use PPO (Proximal Policy Optimization Algorithms), A3C (Asynchronous Advantage Actor-Critic), and TRPO (Trust Region Policy Optimization).

[0035] The detailed operation of the reinforcement learning environment 1011 is as follows. First, when an action 1014 is received, the state is updated internally (1011-1). Then, it is determined whether to end the reinforcement learning episode (1011-2). If it is determined to end (1011-2; Yes), then it is determined whether to end the learning itself (1011-3). If it is determined to end the learning (1011-3; Yes), a current model saving process is performed (1015) and the reinforcement learning model 111 is output. This model saving process is performed according to the machine learning method and the method possessed by the machine learning. If it is determined not to end the learning (1011-3; No), the state is initialized (1011-4). Then, next, it is merged with the case where it is determined not to end in the episode end determination (1011-2; No) and a data placement estimation process is performed (1011-5). This process is performed using the sample data 110. Then, the node configuration 202 and the data placement, which are the internal state, are saved (1015). key Based on 203, a reward for the query is calculated (1011-6), and a calculation is made to output a reward 1013. When starting reinforcement learning, the process starts with state initialization 1011-4.

[0036] 12 shows an example of the reward 2013. The reward is an evaluation value for the node configuration 202 and the data placement key 203, calculated from the query processing load (processing cost) and the node price.

[0037] An example of an action 2014 is shown in Fig. 13. An action indicates a change in the internal state of the reinforcement learning learning module 101 and the reinforcement learning estimation module 201. It indicates which type of node configuration to add, and for which table to set a sort key or distribution key. An action can also be set to change only one part of the state.

[0038] FIG. 14 shows an example of the state update (1011-1) process. First, the reinforcement learning learning environment 1011 determines whether the action includes update information for the node configuration (S101). If it does (S101; Yes), the node configuration of the state is updated (S102). For example, in the case of an action to increase the number of small type nodes by 1, the number of small type nodes in the node configuration is increased by 1. Next, it determines whether the action includes an update of the table allocation key (S103). If it does (S103; Yes), the table allocation key of the state is updated. If it includes an update of the sort key, the sort key is added to the end of the sort key of the state. If it includes an update of the distribution key, the action is ignored if a distribution key has already been set in the state. If it has not been set, the distribution key is set in the state.

[0039] FIG. 15 shows an example of the episode end determination process (1012-2). First, the reinforcement learning environment 1011 acquires the previous reward value 1013 and value It is determined whether 1013 is negative (S201). If it is negative (S201; Yes), it is determined that the episode has ended, and the number of times the episode has been performed is incremented (S204). The number of times the episode has been performed is a value that the reinforcement learning environment has, indicating the current number of episodes. This value is initialized to 0 only once when learning begins. If the reward 1013 is 0 or greater, it is next determined whether the state has changed compared to the previous time (S202). If no change in the state is observed (S202; Yes), it is determined that the episode has ended, and the number of times the episode has been performed is incremented (S204). If there is a change in the state (S202; No), it is determined that the episode continues (S203).

[0040] 16 shows an example of the learning end determination process (1013-3). In this process, the reinforcement learning environment 1011 determines whether the number of episodes performed is greater than the episode count 105 (S103). If it is smaller (S103; No), it is determined to continue learning (S302), and if it is greater (S103; Yes), it is determined to end the learning process (S303).

[0041] An example of the state initialization (1011-4) process is shown in Fig. 17. In this process, the reinforcement learning environment 1011 first empties the node configuration, which is the state (S401), and also empties the data allocation keys (S402). As data allocation keys, the sort keys and distribution keys of all tables are emptied.

[0042] In Figure 18, Sample acquisition 1 shows a flowchart showing the processing performed by a processing unit 109. sample The data 110 is used as an input for the data allocation estimation 1011-5. The sampling process S1101 to S1108 is performed for all tables, one by one. Sample acquisition The processing unit 109 selects one unprocessed table (S1101). Next, the number of items in the table is obtained (S1102). At this time, the ratio and the minimum number of items are obtained from the sample acquisition method 107. Then, it is determined whether the number of items*ratio>minimum number of items (S1103). If the number of items*ratio>minimum number of items (S1103; Yes), the row of the number of items*ratio number is obtained according to the sampling method indicated in the sample acquisition method 107 and saved (S1104). If the number of items*ratio<minimum number of items (S1103; No), it is further determined whether the number of items>minimum number of items (S1105). If the number of items>minimum number of items (S1105; Yes), the row of the minimum number of items is obtained according to the sampling method indicated in the sample acquisition method 107 and saved (S1106). If the number of records is less than the minimum number of records (S1105; No), all rows are acquired and saved according to the sampling method shown in the sample acquisition method 107 (S1107). After that, it is determined whether processing has been completed for all tables (S1108), and if there are unprocessed tables (S1108; No), processing is performed again from S1101.

[0043] 19 shows an example of data placement estimation (1011-5). First, the reinforcement learning learning environment 1011 acquires a node configuration from the state, and determines whether the number of nodes in the node configuration is 0 (S501). If the number of nodes is 0 (S501; Yes), data placement is deemed impossible, the reward is set to 0, and the process ends (S511). In this case, the next reward calculation process (1011-6) is also skipped, and the reward 1013 is output as 0.

[0044] If the number of nodes is 1 or more (S501; No), then, on the computer or system that executes the reinforcement learning module 101, virtual areas for the number of nodes are generated (S502). The following processing from S503 to S510 is processing performed for all tables, one table at a time. Therefore, first, one unprocessed table is selected (S503). Next, the sample data 110 of the selected table is read (S504). Then, a distribution key is obtained from the state, and the sample data is assigned to the virtual area of ​​each node according to the distribution key (S505). If there is no distribution key, the sample data is assigned to the virtual area of ​​the node in a round robin manner (S506). If the distribution key is set to something other than all, a hash of the distribution key value is taken for each column data of each sample data, and the sample data is assigned to the virtual area of ​​the node according to the hash value (S507). If the distribution key is set to all, all data is assigned to the virtual areas of all nodes (S508). Next, a sort key is extracted, and the sample data assigned to the virtual area in each node is sorted by the sort key. Thereafter, it is determined whether the processing has been completed for all tables (S510), and if there are any unprocessed tables (S510; No), the processing is carried out again from S503 for the unprocessed tables.

[0045] 20-23 show an example of the reward calculation (1011-6) process. First, the reinforcement learning module 101 checks the capacity to be allocated to each node in the flow shown in FIG. 20. The process from S601 to S603 is performed for all nodes, one by one. Therefore, first, one unprocessed node is selected (S601). Next, the node allocation capacity assumed to be allocated to the node is calculated from the capacity of the sample data allocated to the node in FIG. 19 (S602). This is calculated as (node ​​allocation capacity) = (sample data allocation capacity) * (100 / X) when X% of the data is extracted as sample data from the total number of cases. X is a value determined by the flow shown in FIG. 18. Then, using the obtained value, it is determined whether the node allocation capacity < the disk capacity of the node (S603). The disk capacity of the node can be obtained from the type of the node and the node information 104. If node allocation capacity < node disk capacity (S603; Yes), it is determined whether processing has been completed for all nodes (S604), and if there are unprocessed nodes (S604; No), processing is performed again from S601. Then, after processing has been completed for all nodes, the flow proceeds to the flow shown in Figure 21. If node allocation capacity > node disk capacity (S603; No), reward 1013 is set to 0, and this reward calculation process (1011-6) is terminated.

[0046] In the flow shown in FIG. 21, the processing cost of a query is estimated. The processing from S701 to S704 is performed for all queries one by one. The reinforcement learning module 101 therefore first selects one unprocessed query (S701). This can be done by selecting one query from the query information 106. Next, using the information on the sample data arranged in each virtual area of ​​each node obtained by the flow shown in FIG. 19, the cardinality when the query is executed is calculated (S702). Then, the processing cost of the query is estimated by a cost estimation function of the database (S703). S702 and S703 can be performed by a general database processing function.

[0047] Then, it is confirmed whether the processing cost calculation has been performed for all queries (S704), and if there are unprocessed queries (S704; No), the process is performed again from S701. After the processing cost calculation for all queries is completed (S704; Yes), the query execution time is weighted to calculate the overall processing cost (S705). This can be calculated, for example, by multiplying the simple query execution cost by the weight of the execution ratio shown in the query information 106. That is, if the cost of query 1 is C1 and the execution ratio is 0.4, and the cost of query 2 is C2 and the execution ratio is 0.6, the calculation can be made as C1*0.4+C2*0.6. Finally, the overall processing cost calculated in this way is compared with the target performance (S706). The processing cost corresponds to the query execution time. If the processing cost exceeds the target performance (S706; No), the reward 1013 is set to 0 (S707), and this reward calculation process (1011-6) is terminated. If the total processing cost is lower than the target performance 102 (S706; Yes), the process proceeds to the process shown in FIG.

[0048] FIG. 22 shows the process of calculating the price corresponding to the node configuration. The reinforcement learning module 101 first acquires the node configuration from the state (S801). Next, in combination with the node information 104, the total price according to the current node configuration is calculated (S802). The total price is then compared with the price constraint 103 (S803). If the total price exceeds the price constraint (S803; No), the reward 1013 is set to -10 (S804), and this reward calculation process (1011-6) is terminated. If the total price is below the price constraint (S803; Yes), the process proceeds to the process shown in FIG. 23.

[0049] 23 shows a process of calculating the reward 1013 from the total processing cost and the total price. The reinforcement learning module 101 calculates the reward 1013 as, for example, 1 / (total processing cost*total price) (S901).

[0050] 24 is an overall processing flow diagram of the reinforcement learning estimation module 201. This processing flow uses the reinforcement learning model 111 output from the reinforcement learning module to determine the optimal node configuration 202 and data allocation. key 2. This is a flow for outputting 203. In this process, many inputs and processing modules similar to those of the reinforcement learning module 101 are used. The reinforcement learning estimation module 201 is composed of a reinforcement learning estimation environment 2011 and a reinforcement learning model 111. The reinforcement learning estimation environment 2011 has an internal state, and calculates a reward 1013 according to this state. The state is composed of a node configuration 202 and a data arrangement key 203 (the node configuration 202 and the data arrangement key 203 shown in FIG. 2 are the state output to the outside). The reward 1013 is input to the reinforcement learning model 111, which calculates an action 1014. The action 1014 is input to the reinforcement learning estimation environment 2011, which updates the internal state according to the input action 1014, and newly calculates the reward 1013.

[0051] The detailed operation of the reinforcement learning estimation environment 2011 is as follows. First, when the reinforcement learning estimation environment 2011 receives an action 1014, it updates the state internally (1011-1), and then determines whether to end the reinforcement learning episode (1011-2). If it is determined to end, it saves the internal state 2015. At this time, the node configuration 202 and data placement key 203 are saved and output. If it does not end, it performs data placement estimation processing (1011-5). Then, it calculates a query cost based on the data placement (1011-6), and calculates a reward 1013 based on the result. When starting reinforcement learning estimation, processing begins with state initialization 1011-4. FIG. 25 The elements of each process in are similar to the elements in each process shown in Fig. 2. The difference is that the episode ends after one episode, and at the end, instead of saving the model, the node configuration 202 and data are saved as internal state. arrangementThe key 203 is stored, and the action 1014 is calculated from the reward 1013 by the reinforcement learning model 111. arrangement The key 203 is the output required by this system, and a database administrator or the like will actually perform data allocation in the distributed parallel database based on this output. It is also possible for the system to automatically change the data allocation in the distributed parallel database based on this output. The reinforcement learning model 111 is a model generated by reinforcement learning. This reinforcement learning model usually has an interface that takes reward 1013 as input and action 1014 as output.

[0052] In this way, in this embodiment, a data allocation is estimated from sample data, a node configuration, and an allocation key, and a node configuration and an allocation key obtained from an action obtained by reinforcement learning based on a reward obtained from the data allocation are output and updated. For example, in a data allocation model generation system that generates a node configuration and a data allocation key in a distributed parallel database by reinforcement learning, a sample acquisition processing unit (e.g., sample acquisition processing unit 109) that acquires sample data from data stored in the distributed parallel database based on a predetermined acquisition method (e.g., sample acquisition method 107 shown in FIG. 8), the node configuration (e.g., node information 104 shown in FIG. 5 or node configuration 202 shown in FIG. 9), and the data allocation key (e.g., data allocation key 203 shown in FIG. 10) having information on the sort order of columns constituting the data and information on the distribution method between nodes are set as states in the reinforcement learning, and a data allocation estimation unit (e.g., reinforcement learning study a processing unit for performing data placement estimation processing of the environment 1011), and a reward calculation unit (for example, a reinforcement learning unit) for calculating a reward in the reinforcement learning based on the result of estimating the placement of the data, the node configuration, and the processing cost of a query executed on the distributed parallel database. studya processing unit that performs a reward calculation process for the environment 1011), a reinforcement learning unit (e.g., a reinforcement learning agent 1012) that outputs the node configuration and the data placement key that have been reinforced learned in response to the calculated reward as an action, and a state update unit (e.g., a reinforcement learning agent 1013) that updates the node configuration and the data placement key that have been output as the action as a new state. study A processing unit that performs state update processing of the environment 1011. Therefore, a model that optimizes the physical data design and node design in the distributed parallel database can be automatically configured by reinforcement learning, and it becomes possible to reduce the work cost by the database administrator while maximizing the cost performance of the entire system.

[0053] 10, 19, etc., the data allocation estimation unit allocates the data to nodes in a round robin manner if a distribution key is not set in the data allocation key, allocates the data to nodes based on the hash value of the distribution key if a distribution key is set in the data allocation key, and copies the data and allocates it to all nodes if a distribution key is set and indicates all nodes. Thus, data in a distributed parallel database can be flexibly allocated to nodes depending on the setting status of the distribution key.

[0054] 21, 22, etc., the reward calculation unit receives input of information related to the target performance of the distributed parallel database or the price constraint of the node, and sets the reward low if the processing cost of the query does not meet the target performance, and sets the reward to a negative value if the price of the node does not meet the price constraint. Therefore, it is possible to set an appropriate reward according to the target performance of the distributed parallel database and the price constraint of the node.

[0055] Furthermore, the reward calculation unit can set the reward using the processing cost of the query weighted based on an execution rate indicating a predetermined execution frequency of the query, and therefore can appropriately set the reward in consideration of the execution frequency of the query.

[0056] Furthermore, the reward calculation unit can calculate the execution rate by analyzing the execution log of the query, and set the reward using the processing cost of the query weighted based on the calculated execution rate. Therefore, even if the execution frequency of a query is not known in advance, it is possible to appropriately set the reward in consideration of the actual execution frequency of the query. EXAMPLES

[0057] Hereinafter, a physical data placement and node configuration automatic optimization system according to the second embodiment will be described with reference to Fig. 25 to Fig. 31. In the second embodiment, similar to the first embodiment, a reinforcement learning system first learns a machine learning model based on inputs such as target performance, price constraints, node information, query information, and a database. Then, the created reinforcement learning model is used to estimate optimal physical data placement and node configuration. The difference from the first embodiment is that, when calculating a reward 1013, sample data 110 is used in the first embodiment, whereas data distribution 303 is used in the second embodiment.

[0058] Fig. 25 is an overall configuration diagram of a model creation process during learning in the second embodiment. A reinforcement learning module 301 receives the target performance 102, the price constraint 103, the node information 104, the number of episodes 105, the query information 106, and the data distribution 303 as inputs, and outputs a reinforcement learning model 111. The data distribution 303 is calculated by a data distribution acquisition processing unit 302 performing processing based on the DB table 108 and the query information 106. The only difference from Fig. 1 in the first embodiment is the data distribution acquisition processing unit 302 and the data distribution 303.

[0059] FIG. 26 shows an example 2This system uses almost the same common input as the system during reinforcement learning shown in FIG. Estimation The module 401 receives the target performance 102, the price constraint 103, the node information 104, the query information 106, and the data distribution 303 as input. The data distribution 303 is calculated by the data distribution acquisition processing unit 302 performing processing based on the DB table 108 and the query information 106. The reinforcement learning estimation module 401 also outputs the node configuration 202 and the data allocation key 203. The difference from FIG. 2 of the first embodiment is only the data distribution acquisition processing unit 302 and the data distribution 303, similar to the difference between FIG. 1 and FIG. 25 of the first embodiment.

[0060] Fig. 27 is an overall processing flow diagram of the reinforcement learning module 101. The only difference from Fig. 11 is that the data distribution 303 is used instead of the sample data 110, and accordingly, the data allocation estimation 1011-5 is replaced with the data distribution estimation process 1011-7, and the reward calculation 1011-6 is replaced with the reward calculation 1011-8 based on the data distribution. The other modules and the overall processing flow are the same as those shown in Fig. 11 of the first embodiment.

[0061] FIG. 28 shows an example of processing performed by the data distribution acquisition processing unit 302. The data distribution indicates a general data distribution in a database, and may be, for example, a histogram of frequency for each item indicating how many items exist for each value of each column, or a histogram for each range indicating how much data is included in a certain interval of each column. In this processing, first, a data distribution for each table is created. To this end, the data distribution acquisition processing unit 302 first selects one unprocessed table (S3101). Next, a data distribution for the table is generated (S3102). This processing uses a data distribution generation processing for a conventionally known normal database. Then, it is confirmed whether the calculation of data distribution for all tables is completed (S3103), and if there is an unprocessed table (S3103; No), the processing is executed again from S3101. After the data distribution of all tables is acquired (S3103; Yes), the query information 106 is read and the combination of tables to be joined is enumerated (S3104).

[0062] Then, one pair to be joined is selected (S3105), and the join is actually performed for each pair, generating the data distribution after the join (S3106). This is repeated for all the join pairs (S3107). In S3104, all the join processes included in the query are first listed, and then all the join orders are listed. For example, when joining tables A, B, and C, it is possible to first join A and B and then join with C, or to first join B and C and then join with A, or to join C and A and then join with B, and all the possibilities are listed. In the processes from S3105 to S3107, one of the join pairs listed in S3104 is taken out, and the data distribution after the join process is generated. For example, A and B are joined, and the data distribution of the joined table is generated, and then A and B are joined again, and then joined with C, and the data distribution of the resulting table is also generated. Finally, all the generated data distribution information is saved (S3108).

[0063] An example of the data distribution estimation process (1011-7) is shown in Figure 29. First, the node configuration is obtained from the state, and it is determined whether the number of nodes in the node configuration is 0 (S3201). If the number of nodes is 0 (S3201; Yes), data placement is deemed impossible, the reward is set to 0, and the process ends (S3210). In this case, the next reward calculation process (1011-8) is also skipped, and the reward 1013 is output as 0.

[0064] If the number of nodes is 1 or more (S3201; No), then a virtual area (data distribution storage area) for storing the data distribution for each node is generated on the computer or system that executes the reinforcement learning module 101 (S3202). The next processing from S3203 to S3208 is processing performed for all data distributions, one by one. Therefore, first, one unprocessed data distribution is selected (S3203). Next, the distribution key of the table targeted by the selected data distribution is obtained from the state (S3204). For the data distribution of the join result of multiple tables, the following is performed. First, if only one table has a distribution key, the distribution key is extracted. If multiple tables have distribution keys, one distribution key is selected randomly. If all tables do not contain a distribution key, and the distribution keys of all tables are all, the distribution key is set to all. If all tables do not contain a distribution key and one or more tables do not have a distribution key, it is set to a state where there is no distribution key. The data distribution is allocated to each node according to the obtained distribution key.

[0065] First, if there is no distribution key, the data distribution is allocated to each node in a round robin manner (S3205). For example, if the data distribution is a frequency histogram, the frequency is allocated equally to each node. In the case of a range histogram, the data distribution is allocated to each node in a round robin manner for each range division. Next, if there is a distribution key, the data distribution is allocated based on hash (S3206). For example, if the data distribution is a frequency histogram, allocation to nodes is performed according to the hash of each value. In the case of a range histogram, the data distribution is allocated to nodes for each certain range according to the hash of the first value of the range. Finally, if the distribution key is all, the data distribution is allocated to all nodes (S3207). Then, it is confirmed whether processing has been completed for all data distributions (S3208), and if there is unprocessed data distribution (S3208; No), processing is performed again from S3203. After processing has been completed for all data distributions (S3208; Yes), the data distribution information for each node is saved (S3209).

[0066] Many parts of the reward calculation (1101-8) in the second embodiment are similar to those in the first embodiment. In the first embodiment, the reward calculation process (1101-6) is shown in Fig. 20 to Fig. 23, but the reward calculation process in the second embodiment is obtained by replacing the process in Fig. 21 in the first embodiment with the process in Fig. 30.

[0067] In the flow shown in FIG. 30, the processing cost of a query is estimated. The processing from S3301 to S3303 is performed for all queries one by one. Therefore, first, the reinforcement learning module 101 selects one unprocessed query (S3301). This can be done by selecting one query from the query information 106. Next, the query cost is calculated based on the data distribution of each node obtained by the flow shown in FIG. 29. This is done by directly utilizing the cost estimation function based on data distribution that a general database engine has (S3302). Then, it is confirmed whether the processing cost has been calculated for all queries (S3303), and if there is an unprocessed query (S3303; No), the processing is performed again from S3301. After the processing cost calculation for all queries is completed (S3303; Yes), weighting of the query execution time is performed and the overall processing cost is calculated (S3304). As in the first embodiment, this can be calculated by multiplying the simple query execution cost by the weight of the execution ratio indicated in the query information 106. Finally, the total processing cost calculated in this manner is compared with the target performance (S3305). The processing cost corresponds to the query execution time. If the processing cost exceeds the target performance (S3305; No), the reward 1013 is set to 0, and this reward calculation process (1011-8) is terminated. If the total processing cost is lower than the target performance 102 (S3305; Yes), the process proceeds to the process shown in FIG. 22.

[0068] 31 is an overall processing flow diagram of the reinforcement learning estimation module 401. As with the reinforcement learning estimation module 201 of the first embodiment, this processing flow uses the reinforcement learning model 111 output from the reinforcement learning module to determine the optimal node configuration 202 and data allocation. key This is a flow that outputs 203. Data distribution 303, data distribution estimation 1011-7, and reward calculation 1011-8 in this flow are the same as the processes shown in Fig. 27, and other processes and the entire process flow are the same as the processes in known example 1 shown in Fig. 24. By this process, the target node configuration 202 and data placement key 203 can be obtained.

[0069] In this embodiment, as described above with reference to Figs. 27-29 and the like, in a data allocation model generation system that generates a node configuration and a data allocation key in a distributed parallel database by reinforcement learning, a data distribution acquisition unit (e.g., a data distribution acquisition section) that acquires the data distribution of each table and the data distribution of a joined table that is joined by a query (e.g., the query information 106 shown in Fig. 7) based on data stored in the distributed parallel database and a query executed on the distributed parallel database, is provided. process a data allocation estimation unit (e.g., a reinforcement learning unit 302) that estimates the allocation of the data based on the state and the data distribution, the node configuration (e.g., the node information 104 shown in FIG. 5 or the node configuration 202 shown in FIG. 9), and the data allocation key (e.g., the data allocation key 203 shown in FIG. 10) that has information on the sort order of the columns that make up the data and information on the distribution method between the nodes, as a state in the reinforcement learning, study a processing unit for performing data placement estimation processing of the environment 1011), and a reward calculation unit (for example, a reinforcement learning unit) for calculating a reward in the reinforcement learning based on the result of estimating the placement of the data, the node configuration, and the processing cost of a query executed on the distributed parallel database. study a processing unit that performs a reward calculation process for the environment 1011), a reinforcement learning unit (e.g., a reinforcement learning agent 1012) that outputs the node configuration and the data placement key that have been reinforced learned in response to the calculated reward as an action, and a state update unit (e.g., a reinforcement learning agent 1013) that updates the node configuration and the data placement key that have been output as the action as a new state. study A processing unit for performing state update processing of the environment 1011. Therefore, even if the data distribution 303 is used instead of the sample data, the same effect as in the first embodiment can be obtained.

[0070] 29 etc., the data allocation estimation unit allocates the data to nodes in a round robin manner if a distribution key is not set in the data allocation key, allocates the data to nodes based on the hash value of the distribution key if a distribution key is set in the data allocation key, and copies the data and allocates it to all nodes if a distribution key is set and indicates all nodes. Therefore, as in the first embodiment, data in a distributed parallel database can be flexibly allocated to nodes depending on the setting status of the distribution key.

[0071] 30 and the like, the reward calculation unit receives input of information related to the target performance of the distributed parallel database or the price constraint of the node, and sets the reward low if the processing cost of the query does not meet the target performance, and sets the reward to a negative value if the price of the node does not meet the price constraint. Therefore, as in the first embodiment, the reward can be set appropriately according to the target performance of the distributed parallel database and the price constraint of the node.

[0072] Furthermore, the reward calculation unit can set the reward using the processing cost of the query weighted based on an execution rate indicating a predetermined execution frequency of the query. Therefore, as in the first embodiment, the reward can be appropriately set in consideration of the execution frequency of the query.

[0073] Moreover, the reward calculation unit can calculate the execution ratio by analyzing the execution log of the query, and set the reward using the processing cost of the query weighted based on the calculated execution ratio. Therefore, as in the case of the first embodiment, even if the execution frequency of the query is not known in advance, the reward can be appropriately set in consideration of the actual execution frequency of the query.

[0074] Although the embodiments of the present invention have been described above, the present invention is not limited to these exemplified embodiments, and various modifications and combinations are possible without departing from the spirit of the invention. For example, the above-mentioned embodiments have been described in detail to explain the present invention in an easy-to-understand manner, and the present invention is not necessarily limited to those having all of the configurations described. In addition, it is possible to replace a part of the configuration of one embodiment with the configuration of another embodiment, and it is also possible to add the configuration of another embodiment to the configuration of one embodiment. In addition, it is possible to add, delete, or replace a part of the configuration of each embodiment with another configuration. [Explanation of symbols]

[0075] 101: Reinforcement Learning Learning Module 102: Target performance 103: Price Constraints 104: Node information 105: Number of episodes 106: Query information 107:Sample acquisition method 108:DB table 109: Sample acquisition processing unit 110: Sample data 111: Reinforcement learning model 201: Reinforcement learning estimation module 202: Node configuration 203: Data placement key 1011: Reinforcement learning environment 1012: Reinforcement learning agent 301: Reinforcement learning learning module (Example 2) 302: Data distribution acquisition processing unit 303: Data Distribution 401: Reinforcement learning estimation module (Example 2)

Claims

1. A data placement model generation system that generates a node configuration and a data placement key in a distributed parallel database by reinforcement learning, a sample acquisition processing unit that acquires sample data from the data stored in the distributed parallel database based on a predetermined acquisition method; a data placement estimation unit that estimates the placement of the data based on the state and the sample data, using the node configuration and the data placement key, which has information about the sort order of the columns constituting the data and information about a distribution method between nodes, as a state in the reinforcement learning; a reward calculation unit that calculates a reward in the reinforcement learning based on a result of estimating the data placement, the node configuration, and a processing cost of a query executed on the distributed parallel database; a reinforcement learning unit that outputs the node configuration and the data placement key that have been reinforced learned in response to the calculated reward as an action; a state update unit that updates the node configuration and the data placement key output as the action as a new state; A data placement model generating system comprising:

2. 2. The data placement model generation system according to claim 1, the data placement estimation unit assigns the data to nodes in a round robin manner when a distribution key is not set in the data placement key, assigns the data to nodes based on a hash value of the distribution key when a distribution key is set in the data placement key, and copies the data and assigns it to all nodes when a distribution key is set and the distribution key indicates all nodes. A data placement model generation system comprising:

3. 2. The data placement model generation system according to claim 1, The reward calculation unit receives an input of information regarding a target performance of a distributed parallel database or a price constraint of a node, If the processing cost of the query does not satisfy the target performance, the reward is set low, and if the price of the node does not satisfy the price constraint, the reward is set to a negative value. A data placement model generation system comprising:

4. 4. The data placement model generation system according to claim 3, the reward calculation unit sets the reward using a processing cost of the query weighted based on an execution rate indicating a predetermined execution frequency of the query; A data placement model generation system comprising:

5. 5. The data placement model generation system according to claim 4, the reward calculation unit calculates the execution ratio by analyzing an execution log of the query, and sets the reward by using a processing cost of the query weighted based on the calculated execution ratio; A data placement model generation system comprising:

6. A data placement model generation system that generates a node configuration and a data placement key in a distributed parallel database by reinforcement learning, a data distribution acquisition unit that acquires a data distribution of each table and a data distribution of a joined table that is joined by a query, based on data stored in the distributed parallel database and a query executed on the distributed parallel database; a data placement estimation unit that estimates the placement of the data based on the state and the data distribution, using the node configuration and the data placement key, which has information about the sort order of the columns constituting the data and information about the distribution method between the nodes, as a state in the reinforcement learning; a reward calculation unit that calculates a reward in the reinforcement learning based on a result of estimating the data placement, the node configuration, and a processing cost of a query executed on the distributed parallel database; a reinforcement learning unit that outputs the node configuration and the data placement key that have been reinforced learned in response to the calculated reward as an action; a state update unit that updates the node configuration and the data placement key output as the action as a new state; A data placement model generating system comprising:

7. 7. The data placement model generation system according to claim 6, the data placement estimation unit assigns the data to nodes in a round robin manner when a distribution key is not set in the data placement key, assigns the data to nodes based on a hash value of the distribution key when a distribution key is set in the data placement key, and copies the data and assigns it to all nodes when a distribution key is set and the distribution key indicates all nodes. A data placement model generation system comprising:

8. 7. The data placement model generation system according to claim 6, The reward calculation unit receives an input of information regarding a target performance of a distributed parallel database or a price constraint of a node, If the processing cost of the query does not satisfy the target performance, the reward is set low, and if the price of the node does not satisfy the price constraint, the reward is set to a negative value. A data placement model generation system comprising:

9. 9. The data placement model generation system according to claim 8, the reward calculation unit sets the reward using a processing cost of the query weighted based on an execution rate indicating a predetermined execution frequency of the query; A data placement model generation system comprising:

10. 10. The data placement model generation system according to claim 9, the reward calculation unit calculates the execution ratio by analyzing an execution log of the query, and sets the reward by using a processing cost of the query weighted based on the calculated execution ratio; A data placement model generation system comprising:

Images