Self-adaptive performance optimization method, device and equipment for distributed database

Abstract

The invention provides an adaptive performance optimization method, device and equipment for a distributed database, and relates to the technical field of database system optimization, and the method comprises the steps: for any database node, obtaining first performance data of the database node and second performance data of each fragment in each database contained in the database node; for any fragment in any database contained in the database node, calculating a popularity score of the fragment according to the first performance data of the database node and the second performance data of the fragment; according to the popularity score of the fragment and a configured popularity classification rule, determining a popularity classification result of the fragment; according to a configured performance optimization rule, determining a target optimization strategy corresponding to the popularity score and the popularity classification result; and based on the target optimization strategy, carrying out adaptive performance optimization on the fragments. According to the method, the overall resource utilization efficiency and service performance of the database system are remarkably improved.

Description

Technical Field

[0001] The present application relates to the technical field of database system optimization, and in particular to a method, apparatus and device for adaptive performance optimization of a distributed database. Background Art

[0002] With the development of big data technology, distributed database systems have become core infrastructure for supporting large-scale, highly concurrent businesses. GBase 8s, a domestically produced database supporting massively parallel processing, is widely used in finance, telecommunications, e-commerce, and other fields. In real-world applications, database performance bottlenecks often stem from uneven data distribution and skewed node loads. Traditional database systems typically employ static data sharding strategies, such as initial sharding based on primary key hashing or range sharding. These strategies can meet certain load balancing requirements during initial deployment, but as data volume, access patterns, and business logic evolve, static strategies often face the following challenges: First, hot data can lead to excessive access frequencies on some shards, straining resources on some nodes; second, cold data can permanently occupy significant storage and resources, yet utilization is extremely low; third, node load cannot be dynamically migrated or scheduled, leading to overloaded nodes on some nodes while others remain idle; fourth, the sharding granularity is fixed and lacks flexibility, making it difficult to adjust to business dynamics, resulting in wasted resources and reduced query efficiency; the inability to implement finer-grained sharding for hot data areas limits the scope for query performance optimization; and fifth, the lack of flexible, rule-driven mechanisms. Although some existing technologies have introduced intelligent prediction methods such as machine learning or neural networks to optimize data distribution and load sharding, such methods are usually complex, black-box, have high debugging and implementation costs, are difficult to explain and maintain, and are not suitable for financial or government systems that have extremely high requirements for security and controllability.

[0003] Existing technology 1 proposes an Elasticsearch-based index sharding optimization mechanism for full-text retrieval databases. Although sharding optimization is also performed, its sharding optimization focuses on adjusting the index structure and is difficult to apply to row-level heat identification and sharding refinement of structured data tables. In addition, this solution relies on index reconstruction when adjusting sharding, requiring the suspension of writing or adjustment of routing rules, and cannot perform imperceptible and lock-free dynamic adjustments at runtime.

[0004] Existing technology 2 discloses a hot and cold data perception and automatic sharding adjustment strategy under a distributed data storage architecture for object-oriented or key-value data. Its optimization logic is based on historical access logs and static heat models. It lacks linkage with the internal execution engine of the database, and does not form a direct coupling relationship between the policy instruction system and the execution action. Summary of the Invention

[0005] The purpose of the embodiments of the present application is to provide a method, device and equipment for adaptive performance optimization of a distributed database, so as to solve the above-mentioned problems existing in the prior art and dynamically optimize the data distribution structure and resource utilization efficiency in large-scale data access scenarios.

[0006] In a first aspect, a method for adaptive performance optimization of a distributed database is provided, which is applied to a distributed database system, wherein the system includes: multiple database nodes, each database node includes at least one database, and each database includes at least one shard. The method may include: For any database node, obtain first performance data of the database node and second performance data of each shard in each database included in the database node; For any shard in any database included in the database node, calculate a heat score of the shard based on the first performance data of the database node and the second performance data of the shard; Determine a heat classification result for the shard based on the heat score of the shard and the configured heat classification rules; Determine the target optimization strategy corresponding to the popularity score and the popularity classification result according to the configured performance optimization rules; Based on the target optimization strategy, adaptive performance optimization is performed on the shards.

[0007] In an optional implementation, the method for obtaining the first performance data and the second performance data includes: Obtaining a system statistics table of the database node by using the configured performance detection virtual table corresponding to the database node; According to the configured tuple generation logic, the system statistical information table is aggregated and analyzed to obtain the first performance data and the second performance data.

[0008] In an optional implementation, the first performance data includes: system load characteristics and business access patterns; the second performance data includes: number of data rows and access frequency; the popularity classification rule includes: first threshold and second threshold; Calculating a heat score of the shard according to the first performance data of the database node and the second performance data of the shard, including: Determining a response time weight and an access density coefficient according to the system load characteristics and the service access pattern; Calculating a heat score of the shard based on the access frequency, the response time weight, the number of data rows, and the access density coefficient; Determining a heat classification result for the shard based on the heat score of the shard and the configured heat classification rules includes: If the popularity score of the shard is greater than the first threshold, the shard is a hotspot shard; If the popularity score of the shard is not greater than the first threshold and greater than the second threshold, the shard is a normal shard; If the heat score of the shard is not greater than the second threshold, the shard is a cold data shard.

[0009] In an optional implementation, determining a target optimization strategy corresponding to the popularity score and the popularity classification result according to a configured performance optimization rule includes: When the heat score is greater than a configured third threshold and the shard is a hotspot shard, the target optimization strategy is a shard splitting strategy; When the heat score is less than a configured fourth threshold and the shard is a cold data shard, the target optimization strategy is a shard merging strategy.

[0010] In an optional implementation, the database is used to store at least one logical table; the logical table is stored on at least two shards; After determining the heat classification result of the shard, the method further includes: For any logical table in the database, obtain the popularity scores of different shards corresponding to the logical table; The shard with the highest popularity score is used as the first shard corresponding to the logical table; the shard with the lowest popularity score is used as the second shard corresponding to the logical table; If the ratio of the heat scores of the first shard and the second shard is greater than a configured fifth threshold, the logical table is determined to be an access load tilted logical table.

[0011] In an optional implementation, after determining the logic table as an access load tilting logic table, the method further includes: Optimize the shards whose heat scores exceed a sixth threshold among the different shards corresponding to the access load tilt logic table as shards to be optimized; Adopting a shard splitting strategy to perform adaptive performance optimization on the shard to be optimized, thereby obtaining multiple optimized shards; Determining at least one target database node corresponding to the optimized shard based on the first performance data of different database nodes and the second performance data of each shard in each database contained in the corresponding database node; The optimized shards are stored on the corresponding target database nodes.

[0012] In an optional implementation, the first performance data further includes: node load; The method further comprises: If the node load of any database node is greater than a configured seventh threshold and the system includes a database node whose node load is less than a configured eighth threshold, the database node whose node load is greater than the configured seventh threshold is used as the first node, and the database node whose node load is less than the configured eighth threshold is used as the second node; The shards in the first node whose access frequency is higher than the configured ninth threshold are migrated to the second node.

[0013] In a second aspect, a distributed database adaptive performance optimization device is provided, which is applied to a distributed database system. The system includes: multiple database nodes, each database node includes at least one database, and each database includes at least one shard. The device may include: an acquiring unit, configured to acquire, for any database node, first performance data of the database node and second performance data of each shard in each database included in the database node; a calculation unit, configured to calculate, for any shard in any database included in the database node, a heat score of the shard based on the first performance data of the database node and the second performance data of the shard; a determination unit, configured to determine a heat classification result of the shard based on the heat score of the shard and a configured heat classification rule; and determine a target optimization strategy corresponding to the heat score and the heat classification result based on a configured performance optimization rule; The optimization unit is used to perform adaptive performance optimization on the shards based on the target optimization strategy.

[0014] In a third aspect, an electronic device is provided, the electronic device including a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other via the communication bus; Memory for storing computer programs; The processor is configured to implement any of the method steps described in the first aspect when executing a program stored in the memory.

[0015] In a fourth aspect, a computer-readable storage medium is provided, wherein a computer program is stored in the computer-readable storage medium, and when the computer program is executed by a processor, any of the method steps described in the first aspect is implemented.

[0016] This application analyzes the collected first performance data of database nodes and the second performance data of shards to obtain shard popularity scores and popularity classification results. Based on the popularity scores and popularity classification results, the application matches corresponding adaptive performance optimization strategies to perform adaptive performance optimization on each shard, significantly improving the resource utilization and query response performance of large-scale distributed database systems under dynamic load changes. This application has the advantages of controllable rules, explainable behavior, fast response speed, and low system complexity.

[0017] Compared with the existing technology 1, this application is aimed at structured data query-intensive business scenarios. Through the database kernel-level pseudo-table mechanism, it collects running performance indicators in real time and completes multi-dimensional heat modeling without the need for persistent storage on disk. It has the advantages of high real-time performance and system-level embedding.

[0018] Compared with the existing technology 2, this application forms a standardized and combinable optimization strategy chain by binding the access pattern analysis results with predefined operation codes (OP codes) and rule bases, which not only ensures the flexibility of the strategy but also has structured maintainability. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following is a brief introduction to the drawings required for use in the embodiments of the present application. 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 relevant drawings can be obtained based on these drawings without creative work.

[0020] Figure 1 An architecture diagram of a distributed database adaptive performance optimization system provided in an embodiment of the present application; Figure 2 A schematic diagram of a flow chart of a method for adaptive performance optimization of a distributed database provided in an embodiment of the present application; Figure 3 A schematic diagram of the structure of a distributed database adaptive performance optimization device provided in an embodiment of the present application; Figure 4 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0021] The following, in conjunction with the accompanying drawings, provides a clear and complete description of the technical solutions in the embodiments of this application. Obviously, the described embodiments represent only a portion of the embodiments of this application and do not constitute a complete set of embodiments. All other embodiments derived by persons of ordinary skill in the art based on the embodiments of this application without inventive effort are intended to fall within the scope of protection of this application. Unless otherwise defined, technical or scientific terms used in this application should have the same ordinary meanings as those understood by persons of ordinary skill in the art. The terms "first," "second," and similar expressions used in this application do not denote any order, quantity, or importance; they are merely used to distinguish between different components. Terms such as "include" or "comprising" mean that the element or object preceding the term includes the elements or objects listed after the term, and their equivalents, without excluding other elements or objects. Terms such as "connect," "couple," or "connected" are not limited to physical or mechanical connections but may include electrical connections, whether direct or indirect. Terms such as "upper," "lower," "left," and "right" are used solely to indicate relative positional relationships. When the absolute position of the described objects changes, the relative positional relationships may also change accordingly.

[0022] The distributed database adaptive performance optimization method provided in the embodiment of the present application can be applied in Figure 1 In the system architecture shown, the system can be a GBase 8s database or other relational database system with a parallel computing architecture; the system includes: multiple database nodes; each database node is an independent server (or virtual machine / container) with CPU, memory, disk and network resources for actually storing data shards and executing SQL queries; multiple database nodes perform data synchronization, load balancing and failover through inter-node communication (such as Gossip protocol or RPC); different nodes can be deployed in different geographical areas to isolate nodes; each database node includes one or more databases, and each database can store multiple logical tables; the data of each logical table is stored on different shards according to the shard key, and each shard stores a subset of the data; shards are horizontally split data blocks; such as Figure 1 As shown, the system may include: a performance monitoring module, a heat analysis module, a rule engine module and an optimization module; wherein the performance monitoring module is in communication connection with the heat analysis module; the heat analysis module is in communication connection with the rule engine module; the rule engine module is in communication connection with the optimization module; A performance monitoring module is used to obtain first performance data of each database node in the system and second performance data of each shard in each database contained in each database node; A heat analysis module is used to calculate the heat score of each shard based on the first performance data of each database node and the second performance data of each shard in each database contained in each database node; and determine the heat classification result of each shard based on the heat score of each shard and the configured heat classification rules; The rule engine module is used to determine the target optimization strategy corresponding to the popularity score and popularity classification results based on the configured performance optimization rules; The optimization module is used to perform adaptive performance optimization on shards based on target optimization strategies.

[0023] In some embodiments of the present application, the system further comprises: a feedback and optimization module; The feedback and optimization module is used to compare the first performance data and the second performance data before and after the adaptive performance optimization to evaluate the adaptive performance optimization effect; adjust the optimization strategy or cancel the previous optimization according to the adaptive performance optimization effect.

[0024] The preferred embodiments of the present application are described below in conjunction with the drawings in the specification. It should be understood that the preferred embodiments described herein are only used to illustrate and explain the present application and are not used to limit the present application. In addition, the embodiments and features in the embodiments of the present application can be combined with each other if there is no conflict.

[0025] Figure 2 The following is a flow chart of a method for optimizing the adaptive performance of a distributed database provided in an embodiment of the present application. Figure 2 As shown, the method may include: Step S210: For any database node, obtain first performance data of the database node and second performance data of each shard in each database included in the database node.

[0026] In a specific embodiment, a method for obtaining the first performance data and the second performance data includes: The system statistics table of the database node is obtained by using the performance detection virtual table corresponding to the configured database node. The performance detection virtual table is built based on the pseudo-table mechanism provided by the GBase 8s database and is used to dynamically collect and summarize the operating status and load information of each database shard, and periodically refresh it according to the preset sampling interval during the database operation. The table name of the performance detection virtual table can be v_monitor_stats. The performance detection virtual table is essentially a non-persistent data structure, dynamically generated only during the query execution cycle, and does not occupy physical storage resources. The performance detection virtual table is implicitly created during database initialization. During the query execution phase, the tuple generation logic (Tuple Scan Function) implemented within the database is called to return system metadata or runtime memory data in real time to dynamically summarize performance indicator data from other system tables. The collection results are periodically updated during the database operation, serving as a unified information interface for subsequent access pattern analysis and rule judgment. Unlike traditional temporary tables, the life cycle of the performance detection virtual table is limited to a single query cycle, does not need to be explicitly created or deleted, and the data content changes in real time with the query. Based on the configured tuple generation logic, the system statistics table is aggregated and analyzed to obtain first performance data and second performance data. The first performance data includes: node resource utilization, node load, system load characteristics, service access mode, and operating status parameters (CPU, memory, I / O); the second performance data includes: number of shard data rows, shard data size, and access frequency. The system statistics table includes a query log table, a node resource status table, a shard distribution information table, a data rule table, and a table definition information table. Specifically, based on the configured tuple generation logic, the system statistical information table (sysresusage) collected during database operation is aggregated and analyzed, and the system resource usage of each database node is dynamically monitored to obtain first performance data and second performance data, including: Perform statistical analysis on the access frequency and response performance of each logical table and its shards in the database system within a certain time window, and build query aggregation logic by correlating query logs with shard metadata. This query aggregation logic is based on the database operation log and dynamically calculates the access characteristics of each shard. The specific processing logic is as follows: extract query records within a specified time range (for example, the last 15 minutes) from the database query log table (sysquerylog), associate them with the shard metadata table (syssegments), and connect the query records with the corresponding shard information based on the logical table name field; perform query aggregation based on the logical table name, shard number and the node where it is located. Group statistics to generate performance indicators; wherein, performance indicators may include: access_freq: used to indicate the total number of times each shard is accessed within a specified time period, that is, the number of times each shard is hit in the statistical query log; avg_runtime: used to indicate the average execution time of the query carried by the corresponding shard, reflecting the response performance of the shard; snapshot_time: used to indicate the time snapshot corresponding to this group of statistical data, usually recorded as the current query execution time point, for subsequent timing analysis and comparison; the results of the above-mentioned aggregate analysis will be filled in the performance detection virtual table structure in real time for heat score calculation, access pattern recognition and policy rule matching. Through the above-mentioned embodiments, the present application can perceive the dynamic access status of each logical table shard in the database in real time without relying on persistent storage, effectively supporting high-frequency decision-making and resource scheduling; A pre-configured tuple generation mechanism is used to periodically filter and process the system operation sampling data within the last 5 minutes to obtain the first performance data and the second performance data; among them, by extracting all resource sampling records with a sampling time greater than the current time minus 5 minutes and analyzing them, the operation status parameters are obtained, including: CPU usage (cpu_usage): used to indicate the average CPU occupancy ratio of the database node in the specified time window, used to measure the usage pressure of the processor resources; I / O usage (io_usage): used to indicate the resource occupancy of the disk or other input and output devices, used to evaluate the read and write pressure of the storage system; memory usage (mem_usage): used to reflect the current memory load level of the database node, which is a direct indicator of whether the memory resources are tight; the obtained operation status parameters are presented in a structured manner in the performance detection virtual table, and shard heat modeling and optimization strategy formulation are performed; this application does not require data persistence operations, and can achieve high-frequency performance perception through a lightweight scanning mechanism to ensure the real-time and accuracy of system scheduling.

[0027] In some embodiments of the present application, the fields of the performance detection virtual table may include: tabname: used to represent the name of the logical table, the data type is a variable-length string (VARCHAR), and the maximum length is 128 characters; partnum: used to represent the shard number to which the current record belongs, the type is an integer; nodeid: used to represent the database node number where the current shard is located, the type is an integer; access_freq: used to represent the frequency with which the shard is accessed per unit time, the type is an integer; avg_runtime: used to represent the average response time of the query corresponding to the shard, the type is a floating-point number; cpu_load: used to represent the real-time load of the node CPU, the type is a floating-point number; io_load: used to represent the utilization rate of the node I / O resources, the type is a floating-point number; mem_usage: used to represent the proportion of node memory occupied, the type is a floating-point number; row_count: used to represent the number of data rows stored in the current shard, the type is an integer; data_size: used to represent the physical storage space occupied by the current shard, the type is an integer; snapshot_time: used to represent the snapshot generation time of this record, the type is a timestamp, accurate to seconds. In the performance monitoring virtual table, each record dynamically reflects the access load, resource consumption, and data size of a shard at a certain point in time, thus forming a real-time monitoring snapshot. The pseudo-table architecture adopted by the performance detection virtual table of this application has the following advantages: strong dynamics: the latest data snapshot is generated for each query, and no persistent storage is required, which is suitable for frequent scheduling and fast rule matching; low resource overhead: resident in memory or generated through lightweight scanning functions, greatly reducing the storage and I / O burden; one table for multiple uses: subsequent access pattern analysis, rule evaluation, load scheduling and other processes can be directly based on the pseudo-table operation, avoiding complex multi-table JOIN and improving query performance; clear lifecycle control: automatically generated when the query starts, and resources are released when the query ends, reducing the system burden; good distributed support: each node can independently execute local performance data filling, and automatically aggregate through the global view; the performance detection virtual table designed by this application in combination with the pseudo-table mechanism to collect performance data of database nodes and shards can obtain database operation status information efficiently, in real time and in a structured manner, providing an accurate, lightweight and scalable data foundation for the subsequent shard optimization and load balancing strategy execution, thereby significantly improving the system's adaptive adjustment capabilities and overall operating efficiency.

[0028] This application introduces a pseudo-table mechanism as a unified summary interface for performance monitoring data, which can achieve high-frequency, real-time, and structured collection of database operation status with extremely low resource overhead, avoiding the I / O bottlenecks and management complexity problems caused by large-scale persistent storage in traditional monitoring methods, and greatly improving the timeliness and lightness of monitoring data.

[0029] Step S220: For any shard in any database included in the database node, calculate the heat score of the shard based on the first performance data of the database node and the second performance data of the shard.

[0030] In a specific implementation, the heat score of a shard is calculated based on the first performance data of the database node and the second performance data of the shard, including: Determine the response time weight and access intensity coefficient based on system load characteristics and business access patterns. Calculate the heat score of the shard based on access frequency, response time weight, number of data rows, and access intensity coefficient. The heat score (Heat_Score) is calculated using the following formula: Heat score = (access frequency × response time weight) + (number of data rows × access intensity coefficient). The response time weight and access intensity coefficient are determined based on system load characteristics and business access patterns to ensure that the heat score accurately reflects the actual load pressure.

[0031] Step S230: Determine the heat classification result of the shard based on the heat score of the shard and the configured heat classification rule; and determine the target optimization strategy corresponding to the heat score and heat classification result based on the configured performance optimization rule.

[0032] In the specific implementation, all shards are divided into three categories based on heat scores: hot shards, normal shards, and cold data shards. The heat classification result of the shard is determined based on the heat score of the shard and the configured heat classification rules, including: If the heat score of the shard is greater than the first threshold, the shard is a hot data shard; if the heat score of the shard is not greater than the first threshold and is greater than the second threshold, the shard is a normal shard; if the heat score of the shard is not greater than the second threshold, the shard is a cold data shard; wherein the heat classification rule includes: a first threshold and a second threshold.

[0033] In some embodiments of the present application, after determining the heat classification result of the shard, the method further includes: For any logical table in the database, obtain the heat scores of different shards corresponding to the logical table; use the shard with the largest heat score as the first shard corresponding to the logical table; use the shard with the smallest heat score as the second shard corresponding to the logical table; if the ratio of the heat scores of the first shard and the second shard is greater than the configured fifth threshold, the logical table is determined to be an access load-skewed logical table.

[0034] Specifically, when any logical table is determined to be an access load tilt logical table, it is recorded in a temporary table used to temporarily store relevant analysis results. Each record in the temporary table corresponds to an analysis snapshot of a shard, including key indicators such as the shard's heat score, heat classification results, load tilt mark, and node information. The life cycle of the temporary table is limited to the current query or analysis cycle, and the name can be t_shard_access_profile. The structure of the temporary table may include: tabname: used to represent the name of the logical table, the type is a variable-length string with a maximum length of 128 characters; partnum: used to represent the shard number corresponding to the current record, used to uniquely identify the shard, and the type is an integer; nodeid: used to represent the database node number where the shard is located, the type is an integer; heat_scor e: indicates the heat score calculated for the shard during the current cycle. This is a floating-point number that reflects the access intensity and resource consumption of the shard. access_level: indicates the classification level of the shard based on the heat score. This is a string with possible values: "HOT" (hot shard), "NORMAL" (normal shard), and "COLD" (cold data shard). is_skewed: a Boolean field that marks whether the current shard is a member of a logical table with skewed access load. If TRUE, this indicates that the shard belongs to a logical table with highly concentrated access distribution and requires optimization. snapshot_time: indicates the time snapshot corresponding to the above analysis results. This is used to track the trend of the shard's heat status over time. This is a timestamp with accuracy to seconds. The temporary table of this application serves as an intermediate analysis result carrier, supporting the rule engine's further processing of tasks such as heat classification, skew identification, and optimization strategy decision-making. It also provides structured input for subsequent scheduling modules to facilitate the execution of optimization actions and maintenance of system operation logs. The is_skewed field of the shards that access the load skew logic table in the temporary table is uniformly marked as TRUE, otherwise marked as FALSE, so that the subsequent rule engine module can identify and process the relevant shards in the load skew logic table based on this. Through this structured output, this application provides an efficient and standardized data interface, avoiding the overhead of repeated parsing and statistics in the subsequent decision-making stage.

[0035] In specific implementations, performance optimization rules include different popularity scores, different popularity classification results, and corresponding target optimization strategies. Based on the configured performance optimization rules, the target optimization strategy corresponding to the popularity score and popularity classification result is determined, including: When the heat score is greater than the configured third threshold and the shard is a hot shard, the target optimization strategy is the shard split strategy; the third threshold can be 80; When the heat score is less than the configured fourth threshold and the shard is a cold data shard, the target optimization strategy is the shard merge strategy.

[0036] In some embodiments of the present application, the performance optimization rule is a structured rule configuration table used to store and maintain various performance optimization rules; the table name can be rebalance_rule_set, which is a standard persistent data table in the database. The performance optimization rule is used to parameterize and manage the execution priorities of various optimization actions; in the actual workflow, the system first periodically scans all shard records; for each shard record, according to the heat score and heat classification results, the performance optimization rules in the system rule library are matched one by one.

[0037] In some embodiments of the present application, the fields of the performance optimization rule may include: Rule number rule_id is the unique primary key of the rule and its type is integer; Operation instruction code op_code is used to identify the optimization operation type corresponding to the rule. The type is a string (up to 32 characters). For example, HOT_SPLIT means splitting a hot shard, COLD_MERGE means merging cold data shards, and HOT_MIGRATE means migrating a hot shard. Action type action_type: used to indicate the execution category of the optimization rule, the type is a string; typical values ​​include SPLIT, MERGE, and MIGRATE; the action type is the corresponding target optimization strategy; Execution parameters action_params: used to define detailed operation parameters, the type is a variable-length string (up to 512 characters); for example, split granularity, target node selection criteria, trigger threshold, etc. Rule priority: The type is an integer. The smaller the value, the higher the priority. When multiple applicable rules exist, the system will execute the high-priority rule first. Enable flag enable_flag: Boolean type, used to identify whether the rule is enabled; if it is TRUE, the rule is valid in the rule engine; Rule creation time create_time: The type is a timestamp, used to record the definition time of the rule; Rule last update time update_time: The type is a timestamp, used to support dynamic adjustment and version control of rules.

[0038] Through the performance optimization rules of this application, a modular, configurable and combinable management method for performance optimization strategies is achieved.

[0039] This application dynamically extracts access popularity characteristics and load skew for each shard through comprehensive calculations of multi-dimensional indicators such as query frequency, response time, and node load. This allows for timely identification of hotspot data, cold data, and access concentration risks, providing accurate and granular data support for subsequent decision-making. Compared to traditional methods that rely on fixed thresholds or manual interpretation, this application enables intelligent and dynamic identification of access behavior, significantly reducing the need for human intervention.

[0040] Step S240: Based on the target optimization strategy, adaptive performance optimization is performed on the shard.

[0041] In the specific implementation, based on the target optimization strategy, the shard is adaptively optimized for performance, including: When the target optimization strategy is the shard splitting strategy, adaptive performance optimization is performed on the shard, including: Get the current sharding strategy and sharding parameters for the shard; maintain the current sharding strategy unchanged and optimize the sharding parameters under the current sharding strategy; sharding strategies include primary key range distribution and hash distribution; when the sharding strategy is primary key range distribution, the sharding parameters include primary key range distance, and sharding parameter optimization includes: equal distance division by primary key range: that is, according to the minimum and maximum primary key values ​​of the shard, it is divided into several sub-intervals according to the set granularity, and a new sub-shard is created for each sub-interval. The system adopts a batch scanning and migration strategy when dividing data to avoid node pressure fluctuations caused by one-time large-scale data operations; when the sharding strategy is hash distribution, the sharding parameters include the number of hash buckets, and sharding parameter optimization includes: increasing the number of hash buckets, redistributing sub-shards, and redistributing data ownership according to the new hash rules to generate a new sharding structure. At the same time, the system sharding metadata table is synchronously updated to ensure that the query optimizer can correctly route to the newly generated sub-shards; specifically, the division distance and the number of increased hash buckets can be pre-configured by the user; When the target optimization strategy is the shard merging strategy, adaptive performance optimization is performed on the shard, including: For any shard whose target optimization strategy is the shard merging strategy, adjacent shards with the target optimization strategy of the shard merging strategy are identified as the shards to be merged with the shard; the shard and the corresponding shard to be merged are merged to obtain the merged shard; specifically, according to the preset merge strategy, cold shards with low access frequency and small data size are selected as the merge targets, and their data is uniformly copied to the newly created large shard, merging multiple adjacent cold data shards into one large shard; after the merger is completed, the original small shards are cleaned up uniformly, and the old shard records are deleted from the system metadata, and the index is rebuilt to ensure query performance. During the merge process, the system monitors disk usage and I / O load in real time to prevent the data aggregation process from affecting other business queries.

[0042] In some embodiments of the present application, when any logical table is determined to be an access load-skewed logical table, adaptive performance optimization is performed on the shard, including: Among the different shards corresponding to the access load tilt logic table, the shards whose heat scores exceed the sixth threshold are selected as shards to be optimized; a shard splitting strategy is adopted to perform adaptive performance optimization on the shards to be optimized to obtain multiple optimized shards; at least one target database node corresponding to the optimized shard is determined based on the first performance data of different database nodes and the second performance data of each shard in each database contained in the corresponding database node; and the optimized shard is stored on the corresponding target database node.

[0043] In the above-described embodiment of the present application, the hotspot shards under the access load skew logic table are finely split, localizing the hotspot load to obtain multiple optimized shards. Based on the current real-time load of each node, the multiple optimized shards are distributed as evenly as possible across different nodes to eliminate the tendency of access concentration. The split and migration operations are performed simultaneously, and the query optimizer's routing information is refreshed after the operation is completed to ensure that subsequent access requests can correctly hit the new shard location.

[0044] In some other embodiments of the present application, the method further includes: If the node load of any database node is greater than the configured seventh threshold and the system includes database nodes with a node load less than the configured eighth threshold, the database node with a node load greater than the configured seventh threshold will be used as the first node, and the database node with a node load less than the configured eighth threshold will be used as the second node; the shards in the first node with an access frequency higher than the configured ninth threshold will be migrated to the second node, and the mapping relationship between the database nodes and the shards will be updated.

[0045] In the above embodiment of the present application, several shards with the highest access frequency on a node with an overload threshold are migrated to an idle node or a low-load node; first, the resource status of the first node and the second node is evaluated, and after confirming that the second node meets the migration safety requirements in terms of CPU, memory, and I / O indicators, the data migration process is started; during the migration, the system adopts a temporary double-write mechanism, applying incremental updates to the source and target shards simultaneously during data synchronization to ensure data consistency. After the migration is completed, the system switches the query route to the second node and releases the source node resources to complete the dynamic rebalancing of the node load.

[0046] In some further embodiments of the present application, the method further includes: If the resource utilization of any database node is greater than the configured tenth threshold and the system includes database nodes with resource utilization less than the configured eleventh threshold, the database node with resource utilization greater than the configured tenth threshold will be used as the third node, and the database node with resource utilization less than the configured eleventh threshold will be used as the fourth node; the shards in the third node with an access frequency higher than the configured twelfth threshold will be migrated to the fourth node, and the mapping relationship between the database nodes and the shards will be updated.

[0047] In some embodiments of the present application, the system periodically (for example, every 30 minutes or 1 hour) determines the shard's heat score, heat classification results, and whether the access load is tilted. In actual applications, the user can flexibly adjust the trigger period, heat threshold, tilt coefficient, classification rules, and other parameters of the shard's heat score, heat classification results, and whether the access load is tilted in the system configuration to adapt to business system scenarios of different scales and different load characteristics. The present application can efficiently and dynamically identify the trend of shard access heat changes, node load imbalance, and data access tilt risks, providing accurate and reliable basic support for subsequent adaptive shard optimization and load balancing strategy decisions, significantly improving the adaptive response capabilities of the entire system to changing load environments.

[0048] In other embodiments of the present application, during the entire adaptive performance optimization process, before the adaptive performance optimization, the first performance data (including node status and resource margin), the second performance data and data integrity are automatically detected, and execution can only be performed after confirming that the conditions meet the requirements; if abnormal conditions such as insufficient disk space, node unavailability, index conflict, etc. occur during the execution process, the system will automatically terminate the current adjustment action, roll back the unfinished data changes, and record the abnormality in the system log for subsequent audit and analysis; after the adaptive performance optimization, the system uniformly updates the sharding metadata and refreshes the global routing cache to ensure that the database system completely switches to the new sharding structure within the next query cycle.

[0049] This application achieves efficient implementation of optimization actions and continuity guarantee of system operation in the adaptive performance optimization process, ensuring that the database distribution structure can always maintain the optimal state under dynamic access load change scenarios, effectively supporting the stable operation of high-concurrency, high-availability, and high-elasticity large-scale distributed database systems.

[0050] In some other embodiments of the present application, the present application further designs an automatic feedback mechanism for the policy execution results. Before and after each adaptive performance optimization, the first performance data of the node and the second performance data of the shard are automatically obtained, the performance data before and after the optimization are compared, the performance improvement ratio is calculated, and a structured feedback record is formed; based on the structured feedback record, the thresholds and parameters in the method of the present application are determined, and the "evolutionary tuning" of the policy behavior is gradually formed, thereby establishing a data-driven policy self-learning capability.

[0051] In some other embodiments of the present application, in order to adapt to short-term hot spot problems caused by sudden access changes, the present application also supports a "hot spot sub-shard buffer mechanism". When the access frequency of a certain shard increases rapidly in a short period of time, that is, the change rate of the access frequency within a preset time period exceeds the preset change rate threshold, and the heat score of the shard is not greater than the third threshold and the shard is not a hot spot shard, a hot spot mirror sub-shard is constructed for the shard to temporarily relieve the pressure, and after the hot spot of the shard falls back (that is, the change rate of the access frequency within the preset time period does not exceed the preset change rate threshold), the shard and the hot spot mirror sub-shard are automatically merged to release resources; through this embodiment, the present application effectively avoids system shocks caused by frequent splitting and merging of shards in a short period of time, and improves the elasticity and robustness of the shard adjustment strategy.

[0052] This application not only constructs a complete closed-loop mechanism of performance perception, behavior recognition, rule matching, and adjustment execution at the structural level, but also further supplements the shortcomings of existing technologies in practicality, dynamism, and intelligence through strategy feedback, self-learning evolution, and hotspot granularity control design, and has significant technological progress and creativity.

[0053] Corresponding to the above method, the embodiment of the present application also provides a distributed database adaptive performance optimization device, such as Figure 3 As shown, the device includes: An acquiring unit 310 is configured to acquire, for any database node, first performance data of the database node and second performance data of each shard in each database included in the database node; A calculation unit 320 is configured to calculate, for any shard in any database included in the database node, a heat score of the shard based on the first performance data of the database node and the second performance data of the shard; The determining unit 330 is configured to determine a heat classification result of the shard based on the heat score of the shard and the configured heat classification rule; and to determine a target optimization strategy corresponding to the heat score and the heat classification result based on the configured performance optimization rule; The optimization unit 340 is used to perform adaptive performance optimization on the shards based on the target optimization strategy.

[0054] The functions of each functional unit of the adaptive performance optimization device for a distributed database provided in the above embodiments of the present application can be achieved through the above method steps. Therefore, the specific working process and beneficial effects of each unit in the adaptive performance optimization device for a distributed database provided in the embodiments of the present application will not be repeated here.

[0055] The present application also provides an electronic device, such as Figure 4 As shown, it includes a processor 410 , a communication interface 420 , a memory 430 and a communication bus 440 , wherein the processor 410 , the communication interface 420 , and the memory 430 communicate with each other via the communication bus 440 .

[0056] Memory 430, for storing computer programs; The processor 410 is configured to execute the program stored in the memory 430 by performing the following steps: For any database node, obtain first performance data of the database node and second performance data of each shard in each database contained in the database node; For any shard in any database included in the database node, calculate the heat score of the shard based on the first performance data of the database node and the second performance data of the shard; Determine the shard's heat classification result based on the shard's heat score and configured heat classification rules; Determine the target optimization strategy corresponding to the popularity score and popularity classification results based on the configured performance optimization rules; Based on the target optimization strategy, adaptive performance optimization is performed on the shards.

[0057] The communication bus mentioned above can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus. This communication bus can be divided into address buses, data buses, and control buses. For ease of illustration, the figure uses only one thick line, but this does not mean that there is only one bus or only one type of bus.

[0058] The communication interface is used for communication between the above electronic device and other devices.

[0059] The memory may include random access memory (RAM) or non-volatile memory (NVM), such as at least one disk storage. Alternatively, the memory may be at least one storage device located away from the processor.

[0060] The above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, and discrete hardware components.

[0061] The implementation methods and beneficial effects of the various components of the electronic device in the above embodiments to solve the problems can be found in Figure 2 The various steps in the embodiment shown are implemented, therefore, the specific working process and beneficial effects of the electronic device provided by the embodiment of the present application are not repeated here.

[0062] In another embodiment provided by the present application, a computer-readable storage medium is also provided, which stores instructions. When the computer-readable storage medium is run on a computer, the computer executes the adaptive performance optimization method for a distributed database in any of the above embodiments.

[0063] In another embodiment provided by the present application, a computer program product including instructions is also provided, which, when executed on a computer, enables the computer to execute any of the adaptive performance optimization methods for a distributed database in the above embodiments.

[0064] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the embodiments of the present application may take the form of entirely hardware embodiments, entirely software embodiments, or embodiments combining software and hardware. Furthermore, the embodiments of the present application may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0065] The embodiments of the present application are described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the steps in the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0066] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0067] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0068] Although preferred embodiments of the present invention have been described, those skilled in the art may make additional changes and modifications to these embodiments once they become aware of the basic creative concepts. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications that fall within the scope of the embodiments of the present invention.

[0069] Obviously, those skilled in the art can make various changes and modifications to the embodiments of the present application without departing from the spirit and scope of the embodiments of the present application. Thus, if these modifications and variations of the embodiments of the present application fall within the scope of the present application and its equivalents, the embodiments of the present application are also intended to include these modifications and variations.

Claims

1. A method for adaptive performance optimization of a distributed database, characterized in that: Applied to a distributed database system, the system includes: multiple database nodes, each database node includes at least one database, and each database includes at least one shard, the method includes: For any database node, obtain first performance data of the database node and second performance data of each shard in each database included in the database node; For any shard in any database included in the database node, calculate a heat score of the shard based on the first performance data of the database node and the second performance data of the shard; Determine a heat classification result for the shard based on the heat score of the shard and the configured heat classification rules; Determine the target optimization strategy corresponding to the popularity score and the popularity classification result according to the configured performance optimization rules; Based on the target optimization strategy, adaptive performance optimization is performed on the shards.

2. The method according to claim 1, wherein The method for obtaining the first performance data and the second performance data includes: Obtaining a system statistics table of the database node by using the configured performance detection virtual table corresponding to the database node; According to the configured tuple generation logic, the system statistical information table is aggregated and analyzed to obtain the first performance data and the second performance data.

3. The method according to claim 1, wherein The first performance data includes: system load characteristics and business access patterns; the second performance data includes: number of data rows and access frequency; the heat classification rules include: first threshold and second threshold; Calculating a heat score of the shard according to the first performance data of the database node and the second performance data of the shard, including: Determining a response time weight and an access density coefficient according to the system load characteristics and the service access pattern; Calculating a heat score of the shard based on the access frequency, the response time weight, the number of data rows, and the access density coefficient; Determining a heat classification result for the shard based on the heat score of the shard and the configured heat classification rules includes: If the popularity score of the shard is greater than the first threshold, the shard is a hotspot shard; If the popularity score of the shard is not greater than the first threshold and greater than the second threshold, the shard is a normal shard; If the heat score of the shard is not greater than the second threshold, the shard is a cold data shard.

4. The method according to claim 1, wherein Determine the target optimization strategy corresponding to the popularity score and the popularity classification result according to the configured performance optimization rules, including: When the heat score is greater than a configured third threshold and the shard is a hotspot shard, the target optimization strategy is a shard splitting strategy; When the heat score is less than a configured fourth threshold and the shard is a cold data shard, the target optimization strategy is a shard merging strategy.

5. The method according to claim 1, wherein The database is used to store at least one logical table; The logical table is stored on at least two shards; After determining the heat classification result of the shard, the method further includes: For any logical table in the database, obtain the popularity scores of different shards corresponding to the logical table; The shard with the highest popularity score is used as the first shard corresponding to the logical table; the shard with the lowest popularity score is used as the second shard corresponding to the logical table; If the ratio of the heat scores of the first shard and the second shard is greater than a configured fifth threshold, the logical table is determined to be an access load tilted logical table.

6. The method according to claim 5, wherein After determining the logic table as an access load tilting logic table, the method further includes: Optimize the shards whose heat scores exceed a sixth threshold among the different shards corresponding to the access load inclination logic table as shards to be optimized; Adopting a shard splitting strategy to perform adaptive performance optimization on the shard to be optimized, thereby obtaining multiple optimized shards; Determining at least one target database node corresponding to the optimized shard based on the first performance data of different database nodes and the second performance data of each shard in each database contained in the corresponding database node; The optimized shards are stored on the corresponding target database nodes.

7. The method according to claim 3, wherein The first performance data also includes: node load; The method further comprises: If the node load of any database node is greater than a configured seventh threshold and the system includes a database node whose node load is less than a configured eighth threshold, the database node whose node load is greater than the configured seventh threshold is used as the first node, and the database node whose node load is less than the configured eighth threshold is used as the second node; The shards in the first node whose access frequency is higher than the configured ninth threshold are migrated to the second node.

8. An adaptive performance optimization device for a distributed database, characterized in that: Applied to a distributed database system, the system includes: multiple database nodes, each database node includes at least one database, each database includes at least one shard, and the device includes: an acquiring unit, configured to acquire, for any database node, first performance data of the database node and second performance data of each shard in each database included in the database node; a calculation unit, configured to calculate, for any shard in any database included in the database node, a heat score of the shard based on the first performance data of the database node and the second performance data of the shard; a determination unit, configured to determine a heat classification result of the shard based on the heat score of the shard and a configured heat classification rule; and determine a target optimization strategy corresponding to the heat score and the heat classification result based on a configured performance optimization rule; The optimization unit is used to perform adaptive performance optimization on the shards based on the target optimization strategy.

9. An electronic device, characterized in that: The electronic device includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus; Memory for storing computer programs; A processor, configured to implement the method according to any one of claims 1 to 7 when executing a program stored in a memory.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Images