Historical data migration method and system for load awareness and space optimization and medium

Abstract

The embodiment of the invention provides a historical data migration method and system for load awareness and space optimization and a medium, and the method comprises the steps that a starting program carries out log record initialization and loads configuration parameters, and the configuration parameters comprise database connection information, a load threshold value, a space threshold value and transaction submission interval information; periodically monitoring performance indexes of the source database, and evaluating current load condition information of the source database; analyzing the space occupation condition and the historical data proportion of each data table based on the current load condition information, and automatically identifying and selecting the data table needing to be migrated; analyzing whether the data table needing to be migrated conforms to a data migration task or not based on load monitoring and a data table selection result; if yes, migrating the historical data from the source end to the target end, processing the historical data in batches according to time intervals, recording query time consumption and dynamically adjusting the time intervals; through batch processing and a controllable transaction submission interval, a large number of system resources are prevented from being occupied for a long time, and stable operation of the database is guaranteed.

Description

Technical Field

[0001] The present application relates to the technical field of data migration, and in particular to a method, system and medium for load-aware and space-optimized historical data migration. Background Art

[0002] With the rapid development of information technology, enterprises have accumulated a large amount of business data, especially historical data, in their daily operations. These historical data are of great value in data analysis, business decision-making, and regulatory compliance. However, as time goes by, the amount of historical data has increased dramatically, bringing many challenges to the data storage, management, and query of enterprises. Traditional database systems are mainly aimed at real-time business optimization and are difficult to efficiently process massive historical data, resulting in tight storage space, degraded database performance, and complicated data management.

[0003] At present, common historical data migration and archiving methods mainly include manual data migration, the use of commercial data migration tools, and data archiving based on partition tables. Manual data migration relies on operation and maintenance personnel to write scripts or use the export / import tools that come with the database, which has defects such as high labor costs, lack of intelligent scheduling, and insufficient transaction control. Although commercial data migration tools provide automation and graphical interfaces, they are usually costly, have poor adaptability, and have obvious performance bottlenecks when processing large-scale data. The archiving method based on partition tables relies on the partitioning function of a specific database, is complex to operate, lacks load awareness, and is prone to affecting business operations when the system load is high.

[0004] In summary, the existing historical data migration solutions have obvious deficiencies in terms of automation, intelligent optimization and scalability, and are difficult to meet the needs of enterprises for efficient and reliable data migration and archiving in a big data environment. Therefore, there is an urgent need for a historical data migration method and system that can sense the system load, intelligently select migration objects based on table space size, and control transaction commit intervals, so as to achieve optimal resource utilization, system performance guarantee and high efficiency of data migration. Summary of the invention

[0005] The purpose of the embodiments of the present application is to provide a load-aware and space-optimized historical data migration method, system and medium, which avoids occupying a large amount of system resources for a long time and ensures the stable operation of the database through batch processing and controllable transaction submission intervals.

[0006] The embodiment of the present application also provides a method for migrating historical data with load awareness and space optimization, including:

[0007] The startup program performs log record initialization and loads configuration parameters, wherein the configuration parameters include database connection information, load threshold, space threshold and transaction commit interval information;

[0008] Monitor the performance indicators of the source database at preset time intervals, and evaluate the current load information of the source database based on the performance indicators;

[0009] Analyze the space usage and historical data ratio of each data table based on the current load information, and automatically identify and select the data tables that need to be migrated;

[0010] Analyze whether the data tables to be migrated meet the data migration task based on load monitoring and data table selection results;

[0011] If it does, the historical data will be migrated from the source to the target, processed in batches according to time intervals, the query time will be recorded and the time interval will be adjusted dynamically. If it does not, the process ends and the performance indicators of the source database will continue to be monitored at the preset time intervals.

[0012] Optionally, in the load-aware and space-optimized historical data migration method described in the embodiment of the present application, starting the program to initialize log records and load configuration parameters specifically includes:

[0013] Initialization component, the initialization component includes a database connection pool and a log component, starts a program, obtains program parameters, initializes log records based on the program parameters, and obtains initialization status information;

[0014] Compare the initialization state information with the set state information to obtain the initialization deviation rate;

[0015] Determining whether the initialization deviation rate is greater than or equal to a set deviation rate threshold;

[0016] If it is greater than or equal to, generating correction information, and adjusting the program parameters based on the correction information;

[0017] If it is less than, the configuration parameters are loaded.

[0018] Optionally, in the load-aware and space-optimized historical data migration method described in the embodiment of the present application, the load L(t) of the source database at time t is calculated as follows:

[0019]

[0020] Among them, α+β+γ=1 is the weight coefficient, which reflects the importance of each indicator;

[0021] Load judgment conditions:

[0022] If L(t)≤Θ, the source database load is low and suitable for data migration tasks; otherwise, the source database load is high and the migration task is postponed.

[0023] Optionally, in the load-aware and space-optimized historical data migration method described in the embodiment of the present application, the space occupancy and historical data ratio of each data table are analyzed based on the current load situation information, and the data table to be migrated is automatically identified and selected, specifically including:

[0024] Query the database metadata to obtain the data space and index space of each table;

[0025] Calculate the total space S of each table based on data space and index space i , calculate the total space of all tables

[0026] According to the set thresholds Φ and Ψ, filter out the table T that needs to be migrated i , generate a migration candidate list and obtain the data tables that need to be migrated.

[0027] Optionally, in the load-aware and space-optimized historical data migration method described in the embodiment of the present application, analyzing whether the data table to be migrated meets the data migration task based on the load monitoring and data table selection results specifically includes:

[0028] For each migration target table T i , determine the time range to be migrated.

[0029] Initialization time interval Δt 1 , enter the migration loop mode:

[0030] According to the current time interval Δt j , divide the data into batches D i，j , record the query start time T start ;

[0031] Execute data export operation and export D by time range i，j , record the query end time T end , calculate the query time Q j =T end -T start ;

[0032] Adjust next time interval Execute data import to the target end, control transaction submission, and record migration progress;

[0033] If Q j If it exceeds 5 minutes, shorten Δt j If Q j If it is less than 5 minutes, extend Δt j , until the data migration of all time periods is completed.

[0034] Optionally, in the load-aware and space-optimized historical data migration method described in the embodiment of the present application, if it meets the requirements, the historical data is migrated from the source end to the target end, processed in batches according to time intervals, the query time is recorded, and the time interval is dynamically adjusted, specifically including:

[0035] Table T i The dataset D to be migrated i Divide into multiple time periods according to the time interval Δt, and the length of each time period is Δt j , dynamically adjust Δt j To control the query time Q j Close to 5 minutes;

[0036]

[0037] Among them, Q desired =5 minutes, initial setting Δt 1 = 3 hours;

[0038] Each time period of data D is processed i，j Finally, commit the transaction once to avoid long transactions occupying a large amount of system resources.

[0039] In a second aspect, an embodiment of the present application provides a load-aware and space-optimized historical data migration system, the system comprising: a memory and a processor, the memory comprising a program of a load-aware and space-optimized historical data migration method, and when the program of the load-aware and space-optimized historical data migration method is executed by the processor, the following steps are implemented:

[0040] The startup program performs log record initialization and loads configuration parameters, wherein the configuration parameters include database connection information, load threshold, space threshold and transaction commit interval information;

[0041] Monitor the performance indicators of the source database at preset time intervals, and evaluate the current load information of the source database based on the performance indicators;

[0042] Analyze the space usage and historical data ratio of each data table based on the current load information, and automatically identify and select the data tables that need to be migrated;

[0043] Analyze whether the data tables to be migrated meet the data migration task based on load monitoring and data table selection results;

[0044] If it does, the historical data will be migrated from the source to the target, processed in batches according to time intervals, the query time will be recorded and the time interval will be adjusted dynamically. If it does not, the process ends and the performance indicators of the source database will continue to be monitored at the preset time intervals.

[0045] Optionally, in the load-aware and space-optimized historical data migration system described in the embodiment of the present application, starting a program to initialize log records and load configuration parameters specifically includes:

[0046] Initialization component, the initialization component includes a database connection pool and a log component, starts a program, obtains program parameters, initializes log records based on the program parameters, and obtains initialization status information;

[0047] Compare the initialization state information with the set state information to obtain the initialization deviation rate;

[0048] Determining whether the initialization deviation rate is greater than or equal to a set deviation rate threshold;

[0049] If it is greater than or equal to, generating correction information, and adjusting the program parameters based on the correction information;

[0050] If it is less than, the configuration parameters are loaded.

[0051] Optionally, in the load-aware and space-optimized historical data migration system described in the embodiment of the present application, the load L(t) of the source database at time t is calculated as follows:

[0052]

[0053] Among them, α+β+γ=1 is the weight coefficient, which reflects the importance of each indicator;

[0054] Load judgment conditions:

[0055] If L(t)≤θ, the source database load is low and suitable for data migration tasks; otherwise, the source database load is high and the migration task is postponed.

[0056] In a third aspect, an embodiment of the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium includes a program for a historical data migration method for load perception and space optimization. When the program for the historical data migration method for load perception and space optimization is executed by a processor, the steps of the historical data migration method for load perception and space optimization as described in any one of the above items are implemented.

[0057] As can be seen from the above, a load-aware and space-optimized historical data migration method, system and medium are provided in an embodiment of the present application. Log recording is initialized by starting a program and configuration parameters are loaded, wherein the configuration parameters include database connection information, load threshold, space threshold and transaction submission interval information; the performance indicators of the source database are monitored at preset time intervals, and the current load status information of the source database is evaluated based on the performance indicators; the space occupancy and historical data ratio of each data table are analyzed based on the current load status information, and the data tables to be migrated are automatically identified and selected; based on the load monitoring and data table selection results, whether the data tables to be migrated meet the data migration task is analyzed; if they meet the requirements, the historical data is migrated from the source end to the target end, and the query time is recorded and the time interval is adjusted dynamically. If they do not meet the requirements, the query is terminated and the performance indicators of the source database are continuously monitored at preset time intervals; through batch processing and controllable transaction submission intervals, a large amount of system resources can be avoided to be occupied for a long time, lock contention and rollback risks can be reduced, and the stable operation of the database can be ensured. BRIEF DESCRIPTION OF THE DRAWINGS

[0058] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the embodiments of the present application will be briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present application and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.

[0059] Figure 1 A flow chart of a load-aware and space-optimized historical data migration method provided in an embodiment of the present application;

[0060] Figure 2 A flow chart of a method for loading configuration parameters of a historical data migration method with load awareness and space optimization provided in an embodiment of the present application. DETAILED DESCRIPTION

[0061] The technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all of the embodiments. The components of the embodiments of the present application described and shown in the drawings here can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present application provided in the drawings is not intended to limit the scope of the application claimed for protection, but merely represents the selected embodiments of the present application. Based on the embodiments of the present application, all other embodiments obtained by those skilled in the art without making creative work belong to the scope of protection of the present application.

[0062] It should be noted that similar reference numerals and letters represent similar items in the following drawings, so once an item is defined in one drawing, it does not need to be further defined and explained in subsequent drawings. At the same time, in the description of this application, the terms "first", "second", etc. are only used to distinguish the description and cannot be understood as indicating or implying relative importance.

[0063] Please refer to Figure 1 , Figure 1 1 is a flow chart of a load-aware and space-optimized historical data migration method in some embodiments of the present application. The load-aware and space-optimized historical data migration method is used in a terminal device, and the load-aware and space-optimized historical data migration method includes the following steps:

[0064] S101, starting a program to initialize log records and load configuration parameters, which include database connection information, load threshold, space threshold and transaction commit interval information;

[0065] S102, monitoring the performance indicators of the source database at preset time intervals, and evaluating the current load information of the source database based on the performance indicators; the preset time interval is set by those skilled in the art according to actual needs;

[0066] S103, analyzing the space occupancy and historical data ratio of each data table based on the current load information, and automatically identifying and selecting the data table to be migrated;

[0067] S104, analyzing whether the data table to be migrated meets the data migration task based on the load monitoring and data table selection results;

[0068] S105, if it meets the requirements, the historical data is migrated from the source end to the target end, processed in batches according to the time interval, the query time is recorded and the time interval is adjusted dynamically. If it does not meet the requirements, it ends and continues to monitor the performance indicators of the source database according to the preset time interval.

[0069] It should be noted that by real-time monitoring of key performance indicators such as CPU, memory, and disk I / O of the source database, data migration is ensured only when the source database load is low, minimizing the impact on online business. Secondly, based on the intelligent selection mechanism of table space size, tables that occupy large space are automatically identified and migrated, optimizing the utilization of storage resources and reducing migration costs. At the same time, the system avoids long transactions from occupying a large amount of system resources through batch processing and controllable transaction submission intervals, reduces lock contention and rollback risks, and ensures the stable operation of the database.

[0070] Please refer to Figure 2 , Figure 2This is a flow chart of a method for loading configuration parameters of a load-aware and space-optimized historical data migration method in some embodiments of the present application. According to an embodiment of the present invention, the startup program performs log record initialization and loads configuration parameters, specifically including:

[0071] S201, initializing components, including a database connection pool and a log component, starting a program, obtaining program parameters, initializing log records based on the program parameters, and obtaining initialization status information;

[0072] S202, comparing the initialization state information with the set state information to obtain an initialization deviation rate;

[0073] S203, determining whether the initialization deviation rate is greater than or equal to a set deviation rate threshold;

[0074] S204, if it is greater than or equal to, generating correction information, and adjusting the program parameters based on the correction information;

[0075] S205: If it is less than, then load the configuration parameters.

[0076] According to an embodiment of the present invention, the load L(t) of the source database at time t is calculated as follows:

[0077]

[0078] Among them, α+β+γ=1 is the weight coefficient, which reflects the importance of each indicator;

[0079] Load judgment conditions:

[0080] If L(t)≤θ, the source database load is low and suitable for data migration tasks; otherwise, the source database load is high and the migration task is postponed.

[0081] It should be noted that the present invention has the functions of full-process automated management and breakpoint resumption, which improves the reliability and efficiency of the migration process, reduces the need for manual intervention, and reduces operation and maintenance costs. After the migration is completed, the system automatically performs data consistency verification and safely deletes the migrated data at the source, freeing up storage space and simplifying the data management process. Finally, the system design has good scalability and adaptability, and can support database systems of different sizes and types to meet diverse business needs. In summary, the present invention realizes efficient, reliable and resource-friendly historical data migration and archiving through technical means such as intelligent load perception, space optimization selection, controllable transaction submission and automated management, and has broad application prospects and significant technical advantages.

[0082] According to an embodiment of the present invention, the space occupancy and historical data ratio of each data table are analyzed based on the current load information, and the data table to be migrated is automatically identified and selected, specifically including:

[0083] Query the database metadata to obtain the data space and index space of each table;

[0084] Calculate the total space S of each table based on data space and index space i , calculate the total space S of all tables total ;

[0085] According to the set thresholds Φ and Ψ, filter out the table T that needs to be migrated i , generate a migration candidate list and obtain the data tables that need to be migrated.

[0086] It should be noted that if the load status output by the load monitoring module is less than the threshold and the migration candidate list output by the data table analysis module is not empty, the data migration module is started, otherwise it enters the waiting state and periodically re-detects.

[0087] According to an embodiment of the present invention, analyzing whether a data table to be migrated meets the data migration task based on the load monitoring and data table selection results specifically includes:

[0088] For each migration target table T i , determine the time range to be migrated.

[0089] Initialization time interval Δt 1 , enter the migration loop mode:

[0090] According to the current time interval Δt j , divide the data into batches D i，j , record the query start time T start ;

[0091] Execute data export operation and export D by time range i，j , record the query end time T end , calculate the query time Q j =T end -T start ;

[0092] Adjust next time interval Execute data import to the target end, control transaction submission, and record migration progress;

[0093] If Q j If it exceeds 5 minutes, shorten Δt j If Q j If it is less than 5 minutes, extend Δt j , until the data migration of all time periods is completed.

[0094] According to an embodiment of the present invention, if the conditions are met, the historical data is migrated from the source end to the target end, processed in batches according to time intervals, the query time is recorded, and the time interval is dynamically adjusted, specifically including:

[0095] Table T i The dataset D to be migrated i Divide into multiple time periods according to the time interval Δt, and the length of each time period is Δt j , dynamically adjust Δt j To control the query time Q j Close to 5 minutes;

[0096]

[0097] Among them, Q desired =5 minutes, initial setting Δt 1 = 3 hours;

[0098] Each time period of data D is processed i，j Finally, commit the transaction once to avoid long transactions occupying a large amount of system resources.

[0099] It should be noted that the intelligent selection mechanism based on the size of the table space ensures that the migration tasks are concentrated on the tables that occupy larger space, improves the utilization efficiency of storage resources, reduces the migration cost, and improves the reliability and efficiency of migration: the time interval batch migration and dynamic adjustment algorithm ensure that each query takes about 5 minutes to avoid long-term queries occupying system resources. The controllable transaction submission interval and batch migration strategy prevent long transactions from occupying a large amount of system resources and reduce lock contention and rollback risks. The automation and breakpoint resume functions improve the reliability and efficiency of the migration process and reduce the need for manual intervention.

[0100] In a second aspect, an embodiment of the present application provides a load-aware and space-optimized historical data migration system, the system comprising: a memory and a processor, the memory comprising a program of a load-aware and space-optimized historical data migration method, and when the program of the load-aware and space-optimized historical data migration method is executed by the processor, the following steps are implemented:

[0101] Start the program to initialize logging and load configuration parameters, including database connection information, load threshold, space threshold, and transaction commit interval information;

[0102] Monitor the performance indicators of the source database at preset time intervals, and evaluate the current load information of the source database based on the performance indicators;

[0103] Analyze the space usage and historical data ratio of each data table based on the current load information, and automatically identify and select the data tables that need to be migrated;

[0104] Analyze whether the data tables to be migrated meet the data migration task based on load monitoring and data table selection results;

[0105] If it does, the historical data will be migrated from the source to the target, processed in batches according to time intervals, the query time will be recorded and the time interval will be adjusted dynamically. If it does not, the process ends and the performance indicators of the source database will continue to be monitored at the preset time intervals.

[0106] It should be noted that by real-time monitoring of key performance indicators such as CPU, memory, and disk I / O of the source database, data migration is ensured only when the source database load is low, minimizing the impact on online business. Secondly, based on the intelligent selection mechanism of table space size, tables that occupy large space are automatically identified and migrated, optimizing the utilization of storage resources and reducing migration costs. At the same time, the system avoids long transactions from occupying a large amount of system resources through batch processing and controllable transaction submission intervals, reduces lock contention and rollback risks, and ensures the stable operation of the database.

[0107] According to an embodiment of the present invention, the startup program performs log record initialization and loads configuration parameters, specifically including:

[0108] Initialization component, the initialization component includes a database connection pool and a log component, starts a program, obtains program parameters, initializes log records based on the program parameters, and obtains initialization status information;

[0109] Compare the initialization state information with the set state information to obtain the initialization deviation rate;

[0110] Determine whether the initialization deviation rate is greater than or equal to the set deviation rate threshold;

[0111] If it is greater than or equal to, generating correction information, and adjusting the program parameters based on the correction information;

[0112] If it is less than, the configuration parameters are loaded.

[0113] It should be noted that the present invention has the functions of full-process automated management and breakpoint resumption, which improves the reliability and efficiency of the migration process, reduces the need for manual intervention, and reduces operation and maintenance costs. After the migration is completed, the system automatically performs data consistency verification and safely deletes the migrated data at the source, freeing up storage space and simplifying the data management process. Finally, the system design has good scalability and adaptability, and can support database systems of different sizes and types to meet diverse business needs. In summary, the present invention realizes efficient, reliable and resource-friendly historical data migration and archiving through technical means such as intelligent load perception, space optimization selection, controllable transaction submission and automated management, and has broad application prospects and significant technical advantages.

[0114] According to an embodiment of the present invention, the load L(t) of the source database at time t is calculated as follows:

[0115]

[0116] Among them, α+β+γ=1 is the weight coefficient, which reflects the importance of each indicator;

[0117] Load judgment conditions:

[0118] If L(t)≤θ, the source database load is low and suitable for data migration tasks; otherwise, the source database load is high and the migration task is postponed.

[0119] It should be noted that the present invention has the functions of full-process automated management and breakpoint resumption, which improves the reliability and efficiency of the migration process, reduces the need for manual intervention, and reduces operation and maintenance costs. After the migration is completed, the system automatically performs data consistency verification and safely deletes the migrated data at the source, freeing up storage space and simplifying the data management process. Finally, the system design has good scalability and adaptability, and can support database systems of different sizes and types to meet diverse business needs. In summary, the present invention realizes efficient, reliable and resource-friendly historical data migration and archiving through technical means such as intelligent load perception, space optimization selection, controllable transaction submission and automated management, and has broad application prospects and significant technical advantages.

[0120] According to an embodiment of the present invention, the space occupancy and historical data ratio of each data table are analyzed based on the current load information, and the data table to be migrated is automatically identified and selected, specifically including:

[0121] Query the database metadata to obtain the data space and index space of each table;

[0122] Calculate the total space S of each table based on data space and index space i , calculate the total space of all tables

[0123] According to the set thresholds Φ and Ψ, filter out the table T that needs to be migrated i , generate a migration candidate list and obtain the data tables that need to be migrated.

[0124] It should be noted that if the load status output by the load monitoring module is less than the threshold and the migration candidate list output by the data table analysis module is not empty, the data migration module is started, otherwise it enters the waiting state and periodically re-detects.

[0125] According to an embodiment of the present invention, analyzing whether a data table to be migrated meets the data migration task based on the load monitoring and data table selection results specifically includes:

[0126] For each migration target table T i , determine the time range to be migrated.

[0127] Initialization time interval Δt 1 , enter the migration loop mode:

[0128] According to the current time interval Δt j , divide the data into batches D i，j , record the query start time T start ;

[0129] Execute data export operation and export D by time range i，j , record the query end time T end , calculate the query time Q j =T end -T start ;

[0130] Adjust next time interval Execute data import to the target end, control transaction submission, and record migration progress;

[0131] If Q j If it exceeds 5 minutes, shorten Δt j If Q j If it is less than 5 minutes, extend Δt j , until the data migration of all time periods is completed.

[0132] According to an embodiment of the present invention, if the conditions are met, the historical data is migrated from the source end to the target end, processed in batches according to time intervals, the query time is recorded, and the time interval is dynamically adjusted, specifically including:

[0133] Table T i The dataset D to be migrated i Divide into multiple time periods according to the time interval Δt, and the length of each time period is Δt j , dynamically adjust Δt j To control the query time Q j Close to 5 minutes;

[0134]

[0135] Among them, Q desired =5 minutes, initial setting Δt 1 = 3 hours;

[0136] Each time period of data D is processed i，j Finally, commit the transaction once to avoid long transactions occupying a large amount of system resources.

[0137] It should be noted that the intelligent selection mechanism based on the size of the table space ensures that the migration tasks are concentrated on the tables that occupy larger space, improves the utilization efficiency of storage resources, reduces the migration cost, and improves the reliability and efficiency of migration: the time interval batch migration and dynamic adjustment algorithm ensure that each query takes about 5 minutes to avoid long-term queries occupying system resources. The controllable transaction submission interval and batch migration strategy prevent long transactions from occupying a large amount of system resources and reduce lock contention and rollback risks. The automation and breakpoint resume functions improve the reliability and efficiency of the migration process and reduce the need for manual intervention.

[0138] A third aspect of the present invention provides a computer-readable storage medium, which includes a program for a historical data migration method for load perception and space optimization. When the program for a historical data migration method for load perception and space optimization is executed by a processor, the steps of the historical data migration method for load perception and space optimization as described above are implemented.

[0139] The present invention discloses a load-aware and space-optimized historical data migration method, system and medium. The method includes: performing log record initialization and loading configuration parameters by starting a program. The configuration parameters include database connection information, load threshold, space threshold and transaction submission interval information. The method monitors the performance index of a source database at a preset time interval, and evaluates the current load status information of the source database based on the performance index. The method analyzes the space occupancy and historical data ratio of each data table based on the current load status information, and automatically identifies and selects the data table to be migrated. The method analyzes whether the data table to be migrated meets the data migration task based on the load monitoring and data table selection results. If the data table meets the data migration task, the historical data is migrated from the source end to the target end, and the data is processed in batches according to the time interval. The query time is recorded and the time interval is adjusted dynamically ... query time is processed in batches according to the time interval. The query time is recorded and the time interval is adjusted dynamically. If the data table meets the data migration task, the historical data is migrated from the source end to the target end, and the query time is processed in batches according to the time interval. The query time is recorded and the time interval is adjusted dynamically. If the data table meets the data migration task, the historical data is migrated from the source end to the target end, and the query time is processed in

[0140] In the several embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. The device embodiments described above are only schematic. For example, the division of units is only a logical function division. There may be other division methods in actual implementation, such as: multiple units or components can be combined, or can be integrated into another system, or some features can be ignored or not executed. In addition, the coupling, direct coupling, or communication connection between the components shown or discussed can be through some interfaces, and the indirect coupling or communication connection of devices or units can be electrical, mechanical or other forms.

[0141] The units described above as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units; they may be located in one place or distributed on multiple network units; some or all of the units may be selected according to actual needs to achieve the purpose of the present embodiment.

[0142] In addition, all functional units in the embodiments of the present invention may be integrated into one processing unit, or each unit may be separately used as a unit, or two or more units may be integrated into one unit; the above-mentioned integrated units may be implemented in the form of hardware or in the form of hardware plus software functional units.

[0143] Those skilled in the art can understand that: all or part of the steps of implementing the above method embodiments can be completed by hardware related to program instructions, the aforementioned program can be stored in a readable storage medium, and when the program is executed, it executes the steps of the above method embodiments; and the aforementioned storage medium includes: mobile storage devices, read-only memories (ROM, Read-Only Memory), random access memories (RAM, Random Access Memory), disks or optical disks, and other media that can store program codes.

[0144] Or, if the above-mentioned integrated unit of the present invention is implemented in the form of a software function module and sold or used as an independent product, it can also be stored in a readable storage medium. Based on this understanding, the technical solution of the embodiment of the present invention can be essentially or partly reflected in the form of a software product that contributes to the prior art. The software product is stored in a storage medium and includes several instructions for a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the methods described in each embodiment of the present invention. The aforementioned storage medium includes: various media that can store program codes, such as mobile storage devices, ROM, RAM, magnetic disks or optical disks.

Claims

1. A load-aware and space-optimized historical data migration method, characterized in that: include: The startup program performs log record initialization and loads configuration parameters, wherein the configuration parameters include database connection information, load threshold, space threshold and transaction commit interval information; Monitor the performance indicators of the source database at preset time intervals, and evaluate the current load information of the source database based on the performance indicators; Analyze the space usage and historical data ratio of each data table based on the current load information, and automatically identify and select the data tables that need to be migrated; Analyze whether the data tables to be migrated meet the data migration task based on load monitoring and data table selection results; If it does, the historical data will be migrated from the source to the target, processed in batches according to time intervals, the query time will be recorded and the time interval will be adjusted dynamically. If it does not, the process ends and the performance indicators of the source database will continue to be monitored at the preset time intervals.

2. The method for migrating historical data with load awareness and space optimization according to claim 1 is characterized in that: Start the program to initialize logging and load configuration parameters, including: Initialization component, the initialization component includes a database connection pool and a log component, starts a program, obtains program parameters, initializes log records based on the program parameters, and obtains initialization status information; Compare the initialization state information with the set state information to obtain the initialization deviation rate; Determining whether the initialization deviation rate is greater than or equal to a set deviation rate threshold; If it is greater than or equal to, generating correction information, and adjusting the program parameters based on the correction information; If it is less than, the configuration parameters are loaded.

3. The load-aware and space-optimized historical data migration method according to claim 2 is characterized in that: The source database load L(t) at time t is calculated as follows: Among them, α+β + γ= 1 is the weight coefficient, reflecting the importance of each indicator; Load judgment conditions: If L(t)≤θ, the source database load is low and suitable for data migration tasks; otherwise, the source database load is high and the migration task is postponed.

4. The method for historical data migration with load perception and space optimization according to claim 3 is characterized in that: Based on the current load information, the system analyzes the space usage and historical data ratio of each data table, and automatically identifies and selects the data tables that need to be migrated, including: Query the database metadata to obtain the data space and index space of each table; Calculate the total space S of each table based on data space and index space i , calculate the total space S of all tables lolad ; ； According to the set thresholds Φ and Ψ, filter out the table T that needs to be migrated i , generate a migration candidate list and obtain the data tables that need to be migrated.

5. The method for historical data migration with load perception and space optimization according to claim 1 is characterized in that: Based on the load monitoring and data table selection results, analyze whether the data tables to be migrated meet the data migration task requirements, including: For each migration target table T i , determine the time range to be migrated; Initialize the time interval Δt1 and enter the migration cycle mode: According to the current time interval Δt j , divide the data into batches D i,j , record the query start time T start ; Execute data export operation and export D by time range i，j , record the query end time T end , calculate the query time Q j =T end -T start ; Adjust next time interval Execute data import to the target end, control transaction submission, and record migration progress; If Q j If it exceeds 5 minutes, shorten Δt j If Q j If it is less than 5 minutes, extend Δt j , until the data migration of all time periods is completed.

6. The method for historical data migration with load perception and space optimization according to claim 5 is characterized in that: If it is in compliance, the historical data will be migrated from the source to the target, processed in batches according to time intervals, the query time will be recorded and the time interval will be adjusted dynamically, including: Table T i The dataset D to be migrated i Divide into multiple time periods according to the time interval Δt, and the length of each time period is Δt j , dynamically adjust Δt j To control the query time Q j Close to 5 minutes; Among them, Ω desired =5 minutes, initial setting Δt1=3 hours; Each time period of data D is processed i，j Finally, commit the transaction once to avoid long transactions occupying a large amount of system resources.

7. A load-aware and space-optimized historical data migration system, characterized in that: The system includes: a memory and a processor, wherein the memory includes a program of a method for migrating historical data of load perception and space optimization, and when the program of the method for migrating historical data of load perception and space optimization is executed by the processor, the following steps are implemented: The startup program performs log record initialization and loads configuration parameters, wherein the configuration parameters include database connection information, load threshold, space threshold and transaction commit interval information; Monitor the performance indicators of the source database at preset time intervals, and evaluate the current load information of the source database based on the performance indicators; Analyze the space usage and historical data ratio of each data table based on the current load information, and automatically identify and select the data tables that need to be migrated; Analyze whether the data tables to be migrated meet the data migration task based on load monitoring and data table selection results; If it does, the historical data will be migrated from the source to the target, processed in batches according to time intervals, the query time will be recorded and the time interval will be adjusted dynamically. If it does not, the process ends and the performance indicators of the source database will continue to be monitored at the preset time intervals.

8. The load-aware and space-optimized historical data migration system according to claim 7, characterized in that: Start the program to initialize logging and load configuration parameters, including: Initialization component, the initialization component includes a database connection pool and a log component, starts a program, obtains program parameters, initializes log records based on the program parameters, and obtains initialization status information; Compare the initialization state information with the set state information to obtain the initialization deviation rate; Determining whether the initialization deviation rate is greater than or equal to a set deviation rate threshold; If it is greater than or equal to, generating correction information, and adjusting the program parameters based on the correction information; If it is less than, the configuration parameters are loaded.

9. The load-aware and space-optimized historical data migration system according to claim 8, characterized in that: The source database load L(t) at time t is calculated as follows: Among them, α+β+γ=1 is the weight coefficient, which reflects the importance of each indicator; Load judgment conditions: If L(t)≤θ, the source database load is low and suitable for data migration tasks; otherwise, the source database load is high and the migration task is postponed.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium includes a load-aware and space-optimized historical data migration method program. When the load-aware and space-optimized historical data migration method program is executed by a processor, the steps of the load-aware and space-optimized historical data migration method as described in any one of claims 1 to 6 are implemented.

Images