An adaptive performance optimization method, device and equipment for a distributed database

Abstract

This application provides an adaptive performance optimization method, apparatus, and device for distributed databases, relating to the field of database system optimization technology. The method includes: for any database node, obtaining first performance data of the database node and second performance data of each shard in each database contained within the database node; for any shard in any database contained within the database node, calculating a shard popularity score based on the first performance data of the database node and the second performance data of the shard; determining a shard popularity classification result based on the shard popularity score and configured popularity classification rules; determining a target optimization strategy corresponding to the popularity score and popularity classification result based on configured performance optimization rules; and performing adaptive performance optimization on the shard based on the target optimization strategy. This application significantly improves the overall resource utilization efficiency and service performance of the database system.

Description

Technical Field

[0001] This application relates to the field of database system optimization technology, and more specifically, to an adaptive performance optimization method, apparatus, and device for distributed databases. Background Technology

[0002] With the development of big data technology, distributed database systems have become the core infrastructure supporting large-scale, high-concurrency business. GBase 8s, a domestic database supporting large-scale parallel processing, is widely used in finance, telecommunications, e-commerce, and other fields. In actual business, database performance bottlenecks often stem from uneven data distribution and skewed node load. Traditional database systems typically employ static data sharding strategies, such as initial sharding based on primary key hashing or range partitioning. While these strategies can meet certain load balancing requirements in the initial deployment phase, as data volume, access patterns, and business logic continuously change, static strategies often face the following problems: First, hot data is concentrated, leading to excessively high access frequency for some shards and causing resource strain on some nodes; second, cold data occupies a large amount of storage and resources for a long time with extremely low utilization; third, node load cannot be dynamically migrated and scheduled, easily resulting in some nodes being overloaded while others are idle; fourth, the fixed sharding granularity lacks flexibility and cannot be dynamically adjusted according to business needs, leading to resource waste and decreased query efficiency; it also cannot implement finer-grained sharding for hot areas, limiting the scope for query performance optimization; and fifth, it lacks a flexible, rule-driven mechanism. While some existing technologies have introduced intelligent prediction methods such as machine learning or neural networks to optimize data distribution and load sharding, these methods are usually complex, highly black-box, have high debugging and implementation costs, are difficult to explain and maintain, and are not suitable for financial or government systems with extremely high requirements for security and controllability.

[0003] Existing technology 1 proposes an index sharding optimization mechanism based on Elasticsearch for full-text search databases. Although it also performs sharding optimization, its sharding optimization focuses on adjusting the index structure, which is difficult to apply to row-level heat identification and fine-grained sharding processing of structured data tables. Moreover, this solution relies on index reconstruction when adjusting shards, which requires pausing writing or adjusting routing rules, and cannot perform dynamic adjustments without awareness or locks 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, lacks linkage with the internal execution engine of the database, and does not form a direct coupling relationship between the strategy instruction system and the execution action. Summary of the Invention

[0005] The purpose of this application is to provide an adaptive performance optimization method, apparatus, and device for distributed databases, which solves the above-mentioned problems existing in the prior art and can dynamically optimize the data distribution structure and resource utilization efficiency in large-scale data access scenarios.

[0006] Firstly, an adaptive performance optimization method for distributed databases is provided, applied to a distributed database system, the system comprising: multiple database nodes, each database node containing at least one database, and each database containing at least one shard; the method may include: For any given database node, obtain the first performance data of the database node, and the second performance data of each shard in each database contained in the database node; For any shard in any database contained 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; The heat classification result of the segment is determined based on the heat score of the segment and the configured heat classification rules; Based on the configured performance optimization rules, determine the target optimization strategy corresponding to the popularity score and the popularity classification result; 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: Using the configured virtual performance monitoring table corresponding to the database node, obtain the system statistics information table of the database node; Based on the configured tuple generation logic, the system statistics 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: a first threshold and a second threshold; Based on the first performance data of the database node and the second performance data of the shard, the popularity score of the shard is calculated, including: Based on the system load characteristics and the service access pattern, determine the response time weight and access density coefficient; The popularity score of the shard is calculated based on the access frequency, the response time weight, the number of data rows, and the access density coefficient. Based on the popularity score of the segment and the configured popularity classification rules, the popularity classification result of the segment is determined, including: If the popularity score of the segment is greater than the first threshold, then the segment is a hot segment; If the popularity score of the segment is not greater than the first threshold but greater than the second threshold, then the segment is a normal segment. If the heat score of the shard is not greater than the second threshold, then the shard is a cold data shard.

[0009] In an optional implementation, the target optimization strategy corresponding to the popularity score and the popularity classification result is determined according to the configured performance optimization rules, including: When the popularity score is greater than the configured third threshold and the shard is a hotspot shard, the target optimization strategy is a shard splitting strategy; When the popularity score is less than the 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 segment, the method further includes: For any logical table in the database, obtain the popularity score of different segments corresponding to the logical table; The segment with the highest popularity score is designated as the first segment corresponding to the logical table; the segment with the lowest popularity score is designated as the second segment corresponding to the logical table. If the ratio of the popularity scores of the first shard and the second shard is greater than the configured fifth threshold, then the logical table is determined as an access load skew logical table.

[0011] In an optional implementation, after determining the logical table as an access load skew logical table, the method further includes: Among the different shards corresponding to the access load skew logic table, the shards whose popularity score exceeds the sixth threshold are designated as shards to be optimized. A sharding strategy is adopted to adaptively optimize the shards to be optimized, resulting in multiple optimized shards. 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, at least one target database node corresponding to the optimized shard is determined. The optimized shards are stored on the corresponding target database nodes.

[0012] In an optional implementation, the first performance data may further include: node load; 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 whose node load is less than the configured eighth threshold, then the database node whose node load is greater than the configured seventh threshold is designated as the first node, and the database node whose node load is less than the configured eighth threshold is designated as the second node. Fragments in the first node whose access frequency is higher than the configured ninth threshold are migrated to the second node.

[0013] Secondly, an adaptive performance optimization device for a distributed database is provided, applied to a distributed database system, the system comprising: multiple database nodes, each database node containing at least one database, and each database containing at least one shard; the device may include: The acquisition unit is used to acquire, for any database node, first performance data of the database node and second performance data of each shard in each database contained in the database node; The calculation unit is used to calculate the popularity score of any shard in any database contained in the database node, based on the first performance data of the database node and the second performance data of the shard. The determining unit is configured to determine the popularity classification result of the segment based on the popularity score of the segment and the configured popularity classification rules; and to determine the target optimization strategy corresponding to the popularity score and the popularity classification result based on the configured performance optimization rules. An optimization unit is used to perform adaptive performance optimization on the shards based on the target optimization strategy.

[0014] Thirdly, an electronic device is provided, which 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 through the communication bus; Memory, used to store computer programs; When a processor executes a program stored in memory, it implements any of the steps described in the first aspect above.

[0015] Fourthly, a computer-readable storage medium is provided, wherein a computer program is stored therein, and when executed by a processor, the computer program implements the steps of any of the methods described in the first aspect above.

[0016] This application analyzes the first performance data of collected 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, corresponding adaptive performance optimization strategies are matched to perform adaptive performance optimization for each shard, significantly improving the resource utilization and query response performance of large-scale distributed database systems under dynamic load changes. This application has advantages such as controllable rules, interpretable behavior, fast response speed, and low system complexity.

[0017] Compared to the existing technology 1, this application targets business scenarios with intensive structured data queries. Through a database kernel-level pseudo-table mechanism, it collects runtime performance indicators in real time without persistent disk storage, and can complete multi-dimensional heat modeling, with advantages of high real-time performance and system-level embedding.

[0018] Compared to the prior art 2, this application uses predefined operation codes (OP codes) and rule bases to bind access mode analysis results to form a standardized and composable optimization strategy chain, which ensures both the flexibility of the strategy and the structured maintainability. Attached Figure Description

[0019] To more clearly illustrate the technical solutions of the embodiments of this application, the accompanying drawings used in the embodiments of this application will be briefly introduced below. It should be understood that the following drawings only show some embodiments of this application and should not be regarded as a limitation of the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0020] Figure 1 An architecture diagram of an adaptive performance optimization system for a distributed database provided in this application embodiment; Figure 2 A flowchart illustrating an adaptive performance optimization method for a distributed database provided in this application embodiment; Figure 3 This is a schematic diagram of the structure of an adaptive performance optimization device for a distributed database provided in an embodiment of this application; Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0021] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of the embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application. Unless otherwise defined, the technical or scientific terms used in this application should have the ordinary meaning understood by those skilled in the art. The words "first," "second," and similar terms used in this application do not indicate any order, quantity, or importance, but are only used to distinguish different components. The words "comprising" or "including," etc., mean that the element or object preceding the word covers the element or object listed after the word and its equivalents, but do not exclude other elements or objects. The words "connected," "coupled," or "connected," etc., are not limited to physical or mechanical connections, but can include electrical connections, whether direct or indirect. "Up," "down," "left," "right," etc., are only used to indicate relative positional relationships. When the absolute position of the described object changes, the relative positional relationship may also change accordingly.

[0022] The adaptive performance optimization method for distributed databases provided in this application embodiment can be applied to... 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, used for actual data sharding and SQL query execution; multiple database nodes perform data synchronization, load balancing, and failover through inter-node communication (such as the Gossip protocol or RPC); different nodes can be deployed in different geographical regions for inter-node isolation; 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 sharding key, and each shard stores a subset of the data; a shard is a horizontally partitioned data block; such as Figure 1 As shown, the system may include: a performance monitoring module, a popularity analysis module, a rule engine module, and an optimization module; wherein, the performance monitoring module is communicatively connected to the popularity analysis module; the popularity analysis module is communicatively connected to the rule engine module; and the rule engine module and the optimization module are communicatively connected. The performance monitoring module is used to obtain the first performance data of each database node in the system, and the second performance data of each shard in each database contained in each database node. The popularity analysis module is used to calculate the popularity 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 to determine the popularity classification result of each shard based on the popularity score of each shard and the configured popularity classification rules. The rules 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 adaptively optimize the performance of shards based on the target optimization strategy.

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

[0024] The preferred embodiments of this application are described below with reference to the accompanying drawings. It should be understood that the preferred embodiments described herein are for illustration and explanation only and are not intended to limit this application. Furthermore, the embodiments and features in the embodiments of this application can be combined with each other without conflict.

[0025] Figure 2 This is a flowchart illustrating an adaptive performance optimization method for a distributed database provided in an embodiment of this application. Figure 2 As shown, the method may include: Step S210: For any database node, obtain the first performance data of the database node and the second performance data of each shard in each database contained in the database node.

[0026] In a specific embodiment, the method for obtaining the first performance data and the second performance data includes: The system statistics table for the database nodes is obtained by using the configured performance monitoring virtual table corresponding to the database nodes. This performance monitoring virtual table is built based on the pseudo-table mechanism provided by the GBase 8s database. It is used to dynamically collect and summarize the running status and load information of each database shard, and is periodically refreshed at preset sampling intervals during database operation. The table name for the performance monitoring virtual table can be v_monitor_stats. Essentially, the performance monitoring virtual table is a non-persistent data structure, dynamically generated only within the query execution cycle, and does not occupy physical storage resources. The performance monitoring virtual table is implicitly created during database initialization. During the query execution phase, it calls the tuple scan function implemented internally by the database to return system metadata or runtime memory data in real time, dynamically summarizing performance indicator data from other system tables. The collected results are periodically updated during database operation, serving as a unified information interface for subsequent access pattern analysis and rule judgment. Unlike traditional temporary tables, the lifecycle of the performance monitoring virtual table is limited to a single query cycle, requiring no explicit creation or deletion, and the data content changes in real time with each query. Based on the configured tuple generation logic, the system statistics information 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, business access mode, and operating status parameters (CPU, memory, I / O). The second performance data includes: number of sharded data rows, sharded data size, and access frequency. The system statistics information table includes a query log table, a node resource status table, a sharded distribution information table, a data rule table, and a table definition information table. Specifically, based on the configured tuple generation logic, the system statistics table (sysresusage) collected during database operation is aggregated and analyzed to obtain first and second performance data through dynamic monitoring of system resource usage at each database node, including: Statistical analysis is performed on the access frequency and response performance of each logical table and its shards in the database system within a certain time window. Query aggregation logic is constructed by correlating query logs with shard metadata. This aggregation logic dynamically calculates the access characteristics of each shard based on the database runtime logs. The specific processing logic is as follows: Query records within a specified time range (e.g., the last 15 minutes) are extracted from the database query log table (sysquerylog) and correlated with the shard metadata table (syssegments). The query records are then linked to the corresponding shard information based on the logical table name field. The process is further refined based on the logical table name, shard number, and its node. Grouped statistics are generated to produce performance metrics. These metrics may include: `access_freq`, representing the total number of times each shard is accessed within a specified time period (i.e., the number of times each shard is hit in the query log); `avg_runtime`, representing the average execution time of queries carried by the corresponding shard, reflecting the shard's response performance; and `snapshot_time`, representing a snapshot of the statistical data, typically recorded as the current query execution time, used for subsequent time-series analysis and comparison. The results of the above aggregation analysis are populated in real-time into a performance monitoring virtual table structure for heat score calculation, access pattern identification, and policy rule matching. This application, through the above embodiments, 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 system operation sampling data within the last 5 minutes to obtain first and second performance data. Specifically, all resource sampling records with a sampling time greater than the current time minus 5 minutes are extracted and analyzed to obtain operational status parameters, including: CPU utilization (cpu_usage): representing the average CPU usage ratio of database nodes within a specified time window, used to measure processor resource usage pressure; I / O utilization (io_usage): representing the resource usage of disks or other input / output devices, used to assess the read / write pressure of the storage system; and memory utilization (mem_usage): reflecting the current memory load level of database nodes, a direct indicator of memory resource scarcity. These operational status parameters are presented in a structured manner in a performance monitoring virtual table, and sharding heat modeling and optimization strategies are formulated. This application does not require data persistence operations and can achieve high-frequency performance awareness through a lightweight scanning mechanism, ensuring the real-time performance and accuracy of system scheduling.

[0027] In some embodiments of this application, the fields of the performance monitoring virtual table may include: tabname: the name of the logical table, data type is variable-length string (VARCHAR), maximum length is 128 characters; partnum: the shard number to which the current record belongs, type is integer; nodeid: the database node number where the current shard is located, type is integer; access_freq: the frequency of access to the shard per unit time, type is integer; avg_runtime: the average response time of the query corresponding to the shard, type is floating-point number; cpu_load: the real-time load of the node CPU, type is floating-point number; io_load: the utilization rate of the node I / O resources, type is floating-point number; mem_usage: the proportion of node memory usage, type is floating-point number; row_count: the number of data rows stored in the current shard, type is integer; data_size: the physical storage space occupied by the current shard, type is integer; snapshot_time: the snapshot generation time of this record, type is timestamp, accurate to the second. In the performance monitoring virtual table, each record dynamically reflects the access load, resource consumption, and data scale of a certain shard at a certain point in time, thus forming a real-time monitoring snapshot. The pseudo-table architecture used in this application's performance monitoring virtual table has the following advantages: High dynamism: Each query generates a snapshot of the latest data, eliminating the need for persistent storage and making it suitable for frequent scheduling and rapid rule matching; Low resource overhead: Residing in memory or generated through lightweight scan functions greatly reduces storage and I / O burden; Multi-purpose use: Subsequent processes such as access pattern analysis, rule evaluation, and load scheduling can be directly based on this pseudo-table, avoiding complex multi-table JOINs and improving query performance; Clear lifecycle control: Automatically generated upon query startup and released upon query completion, reducing system burden; Excellent distributed support: Each node can independently perform local performance data filling, which is automatically aggregated through a global view. This application's performance monitoring virtual table, designed with a pseudo-table mechanism, collects performance data from database nodes and shards, enabling efficient, real-time, and structured acquisition of database operating status information. This provides an accurate, lightweight, and scalable data foundation for subsequent shard optimization and load balancing strategies, significantly improving the system's adaptive adjustment capabilities and overall operating efficiency.

[0028] This application introduces a pseudo-table mechanism as a unified aggregation interface for performance monitoring data, which enables high-frequency, real-time, and structured collection of database operating status with extremely low resource overhead. This avoids the I / O bottleneck and management complexity issues caused by large amounts of persistent storage in traditional monitoring methods, and greatly improves the timeliness and lightweight nature of monitoring data.

[0029] Step S220: For any shard in any database contained 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 practice, based on the first performance data of the database node and the second performance data of the shard, the shard popularity score is calculated, including: Based on system load characteristics and business access patterns, determine the response time weight and access density coefficient; calculate the heat score of the shard based on access frequency, response time weight, number of data rows, and access density coefficient; the heat score (Heat_Score) can be calculated as: Heat Score = (Access Frequency × Response Time Weight) + (Number of Data Rows × Access Density Coefficient); the response time weight and access density coefficient are determined according to 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 segment based on the heat score and the configured heat classification rules; determine the target optimization strategy corresponding to the heat score and heat classification result based on the configured performance optimization rules.

[0032] In practice, all shards are divided into three categories based on popularity scores: Hot Shards, Normal Shards, and Cold Shards. The popularity classification result for each shard is determined according to its popularity score and the configured popularity classification rules, including: If the popularity score of a shard is greater than the first threshold, the shard is a hot shard; if the popularity score of a shard is not greater than the first threshold but greater than the second threshold, the shard is a normal shard; if the popularity score of a shard is not greater than the second threshold, the shard is a cold data shard. The popularity classification rules include the first threshold and the second threshold.

[0033] In some embodiments of this application, after determining the heat classification result of the fragments, the method further includes: For any logical table in the database, obtain the popularity score of different shards corresponding to the logical table; take the shard with the highest popularity score as the first shard corresponding to the logical table; take the shard with the lowest popularity score as the second shard corresponding to the logical table; if the ratio of the popularity scores of the first shard and the second shard is greater than the configured fifth threshold, then the logical table is determined as the access load skew logical table.

[0034] Specifically, once any logical table is identified as an access load skew logical table, it is recorded in a temporary table used to temporarily store relevant analysis results. Each record in this temporary table corresponds to an analysis snapshot of a shard, including key indicators such as shard popularity score, popularity classification results, load skew flag, and node information. The lifespan of this temporary table is limited to the current query or analysis cycle, and its name can be t_shard_access_profile. The structure of this temporary table can include: tabname: the name of the logical table, a variable-length string with a maximum length of 128 characters; partnum: the shard number corresponding to the current record, used to uniquely identify the shard, an integer; nodeid: the database node number where the shard resides, an integer; heat_scor e: Represents the popularity score calculated for this shard in the current period. It is a floating-point number reflecting the access intensity and resource consumption of the shard. access_level: Represents the classification level of this shard based on its popularity score. It is a string and can take values ​​such as "HOT" (hot shard), "NORMAL" (normal shard), and "COLD" (cold shard). is_skewed: A boolean field used to indicate whether the current shard belongs to the access load skew logic table. If TRUE, it indicates that the shard belongs to the logic table with highly concentrated access and needs to be optimized. snapshot_time: Represents the time snapshot corresponding to the above analysis results. It tracks the trend of the shard's popularity status over time. It is a timestamp, accurate to the second. The temporary table in this application serves as a carrier of intermediate analysis results, supporting the rule engine in further processing tasks such as heat classification, skew identification, and optimization strategy decision-making. It also provides structured input for the subsequent scheduling module, facilitating optimization actions and maintaining system operation logs. In the temporary table, the `is_skewed` field for shards in the access load skew logic table is uniformly marked as TRUE, otherwise as FALSE, so that the subsequent rule engine module can identify and process the relevant shards in the access load skew logic table accordingly. This application provides an efficient and standardized data interface through this structured output, avoiding the overhead of repeated parsing and statistics in the subsequent decision-making stage.

[0035] In practice, the 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 strategies corresponding to the popularity scores and popularity classification results are determined, including: When the popularity score is greater than the configured third threshold and the shard is a hot shard, the target optimization strategy is a shard splitting strategy; where the third threshold can be 80. When the popularity score is less than the configured fourth threshold and the shard is a cold data shard, the target optimization strategy is the shard merging strategy.

[0036] In some embodiments of this 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 priority of various optimization actions. In the actual workflow, the system first periodically scans all shard records; for each shard record, it matches the performance optimization rules in the system rule base one by one according to the popularity score and popularity classification results.

[0037] In some embodiments of this application, the fields of the performance optimization rule may include: The rule ID (rule_id) is the unique primary key of the rule and is of integer type. The operation instruction code `op_code` is used to identify the type of optimization operation corresponding to the rule. The type is a string (maximum 32 characters). For example, `HOT_SPLIT` indicates splitting hot data shards, `COLD_MERGE` indicates merging cold data shards, and `HOT_MIGRATE` indicates performing migration operations on hot data shards. Action type (action_type): This indicates the execution category to which the optimization rule belongs. It is a string; typical values ​​include SPLIT, MERGE, and MIGRATE. The action type is the corresponding target optimization strategy. The `action_params` parameter is used to define detailed operation parameters. It is a variable-length string (maximum 512 characters). Examples include splitting granularity, target node selection criteria, and trigger thresholds. Rule priority: This is an integer. The smaller the value, the higher the priority. When multiple applicable rules exist, the system will execute the higher priority rule first. Enable flag (enable_flag): A boolean value indicating whether the rule is enabled; if TRUE, the rule is valid in the rule engine. rule creation time (create_time): This is a timestamp used to record the time the rule was defined. The last update time of the rule (update_time) is a timestamp used to support dynamic adjustments and version control of the rule.

[0038] The performance optimization rules in this application enable a modular, configurable, and composable management approach for performance optimization strategies.

[0039] This application dynamically extracts the access frequency characteristics and load skew of each shard by comprehensively calculating multiple dimensions such as query frequency, response time, and node load. This allows for the timely identification of hot data, cold data, and concentrated access risks, providing accurate and granular data support for subsequent decision-making. Compared to traditional methods relying on fixed thresholds or manual interpretation, this application achieves intelligent and dynamic identification of access behavior, significantly reducing the need for human intervention.

[0040] Step S240: Based on the target optimization strategy, perform adaptive performance optimization on the partition.

[0041] In practice, based on the target optimization strategy, adaptive performance optimization is performed on the shard, including: When the target optimization strategy is a sharding strategy, adaptive performance optimization is performed on the shard, including: The system retrieves the current sharding strategy and sharding parameters for the current shard. Maintaining the current sharding strategy, it optimizes and adjusts the sharding parameters under the current strategy. The sharding strategy includes primary key range distribution and hash distribution. When the sharding strategy is primary key range distribution, the sharding parameters include the primary key range distance. Optimization of the sharding parameters includes: dividing the shard by equal distance according to the primary key range; that is, dividing the shard into several sub-intervals according to the minimum and maximum primary key values, and creating a new sub-shard for each sub-interval. The system uses a batch scanning and migration strategy when partitioning data to avoid node pressure fluctuations caused by a large-scale data operation at once. When the sharding strategy is hash distribution, the sharding parameters include the number of hash buckets. Optimization of the sharding parameters includes: increasing the number of hash buckets, redistributing sub-shards, and reallocating data ownership according to the new hash rules to generate a new sharding structure. Simultaneously, the system's sharding metadata table is updated to ensure that the query optimizer can correctly route to the newly generated sub-shards. Specifically, the partitioning distance and the number of additional hash buckets can be pre-configured by the user. When the target optimization strategy is a sharding and merging strategy, adaptive performance optimization is performed on the shard, including: For any shard whose target optimization strategy is shard merging, the shards adjacent to it with the same target optimization strategy are identified as shards to be merged. These shards are then merged with the corresponding shards to be merged, resulting in a merged shard. Specifically, based on a preset merging strategy, cold shards with low access frequency and small data size are selected as the merging targets, and their data is uniformly copied to a newly created large shard. Multiple adjacent cold data shards are merged into one large shard. Existing small shards are cleaned up after the merge, and old shard records are deleted from the system metadata. Indexes are rebuilt to ensure query performance. During the merging process, the system monitors disk usage and I / O load in real time to prevent data aggregation from affecting other business queries.

[0042] In some embodiments of this application, when any logical table is determined to be an access load skewed logical table, adaptive performance optimization is performed on the shard, including: In the access load skew logic table, shards with a popularity score exceeding the sixth threshold are designated as shards to be optimized. A sharding splitting strategy is adopted to adaptively optimize the performance of the shards to be optimized, resulting in multiple optimized shards. 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, at least one target database node is determined for each optimized shard. The optimized shards are then stored on the corresponding target database nodes.

[0043] In the above embodiments of this application, the hotspot shards under the access load skew logic table are finely split to localize the hotspot load, resulting in multiple optimized shards. Based on the real-time load of each node, the multiple optimized shards are distributed as evenly as possible across different nodes to eliminate access concentration trends. The splitting and migration operations are performed synchronously, and the routing information of the query optimizer is refreshed after the operations are completed to ensure that subsequent access requests can correctly hit the new shard location.

[0044] In other embodiments of this 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 node loads less than the configured eighth threshold, then the database node with a node load greater than the configured seventh threshold is designated as the first node, and the database node with a node load less than the configured eighth threshold is designated as the second node; the shards in the first node with an access frequency higher than the configured ninth threshold are migrated to the second node, and the mapping relationship between database nodes and shards is updated.

[0045] In the embodiments described above, several shards with the highest access frequency on nodes whose node load exceeds a threshold are migrated to idle or low-load nodes. First, the resource status of the first and second nodes is evaluated. After confirming that the second node meets the migration safety requirements in terms of CPU, memory, and I / O metrics, the data migration process is initiated. During the migration, the system employs a temporary dual-write mechanism, applying incremental updates to both the source and target shards simultaneously during data synchronization to ensure data consistency. After the migration is complete, the system switches the query route to the second node and releases the resources of the source node, completing the dynamic rebalancing of node load.

[0046] In some further embodiments of this 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, then the database node with resource utilization greater than the configured tenth threshold will be designated as the third node, and the database node with resource utilization less than the configured eleventh threshold will be designated as the fourth node; the shards in the third node with access frequency higher than the configured twelfth threshold will be migrated to the fourth node, and the mapping relationship between database nodes and shards will be updated.

[0047] In some embodiments of this application, the system periodically (e.g., every 30 minutes or 1 hour) determines the shard popularity score, popularity classification result, and whether access load is skewed. In practical applications, parameters such as the trigger period, popularity threshold, skew coefficient, and classification rules for the shard popularity score, popularity classification result, and whether access load is skewed can be flexibly adjusted by the user in the system configuration to adapt to business system scenarios of different scales and load characteristics. This application can efficiently and dynamically identify the trend of shard access popularity changes, uneven node load, and data access skew risks, providing accurate and reliable foundational support for subsequent adaptive sharding optimization and load balancing strategy decisions, and significantly improving the adaptive response capability of the entire system to changing load environments.

[0048] In other embodiments of this application, during the entire adaptive performance optimization process, before adaptive performance optimization, the system automatically detects the first performance data (including node status and resource availability), the second performance data, and data integrity. Execution can only proceed after confirming that the conditions meet the requirements. If abnormal situations such as insufficient disk space, node unavailability, or index conflicts occur during execution, the system will automatically stop the current adjustment action, roll back the incomplete data changes, and record the abnormality in the system log for subsequent auditing and analysis. After 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 in the next query cycle.

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

[0050] In some further embodiments of this application, an automatic feedback mechanism for policy execution results is designed. Before and after each adaptive performance optimization, the first performance data of the node and the second performance data of the shard are automatically acquired. The performance data before and after 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 this application are determined, and the "evolutionary tuning" of policy behavior is gradually formed, thereby establishing a data-driven policy self-learning capability.

[0051] In other embodiments of this application, to adapt to short-term hotspot issues caused by sudden changes in access frequency, this application also supports a "hotspot sub-shard buffer mechanism." When the access frequency of a shard increases rapidly in a short period of time (i.e., the rate of change of access frequency within a preset time period exceeds a preset rate of change threshold), and the shard's popularity score is not greater than a third threshold and the shard is not a hotspot shard, a hotspot mirror sub-shard is constructed for the shard to temporarily alleviate pressure. After the hotspot of the shard sub-shard recedes (i.e., the rate of change of access frequency within a preset time period does not exceed the preset rate of change threshold), the shard and the hotspot mirror sub-shard are automatically merged to release resources. Through this embodiment, this application effectively avoids system oscillations caused by frequent splitting and merging of shards within a short period of time, and improves the flexibility and robustness of the shard adjustment strategy.

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

[0053] Corresponding to the above method, embodiments of this application also provide an adaptive performance optimization device for distributed databases, such as... Figure 3 As shown, the device includes: The acquisition unit 310 is used to acquire, for any database node, the first performance data of the database node and the second performance data of each shard in each database contained in the database node. The calculation unit 320 is used to calculate the heat score of any shard in any database contained in the database node, based on the first performance data of the database node and the second performance data of the shard. The determining unit 330 is used to determine the heat classification result of the partition based on the heat score of the partition and the configured heat classification rules; and to determine the target optimization strategy corresponding to the heat score and heat classification result based on the configured performance optimization rules. 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 in the adaptive performance optimization device for distributed databases provided in the above embodiments of this application can be implemented through the above method steps. Therefore, the specific working process and beneficial effects of each unit in the adaptive performance optimization device for distributed databases provided in the embodiments of this application will not be repeated here.

[0055] This 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 through the communication bus 440.

[0056] Memory 430 is used to store computer programs; When the processor 410 executes the program stored in the memory 430, it performs the following steps: For any given database node, obtain the first performance data of the database node, and the second performance data of each shard in each database contained within the database node; For any shard in any database contained in a database node, calculate the shard's popularity score based on the first performance data of the database node and the second performance data of the shard. The popularity classification results of the segments are determined based on the popularity scores of the segments and the configured popularity classification rules; Based on the configured performance optimization rules, determine the target optimization strategies corresponding to the popularity scores and popularity classification results; Based on the target optimization strategy, adaptive performance optimization is performed on the sharding.

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

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

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

[0060] The processors mentioned above can be general-purpose processors, including central processing units (CPUs), network processors (NPs), etc.; they can also be digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0061] The implementation methods and beneficial effects of the various components of the electronic device in the above embodiments for solving the problem can be found in [reference needed]. Figure 2 The steps in the illustrated embodiments are used to implement the electronic device. Therefore, the specific working process and beneficial effects of the electronic device provided in this application will not be repeated here.

[0062] In another embodiment provided in this application, a computer-readable storage medium is also provided, which stores instructions that, when executed on a computer, cause the computer to perform any of the adaptive performance optimization methods for distributed databases in the above embodiments.

[0063] In another embodiment provided in this application, a computer program product containing instructions is also provided, which, when run on a computer, causes the computer to execute any of the adaptive performance optimization methods for distributed databases in the above embodiments.

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

[0065] This application describes embodiments of methods, apparatus (systems), and computer program products according to embodiments of this application with reference to flowchart illustrations and / or block diagrams. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0066] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0067] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0068] Although preferred embodiments have been described in this application, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments as well as all changes and modifications falling within the scope of the embodiments of this application.

[0069] Obviously, those skilled in the art can make various modifications and variations to the embodiments of this application without departing from the spirit and scope of the embodiments of this application. Therefore, if these modifications and variations to the embodiments of this application fall within the scope of this application and its equivalents, then these modifications and variations are also intended to be included in the embodiments of this application.

Claims

1. A method for adaptive performance optimization of a distributed database, the method comprising: Applied to a distributed database system, the system comprising: multiple database nodes, each database node containing at least one database, each database containing at least one shard, the method comprising: For any given database node, obtain first performance data for the database node and second performance data for each shard in each database contained within the database node; the first performance data includes: system load characteristics, business access patterns, and node load; the second performance data includes: number of data rows and access frequency; For any shard in any database contained in the database node, the response time weight and access density coefficient are determined according to the system load characteristics and the business access mode; the shard's popularity score is calculated according to the access frequency, the response time weight, the number of data rows, and the access density coefficient; the popularity score = (access frequency × response time weight) + (number of data rows × access density coefficient). The heat classification result of the segment is determined based on the heat score of the segment and the configured heat classification rules; Based on the configured performance optimization rules, the target optimization strategy corresponding to the popularity score and the popularity classification result is determined; the target optimization strategy includes a segmentation strategy. Based on the target optimization strategy, the shards are adaptively optimized. When the target optimization strategy is a sharding strategy, adaptive performance optimization is performed on the shards based on the target optimization strategy, including: While maintaining the current sharding strategy, the sharding parameters under the current sharding strategy are optimized and adjusted. Specifically, when the sharding strategy is a primary key range distribution, the sharding parameter is the primary key range distance. Optimization of the sharding parameter includes: dividing the sharding by equal intervals according to the primary key range; specifically, dividing the sharding into several sub-intervals according to a set granularity based on the minimum and maximum primary key values, and creating new sub-shards for each sub-interval; when the sharding strategy is a hash distribution, the sharding parameter is the number of hash buckets; optimization of the sharding parameter includes: increasing the number of hash buckets, redistributing sub-shards, and reallocating data ownership according to the new hash rules to generate a new sharding structure, while simultaneously updating the system sharding metadata table. 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 whose node load is less than the configured eighth threshold, then the database node whose node load is greater than the configured seventh threshold is designated as the first node, and the database node whose node load is less than the configured eighth threshold is designated as the second node. Fragments in the first node whose access frequency is higher than the configured ninth threshold are migrated to the second node.

2. The method as described in claim 1, characterized in that, The method for obtaining the first performance data and the second performance data includes: Using the configured virtual performance monitoring table corresponding to the database node, obtain the system statistics information table of the database node; Based on the configured tuple generation logic, the system statistics table is aggregated and analyzed to obtain the first performance data and the second performance data.

3. The method as described in claim 1, characterized in that, The popularity classification rules include: a first threshold and a second threshold; Based on the popularity score of the segment and the configured popularity classification rules, the popularity classification result of the segment is determined, including: If the popularity score of the segment is greater than the first threshold, then the segment is a hot segment; If the popularity score of the segment is not greater than the first threshold but greater than the second threshold, then the segment is a normal segment. If the heat score of the shard is not greater than the second threshold, then the shard is a cold data shard.

4. The method as described in claim 1, characterized in that, Based on the configured performance optimization rules, determine the target optimization strategies corresponding to the popularity score and the popularity classification results, including: When the popularity score is greater than the configured third threshold and the shard is a hotspot shard, the target optimization strategy is a shard splitting strategy; When the popularity score is less than the configured fourth threshold and the shard is a cold data shard, the target optimization strategy is a shard merging strategy.

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

6. The method as described in claim 5, characterized in that, After identifying the logic table as an access load skew logic table, the method further includes: Among the different shards corresponding to the access load skew logic table, the shards whose popularity score exceeds the sixth threshold are designated as shards to be optimized. A sharding strategy is adopted to adaptively optimize the shards to be optimized, resulting in multiple optimized shards. 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, at least one target database node corresponding to the optimized shard is determined. The optimized shards are stored on the corresponding target database nodes.

7. 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 containing at least one database, each database containing at least one shard, and the apparatus includes: The acquisition unit is configured to acquire, for any given database node, first performance data of the database node and second performance data of each shard in each database contained within the database node; the first performance data includes: system load characteristics, business access patterns, and node load; the second performance data includes: number of data rows and access frequency; The calculation unit is used to determine the response time weight and access density coefficient for any shard in any database contained in the database node, based on the system load characteristics and the business access mode; and to calculate the popularity score of the shard based on the access frequency, the response time weight, the number of data rows, and the access density coefficient; the popularity score = (access frequency × response time weight) + (number of data rows × access density coefficient). The determining unit is configured to determine the popularity classification result of the partition based on the popularity score and the configured popularity classification rules; and to determine the target optimization strategy corresponding to the popularity score and the popularity classification result based on the configured performance optimization rules; the target optimization strategy includes a partition splitting strategy. An optimization unit is used to perform adaptive performance optimization on the shards based on the target optimization strategy. The optimization unit is specifically used for: when the target optimization strategy is a sharding strategy, performing adaptive performance optimization on the shards based on the target optimization strategy, including: While maintaining the current sharding strategy, the sharding parameters under the current sharding strategy are optimized and adjusted. Specifically, when the sharding strategy is a primary key range distribution, the sharding parameter is the primary key range distance. Optimization of the sharding parameter includes: dividing the sharding by equal intervals according to the primary key range; specifically, dividing the sharding into several sub-intervals according to a set granularity based on the minimum and maximum primary key values, and creating new sub-shards for each sub-interval; when the sharding strategy is a hash distribution, the sharding parameter is the number of hash buckets; optimization of the sharding parameter includes: increasing the number of hash buckets, redistributing sub-shards, and reallocating data ownership according to the new hash rules to generate a new sharding structure, while simultaneously updating the system sharding metadata table. The apparatus further includes a migration unit, configured to: if the node load of any database node is greater than a configured seventh threshold and the system includes database nodes with node loads less than a configured eighth threshold, then designate the database nodes with node loads greater than the configured seventh threshold as first nodes and the database nodes with node loads less than the configured eighth threshold as second nodes; and migrate the shards in the first node whose access frequency is higher than a configured ninth threshold to the second node.

8. 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 through the communication bus; Memory, used to store computer programs; A processor, when executing a program stored in memory, implements the method of any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the method described in any one of claims 1-6.

Images