Information management device, information management method, and program

Abstract

To provide an information management apparatus and the like that can reduce an effort for creating a rule for moving data stored in an OLTP database to an OLAP database.SOLUTION: A data movement optimization apparatus 10 includes: a table status update section 102 that acquires table status in which data is stored for each of an OLTP database and an OLAP database selected from a plurality of databases; a policy network learning section 105 that performs learning on the number of federated queries that are processing in which it is necessary to query both the OLTP database and the OLAP database for the table status, and a cost incurred when data is moved from the OLTP database to the OLAP database; and data movement section 103 that performs control for moving data from the OLTP database to the LAP database on the basis of a result of learning and the table status.SELECTED DRAWING: Figure 4

Description

Technical Field

[0001] The present invention relates to an information management apparatus, an information management method, and a program. In particular, the present invention relates to an information management apparatus suitable for managing a database used for HTAP (Hybrid Transaction Analytical Processing).

Background Art

[0002] In recent years, with the rapid digitization, the workload in which transaction processing and analysis processing are mixed has been increasing. HTAP is an architecture that supports both transaction processing and analysis processing. In the HTAP architecture, for example, a database used for online transaction processing (OLTP: Online Transaction Processing) (hereinafter sometimes simply referred to as an "OLTP database"), and a database used for online analytical processing (OLAP: Online Analytical Processing) (hereinafter sometimes simply referred to as an "OLAP database") are used. The OLTP database stores relatively recent data in order to perform transaction processing at high speed. On the other hand, the OLAP database stores data for a relatively long period in order to perform analysis processing. And data is moved from the OLTP database to the OLAP database.

[0003] Patent Document 1 discloses a database management system. This database management system includes an OLTP processing unit, an OLAP processing unit, a SCM that stores row-oriented data for OLTP, and on the SCM, OLTP updates data and holds it in row orientation, and after accumulating a predetermined amount of the data held in row orientation, stores the accumulated predetermined amount of data in a column unit block in DRAM, and stores the column unit block in DISK for OLAP, a memory management unit.

Prior Art Documents

Patent Documents

[0004] [Patent Document 1] U.S. Patent Application Publication No. 2013 / 0024573 [Overview of the project] [Problems that the invention aims to solve]

[0005] However, it takes a certain amount of time for data stored in an OLTP database to be moved to an OLAP database. Therefore, if there are queries that perform analytical processing on data stored in an OLTP database (federated queries), it is necessary to temporarily move the data from the OLTP database to the OLAP database before the regular data movement. This operation is costly. Therefore, it is desirable to establish rules for moving data stored in the OLTP database to the OLAP database in order to reduce the number of federated queries, and to move the data according to these rules. However, the state of the database changes dynamically, and it takes considerable effort for the database administrator to design rules for moving data under these conditions. The present invention aims to provide an information management device, an information management method, and a program that can reduce the effort required to create rules for moving data stored in a first database (e.g., an OLTP database) to a second database (e.g., an OLAP database). [Means for solving the problem]

[0006] To solve the above problems, the present invention provides an information management device comprising: an acquisition unit that acquires the state in which data is stored for each of a first database and a second database selected from among a plurality of databases; a learning unit that learns the number of federated queries, which are processes that require querying both the first database and the second database, and the cost required when moving data from the first database to the second database, for each state; and a moving unit that controls the movement of data from the first database to the second database based on the learning results and the state.

[0007] Furthermore, the present invention provides an information management method that acquires the state in which data is stored for each of a first database and a second database selected from among multiple databases, learns the number of federated queries (processes that require querying both the first and second databases) and the cost required to move data from the first database to the second database for each state, and controls the movement of data from the first database to the second database based on the learning results and the state.

[0008] Furthermore, the present invention provides a program for a computer to implement an acquisition function that acquires the state in which data is stored in a first database and a second database, each selected from among multiple databases; a learning function that learns the number of federated queries, which are processes that require querying both the first database and the second database, and the cost required when moving data from the first database to the second database, based on the learning results and the state; and a move function that controls the movement of data from the first database to the second database based on the learning results and the state. [Effects of the Invention]

[0009] According to the invention described in claim 1, it is possible to provide an information management device that can reduce the effort required to create rules for moving data stored in a first database (e.g., an OLTP database) to a second database (e.g., an OLAP database). According to the invention described in claim 2, by considering a reward when moving data from the first database to the second database, machine learning can be performed for cases where the number and cost of federated queries are small. According to the invention described in claim 3, machine learning can be performed more efficiently. According to the invention described in claim 4, an appropriate value can be set as the reward. According to the invention described in claim 5, data can be moved when the number and cost of federated queries are expected to decrease. According to the invention described in claim 6, a more appropriate item can be selected as the basis for calculating the cost. According to the invention described in claim 7, more appropriate states can be targeted as the states of the first database and the second database. According to the invention described in claim 8, the recording period can be expressed more simply. According to the invention described in claim 9, the effort required to create rules for moving data stored in an OLTP database to an OLAP database can be reduced. According to the invention described in claim 10, the content of the federated query can be specified. According to the invention described in claim 11, the number and cost of federated queries can be reduced for a system operating on an HTAP architecture. According to the invention described in claim 12, it is possible to provide an information management method that can reduce the effort required to create rules for moving data stored in a first database (e.g., an OLTP database) to a second database (e.g., an OLAP database). According to the invention described in claim 13, a computer can implement a function that reduces the effort required to create rules for moving data stored in a first database (e.g., an OLTP database) to a second database (e.g., an OLAP database). [Brief explanation of the drawing]

[0010] [Figure 1] This figure shows an example of the configuration of the information processing system in this embodiment. [Figure 2] This diagram illustrates the case where a federated query occurs. [Figure 3] This diagram illustrates the steps a database administrator takes when creating rules for moving data. [Figure 4] This is a block diagram showing the functional configuration of an information processing system. [Figure 5] This diagram illustrates the general operation of an information processing system. [Figure 6] This diagram illustrates the procedure by which a data movement optimization device performs deep machine learning based on table status. [Figure 7] This diagram illustrates an artificial neural network used for deep machine learning. [Figure 8] This diagram shows the parameters used when calculating costs. [Figure 9] This flowchart illustrates the operation of a data migration optimization device when moving data from an OLTP database to an OLAP database using a policy network. [Figure 10] This is a flowchart illustrating the learning process of a policy network. [Figure 11] This is a flowchart illustrating the learning process of a policy network. [Figure 12]This is a diagram that further details flowchart 3 of step S1006 in FIG. 10. [Figure 13] This is a diagram that further details flowchart 4 of step S1011 in FIG. 10. [Figure 14] This is a diagram that further details flowchart 5 of step S1009 in FIG. 10.

Embodiments for Carrying Out the Invention

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

[0012] <Explanation of the Entire Information Processing System 1> FIG. 1 is a diagram showing a configuration example of the information processing system 1 in the present embodiment. As shown in the figure, the information processing system 1 of the present embodiment has a Data Movement optimizer 10, an OLTP system (OLTP) 20, and an OLAT system (OLAP) 30 connected via a network 40.

[0013] The data movement optimizer 10 is an example of an information management device and is a server computer that manages the data of the OLTP system 20 and the OLAP system 30. As described above, the data movement optimizer 10 manages the movement of data from the OLTP system 20 to the OLAP system 30. More specifically, it determines the timing for moving data from the OLTP system 20 to the OLAP system 30.

[0014] The OLTP system 20 is a server computer that includes an OLTP database. In this embodiment, the OLTP database is an example of a first database and is used when performing OLTP. The OLTP system 20 performs real-time (online) transaction processing in accordance with user requests. The OLTP system 20 needs to be excellent at high-speed processing of small amounts of data. Therefore, the OLTP database has fast data retrieval. Because the OLTP system 20 prioritizes performance, the OLTP database only handles recent history. This means that the data retention period for data stored in the OLTP database is relatively short. In general, OLTP databases store recent data, update it in real time, and support high concurrency and strong consistency. Furthermore, the OLTP system 20 focuses on very fast query processing, and the data changed in each request is limited to only a few rows. Therefore, OLTP databases are often row-oriented databases.

[0015] The OLAP system 30 is a server computer that includes an OLAP database. In this embodiment, the OLAP database is an example of a second database and is the database used when performing OLAP. The OLAP system 30 performs analysis processing in real time (online) according to requests from users. The OLAP system 30 needs to handle a large amount of data. Therefore, the OLAP database needs to be a database that can store a large amount of data. For this reason, the OLAP database handles relatively long-term history. This means that the data stored in the OLAP database has a relatively long recording period. Also, in the OLAP database, historical data is given more importance than recent data when storing data. The OLAP database is a database that handles historical data with a small transaction volume, and because it processes historical data in batch processing, it does not have high parallelism. And it handles many rows in a single request. For this reason, the OLAP database is often a column-oriented database.

[0016] Although the data transfer optimization device 10, OLTP system 20, and OLAP system 30 shown in the diagram are each represented as one unit, their functions may be implemented using multiple server computers. Furthermore, while the data transfer optimization device 10, OLTP system 20, and OLAP system are described as server computers, they are not limited to these and may be general-purpose personal computers (PCs), mobile computers, mobile phones, smartphones, tablets, or other computer devices.

[0017] The data movement optimization device 10, the OLTP system 20, and the OLAP system 30 have similar hardware configurations. Specifically, the data movement optimization device 10 has a CPU (Central Processing Unit) 11, memory 12, a storage device 13, and a network interface 14. Similarly, the OLTP system 20 has a CPU 21, memory 22, a storage device 23, and a network interface 24. Furthermore, the OLAP system 30 has a CPU 31, memory 32, a storage device 33, and a network interface 34.

[0018] CPUs 11, 21, and 31 execute programs such as the operating system (OS) and application software. Memory modules 12, 22, and 32 consist of ROM (Read Only Memory) which stores the BIOS (Basic Input Output System), etc., and RAM (Random Access Memory) which is used as main memory. Storage devices 13, 23, and 33 are, for example, HDDs (Hard Disk Drives) and SSDs (Solid State Drives), and store application software and the like. Storage device 23 stores data from the OLTP database, and storage device 33 stores data from the OLAP database. Network interfaces 14, 24, and 34 are communication modules for communicating with external devices.

[0019] Furthermore, the data transfer optimization device 10, the OLTP system 20, and the OLAP system 30 may also be equipped with output devices. Examples of output devices include displays that show images and other information. Furthermore, the data transfer optimization device 10, the OLTP system 20, and the OLAP system 30 may also be equipped with input devices. Examples of input devices include keyboards for inputting characters, and pointing devices such as mice.

[0020] Network 40 is a communication means used for data communication between the data movement optimization device 10, the OLTP system 20, and the OLAP system 30, and is, for example, the Internet, a LAN (Local Area Network), or a WAN (Wide Area Network). The communication lines used for data communication can be wired or wireless, or a combination of both may be used. Furthermore, the data movement optimization device 10, the OLTP system 20, and the OLAP system 30 may be connected via multiple networks and communication lines using relay devices such as gateway devices and routers.

[0021] <Outline of the operation of Information Processing System 1> Information processing system 1 operates under the HTAP architecture. The HTAP architecture supports both transaction processing and analytical processing, and can perform both processes according to user requests. Therefore, if the user request is for transaction processing, the OLTP system 20 is used, and processing is performed using application software that performs transaction processing. If the user request is for analytical processing, the OLAP system 30 is used, and processing is performed using application software that performs analytical processing. As mentioned above, the OLTP database stores recent data, while the OLTP database stores data with more emphasis on historical data than recent data. Therefore, in the HTAP architecture, it is necessary to move data from the OLTP database to the OLAP database. This data movement can be performed periodically, such as daily or annually. However, data movement incurs costs. Here, "costs" include, for example, the cost of using CPUs 11 and 21, the cost of reading and writing data to and from the OLTP and OLAP databases, and the cost of communication using the network 40. Of these, the cost of writing data is particularly large.

[0022] Furthermore, because OLAP databases only store relatively old data, it is sometimes necessary to query data from a period beyond that stored in the OLAP database during analytical processing. In other words, since OLTP databases contain data from a period beyond that of OLAP databases, it is possible to perform analytical processing on more recent data by using the data in the OLTP database. However, this results in queries (federated queries) that perform analytical processing on the data stored in the OLTP database. This is a process that requires querying both the OLTP database and the OLAP database.

[0023] Figure 2 illustrates the case where a federated query occurs. This section shows the OLTP query logs (L1) for the OLTP database, the OLAP query logs (L2) for the OLAP database, and the Table Status (505). The Table Status (505), as will be explained in more detail later, is a table that represents the state in which data is stored within the OLTP and OLTAP databases. The Table Status includes information about the data recording period for each of the OLTP and OLTAP databases. The "recording period" can be represented by the date and time each piece of data was generated. That is, the recording period is represented by the date and time of the oldest data and the date and time of the newest data. In other words, the recording period indicates that data from the period between these two dates and times will be recorded. By referring to log L2, we can see that the period for the analysis processing requested by a single query is from 2019-01-07 09:30:00 (9:30 AM on January 7, 2019) to 2021-03-31 11:00:00 (11:00 AM on March 31, 2021). According to table status 505 at this time, the data retention period for the OLAP database is only until 2020 / 12 / 01 10:55 (10:55 AM on December 1, 2020). Therefore, data from this date and time onward needs to be moved from the OLTP database. In this case, data from 2020 / 12 / 01 11:00 (11:00 AM on December 1, 2020) to 2021-01-04 11:25:00 (11:25 AM on January 4, 2021) needs to be moved from the OLTP database to the OLAP database. In other words, all data from the OLTP database needs to be moved to the OLAP database.

[0024] When a federated query occurs, it requires the temporary movement of necessary data from the OLTP database to the OLAP database. This operation is costly, so fewer federated queries are preferable. Federated queries decrease as the frequency of data movement between the OLTP and OLAP databases increases. However, as mentioned above, data movement is costly. Conversely, the less frequently data is moved, the lower the cost, but the more likely federated queries are to occur. Based on the above, in order to reduce federated queries and costs, it is desirable to perform data migrations dynamically rather than periodically. In other words, to reduce federated queries and costs, it is desirable to perform data movement processes not on a regular basis, but irregularly, for example, depending on the state of the OLTP and OLAP databases. Traditionally, database administrators have created rules for data movement and determined the timing of data movement according to these rules.

[0025] Figure 3 illustrates the steps a database administrator takes when creating rules for moving data. A database administrator needs to follow four steps: Analysis, Design, Implementation, and Management. In "Analysis," the database administrator observes and analyzes query logs over several days. This allows the database administrator to understand the data movement requirements based on the freshness of the data in the OLTP and OLP databases. In "Design," the database administrator translates these requirements into rule designs that improve the performance of the information processing system. In "Implementation," code is created according to the rule designs, and testing verifies whether these rules meet the requirements. After verification, these rules are implemented. Furthermore, in "Management," data is moved according to these rules, but after some time, the requirements change, and the rules need to be updated. In other words, creating rules for moving data is a complex task for database administrators and requires a great deal of effort and time. Therefore, in this embodiment, the data movement optimization device 10 uses deep machine learning to learn when to reduce federated queries and costs when moving data. Then, the data movement optimization device 10 solves the above problem by determining the timing of data movement based on the learning model created from the learning results. To achieve this, in this embodiment, the data movement optimization device 10 has the following configuration.

[0026] <Description of the functional configuration of Information Processing System 1> Next, we will describe the functional configuration of Information Processing System 1. Figure 4 is a block diagram showing the functional configuration of the information processing system 1. This section describes example functional configurations for the Data Movement optimizer 10, the OLTP system 20, and the OLAP system 30. The data movement optimization device 10 comprises a table status storage unit 101, a table status update unit 102, a data movement unit 103, a policy network unit 104, a policy network training unit 105, a query log reader 106, a network status reader 107, a query type identifier 108, and an action manager 109. Furthermore, the OLTP system 20 includes an OLTP database (OLTP) 201, a database load acquisition unit (Database load) 202, and an OLTP query log storage unit (OLTP Query Log file) 203. Furthermore, the OLAP system 30 includes an OLAP database (OLAP) 301, a database load acquisition unit (Database load) 302, and an OLAP query log storage unit (OLAP Query Log file) 303.

[0027] The table status storage unit 101 stores the table status 505. The table status update unit 102 updates the table status 505. In other words, when new data is stored in the OLTP database or OLTAP database, the aforementioned recording period, etc., changes. Therefore, the table status update unit 102 updates the table status 505 accordingly. The table status update unit 102 functions as an acquisition unit that acquires the status of data storage for each of the OLTP database and OLTAP database.

[0028] The data movement unit 103 is an example of a movement unit that controls the movement of data from the OLTP database to the OLTAP database based on the learning results and table status 505. In other words, as described above, the data movement unit 103 dynamically moves data from the OLTP database to the OLTAP database at predetermined timings. In addition, when a federated query occurs, the data movement unit 103 temporarily moves the necessary data from the OLTP database to the OLAP database.

[0029] The policy network unit 104 stores the learned model acquired by the policy network learning unit 105. Here, this learned model is referred to as the policy network. The policy network learning unit 105 is an example of a learning unit. The policy network learning unit 105 learns about the number of federated queries for table status 505 and the cost required to move data from the OLTP database to the OLTAP database. As will be described in more detail later, the policy network learning unit 105 performs learning using deep machine learning.

[0030] The query log reading unit 106 records logs about queries requested by the user. The network status reading unit 107 acquires the communication status of network 40. In this case, it acquires the communication status when moving data from the OLTP database to the OLTAP database. The query type identification unit 108 identifies the type of query requested by the user. That is, it identifies whether it is an OLTP (Online Transaction Processing) performed by the OLTP system 20 or an OLAP (Online Analytical Processing) performed by the OLAP system 30. The operation management unit 109 manages the operation of the entire data transfer optimization device 10.

[0031] As mentioned above, OLTP database 201 is a database used when performing OLTP (Online Transaction Processing). The database load acquisition unit 202 acquires the load on the OLTP database 201. The OLTP query log storage unit 203 stores logs of queries that perform OLTP (Online Transaction Processing).

[0032] As mentioned above, OLAP database 301 is a database used when performing OLAP (Online Analytical Processing). The database load acquisition unit 302 acquires the load on the OLAP database 301. The OLAP query log storage unit 303 stores logs of queries performed using OLAP (Online Analytical Processing).

[0033] <Explanation of the general operation of Information Processing System 1> Figure 5 is a diagram illustrating the schematic operation of information processing system 1. Here, we consider a case where, for example, data acquired from a sensor 501 used in product manufacturing is written to an OLTP database 201 (Data Insertion), and OLTP (Online Transaction Processing) or OLAP (Online Analytical Processing) is performed on this data.

[0034] When a transactional query occurs, the Transactional App 502 queries the OLTP database 201 and performs the necessary processing. On the other hand, when an analytical query occurs, the Analytics Reporting App 503 queries the OLAP database 301 and performs the necessary processing. These processes are distributed by the Integrated Interface 504. In Figure 4, this is done, for example, by the Query Type Identifier 108. Furthermore, the integrated interface 504 performs data movement from the OLTP database 201 to the OLAP database 301. In addition, if a federated query occurs, the integrated interface 504 temporarily moves the relevant data from the OLTP database 201 to the OLAP database 301. In Figure 4, this is performed by the data movement unit 103.

[0035] Furthermore, the data movement optimization device 10 creates a Table Status 505. The Table Status 505 represents the state of the OLTP database 201 and the OLAP database 301, and includes the data recording period as shown in the figure. In this case, the data recording period for data stored in each database (DB) is expressed by the lower limit and upper limit of the time when the data to be stored in each database was generated. That is, the data recording period is the period between the lower limit and the upper limit. In this case, the data recording period for data stored in the OLTP database 201 is from 2020 / 12 / 01 11:00 to 2021 / 01 / 04 11:25. Also, the data recording period for data stored in the OLAP database 301 is from 2016 / 4 / 1 00:00 to 2020 / 12 / 01 10:55.

[0036] <Explanation of Deep Machine Learning> Figure 6 shows the procedure by which the data movement optimization device 10 performs deep machine learning based on the table status 505. Here, the data movement optimization device 10 performs deep machine learning (Deep Q learning) in the following three stages: ACTION, REWORD, and OBSERVATION.

[0037] (ACTION) "ACTION" is an example of the first stage, where either data is moved from OLTP database 201 to OLAP database 301 (Copy data) or no action is taken. The environment at this time is considered to be table status 505. Note that the ACTION could also be to move all the data, move only some of it, or move the data after the load on the resources has decreased.

[0038] (REWORD) "REWORD" is an example of the second stage, determining the reward for table status 505, which is determined by the results of the above ACTION. This REWORD is determined by the number and cost of federated queries. In other words, the REWORD should be larger the smaller the number of federated queries in the state of table status 505. Also, the REWORD should be larger the smaller the cost when data is dynamically moved from the OLTP database to the OLTAP database at a predetermined timing. For example, the data movement optimization device 10 gives a positive reward when the correct ACTION is taken, such as reducing the number of federated queries or moving data when the cost is low. On the other hand, the data movement optimization device 10 gives a negative reward when the incorrect ACTION is taken, such as increasing the number of federated queries or moving data when the cost is high. The REWORD can be calculated by inputting the number and cost of federated queries into a predetermined reward function. In deep machine learning, the REWORD (reward) is learned to minimize the cost (especially the high write cost) and the number of federated queries. Based on the learning results and the table status 505, the data movement optimization device 10 decides to move data from the OLTP database 201 to the OLAP database 301 when it is estimated that the reward will be large.

[0039] (Observation) "OBSERVATION" is an example of the third stage, where the table status 505 is updated (Updated table Status) when data is moved from OLTP database 201 to OLAP database 301.

[0040] Figure 7 shows an artificial neural network used for deep machine learning. The artificial neural network shown has an input layer with 24 nodes and an output layer with 2 nodes. It also has three hidden layers, each with 32, 64, and 32 nodes.

[0041] Figure 8 shows the parameters used when calculating costs. The cost is calculated based on the load on the resources used when moving data from OLTP database 201 to OLAP database 301. In Figure 1, these resources include, for example, CPUs 11 and 21, storage devices 13 and 23, network interfaces 14 and 24, and network 40. Figure 8 shows three types of load: OLTP load on the OLTP system 20, Network load on the network 40, and OLAP load on the OLAP system 30. The load on the OLTP system 20 and the OLAP system 30 includes, for example, CPU utilization, free RAM, average data read per second, and average data write per second. The load on the network 40 includes download speed, upload speed, and availability.

[0042] <Detailed explanation of the operation of the data transfer optimization device 10> Next, a detailed explanation of the operation of the data transfer optimization device 10 will be given. Figure 9 is a flowchart illustrating the operation of the data movement optimization device 10 when moving data from OLTP database 201 to OLAP database 301 using a policy network. First, the policy network reads the query logs (step S901). Next, the policy network inputs the table status to the policy network (input to policy network) (step S902). The policy network then decides whether or not to move data from OLTP database 201 to OLAP database 301 (Data movement?) (Step S903). As a result, if the policy network does not move the data (No in step S903), the process returns to step S901. Conversely, if the policy network decides to move the data (Yes in step S903), it initiates the data movement (step S904). Furthermore, the policy network updates the table status (Step S905).

[0043] Figures 10 and 11 are flowcharts illustrating the learning process of a policy network. In other words, Figures 10 and 11 are flowcharts for creating a policy network. Here, data is moved periodically, and the policy network learns under these conditions. Here, `step` represents the number of queries. Data is periodically moved when `step` exceeds a predetermined threshold. `Episode` represents the number of times data has been periodically moved. First, the data movement optimization device 10 sets the episode threshold and the periodic data movement time. The data movement optimization device 10 also generates policy networks 1 and 2 with random weights (step S1001). Next, the data transfer optimization device 10 sets Episode to 1 (Episode=1) (step S1002). That is, it initializes Episode.

[0044] Furthermore, the data movement optimization device 10 obtains the table status (Store table status) (step S1003). Furthermore, the data transfer optimization device 10 sets step to 1 (Step=1) (step S1004). In other words, it initializes step. Then, the data movement optimization device 10 monitors the query logs (Step S1005). Furthermore, the data movement optimization device 10 checks the table status and query and determines the query type by using the flowchart 3 described later (step S1006).

[0045] Next, the data movement optimization device 10 determines whether the query type is a federated query (Step S1007). As a result, if the query type is a federated query (Yes in step S1007), the data movement optimization device 10 sets the federated query flag to true (Set federated query flag=true) (step S1008). Then, the data movement optimization device 10 determines the approximate cost of data movement using flowchart 5, which will be described later (Determine approximate cost of data movement using flowchart 5) (step S1009). After that, the process proceeds to step S1010. Conversely, if the query type is not a federated query (No in step S1007), the data movement optimization device 10 inputs the table status to the policy network and determines the action (step S1010).

[0046] Next, the data movement optimization device 10 takes the output of policy network 1 and determines a flag indicating the correct action using flowchart 4 (step S1011). Furthermore, the data movement optimization device 10 determines whether this flag was true or not (Is right action=true?) (step S1012). As a result, if this flag is not true (No in step S1012), the data movement optimization device 10 sets REWORD (reward) to -3 (Set reward=-3) (step S1013). Furthermore, if this flag is true (Yes in step S1012), the data movement optimization device 10 determines whether the cost required for data movement was low (If data movement cost=low?) (step S1014). As a result, if the cost is low (Yes in step S1014), the data movement optimization device 10 sets REWORD (reward) to +3 (Set reward=+3) (step S1015). Conversely, if the cost is not low (No in step S1014), the data movement optimization device 10 sets REWORD (reward) to +1 (Set reward=+1) (step S1016).

[0047] In other words, if the data movement is incorrect, a negative reward is given (in the above case, the reward is -3). If the data movement is correct and the cost is low, a positive reward is given, and the amount is large (in the above case, the reward is +3). Furthermore, even if the data movement is correct but the cost is high, a positive reward is given, but the amount is small (in the above case, the reward is +1).

[0048] Then, after setting the REWORD (reward) in steps S1013, S1015, and S1016, the data movement optimization device 10 updates the weights of policy network 2 using the Bellman equation (step S1017). The Bellman equation is an equation that represents a necessary condition for optimality in mathematical optimization known as dynamic programming.

[0049] Next, the data movement optimization device 10 increases the step count (Step S1018). Then, the data movement optimization device 10 determines whether the number of federated queries exceeds a threshold (Step S1019). As a result, if the number of federated queries exceeds a threshold (Yes in step S1019), the data movement optimization device 10 resets the table status to the previous state (step S1020). Then, the process proceeds to step S1023.

[0050] Conversely, if the number of federated queries does not exceed the threshold (No in step S1019), the data movement optimization device 10 determines whether the number of steps exceeds the timing for periodic data movement (Number of steps > periodic data timing?) (step S1021). As a result, if the number of federated queries does not exceed the threshold (No in step S1021), return to step S1005. Conversely, if the number of federated queries exceeds a threshold (Yes in step S1021), the data movement optimization device 10 performs periodic data movement (step S1022).

[0051] Then, the data transfer optimization device 10 increments Episode by 1 (Episode = Episode + 1) (step S1023). After a fixed period of time has elapsed, the data movement optimization device 10 copies the weights of policy network 2 to policy network 1 (step S1024). Next, the data movement optimization device 10 determines whether the number of episodes exceeds a predetermined threshold (step S1025). As a result, if the threshold is exceeded (Yes in step 1025), the data movement optimization device 10 uses policy network 1 as a policy network for dynamically predicting data movement (step S1026). Conversely, if the threshold is not exceeded (No in step S1025), the process returns to step S1002.

[0052] In this way, the data movement optimization device 10 calculates a REWORD (reward) for each query, and the policy network 2 learns accordingly (step S1017). The data movement optimization device 10 then performs periodic data movement when the number of queries, which is called step, exceeds a predetermined threshold (steps S1021 to S1022). Also, when the number of federated queries exceeds a predetermined threshold, the policy network 2 returns to its previous state (steps S1019 to S1020). At regular intervals, the weights of policy network 2 are copied to policy network 1 (step S1024). Here, the period from moving data to moving the next data is considered one episode. When the episode exceeds a predetermined threshold (step S1025), the policy network 1 with the best results is generated and adopted as the policy network used in Figure 9 (step S1026). In other words, the policy network is sequentially updated to one with fewer federated queries and lower costs, until the policy network with the fewest federated queries and lowest costs is created.

[0053] Figure 12 is a diagram that provides a more detailed explanation of flowchart 3 in step S1006 of Figure 10. First, the data movement optimization device 10 inputs the table status and the time range required by the query (Step S1201). Next, the data movement optimization device 10 determines whether the time range is within the recording period of the OLTP database (Is time range id in OLTP range) (step S1202). As a result, if the time range is within the recording period of the OLTP database (Yes in step S1102), the data movement optimization device 10 sets the query type as an OLTP query (Set query type as OLTP query) (step S1203). Conversely, if the time range is not within the recording period of the OLTP database (No in step S1202), the data movement optimization device 10 determines whether the time range is within the recording period of the OLAP database (Is time range id in OATP range) (step S1204). As a result, if the time range is within the recording period of the OLAP database (Yes in step S1204), the data movement optimization device 10 sets the query type as an OLAP query (Set query type as OLAP query) (step S1205). Conversely, if the time range is not within the recording period of the OLAP database (No in step S1204), the data movement optimization device 10 sets the query type as a federated query (Set query type as federated query) (step S1206).

[0054] Figure 13 is a diagram that provides a more detailed explanation of flowchart 4 in step S1011 of Figure 10. First, the data movement optimization device 10 receives an action created by the policy network (Input action provided by policy network) (step S1301). Next, the data movement optimization device 10 determines whether the federated query flag is correct (federated query flag=true) and whether data movement has occurred (data movement actin=move). Alternatively, the data movement optimization device 10 determines whether the federated query flag is incorrect (federated query flag=false) and whether data movement has not occurred (data movement actin=no action) (step S1302). As a result, if the answer in step S1302 is Yes, the data movement optimization device 10 sets the right action flag to true (set right action flag as true) (step S1303). In contrast, if the answer in step S1302 is No, the data movement optimization device 10 sets the flag indicating the correct action to false (set right action flag as false) (step S1304).

[0055] Figure 14 is a diagram that provides a more detailed explanation of flowchart 5 in step S1009 of Figure 10. First, the data movement optimization device 10 receives input (Input OLTP load, OLAP load, Network Status, Query requirement) for the OLTP system 20, the OLAP system 30, the network status 40, and the query requirement (Step S1401). Next, the data movement optimization device 10 calculates the cost of data movement (step S1402). Furthermore, the data movement optimization device 10 determines whether the cost is greater than a predetermined threshold (If cost > predefined threshold) (step S1403). As a result, if the cost is greater than a predetermined threshold (Yes in step S1403), the data movement optimization device 10 determines that the cost required for moving the data is high (Set movement cost = high) (step S1404). Conversely, if the cost is below a predetermined threshold (No in step S1403), the data movement optimization device 10 determines that the cost required for data movement is low (Set movement cost as low) (step S1405).

[0056] As described above, the data movement optimization device 10 can move data stored in an OLTP database to an OLAP database in a way that reduces federated queries and costs. This data movement is performed dynamically according to the state of each database, in a way that reduces federated queries and costs. The timing of the movement is determined by a machine learning model. In other words, the rules for the movement are created automatically by machine learning. Therefore, the effort required of database administrators to create rules for moving data stored in an OLTP database to an OLAP database can be reduced.

[0057] In the information processing system 1 described above, there were two databases: an OLTP database and an OLAP database. However, there may be three or more databases. In this case, the present invention can be applied to cases where data is moved between multiple databases selected from three or more databases.

[0058] <Explanation of information management methods> Here, the processing performed by the data movement optimization device 10 can be understood as an information management method that obtains the table status 505 where data is stored for each of the OLTP database and OLAP database selected from among multiple databases, learns the number of federated queries that require querying both the OLTP database and the OLAP database for the table status 505, and the cost required to move data from the OLTP database to the OLAP database, and controls the movement of data from the OLTP database to the OLAP database based on the learning results and the table status 505.

[0059] <Program Description> Furthermore, the processing performed by the data transfer optimization device 10 in this embodiment, as described above, is realized through the cooperation of software and hardware resources. Specifically, a processor such as a CPU provided in the data transfer optimization device 10 executes a program that realizes each of the functions of the data transfer optimization device 10, thereby realizing each of these functions.

[0060] Therefore, in this embodiment, the processing performed by the data movement optimization device 10 can also be understood as a program that enables a computer to implement: an acquisition function that acquires table status 505 where data is stored for each of the OLTP database and OLAP database selected from among multiple databases; a learning function that learns the number of federated queries, which are processes that require querying both the OLTP database and the OLAP database for the table status 505, and the cost required when moving data from the OLTP database to the OLAP database; and a movement function that controls the movement of data from the OLTP database to the OLAP database based on the learning results and the table status 505.

[0061] Furthermore, the program that implements this embodiment can be provided not only by communication means, but also by being stored on a recording medium such as a CD-ROM.

[0062] Although this embodiment has been described above, the technical scope of the present invention is not limited to the scope described in the above embodiment. It is clear from the claims that various modifications or improvements made to the above embodiment are also included in the technical scope of the present invention. [Explanation of Symbols]

[0063] 1...Information processing system, 10...Data movement optimization device, 20...OLTP system, 30...OLAT system, 40...Network, 101...Table status storage unit, 102...Table status update unit, 103...Data movement unit, 104...Policy network unit, 105...Policy network learning unit, 106...Query log reading unit, 107...Network status reading unit, 108...Query type identification unit, 109...Operation management unit, 505...Table status

Claims

1. A data acquisition unit obtains table status information, including data recording period information, for each of the first and second databases selected from among multiple databases. A learning unit that learns about a reward determined by the table status, the number of federated queries which are processes that require querying both the first database and the second database, and the cost required to move data from the first database to the second database. A movement unit that controls the movement of data from the first database to the second database at a timing when the reward is estimated to be large, An information management device equipped with the following features.

2. The aforementioned learning unit, A first step involves either moving data from the first database to the second database or not, A second stage in which the reward determined by the results of the first stage is determined, A third step is to update the table status when data is moved from the first database to the second database, An information management device according to claim 1, which includes performing machine learning.

3. The information management device according to claim 1, wherein the reward increases as the number of federated queries and the cost decrease.

4. The information management device according to claim 1, wherein the cost is calculated based on the load on resources used when moving data from the first database to the second database.

5. The information management device according to claim 1, wherein the recording period is represented by the lower limit and upper limit of the time when the data to be stored in the first database and the second database, respectively, was generated.

6. The information management device according to claim 1, wherein the first database is an OLTP database used when performing OLTP (Online Transaction Processing), and the second database is an OLAP database used when performing OLAP (Online Analytical Processing).

7. The information management device according to claim 6, wherein the federated query occurs when performing analytical processing on data stored in an OLTP database.

8. The information management device according to claim 6, wherein the OLTP database and the OLAP database are used under a Hybrid Transaction Analytical Processing (HTAP) architecture.

9. A computer-based information management method, For each of the first and second databases selected from among multiple databases, retrieve the table status, including information about the data collection period. The reward is determined by the table status, the number of federated queries which are processes that require querying both the first and second databases, and the cost required to move data from the first database to the second database. Control is performed to move data from the first database to the second database at a timing when the reward is estimated to be large. Information management method.

10. On the computer, A retrieval function that obtains table status information, including data recording period information, for each of the first and second databases selected from among multiple databases. A learning function that learns about a reward determined by the table status, the number of federated queries which are processes that require querying both the first and second databases, and the cost required to move data from the first database to the second database. A movement function that controls the movement of data from the first database to the second database at a timing when the reward is estimated to be large, A program to achieve this.

Images