Distributed database behavior data intelligent acquisition method and system

Abstract

The invention discloses a distributed database behavior data intelligent acquisition method and system, and belongs to the technical field of databases, and the method comprises the following steps: an initial acquisition strategy is responsible for accumulating SQL statistical data to generate a clustering-based adaptive data strategy; the clustering-based self-adaptive acquisition strategy generation is used for periodically generating a latest self-adaptive behavior data acquisition strategy; the clustering-based self-adaptive acquisition strategy application is responsible for replacing an original acquisition strategy, completing SQL statistics and detailed behavior data acquisition based on the latest acquisition strategy and storing the SQL statistics and detailed behavior data to a file system; and the centerless distributed disk storage is used for constructing a distributed detailed behavior data acquisition architecture, and analyzing and dumping the SQL statistics and detailed behavior data which are loaded to the file system to a database system table. According to the method, the data acquisition density can be greatly reduced, the system overhead caused by acquisition is reduced, all SQL queries of the load are covered, and the load portrait accuracy is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a distributed database behavior data intelligent collection method and system. Background Art

[0002] After more than half a century of research and development, database systems have achieved rapid technological progress and increasingly excellent performance. However, the traditional database is a static system and pays more attention to the versatility of the system. On the one hand, it will not automatically tune according to historical query performance, nor will it perform personalized system optimization based on the data and query load characteristics of a certain user. On the other hand, the characteristics of large data scale, high query concurrency, and frequent connection operations in big data application scenarios have put forward higher requirements on the performance of database systems.

[0003] In the past 10 years, with the continuous development of machine learning and artificial intelligence technologies, especially the great breakthroughs of deep learning technology in natural language processing, image recognition and other fields, researchers have begun to think about how to apply machine learning, artificial intelligence and other learning and statistical ideas to the database field to improve the adaptability, stability and performance of database systems. Research on database autonomy technology enabled by artificial intelligence has thus begun.

[0004] According to the characteristics of artificial intelligence algorithms, autonomous optimization of databases based on AI requires the accumulation of a large amount of behavioral data for training and decision-making. Therefore, the behavioral data collection of databases is the basis and an indispensable part of all autonomous optimization. In theory, the finer the granularity of behavioral data collection, the wider the scope, and the higher the frequency, the better the optimization improvement that the AI ​​autonomous model can achieve. However, this will also lead to another serious problem, that is, the high system overhead brought by behavioral data collection greatly affects the query performance of the monitored database, thereby causing serious production accidents. Summary of the invention

[0005] The technical task of the present invention is to address the above shortcomings and provide a distributed database behavior data intelligent collection method and system, which can greatly reduce the data collection density, reduce the system overhead caused by the collection, cover all SQL queries of the load, and improve the accuracy of the load portrait.

[0006] The technical solution adopted by the present invention to solve its technical problem is:

[0007] A distributed database behavior data intelligent collection method, the implementation of the method includes:

[0008] Initial collection strategy, responsible for accumulating SQL statistics for clustering-based adaptive data strategy generation;

[0009] Clustering-based adaptive collection strategy generation, used to periodically generate the latest adaptive behavior data collection strategy;

[0010] Cluster-based adaptive collection strategy application is responsible for replacing the original collection strategy and completing the collection of SQL statistics and detailed behavior data based on the latest collection strategy and storing them in the file system;

[0011] Distributed storage without a center is used to build a distributed detailed behavior data collection architecture, and to parse and dump SQL statistics and detailed behavior data written to the file system into the database system table.

[0012] This method first uses the initial collection strategy to collect historical SQL statistics and behavior data; then uses the clustering algorithm to cluster the historical SQL, and generates an adaptive collection strategy based on the clustering results. The initial strategy is further switched to the adaptive collection strategy to collect detailed SQL behavior data; after the collection is completed, the decentralized data storage mechanism is used to complete the persistent storage of behavior data. Compared with the existing data collection strategy, this method can greatly reduce the data collection density, reduce the system overhead caused by the collection, and cover all SQL queries of the load, improving the accuracy of the load profile.

[0013] Furthermore, the initial collection strategy includes database system table construction, memory-resident SQL statistics and detailed behavior data lists, and an initial collection plan.

[0014] Furthermore, the clustering-based adaptive collection strategy generation includes SQL classification based on SQL templates, SQL clustering within the same SQL template, initial collection plan, and periodic clustering update.

[0015] Furthermore, the clustering-based adaptive collection strategy is applied, including a method for updating the resident memory SQL detailed behavior data list.

[0016] Furthermore, the decentralized distributed disk storage mechanism includes the collection of detailed behavior data of distributed operators, the timing of dumping, and the method of updating the dumped data.

[0017] Furthermore, the initial acquisition strategy,

[0018] 1.1) After the user's production environment database environment is initialized, the collection parameters are automatically configured as the initial collection strategy;

[0019] 1.2) Build SQL behavior data system tables sql_stat, sql_plan, and sql_operator:

[0020] 1.2.1) The sql_stat system table contains five columns of attributes, namely sql_text, current_day, current_hour, count, and elapsed time, among which SQL text is the primary key;

[0021] 1.2.2) The sql_plan system table contains multiple columns, including the auto-increment ID column, sql_text, arrive_time, logic_plan_tree, and physical_plan_tree;

[0022] 1.2.3) The sql_operator system table contains multiple columns, including the auto-increment ID column, sql_text, arrive_time, operator_type, input_size, and output_size;

[0023] 1.3) After the initial collection strategy takes effect, the specific implementation is as follows:

[0024] 1.3.1) In each database node, a day is divided into 24 hours, each hour is defined as a bucket, and two types of lists are constructed in the bucket:

[0025] 1.3.1.1) SQL execution statistics hash list SQL Stat, where the Key is the SQL text and the value is a (count, elapsed time) value pair consisting of the execution frequency count and the total execution time elapsed time;

[0026] 1.3.1.2) SQL behavior detailed behavior data list SQL behavior, where the key is the SQL text, and the value is a string consisting of detailed behavior data including SQL logical plan, physical execution plan, input and output data scale of each operator, etc. Special characters are used to separate various data columns; Since each operator in a distributed database is distributed on different nodes for execution, this method is designed to use a decentralized collection and storage mechanism to achieve distributed storage of SQL behavior;

[0027] 1.3.2) When an external SQL query enters node n in the distributed database cluster, check whether the SQL exists in the SQL Stat list in the current bucket of node n; if so, after the SQL query ends, count+1 is added to the execution frequency of the SQL, and the total execution time is updated to elapsed time+current time based on the current query execution time; if not (the SQL appears for the first time in the current bucket), perform the following operations:

[0028] 1.3.2.1) Insert the current SQL text in the SQL Stat list, set the execution frequency count to 1, and the execution time to current time;

[0029] 1.3.2.2) Insert the current SQL book into the SQL behavior list, collect detailed behavior data and fill it into value by forming a string according to the preset delimiter;

[0030] 1.3.3) At the beginning of the next hour, the two lists in the current bucket are stored in the current node file system, and the storage file names are distinguished by the prefix of year-month-day-hour;

[0031] 1.3.4) Implement decentralized distributed storage strategy.

[0032] Furthermore, the clustering-based adaptive collection strategy generation step is initiated one day after the initial SQL execution strategy is executed. The steps of the adaptive SQL collection strategy include:

[0033] 2.1) Read the sql_stat system table and filter out the SQL execution records for the last n days. n is manually specified by the DBA, with a minimum of 1 and a maximum of 366. The reason is that the DBA has a deep understanding of the query load time pattern.

[0034] 2.2) The sql_text in the filtered query records is further abstracted into SQL templates, and each SQL template forms a large class;

[0035] 2.3) In each category, use the total execution time and execution frequency to calculate the average time average_time corresponding to each sql_text;

[0036] 2.4) In each major category, the K-Means clustering algorithm is used to adaptively cluster sql_text according to the average execution time to form multiple clusters;

[0037] 2.5) After clustering is completed, sort the cluster centers and calculate the average cluster center average_center, and perform the following judgment:

[0038] 2.5.1) If the distance between more than 1 / 3 of the adjacent cluster centers is less than average_center*0.2, it is considered that the distance between clusters is too small, resulting in similar execution plans and execution costs of sql_text in adjacent clusters. The clusters are further merged; the number of clusters is reduced to 4 / 5 of the current number, and the process is skipped to step 2.4) to continue.

[0039] 2.5.2) Otherwise, directly jump to step 2.5) and continue to execute;

[0040] 2.6) Repeatedly execute steps 2.3) to 2.5) until all SQL templates are clustered to form a <SQL template, clustering center, whether to collect (initial value is false)> list SQL cluster;

[0041] 2.7) The above adaptive collection strategy generation process is executed once a day;

[0042] Regarding the application of the clustering-based adaptive collection strategy, after the generation of the clustering-based adaptive SQL collection strategy, switch the distributed database collection parameters to adaptive; the steps of the adaptive SQL collection strategy include:

[0043] 3.1) Build 1 bucket every 1 hour inside each database node;

[0044] 3.2) Generate two types of lists, SQL Stat and SQL behavior, inside each bucket;

[0045] 3.3) When an external SQL query enters the distributed database system, the update strategy of SQL behavior is as follows: Remove the parameters from the current SQL statement, process it to form an SQL template, and find the nearest clustering center in the SQL cluster list; if the value of the whether to collect column corresponding to the clustering center is false, then update the value of the whether to collect column to true, insert the current SQL statement into the SQL behavior list, collect detailed behavior data and fill it into the value as a string composed of a preset delimiter;

[0046] 3.4) After the next hour starts, store the SQL stat and SQL behavior lists in the file system of the current node. The naming of the stored file is distinguished by using the time year-month-day-hour as the prefix, and set all the values of the whether to collect column in the SQL cluster list to false;

[0047] 3.5) Execute the distributed disk storage strategy;

[0048] The centerless distributed disk storage mechanism includes the following steps:

[0049] 4.1) When the physical execution plan is transmitted from the gateway node to the operator execution node, synchronously transmit the SQL text sql_text and the SQL arrival time arrive_time to the operator execution node to uniquely identify an SQL query; insert all the current SQL and its behavior data collection content into the corresponding SQL behavior list of the operator execution node;

[0050] 4.2) The disk-storage mechanism monitors the CPU usage of the current node. If the CPU usage of the current node is less than 50% or shows a downward trend compared with the time window of the previous hour, all behavior data before the current period in the file system will be dumped to the database system table, and then the corresponding behavior data disk file in the file system will be deleted;

[0051] 4.2.1) Parse the SQL Stat list and store it in the system table sql_stat. When storing, first check whether sql_text already exists in the current time period. If it does, update its corresponding execution frequency and execution time columns. If it does not exist, directly insert the current record;

[0052] 4.2.2) Parse the SQL behavior list and store it in the system tables sql_plan and sql_operator.

[0053] The present invention also claims protection for a distributed database behavior data intelligent collection system, comprising:

[0054] Initial collection module, used to accumulate SQL statistics for clustering-based adaptive data strategy generation;

[0055] Clustering-based adaptive collection strategy generation module, used to periodically generate the latest adaptive behavior data collection strategy;

[0056] The clustering-based adaptive collection strategy application module is responsible for replacing the original collection strategy and completing the collection of SQL statistics and detailed behavior data based on the latest collection strategy and storing them in the file system;

[0057] The decentralized distributed storage module is used to build a distributed detailed behavior data collection architecture and parse and dump SQL statistics and detailed behavior data written to the file system into the database system table.

[0058] The system realizes distributed database behavior data collection through the above-mentioned distributed database behavior data intelligent collection method.

[0059] The present invention also claims protection for a distributed database behavior data intelligent collection device, comprising: at least one memory and at least one processor;

[0060] The at least one memory is used to store a machine-readable program;

[0061] The at least one processor is used to call the machine-readable program to implement the above method.

[0062] The present invention also claims protection for a computer-readable medium having computer instructions stored thereon, which can implement the above method when executed by a processor.

[0063] Compared with the prior art, the distributed database behavior data intelligent collection method and system of the present invention has the following beneficial effects:

[0064] Distributed database components are numerous and complex in structure. Traditional system tuning that relies on DBA can no longer effectively solve user needs. Therefore, autonomous tuning based on AI has begun to develop rapidly and iteratively. As a necessary pre-process of AI algorithms, database system behavior data collection has also attracted much attention. Traditional behavior data collection usually adopts a static strategy and cannot be adaptive according to the user load mode, resulting in an inability to achieve a balance between collection granularity and collection overhead. Therefore, the present invention provides a distributed database behavior data intelligent collection method, which first collects SQL statistical information and behavior data through an initial collection strategy; then, based on the SQL statistical information collected by the initial collection strategy, a clustering algorithm is used to generate an adaptive collection strategy, which is adaptively updated in units of days; further, the initial collection strategy is switched to an adaptive collection strategy for SQL detailed behavior data collection. After completing the SQL statistical information and detailed behavior data collection, a decentralized distributed disk storage mechanism is used to dump all data to the database system table. Compared with the traditional behavior data collection system and method, the intelligent collection system and method proposed in the present invention can further reduce the collection granularity, thereby reducing the collection overhead, and comprehensively cover all SQL statements of the load, thereby improving the accuracy of the load portrait. BRIEF DESCRIPTION OF THE DRAWINGS

[0065] Figure 1 It is a diagram showing the architecture of a distributed database behavior data intelligent collection method provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0066] The present invention will be further described below in conjunction with specific embodiments.

[0067] The embodiment of the present invention provides a distributed database behavior data intelligent collection method, the implementation of the method includes:

[0068] Initial collection strategy, responsible for accumulating SQL statistics for clustering-based adaptive data strategy generation;

[0069] Clustering-based adaptive collection strategy generation, used to periodically generate the latest adaptive behavior data collection strategy;

[0070] Cluster-based adaptive collection strategy application is responsible for replacing the original collection strategy and completing the collection of SQL statistics and detailed behavior data based on the latest collection strategy and storing them in the file system;

[0071] Distributed storage without a center is used to build a distributed detailed behavior data collection architecture, and to parse and dump SQL statistics and detailed behavior data written to the file system into the database system table.

[0072] The initial collection strategy includes database system table construction, resident SQL statistics and detailed behavior data lists, and initial collection plans.

[0073] The clustering-based adaptive collection strategy generation includes SQL classification based on SQL templates, SQL clustering within the same SQL template, initial collection plans, and periodic clustering updates.

[0074] The application of clustering-based adaptive collection strategy includes a method for updating the detailed behavior data list of resident SQL.

[0075] The decentralized distributed disk storage mechanism includes the collection of detailed behavior data of distributed operators, dumping timing and dumping data updating method.

[0076] This method first uses the initial collection strategy to collect historical SQL statistics and behavior data; then uses the clustering algorithm to cluster the historical SQL, and generates an adaptive collection strategy based on the clustering results. The initial strategy is further switched to the adaptive collection strategy to collect detailed SQL behavior data; after the collection is completed, the decentralized data storage mechanism is used to complete the persistent storage of behavior data. Compared with the existing data collection strategy, this method can greatly reduce the data collection density, reduce the system overhead caused by the collection, and cover all SQL queries of the load, improving the accuracy of the load profile.

[0077] Combination Figure 1 As shown, the specific implementation of this method is as follows:

[0078] 1. Initial collection strategy.

[0079] 1.1. After the user's production environment database environment is initialized, the system automatically configures the collection parameters as the initial collection strategy.

[0080] 1.2. Build SQL behavior data system tables sql_stat, sql_plan and sql_operator:

[0081] 1.2.1、sql_stat system table contains 5 columns, namely sql_text, current_day, current_hour, count, elapsed time, among which SQL text is the primary key;

[0082] 1.2.2, The sql_plan system table contains multiple columns, including the auto-increment ID column, sql_text, arrive_time, logic_plan_tree, and physical_plan_tree;

[0083] 1.2.3. The sql_operator system table contains multiple columns of attributes, including the auto-increment ID column, sql_text, arrive_time, operator_type, input_size, and output_size.

[0084] 1.3. After the initial collection strategy takes effect, the specific implementation is as follows:

[0085] 1.3.1. In each database node, a day is divided into 24 hours, each hour is defined as a bucket, and two types of lists are constructed in the bucket:

[0086] 1.3.1.1、SQL execution statistics hash list SQL Stat, where Key is SQL text, and value is a pair of (count, elapsed time) consisting of execution frequency count and total execution time elapsed time;

[0087] 1.3.1.2、SQL behavior detailed behavior data list SQL behavior, where Key is SQL text, value is a string consisting of detailed behavior data including SQL logical plan, physical execution plan, input and output data scale of each operator, etc., and special characters are used to separate various data columns; because each operator in the distributed database is distributed on different nodes for execution, this patent design adopts a decentralized collection and storage mechanism to realize the distributed storage of SQL behavior. For details, see the introduction of the decentralized storage module component.

[0088] 1.3.2. When an external SQL query enters node n in the distributed database cluster, check whether the SQL exists in the SQL Stat list in the current bucket of node n; if so, after the SQL query ends, count the execution frequency of the SQL + 1, and update the total execution time to elapsed time + current time based on the current query execution time; if not (the SQL appears for the first time in the current bucket), perform the following operations:

[0089] 1.3.2.1. Insert the current SQL text in the SQL Stat list, set the execution frequency count to 1, and the execution time to current time;

[0090] 1.3.2.2. Insert the current SQL book into the SQL behavior list, collect detailed behavior data and fill it into value by forming a string according to the preset delimiter;

[0091] 1.3.3. At the beginning of the next hour, the two lists in the current bucket are stored in the current node file system, and the storage files are named with the prefix of year-month-day-hour to distinguish them;

[0092] 1.3.4. Execute the decentralized distributed storage strategy. For details, see the introduction to the distributed decentralized storage module components.

[0093] 2. Clustering-based adaptive acquisition strategy generation.

[0094] One day after the initial SQL execution strategy is executed, the clustering-based adaptive SQL collection strategy generation step is started. The key steps of the adaptive SQL collection strategy are as follows:

[0095] 2.1. Read the sql_stat system table and filter out the SQL execution records for the last n days. n is manually specified by the DBA, with a minimum of 1 and a maximum of 366. The reason is that the DBA has a deep understanding of the query load time pattern.

[0096] 2.2. The sql_text in the filtered query records is further abstracted into SQL templates, and each SQL template forms a large class;

[0097] 2.3. In each major category, use the total execution time and execution frequency to calculate the average time average_time corresponding to each sql_text;

[0098] 2.4. Within each major category, use the K-Means clustering algorithm to adaptively cluster sql_text according to the average execution time, forming multiple clusters; the initial number of clusters K of the K-Means algorithm is set to 20. This patent believes that although the parameters of sql_text within each cluster are different, they basically have the same execution plan and execution overhead. The execution plans and execution overheads of sql_text in different clusters are very different.

[0099] 2.5. After clustering is completed, sort the cluster centers and calculate the average cluster center average_center, and perform the following judgment:

[0100] 2.5.1. If the distance between more than 1 / 3 of the adjacent cluster centers is less than 0.2 * average_center, it is considered that the distance between the clusters is too small, resulting in the execution plans and execution overheads corresponding to the sql_text in the adjacent clusters still being similar, and further merge the clusters; reduce the number of clusters to 4 / 5 of the current, and jump to step 2.4 to continue execution;

[0101] 2.5.2. Otherwise, directly jump to step 2.5 to continue execution;

[0102] 2.6. Repeatedly execute steps 2.3 to 2.5 until all SQL templates are clustered, forming a list SQL cluster of <SQL template, cluster center, whether to collect (initial value is false)>;

[0103] 2.7. The above process of generating the adaptive collection strategy is executed once a day.

[0104] 3. Application of the adaptive collection strategy based on clustering.

[0105] After the adaptive SQL collection strategy based on clustering is generated, switch the distributed database collection parameters to be adaptive; the key steps of the adaptive SQL collection strategy are as follows:

[0106] 3.1. Similar to the initial collection strategy, build 1 bucket within each database node every 1 hour.

[0107] 3.2. Similar to the initial collection strategy, generate two types of lists, SQL Stat and SQL behavior, within each bucket.

[0108] 3.3. When an external SQL query enters the distributed database system, the update strategy of SQL Stat is the same as the initial collection strategy, and the update strategy of SQL behavior is as follows: remove the parameters of the current SQL statement, process it into an SQL template, and find the nearest cluster center in the SQL cluster list; if the value of the collection column corresponding to the cluster center is false, update the collection column value to true, insert the current SQL statement into the SQL behavior list, collect detailed behavior data and fill it into value according to the preset delimiter to form a string;

[0109] 3.4. At the beginning of the next hour, the SQL stat and SQL behavior lists are stored in the current node file system. The storage files are named with the prefix of year-month-day-hour to distinguish them. The SQL cluster list collection columns are all set to false.

[0110] 3.5. Execute the distributed disk storage strategy. For details, see the introduction to the decentralized distributed disk storage module components.

[0111] 4. No central distributed disk storage mechanism.

[0112] Traditional behavior data storage in distributed databases requires that all behavior data be aggregated to the gateway node for unified storage. This storage mechanism will greatly affect the system query performance. Therefore, this patent designs a decentralized distributed storage mechanism, which is implemented in conjunction with the key steps of the collection strategy as follows:

[0113] 4.1. Since the detailed behavior data collection of the SQL behavior list involves multiple operators distributed on multiple nodes, when the physical execution plan is transmitted from the gateway node to the operator execution node, the SQL text sql_text and SQL arrival time arrive_time are synchronously transmitted to the operator execution node to uniquely identify an SQL query; the current SQL and its behavior data collection content are all inserted into the SQL behavior list corresponding to the operator execution node;

[0114] 4.2. The disk-storage mechanism monitors the CPU usage of the current node. If the CPU usage of the current node is lower than 50% or shows a downward trend compared with the time window of the previous hour, all behavior data before the current period in the file system will be dumped to the database system table, and then the corresponding behavior data disk file in the file system will be deleted;

[0115] 4.2.1、Parse the SQL Stat list and store it in the system table sql_stat. Since the same SQL may be queried in different nodes, when storing it, first check whether sql_text already exists in the current time period. If it does, update its corresponding execution frequency and execution time columns. If it does not exist, directly insert the current record;

[0116] 4.2.2. Parse the SQL behavior list and store it in the system tables sql_plan and sql_operator. Although there may be operator behavior data collection records for the same SQL in different nodes, the clever design of the sql_operator table successfully avoids the existence check before insertion. Therefore, the corresponding operator behavior data can be directly inserted.

[0117] The embodiment of the present invention also provides a distributed database behavior data intelligent collection system, including:

[0118] Initial collection module, used to accumulate SQL statistics for clustering-based adaptive data strategy generation;

[0119] Clustering-based adaptive collection strategy generation module, used to periodically generate the latest adaptive behavior data collection strategy;

[0120] The clustering-based adaptive collection strategy application module is responsible for replacing the original collection strategy and completing the collection of SQL statistics and detailed behavior data based on the latest collection strategy and storing them in the file system;

[0121] The decentralized distributed storage module is used to build a distributed detailed behavior data collection architecture, and parse and dump the SQL statistics and detailed behavior data written to the file system into the database system table.

[0122] The initial collection strategy includes database system table construction, resident SQL statistics and detailed behavior data lists, and initial collection plans.

[0123] The clustering-based adaptive collection strategy generation includes SQL classification based on SQL templates, SQL clustering within the same SQL template, initial collection plans, and periodic clustering updates.

[0124] The application of clustering-based adaptive collection strategy includes a method for updating the detailed behavior data list of resident SQL.

[0125] The decentralized distributed disk storage mechanism includes the collection of detailed behavior data of distributed operators, dumping timing and dumping data updating method.

[0126] The system realizes distributed database behavior data collection through the distributed database behavior data intelligent collection method described in the above embodiment.

[0127] 1. The initial acquisition module,

[0128] 1.1. After the user's production environment database environment is initialized, the system automatically configures the collection parameters as the initial collection strategy.

[0129] 1.2. Build SQL behavior data system tables sql_stat, sql_plan and sql_operator:

[0130] 1.2.1、sql_stat system table contains 5 columns, namely sql_text, current_day, current_hour, count, elapsed time, among which SQL text is the primary key;

[0131] 1.2.2, The sql_plan system table contains multiple columns, including the auto-increment ID column, sql_text, arrive_time, logic_plan_tree, and physical_plan_tree;

[0132] 1.2.3. The sql_operator system table contains multiple columns of attributes, including the auto-increment ID column, sql_text, arrive_time, operator_type, input_size, and output_size.

[0133] 1.3. After the initial collection strategy takes effect, the specific implementation is as follows:

[0134] 1.3.1. In each database node, a day is divided into 24 hours, each hour is defined as a bucket, and two types of lists are constructed in the bucket:

[0135] 1.3.1.1、SQL execution statistics hash list SQL Stat, where Key is SQL text, and value is a pair of (count, elapsed time) consisting of execution frequency count and total execution time elapsed time;

[0136] 1.3.1.2、SQL behavior detailed behavior data list SQL behavior, where Key is SQL text, value is a string consisting of detailed behavior data including SQL logical plan, physical execution plan, input and output data scale of each operator, etc., and special characters are used to separate various data columns; because each operator in the distributed database is distributed on different nodes for execution, this patent design adopts a decentralized collection and storage mechanism to realize the distributed storage of SQL behavior. For details, see the introduction of the decentralized storage module component.

[0137] 1.3.2. When an external SQL query enters node n in the distributed database cluster, check whether the SQL exists in the SQL Stat list in the current bucket of node n; if so, after the SQL query ends, count the execution frequency of the SQL + 1, and update the total execution time to elapsed time + current time based on the current query execution time; if not (the SQL appears for the first time in the current bucket), perform the following operations:

[0138] 1.3.2.1. Insert the current SQL text in the SQL Stat list, set the execution frequency count to 1, and the execution time to current time;

[0139] 1.3.2.2. Insert the current SQL book into the SQL behavior list, collect detailed behavior data and fill it into value by forming a string according to the preset delimiter;

[0140] 1.3.3. At the beginning of the next hour, the two lists in the current bucket are stored in the current node file system, and the storage files are named with the prefix of year-month-day-hour to distinguish them;

[0141] 1.3.4. Execute the decentralized distributed storage strategy. For details, see the introduction to the distributed decentralized storage module components.

[0142] 2. The clustering-based adaptive acquisition strategy generation module,

[0143] One day after the initial SQL execution strategy is executed, the clustering-based adaptive SQL collection strategy generation step is started. The key steps of the adaptive SQL collection strategy are as follows:

[0144] 2.1. Read the sql_stat system table and filter out the SQL execution records for the last n days. n is manually specified by the DBA, with a minimum of 1 and a maximum of 366. The reason is that the DBA has a deep understanding of the query load time pattern.

[0145] 2.2. Further abstract the sql_text in the filtered query records into SQL templates, and each SQL template forms a major category;

[0146] 2.3. Inside each major category, calculate the average time average_time corresponding to each sql_text using the total execution time and execution frequency;

[0147] 2.4. Inside each major category, adaptively cluster the sql_text using the K-Means clustering algorithm according to the average execution time to form multiple clusters; the initial number of clusters K of the K-Means algorithm is set to 20. This patent believes that although the parameters of the sql_text within each cluster are different, they basically have the same execution plan and execution overhead. The execution plans and execution overheads of the sql_text in different clusters are very different.

[0148] 2.5. After clustering, sort the cluster centers and calculate the average cluster center average_center, and perform the following judgment:

[0149] 2.5.1. If the distance between more than 1 / 3 of the adjacent cluster centers is less than 0.2 * average_center, it is considered that the distance between the clusters is too small, resulting in the execution plans and execution overheads corresponding to the sql_text in the adjacent clusters still being similar, and further merge the clusters; reduce the number of clusters to 4 / 5 of the current number, and jump to step 2.4 to continue execution;

[0150] 2.5.2. Otherwise, directly jump to step 2.5 to continue execution;

[0151] 2.6. Repeatedly execute steps 2.3 to 2.5 until all SQL templates are clustered to form a list SQL cluster of <SQL template, cluster center, whether to collect (initial value is false)>;

[0152] 2.7. The above process of generating the adaptive collection strategy is executed once a day.

[0153] 3. The adaptive collection strategy application module based on clustering,

[0154] After the generation of the adaptive SQL collection strategy based on clustering, switch the distributed database collection parameters to adaptive; the key steps of the adaptive SQL collection strategy are as follows:

[0155] 3.1. Similar to the initial collection strategy, build 1 bucket every 1 hour inside each database node.

[0156] 3.2. Similar to the initial collection strategy, generate two types of lists, SQL Stat and SQL behavior, inside each bucket.

[0157] 3.3. When an external SQL query enters the distributed database system, the update strategy of SQL Stat is the same as the initial collection strategy, and the update strategy of SQL behavior is as follows: remove the parameters of the current SQL statement, process it into an SQL template, and find the nearest cluster center in the SQL cluster list; if the value of the collection column corresponding to the cluster center is false, update the collection column value to true, insert the current SQL statement into the SQL behavior list, collect detailed behavior data and fill it into value according to the preset delimiter to form a string;

[0158] 3.4. At the beginning of the next hour, the SQL stat and SQL behavior lists are stored in the current node file system. The storage files are named with the prefix of year-month-day-hour to distinguish them. The SQL cluster list collection columns are all set to false.

[0159] 3.5. Execute the distributed disk storage strategy. For details, see the introduction to the decentralized distributed disk storage module components.

[0160] 4. The decentralized distributed storage mechanism module,

[0161] Traditional behavior data storage in distributed databases requires that all behavior data be aggregated to the gateway node for unified storage. This storage mechanism will greatly affect the system query performance. Therefore, this patent designs a decentralized distributed storage mechanism, which is implemented in conjunction with the key steps of the collection strategy as follows:

[0162] 4.1. Since the detailed behavior data collection of the SQL behavior list involves multiple operators distributed on multiple nodes, when the physical execution plan is transmitted from the gateway node to the operator execution node, the SQL text sql_text and SQL arrival time arrive_time are synchronously transmitted to the operator execution node to uniquely identify an SQL query; the current SQL and its behavior data collection content are all inserted into the SQL behavior list corresponding to the operator execution node;

[0163] 4.2. The disk-storage mechanism monitors the CPU usage of the current node. If the CPU usage of the current node is lower than 50% or shows a downward trend compared with the time window of the previous hour, all behavior data before the current period in the file system will be dumped to the database system table, and then the corresponding behavior data disk file in the file system will be deleted;

[0164] 4.2.1、Parse the SQL Stat list and store it in the system table sql_stat. Since the same SQL may be queried in different nodes, when storing it, first check whether sql_text already exists in the current time period. If it does, update its corresponding execution frequency and execution time columns. If it does not exist, directly insert the current record;

[0165] 4.2.2. Parse the SQL behavior list and store it in the system tables sql_plan and sql_operator. Although there may be operator behavior data collection records for the same SQL in different nodes, the clever design of the sql_operator table successfully avoids the existence check before insertion. Therefore, the corresponding operator behavior data can be directly inserted.

[0166] The embodiment of the present invention also provides a distributed database behavior data intelligent collection device, including: at least one memory and at least one processor;

[0167] The at least one memory is used to store a machine-readable program;

[0168] The at least one processor is used to call the machine-readable program to implement the distributed database behavior data intelligent collection method described in the above embodiment.

[0169] The embodiment of the present invention also provides a computer-readable medium, on which computer instructions are stored, and when the computer instructions are executed by a processor, the distributed database behavior data intelligent collection method described in the above embodiment is implemented. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code that implements the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0170] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

[0171] The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer by a communication network.

[0172] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.

[0173] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.

[0174] The present invention has been shown and described in detail above through the accompanying drawings and preferred embodiments. However, the present invention is not limited to these disclosed embodiments. Based on the above multiple embodiments, those skilled in the art can know that the code review methods in the above different embodiments can be combined to obtain more embodiments of the present invention, and these embodiments are also within the protection scope of the present invention.

Claims

1. A distributed database behavior data intelligent collection method, characterized in that: The implementation of this method includes: Initial collection strategy, responsible for accumulating SQL statistics for clustering-based adaptive data strategy generation; Clustering-based adaptive collection strategy generation, used to periodically generate the latest adaptive behavior data collection strategy; Cluster-based adaptive collection strategy application is responsible for replacing the original collection strategy and completing the collection of SQL statistics and detailed behavior data based on the latest collection strategy and storing them in the file system; Distributed storage without a center is used to build a distributed detailed behavior data collection architecture, and to parse and dump SQL statistics and detailed behavior data written to the file system into the database system table.

2. A distributed database behavior data intelligent collection method according to claim 1, characterized in that: The initial collection strategy includes database system table construction, resident SQL statistics and detailed behavior data list, and initial collection plan.

3. A distributed database behavior data intelligent collection method according to claim 1, characterized in that: The clustering-based adaptive collection strategy generation includes SQL classification based on SQL templates, SQL clustering within the same SQL template, initial collection plans, and periodic clustering updates.

4. A distributed database behavior data intelligent collection method according to claim 1, characterized in that: The application of clustering-based adaptive collection strategy includes a method for updating the detailed behavior data list of resident SQL.

5. The method for intelligently collecting behavior data from a distributed database according to claim 1, characterized in that: The decentralized distributed disk storage mechanism includes the collection of detailed behavior data of distributed operators, dumping timing and dumping data updating method.

6. A distributed database behavior data intelligent collection method according to claim 1, characterized in that: The initial acquisition strategy, 1.1) After the user's production environment database environment is initialized, the collection parameters are automatically configured as the initial collection strategy; 1.2) Build SQL behavior data system tables sql_stat, sql_plan, and sql_operator: 1.2.1) The sql_stat system table contains five columns of attributes, namely sql_text, current_day, current_hour, count, and elapsed time, among which SQL text is the primary key; 1.2.2) The sql_plan system table contains multiple columns, including the auto-increment ID column, sql_text, arrive_time, logic_plan_tree, and physical_plan_tree; 1.2.3) The sql_operator system table contains multiple columns, including the auto-increment ID column, sql_text, arrive_time, operator_type, input_size, and output_size; 1.3) After the initial collection strategy takes effect, the specific implementation is as follows: 1.3.1) In each database node, a day is divided into 24 hours, each hour is defined as a bucket, and two types of lists are constructed in the bucket: 1.3.1.1) SQL execution statistics hash list SQL Stat, where the Key is the SQL text and the value is a (count, elapsed time) value pair consisting of the execution frequency count and the total execution time elapsed time; 1.3.1.2) SQL behavior, where the key is the SQL text and the value is a string consisting of detailed behavior data including the SQL logical plan, physical execution plan, and the input and output data scale of each operator. Special characters are used to separate the data columns. 1.3.2) When an external SQL query enters node n in the distributed database cluster, check whether the SQL exists in the SQLStat list in the current bucket of node n; if it exists, count+1 is added to the execution frequency of the SQL after the SQL query ends, and the total execution time is updated to elapsed time+current time based on the current query execution time; if it does not exist, perform the following operations: 1.3.2.1) Insert the current SQL text in the SQL Stat list, set the execution frequency count to 1, and the execution time to current time; 1.3.2.2) Insert the current SQL book into the SQL behavior list, collect detailed behavior data and fill it into value by forming a string according to the preset delimiter; 1.3.3) At the beginning of the next hour, the two lists in the current bucket are stored in the current node file system, and the storage file names are distinguished by the prefix of year-month-day-hour; 1.3.4) Implement decentralized distributed storage strategy.

7. A distributed database behavior data intelligent collection method according to claim 6, characterized in that the clustering-based adaptive collection strategy generation step is started one day after the initial SQL execution strategy is executed, and the steps of the adaptive SQL collection strategy include: 2.1) Read the sql_stat system table and filter out the SQL execution records for the last n days; n is manually specified by the DBA, with a minimum of 1 and a maximum of 366; 2.2) The sql_text in the filtered query records is further abstracted into SQL templates, and each SQL template forms a large class; 2.3) In each category, use the total execution time and execution frequency to calculate the average time average_time corresponding to each sql_text; 2.4) In each major category, the K-Means clustering algorithm is used to adaptively cluster sql_text according to the average execution time to form multiple clusters; 2.5) After clustering is completed, sort the cluster centers and calculate the average cluster center average_center, and perform the following judgment: 2.5.1) If the distance between more than 1 / 3 of the adjacent cluster centers is less than average_center*0.2, it is considered that the distance between clusters is too small, resulting in similar execution plans and execution costs of sql_text in adjacent clusters. The clusters are further merged; the number of clusters is reduced to 4 / 5 of the current number, and the process is skipped to step 2.4) to continue. 2.5.2) Otherwise, directly jump to step 2.5) and continue execution; 2.6) Repeatedly execute steps 2.3) to 2.5) until all SQL templates are clustered to form a list SQL cluster of <SQL template, clustering center, whether to collect (initial value is false)>; 2.7) The above process for generating the adaptive collection strategy is executed once a day; For the application of the clustering-based adaptive collection strategy, after the generation of the clustering-based adaptive SQL collection strategy, switch the distributed database collection parameters to adaptive; The steps of the adaptive SQL collection strategy include: 3.1) Build one bucket every hour within each database node; 3.2) Generate two types of lists, SQL Stat and SQL behavior, within each bucket; 3.3) When an external SQL query enters the distributed database system, the update strategy for SQL behavior is as follows: Remove the parameters from the current SQL statement, process it to form an SQL template, and find the nearest clustering center in the SQL cluster list; If the value of the "whether to collect" column corresponding to the clustering center is false, then update the value of the "whether to collect" column to true, insert the current SQL statement into the SQL behavior list, collect detailed behavior data, and fill the value with a string formed according to the preset delimiter; 3.4) After the next hour starts, store the SQL stat and SQL behavior lists in the file system of the current node. The naming of the stored file is distinguished by using the time year-month-day-hour as the prefix, and set all values of the "whether to collect" column in the SQL cluster list to false; 3.5) Execute the distributed disk storage strategy; The centerless distributed disk storage mechanism includes the following steps: 4.1) When the physical execution plan is transmitted from the gateway node to the operator execution node, synchronously transmit the SQL text sql_text and the SQL arrival time arrive_time to the operator execution node to uniquely identify an SQL query; Insert all the collected content of the current SQL and its behavior data into the corresponding SQL behavior list of the operator execution node; 4.2) The disk storage mechanism monitors the CPU usage rate of the current node. If the CPU usage rate at the current time node is lower than 50% or shows a downward trend compared with the CPU usage rate in the previous hour time window, then dump all the behavior data before the current time period in the file system to the database system table, and then delete the corresponding behavior data disk files in the file system; 4.2.1) Parse the SQL Stat list and store it in the system table sql_stat. When storing, first query whether the sql_text exists in the current time period. If it exists, update its corresponding execution frequency and execution time columns. If it does not exist, directly insert the current record; 4.2.2) Parse the SQL behavior list and store it in the system tables sql_plan and sql_operator.

8. A distributed database behavior data intelligent collection system, characterized by comprising: Initial collection module, used to accumulate SQL statistics for clustering-based adaptive data strategy generation; Clustering-based adaptive collection strategy generation module, used to periodically generate the latest adaptive behavior data collection strategy; The clustering-based adaptive collection strategy application module is responsible for replacing the original collection strategy and completing the collection of SQL statistics and detailed behavior data based on the latest collection strategy and storing them in the file system; The decentralized distributed storage module is used to build a distributed detailed behavior data collection architecture and parse and dump SQL statistics and detailed behavior data written to the file system into the database system table. The system realizes distributed database behavior data collection through the distributed database behavior data intelligent collection method described in any one of claims 1 to 7.

9. A distributed database behavior data intelligent collection device, characterized in that: include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is used to call the machine-readable program to implement the method described in any one of claims 1 to 7.

10. A computer-readable medium, characterized in that The computer readable medium stores computer instructions, which, when executed by a processor, can implement the method according to any one of claims 1 to 7.

Images