Automatically optimize resource usage on the target database management system to improve workload performance

Abstract

Functionality is provided for loading a data set from a source database management system (DBMS) into a target DBMS to improve workload performance and reduce resource usage. Using the size and change rate of tables eligible for offloading to the target DBMS, a total central processor unit (CPU) cost corresponding to loading the data set into the target DBMS and a workload cost savings corresponding to executing one or more workloads on the target DBMS based on a set of one or more loading strategies are determined. The total CPU cost is compared to the workload cost savings of each loading strategy. An optimal loading strategy is selected based on comparing the total CPU cost to the workload cost savings of each loading strategy. The data set is automatically loaded from the source DBMS into the target DBMS based on the selected optimal loading strategy.

Description

Technical Field

[0001] The present disclosure relates generally to database management systems, and more particularly to automatically optimizing computer memory and central processing unit resources used by a target database management system to increase accelerated analytical query workload performance. Background Art

[0002] A database is an organized collection of data. A database management system (DBMS) is system software used to create and manage databases. A DBMS enables users to create, read, update, and remove data from a database. In other words, a DBMS acts as an interface between the database and the user or application and ensures that the data is consistently organized and easily accessible.

[0003] The DBMS manages the data, the database engine that allows access to the data, and the database schema that defines the logical structure of the database. These elements help provide concurrency, security, data integrity, and unified management processes. Typical database management tasks supported by a DBMS include change management, performance monitoring, and backup and recovery. Many database management systems (DBMS) can also perform automatic rollback, restart, and recovery, as well as log activities.

[0004] In a relational DBMS, records are stored in tables. Records are often called rows, and record attributes are called columns. Databases are usually not portable across different DBMSs, but different DBMSs can interoperate using standards such as Structured Query Language (SQL), Open Database Connectivity (ODBC), or Java Database Connectivity (JDBC) to allow a single application to work with more than one DBMS.

[0005] Technological advances have led to a continuous increase in the amount of data in all fields. In order to gain valuable insights, users require the ability to perform real-time analysis on large amounts of data in a specific area. However, real-time data analysis requires expensive query operations that are very time-consuming on the central processing unit (CPU). In addition, in traditional DBMS, CPU resources are usually dedicated to transactional workloads rather than analytical workloads.

[0006] Therefore, there is a need in the art to solve the aforementioned problems. Summary of the invention

[0007] From a first aspect, the present invention provides a computer-implemented method for loading a data set from a source database management system (DBMS) into a target database management system to improve workload performance and reduce resource usage. The method includes: using the size and change rate of tables eligible for unloading to the target DBMS, the computer determines the total central processor unit (CPU) cost corresponding to loading the data set into the target DBMS and the workload cost savings corresponding to executing one or more workloads on the target DBMS based on a set of one or more loading strategies; the computer compares the total CPU cost with the workload cost savings of each loading strategy in the set of loading strategies; the computer selects an optimal loading strategy from the set of loading strategies based on the comparison of the total CPU cost with the workload cost savings of each loading strategy; and the computer automatically loads the data set from the source DBMS into the target DBMS according to the selected optimal loading strategy to accelerate the execution of the one or more workloads, thereby improving workload performance and reducing CPU costs.

[0008] From another aspect, the present invention provides a computer system for loading a data set from a source database management system (DBMS) into a target DBMS to improve workload performance and reduce resource usage, the computer system comprising: a bus system; a storage device connected to the bus system, wherein the storage device stores program instructions; and a processor connected to the bus system, wherein the processor executes the program instructions to: use the size and change rate of a table eligible for unloading to the target DBMS to determine the total central processor unit (CPU) cost corresponding to loading the data set into the target DBMS and the workload cost savings corresponding to executing one or more workloads on the target DBMS based on a set of one or more loading strategies; compare the total CPU cost with the workload cost savings of each loading strategy in the set of loading strategies; select an optimal loading strategy from the set of loading strategies based on comparing the total CPU cost with the workload cost savings of each loading strategy; and automatically load the data set from the source DBMS into the target DBMS according to the selected optimal loading strategy to accelerate the execution of the one or more workloads, thereby improving workload performance and reducing CPU costs.

[0009] Viewed from another aspect, the present invention provides a computer program product for loading a data set from a source database management system (DBMS) into a target DBMS to improve workload performance and reduce resource usage. The computer program product includes a computer readable storage medium readable by a processing circuit and storing instructions for execution by the processing circuit to perform a method for performing the steps of the present invention.

[0010] Viewed from another aspect, the invention provides a computer program stored on a computer readable medium and loadable into the internal memory of a digital computer, the computer program comprising software code portions for performing the steps of the invention when said program is run on a computer.

[0011] According to one illustrative embodiment, a computer-implemented method is provided for loading a data set from a source database management system (DBMS) into a target database management system to improve workload performance and reduce resource usage. The computer uses the size and change rate of tables eligible for offloading to the target DBMS to determine a total central processor unit (CPU) cost corresponding to loading the data set into the target DBMS and a workload cost savings corresponding to executing one or more workloads on the target DBMS based on a set of one or more loading strategies. The computer compares the total CPU cost with the workload cost savings of each loading strategy in the set of loading strategies. The computer selects an optimal loading strategy from the set of loading strategies based on comparing the total CPU cost with the workload cost savings of each loading strategy. The computer automatically loads the data set from the source DBMS into the target DBMS according to the selected optimal loading strategy to accelerate the execution of the one or more workloads, thereby improving workload performance and reducing CPU costs. According to other illustrative embodiments, a computer system and a computer program product are provided for loading a data set from a source database management system (DBMS) into a target DBMS to improve workload performance and reduce resource usage.

[0012] According to other illustrative embodiments, a computer collects information about a set of workloads running on a target DBMS and the cost associated with running each corresponding workload on the target DBMS. The computer obtains the size of a set of tables in the target DBMS and the reference count associated with each table in the set of tables based on the usage of the set of workloads. The computer evaluates the frequency of table accesses by the set of workloads for each table in the set of tables in the target DBMS. The computer maps the table access frequency associated with each table referenced by the set of workloads to the memory and CPU cost savings associated with removing tables with the minimum frequency or no frequency of access from the target DBMS to identify table removal candidates. The computer automatically removes the identified table removal candidates from the target DBMS based on the mapping to reduce storage usage and improve workload performance on the target DBMS.

[0013] Therefore, the illustrative embodiment calculates the cost / benefit balance for each table by comparing the CPU cost savings for offloading the workload that references each table with the CPU cost incurred by loading data into each table on the target DBMS. In addition, the illustrative embodiment removes tables that are not used or least used on the target DBMS. Therefore, the illustrative embodiment optimizes memory and CPU usage to increase workload performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] Embodiments of the present invention will now be described, by way of example only, with reference to the accompanying drawings, in which:

[0015] Figure 1 is a diagram of a network of data processing systems in which the illustrative embodiments may be implemented;

[0016] Figure 2 is a diagram of a data processing system in which the illustrative embodiments may be implemented;

[0017] Figure 3A-3B is a flow chart showing a process for initially loading table data according to an illustrative embodiment;

[0018] Figure 4A-4B is a flow chart illustrating a process for refreshing data of a partition table according to an illustrative embodiment;

[0019] Figure 5A-5B is a flow chart illustrating a process for evaluating a table to enable incremental updating of table data in accordance with an illustrative embodiment;

[0020] Figure 6 is a flow chart illustrating a process for removing an unreferenced or minimal referenced table according to an illustrative embodiment;

[0021] Figure 7is a flow chart illustrating a process for selecting an optimal loading strategy according to an illustrative embodiment; and

[0022] Figure 8 is a flow chart illustrating a process for mapping table access frequencies to memory and CPU cost savings in accordance with an illustrative embodiment. DETAILED DESCRIPTION

[0023] The present invention may be a system, method and / or computer program product at any possible level of technical detail integration. The computer program product may include a computer-readable storage medium having computer-readable program instructions thereon for causing a processor to execute various aspects of the present invention.

[0024] A computer-readable storage medium may be a tangible device that can retain and store instructions used by an instruction execution device. A computer-readable storage medium may be, for example, but not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination of the foregoing. A non-exhaustive list of more specific examples of computer-readable storage media includes the following: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device (such as a punch card or a raised structure in a groove on which instructions are recorded), and any suitable combination of the foregoing. As used herein, a computer-readable storage medium should not be understood as being a transient signal itself, such as a radio wave or other freely propagating electromagnetic wave, an electromagnetic wave propagated through a waveguide or other transmission medium (e.g., a light pulse passing through a fiber optic cable), or an electrical signal transmitted through a wire.

[0025] The computer-readable program instructions described herein can be downloaded from a computer-readable storage medium to a corresponding computing / processing device, or downloaded to an external computer or external storage device via a network (e.g., the Internet, a local area network, a wide area network, and / or a wireless network). The network can include copper transmission cables, optical transmission fibers, wireless transmissions, routers, firewalls, switches, gateway computers, and / or edge servers. The network adapter card or network interface in each computing / processing device receives the computer-readable program instructions from the network and forwards the computer-readable program instructions to be stored in a computer-readable storage medium in each computing / processing device.

[0026] The computer-readable program instructions for performing the operation of the present invention may be assembler instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, configuration data for an integrated circuit, or source code or object code written in any combination of one or more programming languages ​​(including object-oriented programming languages ​​such as Smalltalk, C++, etc. and procedural programming languages ​​such as "C" programming languages) or similar programming languages. The computer-readable program instructions may be executed entirely on a user's computer, partially on a user's computer, as an independent software package, partially on a user's computer and partially on a remote computer or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network (including a local area network (LAN) or a wide area network (WAN)), or a connection may be established with an external computer (for example, via the Internet using an Internet service provider). In some embodiments, the electronic circuit includes, for example, a programmable logic circuit, a field programmable gate array (FPGA), or a programmable logic array (PLA), which may execute the computer-readable program instructions by utilizing the state information of the computer-readable program instructions to personalize the electronic circuit so as to perform various aspects of the present invention.

[0027] Various aspects of the present invention are described herein with reference to the flow charts and / or block diagrams of the methods, devices (systems) and computer program products according to embodiments of the present invention. It will be understood that each frame of the flow chart diagram and / or block diagram and the combination of frames in the flow chart diagram and / or block diagram can be implemented by computer-readable program instructions.

[0028] These computer-readable program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device to produce a machine, so that instructions executed by the processor of the computer or other programmable data processing device create a device for implementing the functions / actions specified in the flowchart and / or block diagram blocks. These computer-readable program instructions can also be stored in a computer-readable storage medium, which can instruct the computer, programmable data processing device, and / or other device to function in a specific manner, so that the computer-readable storage medium having the instructions stored therein includes an artifact, which includes instructions for implementing various aspects of the functions / actions specified in the flowchart and / or block diagram blocks.

[0029] Computer-readable program instructions may also be loaded onto a computer, other programmable data processing device or other device to cause a series of operational steps to be performed on the computer, other programmable device or other device to produce a computer-implemented process, so that instructions for the functions / actions specified in the flowchart and / or block diagram blocks are implemented on the computer, other programmable device or other device.

[0030] The flow chart and block diagram in the accompanying drawings show the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention.In this regard, each square frame in the flow chart or block diagram can represent a module, a fragment or a part of an instruction, which includes one or more executable instructions for realizing the specified logical function.In some alternative embodiments, the function pointed out in the square frame may not occur in the order pointed out in the figure.For example, depending on the function involved, two boxes shown in succession can actually be executed substantially at the same time, or these boxes can be executed in reverse order sometimes.It should also be noted that each square frame of the block diagram and / or flow chart and the combination of the square frames of the block diagram and / or flow chart can be realized by a combination of a hardware-based special-purpose system or special-purpose hardware and computer instructions that performs a specified function or action.

[0031] Referring now to the drawings, and in particular to Figure 1 and Figure 2 , which provides a diagram of a data processing environment in which an illustrative embodiment may be implemented. It should be understood that Figure 1 and Figure 2 This is exemplary only and is not intended to assert or imply any limitation with regard to the environments in which different embodiments may be implemented.Many modifications to the depicted environments may be made.

[0032] Figure 1 A diagram of a network of data processing systems in which the illustrative embodiments may be implemented is depicted. Network data processing system 100 is a network of computers, data processing systems, and other devices in which the illustrative embodiments may be implemented. Network data processing system 100 contains network 102, which is a medium for providing communication links between computers, data processing systems, and other devices connected together within network data processing system 100. Network 102 may include connections, such as wired communication links, wireless communication links, and fiber optic cables.

[0033] In the depicted example, server 104, server 106, and server 108 and storage 110 are connected to network 102. Server 104, server 106, and server 108 can be, for example, server computers with high-speed connections to network 102. In addition, server 104 is a source DBMS server, server 106 is a target DBMS server, and server 108 is an offload management server. It should be noted that source DBMS server 104, target DBMS server 106, and offload management server 108 can each represent a group of one or more computers. In addition, DBMS server 104, target DBMS server 106, and offload management server 108 can be computers in a data center. Alternatively, DBMS server 104, target DBMS server 106, and offload management server 108 can be computing nodes in a cloud environment.

[0034] The source DBMS server 104 represents a relational database that stores data in tables and is the source of requests for the data. In other words, the source DBMS server 104 serves queries corresponding to the data from client devices. The data can be any type of data. The target DBMS server 106 represents an accelerator for accelerating queries on the data contained on the source DBMS server 104 and includes a relational database of tables. The offload manager server 108 controls the process of offloading the query workload from the source DBMS server 104 to the target DBMS server 106.

[0035] The offload manager server 108 determines a balance between resource cost and resource cost savings for each table referenced by the query workload by comparing the CPU cost savings for offloading the query workload that references each table from the source DBMS server 104 and the CPU cost incurred by loading data into each referenced table on the target DBMS. In addition, the offload manager server 108 removes unreferenced or minimally referenced tables (e.g., the number of references corresponding to a particular table is below a defined reference threshold number) from the target DBMS 106 to free up memory on the target DBMS 106. Thus, the offload manager server 108 optimizes the memory and CPU usage of the target DBMS 106 to improve the performance of the accelerated analytical query workload.

[0036] Client 112, client 114, and client 116 are also connected to network 102. Clients 112, 114, and 116 are clients of source DBMS server 104. Users of clients 112, 114, and 116 can utilize clients 112, 114, and 116 to query source DBMS server 104 for data. The query can be, for example, an analytical query that requires increased CPU usage in source DBMS server 104. In this example, clients 112, 114, and 116 are shown as desktop or personal computers with a wired communication link to network 102. It should be noted that clients 112, 114, and 116 can be represented as other types of data processing systems, such as network computers, laptop computers, handheld computers, smart phones, smart watches, smart televisions, gaming devices, kiosks, etc., with wired or wireless communications linked to network 102.

[0037] The memory 110 represents a network storage device capable of storing any type of data in a structured format or an unstructured format. In addition, the storage device 110 may represent multiple network storage devices. The memory 110 may store, for example, identifiers and network addresses of multiple source DBMS servers, identifiers and network addresses of multiple target DBMS servers, identifiers and network addresses of multiple client devices, real-time database statistics, and the like. In addition, the memory 110 may store other types of data, such as authentication or credential data that may include user names, passwords, and biometric data associated with client device users and system administrators.

[0038] In addition, it should be noted that network data processing system 100 may include any number of additional servers, client devices, storage devices, and other devices not shown. Program code located in network data processing system 100 may be stored on a computer-readable storage medium and downloaded to a computer or other data processing device for use. For example, program code may be stored on a computer-readable storage medium on source DBMS server 104 and downloaded to client 112 via network 102 for use on client 112.

[0039] In the depicted example, network data processing system 100 may be implemented as a number of different types of communications networks, such as the Internet, an intranet, a local area network (LAN), a wide area network (WAN), or any combination thereof. Figure 1 This is merely an example and not an architectural limitation of the different illustrative embodiments.

[0040] Reference now Figure 2 , depicts a diagram of a data processing system according to an illustrative embodiment. Data processing system 200 is an example of a computer, such as Figure 11 , wherein computer readable program code or instructions implementing the processes of the illustrative embodiments may be placed in the offload management server 108. In this illustrative example, data processing system 200 includes communications fabric 202 that provides communications between processor unit 204, memory 206, persistent storage 208, communications unit 210, input / output (I / O) unit 212, and display 214.

[0041] Processor unit 204 serves to execute instructions for software applications and programs that may be loaded into memory 206. Processor unit 204 may be a collection of one or more hardware CPU devices, or may be a multi-processor core, depending on the particular implementation.

[0042] Memory 206 and persistent storage 208 are examples of storage devices 216. A computer-readable storage device is any hardware capable of storing information, such as but not limited to data, computer-readable program code in functional form, and / or other suitable information, either temporarily or permanently. In addition, a computer-readable storage device does not include a propagation medium. In these examples, memory 206 may be, for example, a random access memory or any other suitable volatile or non-volatile storage device. Persistent storage 208 may take various forms, depending on the specific implementation. For example, persistent storage 208 may include one or more devices. For example, persistent storage 208 may be a hard drive, a flash memory, a rewritable optical disk, a rewritable tape, or some combination thereof. The medium used by persistent storage 208 may be removable. For example, a removable hard drive may be used for persistent storage 208.

[0043] In this example, persistent storage 208 stores offload manager 218. However, it should be noted that even though offload manager 218 is shown as residing in persistent storage 208, in alternative illustrative embodiments, offload manager 218 may be a separate component of data processing system 200. For example, offload manager 218 may be a hardware component coupled to communications fabric 202, or a combination of hardware and software components. In another alternative illustrative embodiment, a first portion of offload manager 218 may be located on data processing system 200, and a second portion of offload manager 218 may be located on a second data processing system, such as a second data processing system. Figure 1 In yet another alternative illustrative embodiment, the uninstall manager 218 may be located in the target DBMS server, instead of or in addition to being located in the target DBMS server.

[0044] The offload manager 218 controls the offloading of long-running analytical query workloads from a source DBMS (e.g. Figure 1The process of offloading a workload from a source DBMS server 104 in the target DBMS to a target DBMS (e.g., target DBMS server 106). The offload manager 218 determines the savings in CPU workload cost by running the workload on the target DBMS (rather than the source DBMS), and determines the savings in CPU cost corresponding to loading data into tables referenced by the workload on the target DBMS. The offload manager 218 also determines which loaded tables on the target DBMS can be determined, thereby saving memory resources of the target DBMS and improving the workload performance of the target DBMS.

[0045] In this example, the persistent storage 208 also stores a source DBMS 220, a total CPU cost 222, a workload cost savings 224, real-time statistics 226, a target DBMS 228, and a load strategy recommendation 230. The source DBMS 220 represents an identifier and a network address of the source DBMS. The unload manager 218 uses the identifier and the network address to connect to and obtain information corresponding to the source DBMS. The information may include, for example, a workload 232. The workload 232 represents a set of one or more workloads running on the source DBMS. The workload 232 may be, for example, an analytical query that requires increased CPU usage on the source DBMS. Table 234 represents a set of tables referenced by the source DBMS to execute the workload 232. Size 236 represents the size of each corresponding table in the set of tables referenced by the workload 232. Change rate 238 represents the modification frequency of each corresponding table in the set of tables referenced by the workload 232.

[0046] Offload manager 218 calculates total CPU cost 222 corresponding to workload 232 using size 236 and rate of change 238. Total CPU cost 222 represents the total CPU usage cost for loading table 234 onto the target DBMS to execute workload 232 on the target DBMS. Workload cost savings 224 represents the reduction in CPU usage by offloading workload 232 from the source DBMS to the target DBMS.

[0047] Real-time statistics 226 represent current real-time information corresponding to table 234, such as size, change rate, errors, etc. Target DBMS 228 represents the identifier and network address of the target DBMS. Unload manager 218 uses the identifier and network address to connect and obtain information corresponding to the target DBMS.

[0048] The table access threshold 240 represents a predefined minimum number of references or accesses to a table loaded on the target DBMS. The unload manager 218 identifies corresponding tables with access times less than or equal to the table access threshold 240 as table removal candidates 242. The table removal candidates 242 represent a set of one or more tables that the unload manager 218 can remove from the target DBMS to increase memory, thereby improving the workload performance of the target DBMS.

[0049] The load strategy suggestion 230 represents a table load plan that the unload manager 218 can use to load data onto a table of the target DBMS. The load strategy suggestion 230 can be, for example, an UNLOAD-based refresh strategy and / or an incremental update strategy. The UNLOAD-based refresh strategy updates table partitions and entire tables referenced by the workload on the target DBMS at a specific point in time. The incremental update strategy continuously updates data on tables referenced by the workload on the target DBMS.

[0050] In this example, the communication unit 210 communicates with the user via a network (eg, Figure 1 The network 102 in the example embodiment provides communications with other computers, data processing systems and devices. Communications unit 210 may provide communications by using physical and wireless communications links. Physical communications links may utilize, for example, wires, cables, universal serial buses, or any other physical technology to establish a physical communications link for data processing system 200. Wireless communications links may utilize, for example, short wave, high frequency, ultra high frequency, microwave, wireless fidelity (Wi-Fi), technology, Global System for Mobile Communications (GSM), Code Division Multiple Access (CDMA), second generation (2G), third generation (3G), fourth generation (4G), 4G Long Term Evolution (LTE), LTE Advanced, or any other wireless communication technology or standard to establish a wireless communication link for the data processing system 200.

[0051] Input / output unit 212 allows for input and output of data with other devices that may be connected to data processing system 200. For example, input / output unit 212 may provide a connection for user input via a keypad, keyboard, mouse, and / or other suitable input devices. Display 214 provides a mechanism for displaying information to a user and may include touch screen functionality to allow a user to make on-screen selections through, for example, a user interface or to enter data.

[0052] Instructions for the operating system, applications, and / or programs may be located in storage devices 216, which communicate with processor unit 204 via communications fabric 202. In this illustrative example, these instructions are in functional form on persistent storage 208. These instructions may be loaded into memory 206 to be executed by processor unit 204. The processes of the different embodiments may be performed by processor unit 204 using computer-implemented instructions, which may be located in a memory such as memory 206. These program instructions are referred to as program code, computer usable program code, or computer readable program code, which may be read and executed by a processor in processor unit 204. In different embodiments, the program instructions may be embodied on different physical computer-readable storage devices, such as memory 206 or persistent storage 208.

[0053] Program code 244 is located in functional form on computer readable medium 246, which is selectively removable and can be loaded or transferred to data processing system 200 to be executed by processor unit 204. Program code 244 and computer readable medium 246 constitute computer program product 248. In one example, computer readable medium 246 can be computer readable storage medium 250 or computer readable signal medium 252. Computer readable storage medium 250 can include, for example, an optical or magnetic disk inserted or placed in a drive or other device as part of persistent storage 208 to be transferred to a storage device as part of persistent storage 208 (e.g., a hard drive). Computer readable storage medium 250 can also take the form of persistent storage, such as a hard drive, thumb drive, or flash memory connected to data processing system 200. In some cases, computer readable storage medium 250 may not be removable from data processing system 200.

[0054] Alternatively, program code 244 may be transmitted to data processing system 200 using computer readable signal media 252. Computer readable signal media 252 may be, for example, a propagated data signal containing program code 244. For example, computer readable signal media 252 may be an electromagnetic signal, an optical signal, and / or any other suitable type of signal. These signals may be sent via a communication link such as a wireless communication link, a fiber optic cable, a coaxial cable, an electric wire, and / or any other suitable type of communication link. In other words, in the illustrative example, the communication link and / or connection may be physical or wireless. Computer readable media may also take the form of non-tangible media, such as a communication link or wireless transmission containing program code.

[0055] In some illustrative embodiments, program code 244 may be downloaded from another device or data processing system to persistent storage 208 via computer readable signal media 252 over a network for use in data processing system 200. For example, program code stored in a computer readable storage medium in a data processing system may be downloaded from the data processing system to data processing system 200 over a network. The data processing system providing program code 244 may be a server computer, a client computer, or other device capable of storing and transmitting program code 244.

[0056] The different components illustrated for data processing system 200 are not meant to provide architectural limitations to the manner in which different embodiments may be implemented. The different illustrative embodiments may be implemented in a data processing system that includes components in addition to or in place of those illustrated for data processing system 200. Figure 2 Other components shown may be different from the illustrative examples shown. Any hardware device or system capable of executing program code may be used to implement the different embodiments. As an example, data processing system 200 may include organic components integrated with inorganic components and / or may be composed entirely of organic components other than humans. For example, a storage device may include an organic semiconductor.

[0057] As another example, a computer-readable storage device in data processing system 200 is any hardware device that can store data. Memory 206, persistent storage 208, and computer-readable storage media 250 are examples of physical storage devices in a tangible form.

[0058] In another example, a bus system can be used to implement the communication structure 202, and can be composed of one or more buses, such as a system bus or an input / output bus. Of course, the bus system can be implemented using any appropriate type of architecture that provides data transmission between different components or devices attached to the bus system. In addition, the communication unit may include one or more devices for sending and receiving data, such as a modem or a network adapter. In addition, the memory may be, for example, a memory 206 or a cache such as found in an interface and a memory controller hub, which may be present in the communication structure 202.

[0059] Accelerators are used to offload long-running analytical queries that are to be accelerated. To accelerate a query, the data of the tables referenced by the query needs to be loaded into the accelerator. When multiple such queries are offloaded to the accelerator, memory consumption increases and query performance degrades.

[0060] Resource limitations (e.g., CPU, memory, and persistent storage) require accelerators to highly optimize resource usage to enhance accelerated query performance. In many cases, unrestricted offloading of table data to system hardware used by accelerators consumes more system resources than necessary. As a result, intelligent table loading and unloading of table data will improve workload or query performance.

[0061] Currently, the Workload Tuner provides recommendations about which tables and data will yield improved performance when offloaded to an accelerator. However, the Workload Tuner provides recommendations based only on query statistics. In other words, the Workload Tuner depends on how the query workload under consideration was derived. If queries that are part of the workload come from the statement cache, then the actual cost of running the query on the DBMS is known.

[0062] However, when Workload Tuner presents query optimization insights by recommending offloading tables to an accelerator, the actual CPU usage cost of loading data into the accelerator is not considered. Over time, as data for multiple tables is loaded into the accelerator, the memory resources used by the accelerator are also depleted, degrading performance.

[0063] The illustrative embodiments load data into a table of a target DBMS (i.e., an accelerator) by considering the CPU usage cost for loading data into each table referenced by the workload to be offloaded from the source DBMS. Thus, the illustrative embodiments calculate a "cost / benefit balance" for each table by comparing the CPU cost savings for offloading the workload / query that references each table with the CPU cost incurred by loading the data into each table on the target DBMS. In addition, the illustrative embodiments provide recommendations to remove data (i.e., tables) that are not used or least used by the target DBMS. Thus, the illustrative embodiments provide system resource optimization by optimizing the memory and CPU resources used by the target DBMS to increase the performance of the accelerated analytical workload / query.

[0064] The illustrative embodiments provide a computer-implemented method for optimizing a data loading strategy from a source DBMS to a target DBMS, the target DBMS containing a subset of a source DBMS data set. The target DBMS uses the data set to run one or more workloads (e.g., analytical queries). The illustrative embodiments collect information about the workloads that hit the source DBMS, the costs corresponding to running the workloads on the source DBMS, the tables referenced by the workloads, and the eligibility of the tables referenced by the workloads to be unloaded to the target DBMS. The illustrative embodiments obtain the size of the tables that are suitable for being unloaded to the target DBMS. In addition, the illustrative embodiments evaluate the change rate of the tables that are eligible to be unloaded to the target DBMS. The illustrative embodiments use the size and change rate of the tables that are eligible for unloading to the target DBMS to determine the total CPU cost corresponding to loading the data set to the target DBMS, and the cost savings corresponding to executing one or more workloads on the target DBMS based on a set of one or more loading strategies. The workload cost savings determined for a particular table include the expected future workload corresponding to the particular table. The illustrative embodiments compare the total CPU cost with the workload cost savings of each loading strategy in the set of loading strategies. The illustrative embodiments select an optimal load strategy from the set of load strategies based on comparing the total cost to the workload cost savings of each load strategy. The illustrative embodiments automatically load the data set into the target DBMS according to the selected optimal load strategy to accelerate the execution of one or more workloads.

[0065] In addition, the illustrative embodiment provides a computer-implemented method for optimizing a data removal strategy corresponding to a target DBMS, thereby accelerating workload performance. The illustrative embodiment collects information about a set of workloads running on the target DBMS and the cost associated with running each corresponding workload on the target DBMS. The illustrative embodiment obtains the size of each table in a set of tables in the target DBMS and the reference count associated with each table in the set of tables based on the use of the workload set. The illustrative embodiment evaluates the frequency of table accesses by the workload set for each table in the set of tables in the target DBMS. The illustrative embodiment maps the frequency of table accesses associated with each table referenced by the workload set to the memory and CPU cost savings associated with removing tables from the target DBMS with minimal or no access frequency to identify table removal candidates. The illustrative embodiment automatically removes the identified tables and removal candidates from the target DBMS based on the mapping.

[0066] The accelerator (i.e., the target DBMS) is particularly suitable for quickly executing a specific type of database workload (e.g., analytical queries). The illustrative embodiment copies all or part of the data from the source database to the target database to increase the workload (e.g., query) performance. The illustrative embodiment dynamically determines whether a query received by the source database is executed in the source database or offloaded to the target database. Whether the illustrative embodiment offloads the query to the target database depends on the type of workload (e.g., analytical query workload). When the illustrative embodiment determines or predicts that the workload will be executed faster on the target database rather than the original data table in the source database, the illustrative embodiment distributes the workload to the target database. Therefore, the illustrative embodiment utilizes the target database to accelerate workload / query execution.

[0067] Previous methods of loading and unloading data into a target database have been manual processes, which were left to the discretion of the database administrator. Although some best practices were noted, previous methods did not support the database administrator in making decisions regarding data loading and unloading strategies.

[0068] The organization of data in the source DBMS can vary between tables. Some tables are unpartitioned tables and some are partitioned by range or growth. The rate of change of data in these tables also varies greatly depending on the purpose of the table. Therefore, a process is needed to recommend and implement an optimal loading strategy.

[0069] The illustrative embodiments ingest information about workload cost savings and the CPU costs corresponding to the initial loading of data into referenced tables by running the workload on the target DBMS (rather than the source DBMS). The illustrative embodiments then determine the rate of change of data in these tables and calculate the CPU costs associated with different load strategies that can be used for a given table. Using this ingested and calculated information, the illustrative embodiments present the actual cost to the user (e.g., a database administrator), which includes the workload performance savings on the target DBMS and the costs associated with loading data into the tables of the target DBMS, as well as the cost of the recommended / selected load strategy, which includes the rationale for minimizing all related data load overhead. The illustrative embodiments also automatically perform the load operation on the target DBMS using the recommended / selected load strategy.

[0070] The illustrative embodiments collect data regarding workload cost savings by running the workload on the target DBMS, for example when the parameter is set to "yes". Given a particular workload, the illustrative embodiments determine the workload cost of running the workload directly on the source DBMS, and in response to the illustrative embodiments determining that running the workload on the target DBMS will reduce the workload cost, recommend running the workload on the target DBMS. Thus, the illustrative embodiments base the recommendation on workload cost savings. The illustrative embodiments obtain information from the source DBMS catalog tables regarding the size of each table referenced by the workload and eligibility to be loaded on the target DBMS.

[0071] The illustrative embodiments may perform two types of data load / refresh strategies to synchronize data on the target DBMS, namely, an UNLOAD-based refresh strategy and an incremental update strategy. The UNLOAD-based refresh strategy updates table partitions and entire tables at a specific point in time. The UNLOAD-based refresh function takes a snapshot of the data on the source DBMS and transfers the data to the target DBMS. The incremental update strategy continuously updates the data on the target DBMS by applying the changes recorded in the source DBMS log. The total CPU cost associated with loading data from the source DBMS to the table of the target DBMS varies depending on the selected load strategy.

[0072] The CPU cost for data synchronization in an incremental update or UNLOAD-based refresh depends on the total size of the data to be transferred to the target DBMS. For incremental updates, the total size of the data to be transferred is the net amount of byte changes that have been applied to the table in the source DBMS. For UNLOAD-based refresh, the total size of the data to be transferred depends on the number and size of the partitions that have been changed, because only entire partitions can be refreshed. Because the incremental update function reads the log of the entire source DBMS subsystem, the estimate of the CPU cost must be based on the overall activity recorded in the log of that subsystem. It is necessary to distinguish between in-scope and out-of-scope changes. In-scope changes represent log activity related to incrementally updating tables on the target DBMS, while out-of-scope changes represent all other activity. The CPU cost of data loading using UNLOAD-based refresh is a function of the number of changed rows and the average row length of the subject table. For incremental updates, when a scan of the source DBMS is required, the CPU cost of data refresh is a function of the average row length of the subject table, the number of inserts, updates, and removes, and the time associated with filtering out-of-scope changes.

[0073] Currently, users calculate and manually perform steps to transfer each reference table under consideration, which is time consuming and error prone, thus making the current process suboptimal. This is part of the problem that the illustrative embodiments consider and solve by including logic that enables automatic calculation of multiple factors and integrates the results to generate specific cost data and recommendations. In addition, the illustrative embodiments have the ability to automatically take action based on the generated recommendations.

[0074] Reference now Figure 3A-3B , shows a flow chart showing a process for initially loading table data according to an illustrative embodiment. In this process, cost savings for workloads / queries can be obtained by running the workloads / queries on the target DBMS, but the table data required to run the workloads / queries on the target DBMS is not yet available. Figure 3A-3B The process shown can be implemented in a computer, for example, Figure 1 The uninstall manager server 108 or Figure 2 A data processing system 200 in FIG.

[0075] The process begins when the computer collects information from a source DBMS about the workload running on the source DBMS and the costs associated with running the workload on the source DBMS (step 302). This is performed by generating an execution plan for the workload against the source DBMS. The source DBMS may be, for example, Figure 1 The computer then uses the collected information to identify a set of tables referenced by the workload (step 304).

[0076] The computer then selects a table from the set of tables (step 306). The computer determines whether the selected table is located in the target DBMS (step 308). The target DBMS may be, for example, Figure 1 4. If the computer determines that the selected table is in the target DBMS, the output of step 308 is yes, and the process proceeds to step 410 in Figure 4. If the computer determines that the selected table is not in the target DBMS, the output of step 308 is no, and then the computer determines the amount of data initially loaded into the selected table by referring to the catalog table of the source DBMS (step 310).

[0077] In addition, the computer calculates a CPU cost value for loading the amount of data into the selected table (step 312). The computer also increments the CPU cost counter by the calculated CPU cost value (step 314). Thereafter, the computer determines whether there is another table in the set of tables (step 316). If the computer determines that there is indeed another table in the set of tables, the output of step 316 is yes, and the process returns to step 306, in which the computer selects another table from the set of tables. If the computer determines that there is no other table in the set of tables, the output of step 316 is no, and the computer determines the total CPU cost for loading the data onto the target DBMS based on the CPU cost counter (step 318).

[0078] The computer compares the total CPU cost of loading the data onto the target DBMS with the workload cost saved by running the workload on the target DBMS (step 320). In addition, the computer takes into account future workload cost savings by loading the table data into the target DBMS. In response to the computer comparing the total CPU cost with the workload cost savings, the computer determines whether the total CPU cost is greater than the workload cost savings (step 322). If the computer determines that the total CPU cost is greater than the workload cost savings, it returns to step 322, and the process terminates thereafter. If the computer determines that the total CPU cost is less than the workload cost savings (the output of step 322 is no), the computer generates a loading strategy recommendation to load the data into the table of the target DBMS (step 324). The process of generating the loading strategy recommendation will be described below. Figure 7 The description is described in detail in the description.

[0079] In addition, the computer determines whether any loaded tables on the target DBMS can be removed (step 326). If the computer determines that the tables already loaded on the target DBMS can be removed, then the output of step 326 is executed, and the process proceeds to Figure 6 If the computer determines that the table already loaded on the target DBMS cannot be removed, the output of step 326 is no, then the computer will collect real-time statistics corresponding to the table in the loading strategy recommendation to determine the update mode of the table (step 328).

[0080] Thereafter, the computer determines whether the tables in the loading strategy recommendation are eligible for incremental updates based on the collected real-time statistical data (step 330). If the computer determines that the tables in the loading strategy recommendation are not eligible for incremental updates based on the collected real-time statistical data, the output of step 330 is no, and the computer performs an unload-based refresh on the tables in the loading strategy recommendation that are not eligible for incremental updates on the target DBMS (step 332). If the computer determines that the tables in the loading strategy recommendation are eligible for incremental updates based on the collected real-time statistical data, the output of step 330 is yes, and the computer enables incremental updates on the tables in the loading strategy recommendation that are eligible for incremental updates on the target DBMS (step 334). In addition, the computer sends the loading strategy recommendation and the reasons to the user for review (step 336). Thereafter, the process terminates.

[0081] Reference now Figure 4A-4B , a flowchart showing a process for refreshing data of a partition table according to an illustrative embodiment is shown. In this process, workload cost savings that can be obtained by running the workload on the target DBMS can be obtained, and the table data required to run the workload on the target DBMS needs to be refreshed. Figure 4A-4B The process shown can be implemented in a computer, for example, Figure 1 The uninstall manager server 108 or Figure 2 A data processing system 200 in FIG.

[0082] The process begins when the computer collects information from a source DBMS about a workload running on the source DBMS and the costs associated with running the workload on the source DBMS (step 402). The computer uses the collected information to identify a set of tables referenced by the workload (step 404). The computer selects a table from the set of tables (step 406).

[0083] The computer determines whether the selected table is located in the target DBMS (step 408). If the computer determines that the selected table is not in the target DBMS, the output of step 408 is no, and the process returns to Figure 3A If the computer determines that the selected table is located in the target DBMS, the output of step 408 is yes, then the computer determines whether incremental updates are enabled on the selected table (step 410). If the computer determines that incremental updates are enabled on the selected table, the output of step 410 is yes, then the process proceeds to Figure 5A If the computer determines that incremental updates are not enabled on the selected table, the output of step 410 is no, and the computer determines whether the selected table is partitioned (step 412).

[0084] If the computer determines that the selected table is partitioned, the output of step 412 is yes, then the computer calculates the CPU cost value for partition reload of the selected table (step 414). The computer can use, for example, the SYSPROC.ACCEL_LOAD_TABLES stored procedure to identify which partitions have changed since the last load / refresh when the table data has been preloaded into the target DBMS. Thereafter, the process proceeds to step 418. If the computer determines that the selected table is not partitioned, the output of step 412 is no, then the computer calculates the CPU cost value for completely reloading the selected table (step 416).

[0085] Thereafter, the computer increases the CPU cost counter by the calculated CPU cost value (step 418). Additionally, the computer determines whether another table exists in the set of tables (step 420). If the computer determines that another table does exist in the set of tables, the output of step 420 is yes, and the process returns to step 406, in which the computer selects another table in the set of tables. If the computer determines that another table does not exist in the set of tables, the output of step 420 is no, and the computer determines the total CPU cost for reloading the data to the table of the target DBMS based on the CPU cost counter (step 422).

[0086] The computer compares the total CPU cost of reloading the data onto the table of the target DBMS with the workload cost savings of running the workload on the target DBMS (step 424). In response to the computer comparing the total CPU cost with the workload cost savings, the computer determines whether the total CPU cost is greater than the workload cost savings (step 426). If the computer determines that the total CPU cost is greater than the workload cost savings, the output of step 426 is yes, and the process terminates thereafter. If the computer determines that the total CPU cost is less than the workload cost savings, the output of step 426 is no, and the computer generates a load strategy recommendation for reloading the data onto the table of the target DBMS (step 428).

[0087] In addition, the computer automatically executes the load strategy recommendation (step 430). In addition, the computer determines whether any loaded tables on the target DBMS can be removed (step 432). If the computer determines that the tables already loaded on the target DBMS can be removed, the output of step 432 is yes, and the process proceeds to Figure 6 Step 602. If the computer determines that the table already loaded on the target DBMS cannot be removed, the output of step 432 is no, and the process terminates thereafter.

[0088] Reference now Figure 5A-5B, a flow chart of a process for evaluating a table with incremental updates of table data enabled according to an illustrative embodiment is shown. In this process, workload cost savings by running the workload on a target DBMS can be obtained, and now since incremental updates of the table are enabled, the table data required to run the workload on the target DBMS can also be obtained. Figure 5A-5B The process shown can be implemented in a computer, for example, Figure 1 The uninstall manager server 108 or Figure 2 A data processing system 200 in FIG.

[0089] The process begins when the computer collects information from a source DBMS about a workload running on the source DBMS and the costs associated with running the workload on the source DBMS (step 502). The computer uses the collected information to identify a set of tables referenced by the workload (step 504). The computer selects a table from the set of tables (step 506).

[0090] The computer determines whether the selected table is located in the target DBMS (step 508). If the computer determines that the selected table is not in the target DBMS, the output of step 508 is no, and the process returns to Figure 3A If the computer determines that the selected table is located in the target DBMS, the output of step 508 is yes, and the computer determines whether incremental updates are enabled on the selected table (step 510).

[0091] If the computer determines that incremental updates are not enabled on the selected table, the output of step 510 is no, and the process returns to Figure 4A If the computer determines that incremental updates are enabled on the selected table, the output of step 510 is yes, and the computer collects real-time statistics corresponding to the selected table (step 512). Thereafter, the computer determines whether the selected table is still eligible for incremental updates based on the collected real-time statistics (step 514).

[0092] If the computer determines that the selected table is still not eligible for incremental update based on the collected real-time statistical data, the output of step 514 is no, and the computer stops the incremental update of the selected table (step 516). Thereafter, the process proceeds to step 520. If the computer determines that the selected table is still eligible for incremental update based on the collected real-time statistical data, the output of step 514 is yes, and the computer continues to perform incremental update of the selected table (step 518).

[0093] The computer calculates the incrementally updated current CPU cost value for the table selected so far (step 520). In addition, the computer increments the CPU cost counter by the calculated current CPU cost value (step 522). In addition, the computer determines whether there is another table in the set of tables (step 524).

[0094] If the computer determines that another table does exist in the set of tables, the output of step 524 is yes, and the process returns to step 506, in which the computer selects another table in the set of tables. If the computer determines that another table does not exist in the set of tables, the output of step 524 is no, and the computer determines the total CPU cost of the incremental update of the table on the target DBMS based on the CPU cost counter (step 526).

[0095] The computer compares the total CPU cost of the incremental update of the table on the target DBMS with the workload cost savings of running the workload on the target DBMS (step 528). In response to the computer comparing the total CPU cost with the workload cost savings, the computer determines whether the total CPU cost is greater than the workload cost savings (step 530). If the computer determines that the total CPU cost is greater than the workload cost savings, the output of step 530 is yes, then the computer generates a recommendation to stop the incremental update of the table on the target DBMS (step 532). Thereafter, the process proceeds to step 536. If the computer determines that the total CPU cost is less than the workload cost savings, the output of step 530 is no, then the computer generates a recommendation to continue incrementally updating the table on the target DBMS (step 534). In addition, the computer automatically executes the recommendation (step 536). Thereafter, the process terminates.

[0096] Reference now Figure 6 , a flow chart of a process for removing unreferenced or minimally referenced tables according to an illustrative embodiment is shown. In this process, information about table data that needs to be loaded into a target DBMS to achieve workload acceleration can be obtained, and it is necessary to check whether there are candidate tables in the target DBMS that can be removed from the target DBMS to maintain system load. Figure 6 The process shown can be implemented in a computer, for example, Figure 1 The uninstall manager server 108 or Figure 2 A data processing system 200 in FIG.

[0097] The process begins when the computer evaluates the tables loaded by the computer on the target DBMS for a plurality of workload hits corresponding to each corresponding table (step 602). In response to the computer evaluating the tables, the computer determines whether there are tables whose corresponding workload hit counts are less than a workload hit count threshold (step 604). If the computer determines that no table has a workload hit count less than the workload hit count threshold, the output of step 604 is no, and the process terminates thereafter. If the computer determines that one or more tables have a corresponding workload hit count less than the workload hit count threshold, the output of step 604 is yes, and the computer generates a list of tables whose workload hit counts are less than the workload hit count threshold (step 606).

[0098] The computer selects a table from the table list (step 608). The computer determines whether the selected table can be removed from the target DBMS (step 610). If the computer determines that the selected table cannot be removed from the target DBMS, the output of step 610 is no, and the computer retains the selected table on the target DBMS (step 612). Thereafter, the process proceeds to step 618. If the computer determines that the selected table can be removed from the target DBMS, the output of step 610 is yes, and the computer adds the selected table to the table removal list (step 614). In addition, the computer calculates the savings in memory and CPU costs by removing the selected table from the target DBMS (step 616).

[0099] The computer determines whether there is another table in the table list (step 618). If the computer determines that there is another table in the table list, the output of step 618 is yes, and the process returns to step 608, in which the computer selects another table in the table list. If the computer determines that there is no another table in the table list, the output of step 618 is no, and the computer calculates the total memory and CPU cost savings by removing the tables in the table removal list from the target DBMS (step 620).

[0100] In addition, the computer generates a table removal suggestion for the target DBMS with total memory and CPU cost savings (step 622). In addition, the computer automatically executes the table removal suggestion (step 624). Thereafter, the process terminates.

[0101] Reference now Figure 7 , a flowchart of a process for selecting an optimal loading strategy is shown in accordance with an illustrative embodiment. Figure 7 The process shown in can be implemented in a computer, for example, Figure 1 The uninstall manager server 108 or Figure 2 A data processing system 200 in FIG.

[0102] The process begins when the computer collects information about the workload hitting the source DBMS, the cost corresponding to running the workload on the source DBMS, the tables referenced by the workload, and the eligibility of the tables referenced by the workload to be offloaded to the target DBMS (step 702). The computer also obtains the size of the tables that are eligible to be offloaded to the target DBMS (step 704). In addition, the computer evaluates the rate of change of the tables that are eligible to be offloaded to the target DBMS (step 706).

[0103] Using the size and change rate of the table eligible for offloading to the target DBMS, the computer determines a total CPU cost corresponding to loading the data set into the target DBMS and a workload cost savings corresponding to executing one or more workloads on the target DBMS based on a set of one or more loading strategies (step 708). The computer compares the total CPU cost to the workload cost savings of each loading strategy in the set of loading strategies (step 710).

[0104] Based on comparing the total CPU cost with the workload cost savings of each loading strategy, the computer selects the optimal loading strategy from the set of loading strategies (step 712). Then, the computer automatically loads the data set into the target DBMS according to the selected optimal loading strategy to accelerate the execution of one or more workloads (step 714). Thereafter, the process terminates.

[0105] Reference now Figure 8 , a flowchart showing a process for mapping table access frequencies to memory and CPU cost savings in accordance with an illustrative embodiment. Figure 8 The process shown in can be implemented in a computer, for example, Figure 1 The uninstall manager server 108 or Figure 2 A data processing system 200 in FIG.

[0106] The process begins when the computer collects information about a set of workloads running on a target DBMS and the cost associated with running each corresponding workload on the target DBMS (step 802). Additionally, the computer obtains the size of a set of tables in the target DBMS and the reference count associated with each table in the set based on the use of the set of workloads (step 804). Furthermore, the computer evaluates the frequency of table accesses by the workload set for each table in the set of tables in the target DBMS (step 806).

[0107] Thereafter, the computer maps the table access frequencies associated with each table referenced by the workload set to the memory and CPU cost savings associated with removing tables that are least or not frequently accessed from the target DBMS to identify table removal candidates (step 808). The computer then automatically removes the identified tables from the target DBMS based on the mapping to reduce memory usage on the target DBMS and increase workload performance (step 810). Thereafter, the process terminates.

[0108] Therefore, the illustrative embodiments of the present invention provide a computer-implemented method, computer system, and computer program product for automatically optimizing computer memory and central processing unit resources used by a target database management system to increase workload performance. Descriptions of various embodiments of the present invention have been given for illustrative purposes, but these descriptions are not intended to be exhaustive or limited to the disclosed embodiments. Many modifications and variations will be apparent to those of ordinary skill in the art without departing from the scope of the described embodiments. The terms used herein are selected to best explain the principles of the embodiments, practical applications of technology found in the market or technical improvements, or to enable other persons of ordinary skill in the art to understand the embodiments disclosed herein.

Claims

1. A computer-implemented method for loading a data set from a source database management system into a target database management system to improve workload performance and reduce resource usage, the computer-implemented method comprising: Collecting real-time statistical data including table size and change rate from the source database management system by a computer; determining, by a computer, a total central processor unit cost corresponding to loading the data set into the target database management system and a workload cost savings corresponding to executing one or more workloads on the target database management system based on a set of one or more load strategies, wherein the set of load strategies is selected from the group consisting of an offload-based refresh strategy and an incremental update strategy; comparing, by the computer, the total central processor unit cost to the workload cost savings of each of the load strategies in the set of load strategies; selecting, by the computer, an optimal loading strategy from the set of loading strategies based on comparing the total CPU cost to the workload cost savings of each loading strategy; and The computer automatically loads the data set from the source database management system to the target database management system according to the selected optimal loading strategy to accelerate the execution of the one or more workloads, thereby improving workload performance and reducing central processing unit costs.

2. The computer-implemented method of claim 1 , further comprising: collecting, by the computer, information about the workload hitting the source database management system, a cost corresponding to running the workload on the source database management system, and eligibility of the table referenced by the workload to be offloaded to the target database management system; Acquiring, by the computer, the size of each of the tables to be unloaded from the source database management system to the target database management system; as well as A change rate of each of the tables to be offloaded to the target database management system is evaluated by the computer.

3. The computer-implemented method of any one of the preceding claims, further comprising: collecting, by the computer, information related to a set of workloads running on the target database management system and costs associated with running each workload on the target database management system; The computer obtains, based on the usage of the set of workloads, a size of a set of tables in the target database management system and a reference count associated with each table in the set of tables; evaluating, by the computer, table access frequency by the set of workloads for each table in the set of tables in the target database management system; mapping, by the computer, the table access frequencies associated with each table referenced by the set of workloads to memory storage and central processing unit cost savings associated with removing tables with minimal or no frequency of access from the target database management system to identify table removal candidates; as well as The identified table removal candidates are automatically removed, by the computer, from the target database management system based on the mapping to reduce memory usage and improve workload performance on the target database management system.

4. The computer-implemented method of claim 1 , further comprising: collecting, by the computer, from a source database management system information about a workload running on the source database management system and a cost associated with running the workload on the source database management system; identifying, by the computer, a set of tables referenced by the workload using the collected information; selecting, by the computer, a table from the set of tables; as well as A determination is made by the computer whether the selected table is located in the target database management system.

5. The computer-implemented method of claim 4, further comprising: In response to the computer determining that the selected table is not in the target database management system, the computer determines, by reference to a catalog table of the source database management system, an amount of data to be loaded into the selected table; calculating, by the computer, a CPU cost value for loading the amount of data into the selected table; as well as A CPU cost counter is incremented by the computer by the calculated CPU cost value.

6. The computer-implemented method of claim 5, further comprising: determining, by the computer based on the CPU cost counter, a total CPU cost for loading data into the set of tables on the target database management system; comparing the total central processor unit cost of loading data into the set of tables on the target database management system by the computer to a workload cost savings of running the workload on the target database management system; In response to the computer comparing the total CPU cost to the workload cost savings, determining by the computer whether the total CPU cost is greater than the workload cost savings; as well as In response to the computer determining that the total CPU cost is less than the workload cost savings, a load strategy recommendation is generated by the computer to load the data onto the set of tables in the target database management system.

7. The computer-implemented method of claim 4, further comprising: In response to the computer determining that the selected table is located in the target database management system, determining by the computer whether incremental updates are enabled on the selected table; In response to the computer determining that incremental updates are not enabled on the selected table, determining, by the computer, whether the selected table is partitioned; responsive to the computer determining that the selected table is partitioned, calculating, by the computer, a central processor unit cost value for reloading a partition of the selected table; as well as A CPU cost counter is incremented by the computer by reloading the calculated CPU cost value for the partition of the selected table.

8. The computer-implemented method of claim 7, further comprising: In response to the computer determining that the selected table is not partitioned, calculating, by the computer, a central processor unit cost value for completely reloading the selected table; as well as The CPU cost counter is incremented by the computer by the calculated CPU cost value to completely reload the selected table.

9. The computer-implemented method of claim 7 or 8, further comprising: In response to the computer determining that incremental updates are enabled on the selected table, collecting, by the computer, real-time statistics corresponding to the selected table; Determining, by the computer, whether the selected table is still eligible for incremental update based on the collected real-time statistical data corresponding to the selected table; In response to the computer determining, based on the collected real-time statistical data, that the selected table is still not eligible for incremental update, stopping, by the computer, incremental update of the selected table; calculating, by said computer, a current CPU cost value for said incremental update of said selected table to date; as well as The CPU cost counter is increased by the computer by the calculated current CPU cost value to perform the incremental update of the table selected so far.

10. The computer-implemented method of claim 9, further comprising: In response to the computer determining that the selected table is still eligible for incremental update based on the collected real-time statistical data, continuing to perform incremental update of the selected table by the computer; calculating, by said computer, said current CPU cost value for said incremental update of said selected table to date; as well as The CPU cost counter is increased by the computer by the calculated current CPU cost value to perform the incremental update of the table selected so far.

11. The computer-implemented method of claim 1 , further comprising: evaluating, by the computer, tables loaded on the target database management system to obtain a plurality of workload hit counts corresponding to each corresponding table; In response to the computer evaluating the tables, determining by the computer whether a workload hit count for any table is less than a threshold workload hit count; as well as In response to the computer determining that one or more tables have corresponding workload hit counts less than the threshold workload hit count, a list of tables having corresponding workload hit counts less than the threshold workload hit count is generated by the computer.

12. The computer-implemented method of claim 11 , further comprising: calculating, by the computer, total memory and central processing unit cost savings by removing tables from the table removal list from the target database management system; generating, by the computer, table removal recommendations for the target database management system for the total memory and central processing unit cost savings; as well as The table removal suggestion is automatically performed by the computer.

13. The computer-implemented method of any one of claims 1-2, 4-8, 10-12, wherein: The workload cost savings determined for a particular table include predicted future queries for the particular table.

14. A computer system for loading a data set from a source database management system into a target database management system to improve workload performance and reduce resource usage, the computer system comprising: Bus system; a storage device connected to the bus system, wherein the storage device stores program instructions; as well as a processor connected to the bus system, wherein the processor executes the program instructions to: Collecting real-time statistics including table size and change rate from the source database management system; determining, using the collected sizes and change rates of tables on the source database management system that are eligible for offloading to the target database management system, a total central processor unit cost corresponding to loading the data set into the target database management system and a workload cost savings corresponding to executing one or more workloads on the target database management system based on a set of one or more load strategies, wherein the set of load strategies is selected from the group consisting of an offload-based refresh strategy and an incremental update strategy; comparing the total central processor unit cost to the workload cost savings for each of the load strategies in the set of load strategies; selecting an optimal loading strategy from the set of loading strategies based on comparing the total CPU cost to the workload cost savings of each loading strategy; and According to the selected optimal loading strategy, the data set is automatically loaded from the source database management system to the target database management system to accelerate the execution of the one or more workloads, thereby improving workload performance and reducing central processing unit costs.

15. The computer system of claim 14, wherein: The processor also executes the program instructions to: collecting information about the workload hitting the source database management system, a cost corresponding to running the workload on the source database management system, and eligibility of the table referenced by the workload to be offloaded to the target database management system; obtaining the size of each of the tables to be unloaded from the source database management system to the target database management system; as well as A change rate for each of the tables to be offloaded to the target database management system is evaluated.

16. The computer system of claim 14 or 15, wherein: The processor also executes the program instructions to: collecting information related to a set of workloads running on the target database management system and costs associated with running each workload on the target database management system; Based on the usage of the set of workloads, obtaining the size of a set of tables in the target database management system and a reference count associated with each table in the set of tables; evaluating table access frequency by the set of workloads for each table in the set of tables in the target database management system; mapping the table access frequency associated with each table referenced by the set of workloads to memory storage and central processing unit cost savings associated with removing tables that are accessed minimally or infrequently from the target database management system to identify table removal candidates; as well as Automatically removes tables identified based on the mapping, removing candidates from the target database management system to reduce memory usage and improve workload performance on the target database management system.

17. The system of claim 14, wherein: The processor also executes the program instructions to: collecting information from a source database management system about a workload running on the source database management system and a cost associated with running the workload on the source database management system; identifying a set of tables referenced by the workload using the collected information; Select a table from the set of tables; as well as; A determination is made as to whether the selected table is located in the target database management system.

18. The system of claim 17, wherein: The processor also executes the program instructions to: In response to the computer determining that the selected table is not in the target database management system, determining an amount of data to be loaded into the selected table by reference to a catalog table of the source database management system; calculating a CPU cost value for loading the amount of data into the selected table; as well as The CPU cost counter is incremented by the calculated CPU cost value.

19. The system of claim 18, wherein: The processor also executes the program instructions to: determining a total CPU cost for loading data into the set of tables on the target database management system based on the CPU cost counter; comparing the total central processor unit cost of loading data into the set of tables on the target database management system to a workload cost savings of running the workload on the target database management system; in response to the computer comparing the total CPU cost to the workload cost savings, determining whether the total CPU cost is greater than the workload cost savings; as well as In response to the computer determining that the total central processor unit cost is less than the workload cost savings, a load strategy recommendation is generated to load the data onto the set of tables in the target database management system.

20. The system of claim 17, wherein: The processor also executes the program instructions to: The computer determines that the selected table is located in the target database management system, and the computer determines whether incremental updates are enabled on the selected table; In response to the computer determining that incremental updates are not enabled on the selected table, determining whether the selected table is partitioned; responsive to the computer determining that the selected table is partitioned, calculating a central processor unit cost value for reloading a partition of the selected table; as well as The calculated CPU unit cost value is reloaded for the partition of the selected table to increment a CPU unit cost counter.

21. The system of claim 20, wherein: The processor also executes the program instructions to: responsive to the computer determining that the selected table is not partitioned, calculating a central processor unit cost value for fully reloading the selected table; as well as The CPU cost counter is incremented by the calculated CPU cost value to completely reload the selected table.

22. The system of claim 20 or 21, wherein: The processor also executes the program instructions to: In response to the computer determining that incremental updates are enabled on the selected table, collecting real-time statistics corresponding to the selected table; determining, based on the collected real-time statistical data corresponding to the selected table, whether the selected table is still eligible for incremental update; In response to the computer determining that the selected table is still not eligible for incremental update based on the collected real-time statistical data, stopping the incremental update of the selected table; calculating a current CPU cost value for said incremental update of said selected table so far; as well as The CPU cost counter is increased by the calculated current CPU cost value to perform the incremental update of the table selected so far.

23. The system of claim 22, wherein: The processor also executes the program instructions to: In response to the computer determining that the selected table is still eligible for incremental update based on the collected real-time statistical data, continuing to perform incremental update of the selected table; calculating said current CPU cost value of said incremental update of said selected table so far; as well as The CPU cost counter is increased by the calculated current CPU cost value to perform the incremental update of the table selected so far.

24. The system of claim 14, wherein: The processor also executes the program instructions to: evaluating tables loaded on the target database management system to obtain a plurality of workload hit counts corresponding to each corresponding table; In response to the computer evaluating the tables, determining whether any table has a workload hit count less than a threshold workload hit count; as well as In response to the computer determining that one or more tables have corresponding workload hit counts less than the threshold workload hit count, a list of tables having corresponding workload hit counts less than the threshold workload hit count is generated.

25. The system of claim 24, wherein: The processor also executes the program instructions to: calculating total memory and central processing unit cost savings by removing tables in the table removal list from the target database management system; as well as generating table removal recommendations for the target database management system for the total memory and central processing unit cost savings; as well as Automatically execute the table removal suggestions.

26. The system of any one of claims 14-15, 17-21, 23-25, wherein: The workload cost savings determined for a particular table include predicted future queries for the particular table.

27. A computer program product for loading a data set from a source database management system into a target database management system to improve workload performance and reduce resource usage, the computer program product include: A computer-readable storage medium, which can be read by a processing circuit and stores instructions for execution by the processing circuit to perform the method as claimed in any one of claims 1 to 13.

28. A computer program stored on a computer readable medium and loadable into the internal memory of a digital computer, the computer program comprising software code portions for performing the method of any one of claims 1 to 13 when said program is run on a computer.

Images