A data synchronization method based on Debezium embedded engine

Through Debezium embedded engine and sharded multi-channel two-level cache queue technology, data inconsistency and timing problems in data synchronization are solved, synchronization efficiency and accuracy are improved, and the needs of multiple data target systems are adapted.

CN118410109BActive Publication Date: 2024-11-05JIANGSU LINGHAO NETWORK TECH CO LTD +1
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410883948.X
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-07-03
Publication Date
2024-11-05
Estimated Expiration
2044-07-03

AI Technical Summary

Technical Problem

Existing data synchronization technology can easily lead to data inconsistency in network delays and host failures, and the synchronization efficiency is inefficient, unable to adapt to multiple data target systems, and the data timing cannot be guaranteed.

Method used

The data synchronization method based on the Debezium embedded engine is adopted, and the data source system changes are captured in real time by decoupling through sharded multi-channel two-level cache queues, and the data structure mapping relationship update is automated to ensure the timing of data batch synchronization.

Benefits of technology

It improves the stability and efficiency of data synchronization, reduces delays, reduces the possibility of errors, enhances the accuracy and reliability of data synchronization, can adapt to changes in the data structure of the data source system, and optimizes the synchronization process.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118410109B_ABST
    Figure CN118410109B_ABST
Patent Text Reader

Abstract

The present invention discloses a data synchronization method based on Debezium embedded engine, and relates to the field of data processing technology. In order to solve the problems of low synchronization efficiency, inability to adapt to multiple data target systems, and inability to ensure data timing in the data synchronization method in the prior art; a data synchronization method based on Debezium embedded engine includes tag mapping relationship and collection; real-time data capture and cache queue decoupling; stock data synchronization; data structure mapping relationship update; batch reverse query of primary key ID; data batch synchronization and timing guarantee; through sharding multi-channel two-level cache queue decoupling, data can be synchronized in parallel to different database tables of multiple different numbers and types of data target systems, achieving efficient, flexible and timing synchronization of data, alleviating source data back pressure, improving data synchronization stability, adapting to multiple data target systems, and ensuring data accuracy and timing.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a data synchronization method based on a Debezium embedded engine. Background Art

[0002] With the continuous deepening of informatization, the demand for data synchronization between different data source systems is increasing. Regarding data synchronization, a Chinese patent with publication number: CN112035577B discloses a data synchronization method and a data synchronization system, the method includes: internal data synchronization of the centralized control and the substation, including: determining the host and the slave; reading the target data from the host; converting it into the data to be synchronized according to the data comparison and conversion law; writing the data to be synchronized to the slave; data synchronization of the database server between the centralized control and the substation, including: centralized control host and centralized control slave, determining the substation host and substation slave; synchronizing the data of the substation host to the substation slave and the centralized control host; synchronizing the data of the centralized control host to the centralized control slave; when the substation host and the centralized control host fail, perform master-slave switching; compare the latest data of the synchronizer with the data of the to-be-synchronized machine, determine the synchronization position of the to-be-synchronized machine, and synchronize data according to the synchronization position.

[0003] However, in the prior art, the synchronization position is determined by comparing the most recent data of the synchronizer with the data of the synchronizer to be synchronized. However, in some cases, such as network delays and data inconsistencies during host failures, data inconsistencies may still occur after synchronization. As a result, there are problems such as low synchronization efficiency, inability to adapt to a variety of data target systems, and inability to guarantee data timing. Summary of the invention

[0004] The purpose of the present invention is to provide a data synchronization method based on the Debezium embedded engine, which can synchronize data in parallel to different database tables of multiple target systems with different numbers and types of data. By decoupling the sharded multi-channel two-level cache queues, the back pressure of the source data is effectively alleviated, and the stability of data synchronization is improved to solve the problems raised in the above background technology.

[0005] To achieve the above object, the present invention provides the following technical solutions:

[0006] A data synchronization method based on Debezium embedded engine includes the following steps:

[0007] Mark mapping relationships and collections: Based on the many-to-one mapping relationship between the data source system and the data target system, mark the data source system library table collection involved in the data structure of the data target system, and mark the column name of the primary key ID mapping relationship between the data source system library table and the data target system library table, as well as the query SQL statement;

[0008] The data source system includes Mysql, PostgreSQL relational database system, and the data target system includes Mysql, PostgreSQL, Elasticsearch data storage system;

[0009] Real-time data capture and cache queue decoupling: The Debezium embedded engine is used to capture data changes in the data source system in real time, and a sharded multi-channel two-level cache queue is used for decoupling;

[0010] Existing data synchronization: By monitoring the specified library table of the data source system, parsing and executing the synchronization commands of the entire existing data and the specified library table, data synchronization is performed;

[0011] Data structure mapping relationship update: By monitoring the data structure refresh command of the specified library table of the data source system, the data structure mapping relationship between the data source system and the target data system is updated in real time;

[0012] Batch reverse query of primary key ID: By monitoring the first-level source library table shard cache queue, batch reverse query of the data source system SQL, obtain the primary key ID of the data structure of the data target system;

[0013] Data batch synchronization and timing guarantee: By monitoring the secondary target library table primary key ID shard cache queue, ensure that data is synchronized to the target data system in batches while maintaining the timing of source library table-level data.

[0014] Furthermore, the tag mapping relationship and the tag data source system library table set in the set specifically include:

[0015] Collect the database table structure information of the data source system and the data target system, analyze the mapping relationship between the data source system and the data target system, and determine the many-to-one mapping relationship between the data source system and the data target system based on the analysis results;

[0016] Based on the many-to-one mapping relationship, the library table structure of the data source system and the data target system is obtained, the library tables in the data source system that need to be synchronized to the data target system are determined, the library tables in the data source system are identified and marked, and a set of library tables of the data source system to be synchronized is generated.

[0017] Furthermore, the tag mapping relationship and the column name of the tag primary key ID mapping relationship in the collection and the query SQL statement specifically include:

[0018] Compare the primary key ID structure of the data source system library table and the data target system library table to determine the primary key ID mapping relationship in the data source system library table set to be synchronized;

[0019] If the primary key ID corresponds directly, directly mark the corresponding column name;

[0020] If the primary key ID does not correspond directly, a query SQL statement is written according to the business logic and data relationship, and the query SQL statement is marked, and the column name of the primary key ID mapping relationship and the query SQL statement are stored in the mapping relationship table.

[0021] Furthermore, real-time data capture and cache queue decoupling are decoupled by using a sharded multi-channel two-level cache queue, specifically:

[0022] Determine the attributes of the business primary key ID, and analyze the database tables and their relationships related to the business primary key ID in the MySQL database;

[0023] Debezium embedded engine configuration is performed based on MySQL data source to match the database table to be monitored. At the same time, Debezium captures the change events containing the business primary key ID in the MySQL database in real time;

[0024] Divide the data into multiple shards according to the hash value of the business primary key ID, and create an independent processing channel for each shard, each of which includes a first-level and a second-level cache queue;

[0025] Each processing channel corresponds to an independent first-level cache queue, which is used to store the latest change events of the channel. Each processing channel shares a second-level cache queue, which is used to store historical data and cold data.

[0026] Distribute the change event to the processing channel of the corresponding shard according to the business primary key ID in the change event, and each processing channel processes the change event distributed to the channel;

[0027] When the queue length of the processing channel exceeds the preset threshold, the reception of new events is suspended, the number of consumers is increased, and the processing speed is reduced. The local cache is updated according to business needs from the first-level cache queue of the processing channel.

[0028] Furthermore, the data is divided into multiple shards according to the hash value of the business primary key ID, and an independent processing channel is created for each shard, which also includes:

[0029] Extract the number of shards corresponding to the corresponding shards to which the change event is distributed and the amount of data corresponding to the shards; wherein the number of shards is the same as the number of processing channels;

[0030] The memory utilization allocation compensation coefficient is obtained according to the data volume of the data corresponding to the shard; wherein the memory utilization allocation compensation coefficient is obtained by the following formula:

[0031] ;

[0032] Where f represents the memory utilization allocation compensation coefficient; Cp Indicates the amount of data corresponding to the shard; C z Indicates the total amount of data before sharding; K a Represents the first adjustment coefficient; K b represents the second adjustment coefficient; wherein the first adjustment coefficient and the second adjustment coefficient are obtained by the following formula:

[0033] ;

[0034] Where n represents the number of shards; C pi Indicates the amount of data corresponding to the i-th shard; C d Indicates the amount of data processing change corresponding to each unit percentage change in memory utilization; and the unit percentage value range is 0.1%-1.2%;

[0035] Obtaining a memory utilization upper limit value and a memory utilization lower limit value corresponding to the processing channel according to the memory utilization allocation compensation coefficient;

[0036] The threshold of the queue length corresponding to the processing channel is obtained by using the memory utilization upper limit value and the memory utilization lower limit value corresponding to the processing channel.

[0037] Further, obtaining the memory utilization upper limit value and the memory utilization lower limit value corresponding to the processing channel according to the memory utilization allocation compensation coefficient includes:

[0038] Extracting the memory utilization allocation compensation coefficient;

[0039] Extracting a preset baseline memory utilization corresponding to a processing channel;

[0040] Obtaining a memory utilization upper limit value and a memory utilization lower limit value by using the memory utilization allocation compensation coefficient and a preset reference memory utilization corresponding to the processing channel;

[0041] The upper limit of memory utilization and the lower limit of memory utilization are obtained by the following formula:

[0042] ;

[0043] Among them, P up and P down They represent the upper limit and lower limit of memory utilization respectively; P0 represents the preset benchmark memory utilization corresponding to the processing channel; C hmax Indicates the maximum amount of cache data that the processing channel can accommodate; C d Indicates the amount of data processing change corresponding to each unit percentage change in memory utilization; and the unit percentage value range is 0.1%-1.2%; P dIndicates the corresponding value of unit percentage; K c represents the third adjustment coefficient, and the third adjustment coefficient is obtained by the following formula:

[0044] ;

[0045] Among them, C hmax Indicates the maximum amount of cache data that the processing channel can accommodate; C n Indicates the maximum amount of data corresponding to the shard.

[0046] Further, the threshold of the queue length corresponding to the processing channel is obtained by using the upper limit value of the memory utilization and the lower limit value of the memory utilization corresponding to the processing channel, including:

[0047] Extracting the upper limit value of memory utilization corresponding to the processing channel;

[0048] Obtaining a first utilization difference using the memory utilization upper limit corresponding to the processing channel and a preset reference memory utilization corresponding to the processing channel;

[0049] Extracting a lower limit value of memory utilization corresponding to the processing channel;

[0050] Obtaining a second utilization difference value by using a memory utilization lower limit value corresponding to the processing channel and a preset reference memory utilization value corresponding to the processing channel;

[0051] Obtaining a threshold of a queue length corresponding to a processing channel by using the first utilization difference and the second utilization difference in combination with a preset initial queue threshold corresponding to the processing channel;

[0052] The threshold of the queue length corresponding to the processing channel is obtained by the following formula:

[0053] ;

[0054] Among them, L y represents the queue length threshold corresponding to the processing channel; L0 represents the preset initial queue threshold corresponding to the processing channel; P up and P down They represent the upper limit and lower limit of memory utilization respectively; P0 represents the preset benchmark memory utilization corresponding to the processing channel; P z It represents the median of the memory utilization range corresponding to the upper and lower limits of memory utilization; P0 represents the preset benchmark memory utilization corresponding to the processing channel.

[0055] Furthermore, the existing data is synchronized, specifically:

[0056] Clarify the scope of databases and tables that need to be synchronized, establish a monitoring mechanism on the data source system, and capture change events of specified databases and tables and trigger synchronization operation information based on the monitoring mechanism;

[0057] Receives and analyzes the change events of the specified database table and the information that triggers the synchronization operation from the data source system to determine the key information of the data range, synchronization method and target location that needs to be synchronized;

[0058] Establish a connection channel with the data target system and record the key status information of the synchronization start position and timestamp;

[0059] Extract the full data of the specified database table from the data source system and convert the data format, and then load the converted full data into the data target system to complete the full data synchronization;

[0060] Record the end position of full data synchronization. At the same time, based on the monitoring mechanism, capture the incremental change events of the specified library table, and perform incremental data processing according to the incremental change events.

[0061] Furthermore, batch reverse query of primary key IDs includes:

[0062] Monitor the first-level cache queue: Monitor and read the change events in the first-level cache queue corresponding to the database table in the data source system in real time, extract key information from the received change events, and parse the data range and conditions that need to be reversed based on the extraction results;

[0063] Batch reverse query of data sources: Build the corresponding SQL query statement based on the parsed data range and conditions to be reversed, and execute the SQL query to retrieve the primary key ID of the target data structure from the data source system;

[0064] Storing reverse query results: De-duplicate the obtained primary key IDs and store the processed primary key IDs in the storage structure. At the same time, the reverse query results and synchronization status are fed back to the data source system.

[0065] Furthermore, data batch synchronization and timing assurance include:

[0066] Monitor the secondary cache queue: monitor the secondary cache queue corresponding to the library table in the target data system in real time, and read the primary key ID batches from the secondary cache queue, where each batch contains at least one group of primary key IDs that need to be synchronized;

[0067] Record event sequence: When publishing data change events to the first-level cache queue, record the sequence information of each event, such as timestamp or incrementing sequence number;

[0068] Constructing a data synchronization task: obtaining sequence information associated with a primary key ID batch from a queue, constructing a data synchronization task according to the primary key ID batch, and executing the synchronization task according to the sequence information.

[0069] Compared with the prior art, the present invention has the following beneficial effects:

[0070] By capturing the changed data of the data source system in real time and decoupling it with sharded multi-channel two-level cache queues, the delay of data synchronization is greatly reduced and the efficiency of data synchronization is improved. The automatic update of data structure mapping relationships reduces the cost of manually maintaining mapping relationships, reduces the possibility of errors, and improves the accuracy and reliability of data synchronization. In the process of batch data synchronization, by monitoring the shard cache queue of the primary key ID of the secondary target library table, the temporal sequence of the source library table-level data is ensured to be maintained. Batch reverse query of the primary key ID and monitoring of the data structure refresh command make the data synchronization process more robust and able to cope with complex situations such as changes in the data structure of the data source system. The data synchronization process is optimized, making the entire synchronization process clearer and more orderly. BRIEF DESCRIPTION OF THE DRAWINGS

[0071] Figure 1 This is a flow chart of the data synchronization method based on the Debezium embedded engine of the present invention. DETAILED DESCRIPTION

[0072] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0073] In order to solve the technical problems in the existing technology, such as low synchronization efficiency, inability to adapt to various data target systems, and inability to ensure data timing, please refer to Figure 1 , this embodiment provides the following technical solutions:

[0074] A data synchronization method based on Debezium embedded engine includes the following steps:

[0075] Mark mapping relationships and collections: Based on the many-to-one mapping relationship between the data source system and the data target system, mark the data source system library table collection involved in the data structure of the data target system, and mark the column name of the primary key ID mapping relationship between the data source system library table and the data target system library table, as well as the query SQL statement;

[0076] In this embodiment, the data source system includes Mysql and PostgreSQL relational database systems, and the data target system includes Mysql, PostgreSQL, and Elasticsearch data storage systems, all of which are systems for storing, querying, and managing data. Mysql uses structured query language for database management, and PostgreSQL provides more data types and a richer set of functions, especially in processing complex queries and large data sets. Mysql and PostgreSQL in the data target system represent data migration from MySQL and PostgreSQL data source systems to MySQL and PostgreSQL in the data target system, and then migration to Elasticsearch for search and analysis. Elasticsearch is used for full-text search, structured search, and analysis, and is suitable for processing large amounts of data and providing fast search and analysis functions.

[0077] Real-time data capture and cache queue decoupling: The Debezium embedded engine is used to capture data changes in the data source system in real time, and a sharded multi-channel two-level cache queue is used for decoupling, alleviating the back pressure of source data.

[0078] Existing data synchronization: By monitoring the specified library table of the data source system, parsing and executing the synchronization commands of the entire existing data and the specified library table, data synchronization is performed;

[0079] Data structure mapping relationship update: By monitoring the data structure refresh command of the specified library table of the data source system, the data structure mapping relationship between the data source system and the target data system is updated in real time;

[0080] Batch reverse query of primary key ID: By monitoring the first-level source library table shard cache queue, batch reverse query of the data source system SQL, obtain the primary key ID of the data structure of the data target system;

[0081] Data batch synchronization and timing guarantee: By monitoring the secondary target library table primary key ID shard cache queue, ensure that data is synchronized to the target data system in batches while maintaining the timing of source library table-level data.

[0082] In this embodiment, by capturing the changed data of the data source system in real time and decoupling using a sharded multi-channel two-level cache queue, the delay of data synchronization is greatly reduced, the efficiency of data synchronization is improved, and the automatic update of the data structure mapping relationship reduces the cost of manually maintaining the mapping relationship, reduces the possibility of errors, and improves the accuracy and reliability of data synchronization. In the process of batch data synchronization, by monitoring the secondary target library table primary key ID shard cache queue, the timing of the source library table-level data is ensured to be maintained. Batch reverse query of the primary key ID and monitoring of the data structure refresh command make the data synchronization process more robust and able to cope with complex situations such as changes in the data structure of the data source system, optimize the data synchronization process, and make the entire synchronization process clearer and more orderly.

[0083] In this embodiment, the tag mapping relationship and set specifically include:

[0084] Collect the database table structure information of the data source system and the data target system, including table name, field name, field type, etc., analyze the mapping relationship between the data source system and the data target system, and determine the many-to-one mapping relationship between the data source system and the data target system based on the analysis results;

[0085] Based on the many-to-one mapping relationship, the library table structure of the data source system and the data target system is obtained, the library table in the data source system that needs to be synchronized to the data target system is determined, the library table in the data source system is identified and marked, and a set of library tables of the data source system to be synchronized is generated;

[0086] Compare the primary key ID structure of the data source system library table and the data target system library table to determine the primary key ID mapping relationship in the data source system library table set to be synchronized;

[0087] If the primary key ID corresponds directly, directly mark the corresponding column name;

[0088] If the primary key ID does not directly correspond, write a query SQL statement based on the business logic and data relationship, mark the query SQL statement, and store the column name of the primary key ID mapping relationship and the query SQL statement in the mapping relationship table to establish the correct primary key ID mapping relationship when synchronizing data;

[0089] In this embodiment, by marking the mapping relationship and the set step, a clear and accurate mapping relationship and primary key ID mapping relationship can be established. When the library table structure of the data source system or the data target system changes, the mapping relationship table can be easily updated to adapt to the new synchronization requirements, improve the accuracy and efficiency of data synchronization, and facilitate subsequent management and maintenance.

[0090] In this embodiment, real-time data capture and cache queue decoupling are performed by using a sharded multi-channel two-level cache queue, specifically:

[0091] Determine the attributes of the business primary key ID, such as the ID generation rule, range, length, etc., and analyze the database tables and their relationships related to the business primary key ID in the MySQL database, such as master-slave tables, association tables, etc.;

[0092] Debezium embedded engine configuration is performed based on MySQL data source to match the database table to be monitored. At the same time, Debezium captures the change events containing the business primary key ID in the MySQL database in real time;

[0093] Divide the data into multiple shards according to the hash value of the business primary key ID. For example, the shard position can be determined by taking the modulus of the number of shards using the hash value of the ID, and create an independent processing channel for each shard. Each of the processing channels includes a first-level and a second-level cache queue.

[0094] Each processing channel corresponds to an independent first-level cache queue, which is used to store the latest change events of the channel. Each processing channel shares a second-level cache queue, which is used to store historical data and cold data.

[0095] Distribute the change event to the processing channel of the corresponding shard according to the business primary key ID in the change event, and each processing channel processes the change event distributed to the channel;

[0096] When the queue length of the processing channel exceeds the preset threshold, the reception of new events is suspended, the number of consumers is increased, and the processing speed is reduced. The local cache is updated according to business needs from the first-level cache queue of the processing channel.

[0097] In this embodiment, a length threshold is set for the queue of each processing channel. When the queue length exceeds the threshold, the back pressure control mechanism is triggered. According to the queue length and the processing capacity of the system, the exponential backoff strategy and dynamic weight allocation can be used to adjust the processing speed. In the first-level cache queue, the LRU algorithm is used to replace the least frequently used data. When the cache queue is full, the least recently used data is removed from the queue. In the second-level cache queue, the data can be replaced according to the timestamp of the data. The older data can be replaced by the new data. When sharding is performed, it is ensured that the change events of the same business primary key ID are processed in the same channel, avoiding the problem of data inconsistency and realizing the partition orderliness of data synchronization and the ability of concurrent data processing.

[0098] Specifically, the data is divided into multiple shards according to the hash value of the business primary key ID, and an independent processing channel is created for each shard, which also includes:

[0099] Extract the number of shards corresponding to the corresponding shards to which the change event is distributed and the amount of data corresponding to the shards; wherein the number of shards is the same as the number of processing channels;

[0100] The memory utilization allocation compensation coefficient is obtained according to the data volume of the data corresponding to the shard; wherein the memory utilization allocation compensation coefficient is obtained by the following formula:

[0101] ;

[0102] Where f represents the memory utilization allocation compensation coefficient; C p Indicates the amount of data corresponding to the shard; C z Indicates the total amount of data before sharding; K a Represents the first adjustment coefficient; K b represents the second adjustment coefficient; wherein the first adjustment coefficient and the second adjustment coefficient are obtained by the following formula:

[0103] ;

[0104] Where n represents the number of shards; C pi Indicates the amount of data corresponding to the i-th shard; C d Indicates the amount of data processing change corresponding to each unit percentage change in memory utilization; and the unit percentage value range is 0.1%-1.2%;

[0105] Obtaining a memory utilization upper limit value and a memory utilization lower limit value corresponding to the processing channel according to the memory utilization allocation compensation coefficient;

[0106] The threshold of the queue length corresponding to the processing channel is obtained by using the memory utilization upper limit value and the memory utilization lower limit value corresponding to the processing channel.

[0107] The technical effect of the above technical solution is: by sharding the data according to the hash value of the business primary key ID, it can ensure that the data is evenly distributed among multiple processing channels, thereby avoiding overload of a single processing channel and improving the processing capacity of the entire system. The memory utilization allocation compensation coefficient is dynamically calculated according to the amount of data corresponding to each shard, and then the upper and lower limits of the memory utilization of the processing channel are adjusted. This allows the system to dynamically allocate resources according to the current load situation, improving resource utilization efficiency.

[0108] Based on the upper and lower limits of memory utilization, the system can further calculate the threshold of the queue length corresponding to the processing channel. This helps to avoid processing delays caused by too long queues or waste of resources caused by too short queues, thereby optimizing queue management. Since the number of shards and processing channels is the same, the system can increase or decrease the number of shards and processing channels as needed to adapt to application scenarios of different scales and loads. This flexibility and scalability enables the system to adapt to various complex business needs. By dividing the data into multiple shards and assigning each shard to a different processing channel, the system can keep other processing channels running normally when a processing channel fails. This improves the availability and fault tolerance of the system. The system can monitor key indicators such as memory utilization and queue length of each processing channel in real time and perform performance tuning based on these indicators. This helps to ensure that the system always runs in the best state and promptly discover and resolve potential performance issues.

[0109] In summary, the above solution improves the system's processing capability, resource utilization efficiency, flexibility and scalability through technical means such as data sharding, dynamic resource allocation and queue management optimization, while ensuring the high availability and maintainability of the system.

[0110] Specifically, obtaining the memory utilization upper limit value and the memory utilization lower limit value corresponding to the processing channel according to the memory utilization allocation compensation coefficient includes:

[0111] Extracting the memory utilization allocation compensation coefficient;

[0112] Extracting a preset baseline memory utilization corresponding to a processing channel;

[0113] Obtaining a memory utilization upper limit value and a memory utilization lower limit value by using the memory utilization allocation compensation coefficient and a preset reference memory utilization corresponding to the processing channel;

[0114] The upper limit of memory utilization and the lower limit of memory utilization are obtained by the following formula:

[0115] ;

[0116] Among them, P up and P down They represent the upper limit and lower limit of memory utilization respectively; P0 represents the preset benchmark memory utilization corresponding to the processing channel; C hmax Indicates the maximum amount of cache data that the processing channel can accommodate; C d Indicates the amount of data processing change corresponding to each unit percentage change in memory utilization; and the unit percentage value range is 0.1%-1.2%; P d Indicates the corresponding value of unit percentage; Kc represents the third adjustment coefficient, and the third adjustment coefficient is obtained by the following formula:

[0117] ;

[0118] Among them, C hmax Indicates the maximum amount of cache data that the processing channel can accommodate; C n Indicates the maximum amount of data corresponding to the shard.

[0119] The technical effect of the above technical solution is that by calculating the memory utilization allocation compensation coefficient and combining it with the benchmark memory utilization of the processing channel, the upper and lower limits of the memory utilization of each processing channel can be determined more accurately. This helps to ensure the rationality of resource allocation, and neither wastes resources nor causes insufficient resources.

[0120] System stability and reliability: Setting upper and lower limits for memory utilization can ensure that the system has a certain buffer space in memory usage, avoiding system crashes or performance degradation due to excessive or low memory usage. This improves system stability and reliability.

[0121] Since the upper and lower limits of memory utilization are dynamically calculated based on the real-time data volume and system resource conditions, the system can adaptively adjust resource allocation to cope with different loads and data processing requirements. This adaptability and flexibility enables the system to better adapt to complex and changing application scenarios.

[0122] By properly setting the upper and lower limits of memory utilization, the system can more effectively manage the queue length, avoiding processing delays caused by too long queues or waste of resources caused by too short queues. At the same time, reasonable memory utilization allocation also helps to improve the data processing capacity of the processing channel, thereby optimizing the overall performance.

[0123] This technical solution allows the configuration of parameters such as baseline memory utilization and maximum cache data volume according to actual needs, and also supports dynamic expansion of the number of processing channels to adapt to greater data processing needs. This configurability and scalability makes the system more flexible and easier to manage.

[0124] The system can monitor key indicators such as memory utilization and queue length in real time, and perform performance tuning and troubleshooting based on these indicators. This monitorability and maintainability helps ensure that the system always runs in the best state and detects and resolves potential problems in a timely manner.

[0125] In summary, the above technical solution optimizes resource allocation and queue management by dynamically calculating the upper and lower limits of memory utilization, thereby improving the stability, reliability, adaptability and performance of the system. At the same time, it also has the advantages of configurability, scalability, monitorability and maintainability, making the system more flexible and easy to manage.

[0126] Specifically, obtaining a threshold value of a queue length corresponding to a processing channel by using an upper limit value of memory utilization and a lower limit value of memory utilization corresponding to the processing channel includes:

[0127] Extracting the upper limit value of memory utilization corresponding to the processing channel;

[0128] Obtaining a first utilization difference using the memory utilization upper limit corresponding to the processing channel and a preset reference memory utilization corresponding to the processing channel;

[0129] Extracting a lower limit value of memory utilization corresponding to the processing channel;

[0130] Obtaining a second utilization difference value by using a memory utilization lower limit value corresponding to the processing channel and a preset reference memory utilization value corresponding to the processing channel;

[0131] Obtaining a threshold of a queue length corresponding to a processing channel by using the first utilization difference and the second utilization difference in combination with a preset initial queue threshold corresponding to the processing channel;

[0132] The threshold of the queue length corresponding to the processing channel is obtained by the following formula:

[0133] ;

[0134] Among them, L y represents the queue length threshold corresponding to the processing channel; L0 represents the preset initial queue threshold corresponding to the processing channel; P up and P down They represent the upper limit and lower limit of memory utilization respectively; P0 represents the preset benchmark memory utilization corresponding to the processing channel; P z It represents the median of the memory utilization range corresponding to the upper and lower limits of memory utilization; P0 represents the preset benchmark memory utilization corresponding to the processing channel.

[0135] The technical effect of the above technical solution is: by dynamically calculating the threshold of the queue length, the system can reasonably adjust the queue length according to the current memory utilization, thereby achieving more effective management of system resources. This helps to avoid system response delays or excessive resource consumption caused by too long queues, and also prevents waste of resources caused by too short queues. The threshold setting of queue length directly affects the processing power and performance of the system. When the queue length is too short, the processing channel may not be able to fully utilize its processing power; when the queue length is too long, it may cause processing delays. By dynamically calculating the threshold of the queue length, it can ensure that the system can maintain high processing efficiency and performance under different loads.

[0136] Dynamically calculating the queue length threshold ensures that the system maintains stable performance when processing different data volumes. When the amount of data increases, the queue length threshold will also increase accordingly to adapt to higher processing requirements; and when the amount of data decreases, the queue length threshold will also decrease accordingly to avoid resource waste. This adaptive adjustment mechanism helps to improve the stability of the system. The technical solution allows parameters such as baseline memory utilization and initial queue threshold to be configured according to actual needs to adapt to different application scenarios and load conditions. At the same time, since the queue length threshold is dynamically calculated, the system can flexibly respond to various complex and changing situations. The system can monitor key indicators such as memory utilization and queue length in real time, and perform performance tuning and troubleshooting based on these indicators. By dynamically calculating the queue length threshold, it is easier to find problems and take appropriate measures to solve them, thereby improving the monitorability and maintainability of the system.

[0137] In summary, the above technical solution achieves more effective management of system resources, optimizes system performance, improves system stability, and increases the configurability, flexibility, monitorability and maintainability of the system by dynamically calculating the threshold of the queue length corresponding to the processing channel.

[0138] In this embodiment, the existing data synchronization is specifically as follows:

[0139] Clarify the scope of databases and tables that need to be synchronized, establish a monitoring mechanism on the data source system, and capture change events of specified databases and tables and trigger synchronization operation information based on the monitoring mechanism;

[0140] Receives and analyzes the change events of the specified database table and the information that triggers the synchronization operation from the data source system to determine the key information of the data range, synchronization method and target location that needs to be synchronized;

[0141] Establish a connection channel with the data target system to ensure normal access and reading of data, and record the key status information of the synchronization start position and timestamp to facilitate subsequent incremental synchronization or recovery operations;

[0142] Extract the full data of the specified database table from the data source system and convert the data format, and then load the converted full data into the data target system to complete the full data synchronization;

[0143] Record the end position of full data synchronization. At the same time, based on the monitoring mechanism, capture the incremental change events of the specified library table, and perform incremental data processing according to the incremental change events, including data extraction, conversion and loading operations. This not only helps in subsequent incremental synchronization or recovery operations, but also can quickly locate and restore the synchronization status when problems occur.

[0144] In this embodiment, the monitoring mechanism can capture the change events of the specified library table and trigger the synchronization operation information in real time, ensuring a rapid response to changes in the data source system, thereby realizing real-time synchronization of data, determining key information such as the data range, synchronization method and target location that need to be synchronized, providing accurate guidance for subsequent data migration, establishing a stable connection channel with the data target system, ensuring reliable transmission and access of data, reducing risks in the data transmission process, realizing efficient and accurate data migration, and further refining the data migration process from the data source system to the data target system, ensuring the integrity, accuracy and timeliness of the data.

[0145] In this embodiment, batch reverse query of primary key ID and batch synchronization of data and time sequence guarantee specifically include:

[0146] Monitor the first-level cache queue: Monitor and read the change events in the first-level cache queue corresponding to the database table in the data source system in real time, extract key information from the received change events, such as the table name involved, the changed field, the type of change, addition, deletion, modification, etc., and parse the data range and conditions that need to be reversed based on the extraction results;

[0147] Batch reverse query of data sources: Build the corresponding SQL query statement based on the parsed data range and conditions to be reversed, and execute the SQL query to retrieve the primary key ID of the target data structure from the data source system;

[0148] Storing reverse query results: De-duplicate the obtained primary key IDs and store the processed primary key IDs in the storage structure. At the same time, the reverse query results and synchronization status are fed back to the data source system.

[0149] Monitor the secondary cache queue: monitor the secondary cache queue corresponding to the library table in the target data system in real time, and read the primary key ID batches from the secondary cache queue, where each batch contains at least one group of primary key IDs that need to be synchronized;

[0150] Record event sequence: When publishing data change events to the first-level cache queue, record the sequence information of each event, such as timestamp or incrementing sequence number;

[0151] Build data synchronization tasks: obtain the sequence information associated with the primary key ID batch from the queue, and build the data synchronization task based on the primary key ID batch, including the steps of querying the corresponding data from the data source system, converting the data format, loading it to the target system, and executing the synchronization task based on the sequence information.

[0152] In this embodiment, by monitoring the first-level and second-level cache queues, change events and primary key ID batches of the data source system and the target data system can be obtained in real time, avoiding unnecessary database polling, greatly improving the efficiency of data synchronization, and constructing data synchronization tasks based on primary key ID batches, making the data synchronization process more flexible and controllable. At the same time, synchronization tasks are executed according to sequence information to ensure the correct order of data in the target system, effectively improving the efficiency and accuracy of data synchronization, and ensuring the timing of data.

[0153] The above description is only a preferred specific implementation manner of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can make equivalent substitutions and changes according to the technical scheme and inventive concept of the present invention within the technical scope disclosed by the present invention, which should be covered by the protection scope of the present invention.

Claims

1. A data synchronization method based on Debezium embedded engine, characterized in that: The following steps are involved: Mark mapping relationships and collections: Based on the many-to-one mapping relationship between the data source system and the data target system, mark the data source system library table collection involved in the data structure of the data target system, and mark the column name of the primary key ID mapping relationship between the data source system library table and the data target system library table, as well as the query SQL statement; The data source system includes a relational database system of Mysql and PostgreSQL, and the data target system includes a data storage system of Mysql, PostgreSQL and Elasticsearch; Real-time data capture and cache queue decoupling: The Debezium embedded engine is used to capture data changes in the data source system in real time, and a sharded multi-channel two-level cache queue is used for decoupling; Existing data synchronization: By monitoring the specified library table of the data source system, parsing and executing the synchronization commands of the entire existing data and the specified library table, data synchronization is performed; Data structure mapping relationship update: By monitoring the data structure refresh command of the specified library table of the data source system, the data structure mapping relationship between the data source system and the target data system is updated in real time; Batch reverse query of primary key ID: By monitoring the first-level source library table shard cache queue, batch reverse query of the data source system SQL, obtain the primary key ID of the data structure of the data target system; Data batch synchronization and timing guarantee: By monitoring the secondary target library table primary key ID shard cache queue, ensure that data is synchronized to the target data system in batches while maintaining the timing of source library table-level data.

2. A data synchronization method based on Debezium embedded engine as claimed in claim 1, characterized in that: The tag mapping relationship and the tag data source system library table set in the set specifically include: Collect the database table structure information of the data source system and the data target system, analyze the mapping relationship between the data source system and the data target system, and determine the many-to-one mapping relationship between the data source system and the data target system based on the analysis results; Based on the many-to-one mapping relationship, the library table structure of the data source system and the data target system is obtained, the library tables in the data source system that need to be synchronized to the data target system are determined, the library tables in the data source system are identified and marked, and a set of library tables of the data source system to be synchronized is generated.

3. A data synchronization method based on Debezium embedded engine as claimed in claim 2, characterized in that: The tag mapping relationship and the column name of the tag primary key ID mapping relationship in the collection and the query SQL statement specifically include: Compare the primary key ID structure of the data source system library table and the data target system library table to determine the primary key ID mapping relationship in the data source system library table set to be synchronized; If the primary key ID corresponds directly, directly mark the corresponding column name; If the primary key ID does not correspond directly, a query SQL statement is written according to the business logic and data relationship, and the query SQL statement is marked, and the column name of the primary key ID mapping relationship and the query SQL statement are stored in the mapping relationship table.

4. A data synchronization method based on Debezium embedded engine as claimed in claim 3, characterized in that: In the decoupling of real-time data capture and cache queue, a sharded multi-channel two-level cache queue is used for decoupling, specifically: Determine the attributes of the business primary key ID, and analyze the database tables and their relationships related to the business primary key ID in the MySQL database; Debezium embedded engine configuration is performed based on MySQL data source to match the database table to be monitored. At the same time, Debezium captures the change events containing the business primary key ID in the MySQL database in real time; Divide the data into multiple shards according to the hash value of the business primary key ID, and create an independent processing channel for each shard, each of which includes a first-level and a second-level cache queue; Each processing channel corresponds to an independent first-level cache queue, which is used to store the latest change events of the channel. Each processing channel shares a second-level cache queue, which is used to store historical data and cold data. Distribute the change event to the processing channel of the corresponding shard according to the business primary key ID in the change event, and each processing channel processes the change event distributed to the channel; When the queue length of the processing channel exceeds the preset threshold, the reception of new events is suspended, the number of consumers is increased, and the processing speed is reduced. The local cache is updated according to business needs from the first-level cache queue of the processing channel.

5. A data synchronization method based on Debezium embedded engine as claimed in claim 4, characterized in that: Divide the data into multiple shards according to the hash value of the business primary key ID, and create an independent processing channel for each shard, including: Extract the number of shards corresponding to the corresponding shards to which the change event is distributed and the amount of data corresponding to the shards; wherein the number of shards is the same as the number of processing channels; The memory utilization allocation compensation coefficient is obtained according to the data volume of the data corresponding to the shard; wherein the memory utilization allocation compensation coefficient is obtained by the following formula: ; Where f represents the memory utilization allocation compensation coefficient; C p Indicates the amount of data corresponding to the shard; C z Indicates the total amount of data before sharding; K a Represents the first adjustment coefficient; K b represents the second adjustment coefficient; wherein the first adjustment coefficient and the second adjustment coefficient are obtained by the following formula: ; Where n represents the number of shards; C pi Indicates the amount of data corresponding to the i-th shard; C d Indicates the amount of data processing change corresponding to each unit percentage change in memory utilization; and the unit percentage value range is 0.1%-1.2%; Obtaining a memory utilization upper limit value and a memory utilization lower limit value corresponding to the processing channel according to the memory utilization allocation compensation coefficient; The threshold of the queue length corresponding to the processing channel is obtained by using the memory utilization upper limit value and the memory utilization lower limit value corresponding to the processing channel.

6. A data synchronization method based on Debezium embedded engine as claimed in claim 5, characterized in that: Obtaining a memory utilization upper limit value and a memory utilization lower limit value corresponding to the processing channel according to the memory utilization allocation compensation coefficient, including: Extracting the memory utilization allocation compensation coefficient; Extracting a preset baseline memory utilization corresponding to a processing channel; Obtaining a memory utilization upper limit value and a memory utilization lower limit value by using the memory utilization allocation compensation coefficient and a preset reference memory utilization corresponding to the processing channel; The upper limit of memory utilization and the lower limit of memory utilization are obtained by the following formula: ; Among them, P up and P down They represent the upper limit and lower limit of memory utilization respectively; P0 represents the preset benchmark memory utilization corresponding to the processing channel; C hmax Indicates the maximum amount of cache data that the processing channel can accommodate; C d Indicates the amount of data processing change corresponding to each unit percentage change in memory utilization; and the unit percentage value range is 0.1%-1.2%; P d Indicates the corresponding value of unit percentage; K c represents the third adjustment coefficient, and the third adjustment coefficient is obtained by the following formula: ; Among them, C hmax Indicates the maximum amount of cache data that the processing channel can accommodate; C n Indicates the maximum amount of data corresponding to the shard.

7. A data synchronization method based on Debezium embedded engine as claimed in claim 6, characterized in that: Obtaining a threshold value of a queue length corresponding to the processing channel by using an upper limit value of memory utilization and a lower limit value of memory utilization corresponding to the processing channel, including: Extracting the upper limit value of memory utilization corresponding to the processing channel; Obtaining a first utilization difference using the memory utilization upper limit corresponding to the processing channel and a preset reference memory utilization corresponding to the processing channel; Extracting a lower limit value of memory utilization corresponding to the processing channel; Obtaining a second utilization difference value by using a memory utilization lower limit value corresponding to the processing channel and a preset reference memory utilization value corresponding to the processing channel; Obtaining a threshold of a queue length corresponding to a processing channel by using the first utilization difference and the second utilization difference in combination with a preset initial queue threshold corresponding to the processing channel; The threshold of the queue length corresponding to the processing channel is obtained by the following formula: ; Among them, L y represents the queue length threshold corresponding to the processing channel; L0 represents the preset initial queue threshold corresponding to the processing channel; P up and P down They represent the upper limit and lower limit of memory utilization respectively; P0 represents the preset benchmark memory utilization corresponding to the processing channel; P z It represents the median of the memory utilization range corresponding to the upper and lower limits of memory utilization; P0 represents the preset benchmark memory utilization corresponding to the processing channel.

8. A data synchronization method based on Debezium embedded engine as claimed in claim 7, characterized in that: Existing data synchronization, specifically: Clarify the scope of databases and tables that need to be synchronized, establish a monitoring mechanism on the data source system, and capture change events of specified databases and tables and trigger synchronization operation information based on the monitoring mechanism; Receives and analyzes the change events of the specified database table and the information that triggers the synchronization operation from the data source system to determine the key information of the data range, synchronization method and target location that needs to be synchronized; Establish a connection channel with the data target system and record the key status information of the synchronization start position and timestamp; Extract the full data of the specified database table from the data source system and convert the data format, and then load the converted full data into the data target system to complete the full data synchronization; Record the end position of full data synchronization. At the same time, based on the monitoring mechanism, capture the incremental change events of the specified library table, and perform incremental data processing according to the incremental change events.

9. A data synchronization method based on Debezium embedded engine as claimed in claim 8, characterized in that: Batch reverse query of primary key ID, including: Monitor the first-level cache queue: Monitor and read the change events in the first-level cache queue corresponding to the database table in the data source system in real time, extract key information from the received change events, and parse the data range and conditions that need to be reversed based on the extraction results; Batch reverse query of data sources: Build the corresponding SQL query statement based on the parsed data range and conditions to be reversed, and execute the SQL query to retrieve the primary key ID of the target data structure from the data source system; Storing reverse query results: De-duplicate the obtained primary key IDs and store the processed primary key IDs in the storage structure. At the same time, the reverse query results and synchronization status are fed back to the data source system.

10. A data synchronization method based on Debezium embedded engine as claimed in claim 9, characterized in that: Data batch synchronization and timing guarantee, including: Monitor the secondary cache queue: monitor the secondary cache queue corresponding to the library table in the target data system in real time, and read the primary key ID batches from the secondary cache queue, where each batch contains at least one set of primary key IDs that need to be synchronized; Record event sequence: When publishing data change events to the first-level cache queue, record the sequence information of each event, such as timestamp or incrementing sequence number; Constructing a data synchronization task: obtaining sequence information associated with a primary key ID batch from a queue, constructing a data synchronization task according to the primary key ID batch, and executing the synchronization task according to the sequence information.