A database management method and apparatus

Abstract

The application provides a database management method and device. The database management method comprises: obtaining statistical data of a first database resource in a database, the statistical data being statistical use data of the first database resource; extracting a first use feature of the first database resource from the statistical data; inputting the first use feature into a competition evaluation model to obtain an evaluation result, the competition evaluation model being trained based on a second use feature of at least one database resource; and outputting competition warning information when the evaluation result indicates that the second database resource is an abnormal resource, the competition warning information being used to prompt that the second database resource has a competition bottleneck, and the second database resource being part or all of the first database resource. In the application, the competition evaluation model is used to identify the database resource having the competition bottleneck in the database system, so that the competition bottleneck can be accurately located.

Description

Technical Field

[0001] This application relates to the field of information technology, and in particular to a database management method and apparatus. Background Technology

[0002] With the acceleration of informatization and the explosive growth of data, database management systems (DBMS) are playing an increasingly important role as core software for data management and data analysis.

[0003] Online transaction processing (OLTP) is the primary application of traditional relational databases, focusing primarily on transaction processing efficiency, typically measured in transactions per second. OLTP systems emphasize database memory efficiency, command rates for various memory metrics, bind variables, and concurrent operations.

[0004] Currently, the high concurrency of OLTP systems leads to frequent resource contention within the system. Furthermore, due to the large number of resources within the system, it is impossible to accurately pinpoint the location of the database resource where the competitive bottleneck occurs. Summary of the Invention

[0005] This application provides a database management method and apparatus to accurately locate database resources where a contention bottleneck occurs.

[0006] Firstly, this application provides a database management method that can be applied in a database management system. The method may include: obtaining statistical data on a first database resource in the database, wherein the statistical data is the usage data of the first database resource obtained through statistics; extracting a first usage feature of the first database resource from the statistical data; inputting the first usage feature into a competition evaluation model to obtain an evaluation result; and outputting a competition alarm message when the evaluation result indicates that a second database resource is an abnormal resource, the competition alarm message being used to indicate that a competition bottleneck has occurred in the second database resource. Here, the competition evaluation model is trained based on a second usage feature of the first database resource, and the second database resource is part or all of the first database resource; that is, the second database resource can be one or more first database resources.

[0007] In some possible implementations, the first database resource mentioned above can be a lock in the database (which can also be understood as the critical section corresponding to the lock), a page, etc.

[0008] Understandably, the above competition evaluation model is trained based on the second usage features of the first database resource. Here, the second usage features can be usage feature samples of the first database resource specifically used to train the competition evaluation model, or usage features extracted from the historical usage data (or existing usage data) of the first database resource.

[0009] In some possible implementations, the output contention alert information can carry the identifier of the second database resource. This allows the database administrator (DBA) to accurately locate the database resource experiencing the contention bottleneck based on the identifier of the second database resource after seeing the contention alert information. Consequently, the DBA can perform precise tuning on the contention bottleneck, thereby improving the maintainability of the database.

[0010] In this application, a contention assessment model is used to identify database resources experiencing contention bottlenecks in the database system, thus achieving accurate location of these bottleneck resources. Furthermore, after locating the bottleneck, the database management system informs the DBA, enabling the DBA to precisely optimize the bottleneck, significantly reducing the DBA's workload and helping them to promptly identify problems, thereby improving database maintainability.

[0011] In some possible implementations, the method further includes: when the evaluation result indicates that the second database resource is an abnormal resource, obtaining the tuning information corresponding to the second database resource; outputting the tuning information, which is used to suggest the tuning strategy associated with the second database resource.

[0012] Understandably, to help DBAs analyze the status of the database system, the database management system can also associate different tuning strategies with different secondary database resources. When the evaluation results indicate that a secondary database resource is an abnormal resource, the database management system can output not only contention alerts but also tuning information associated with the secondary database resource. This suggests suitable tuning strategies for the secondary database resource, allowing DBAs to quickly locate performance issues, analyze the system status under load, and provide corresponding tuning solutions based on both contention alerts and tuning information, thereby enhancing the maintainability of the database system.

[0013] In some possible implementations, obtaining statistical data of the first database resource in the database includes: querying a first view in the database corresponding to the first database resource to obtain statistical data, wherein the first view is used to collect usage data of the first database resource.

[0014] Understandably, the first view can be either a lock view or a page view.

[0015] In some possible implementations, when the first database resource includes at least one type of lock in the database, for the first lock among the at least one type of lock, the first view includes one or a combination of the following data: the number of queries for the first lock, the blocking sleep time of the first lock, the number of times the acquisition of the first lock failed, and the number of times the acquisition of the first lock was successful. Here, the first lock can be understood as any type of lock among the at least one type of lock.

[0016] In this application, when the first database resource includes at least one type of lock in the database, the first view may further include: the identifier of the session to which the first lock belongs and the lock type of the first lock.

[0017] In some possible implementations, the first database resource includes at least one type of lock in the database, and the second database resource is an exception lock.

[0018] In some possible implementations, when the second database resource is a bucket lock (SPIN_BUCKET), the contention alarm message carries the page identifier associated with the bucket lock.

[0019] In some possible implementations, the first usage feature is input into the competitive evaluation model to obtain an evaluation result, including: calculating the distance between the first usage feature and the winning feature; if the distance exceeds a preset threshold, the first evaluation result is output, which is used to indicate that the second database resource is an abnormal resource; or, if the distance does not exceed the preset threshold, the second evaluation result is output, which is used to indicate that the second database resource is a non-abnormal resource.

[0020] In some possible implementations, when the first database resource includes at least one page in the database, for the first page among the at least one page, the first view includes one or a combination of the following data: the number of lock queries on the bucket containing the first page, the number of copies of the first page, and the length of the bucket containing the first page. Here, the first page can be understood as any one of the at least one pages.

[0021] In this application, when the first database resource includes at least one page in the database, the aforementioned first view may further include: an identifier of the storage pool where the first page is located, a file to which the first page belongs, and an identifier of the first page.

[0022] In some possible implementations, the second database resource is a hot page when the first database resource includes at least one page in the database.

[0023] In some possible implementations, the first usage feature is input into a competitive evaluation model to obtain an evaluation result, including: performing binary classification on the first usage feature using a logistic regression function and outputting an evaluation result, wherein the evaluation result is used to indicate whether the second database resource is an anomalous resource.

[0024] In some possible implementations, querying a first view in the database corresponding to a first database resource includes querying the first view using a selection statement (such as a select statement).

[0025] Secondly, this application provides a database management device, which can be a database management system or a chip or system-on-a-chip within the database management system, or a functional module within the database management system for implementing the methods described in the first aspect or any possible embodiment of the first aspect. The database management device can perform the functions of the database management system described in the first aspect or any possible embodiment of the first aspect, and these functions can be implemented by hardware executing corresponding software. The hardware or software here may include one or more modules corresponding to the aforementioned functions. The database management device includes: a feature extraction module, configured to: acquire statistical data of a first database resource in the database, the statistical data being usage data of the first database resource obtained through statistics; extract a first usage feature of the first database resource from the statistical data; an evaluation module, configured to input the first usage feature into a competitive evaluation model to obtain an evaluation result, the competitive evaluation model being trained based on a second usage feature of the first database resource; and an alarm module, configured to output a competitive alarm message when the evaluation result indicates that the second database resource is an abnormal resource, the competitive alarm message indicating that a competitive bottleneck has occurred in the second database resource, and the second database resource being part or all of the first database resource.

[0026] In some possible implementations, the alarm module is also used to: obtain tuning information corresponding to the second database resource when the evaluation result indicates that the second database resource is an abnormal resource; output the tuning information, which is used to prompt the tuning strategy associated with the second database resource.

[0027] In some possible implementations, the feature extraction module is specifically used to query the first view in the database corresponding to the first database resource to obtain statistical data, wherein the first view is used to collect usage data of the first database resource.

[0028] In some possible implementations, when the first database resource includes at least one type of lock in the database, for the first lock in the at least one type of lock, the first view includes one or a combination of the following data: the number of queries for the first lock, the blocking sleep time of the first lock, the number of times the first lock was failed to be acquired, and the number of times the first lock was successfully acquired.

[0029] In some possible implementations, the second database resource is an exception lock.

[0030] In some possible implementations, when the second database resource is a bucket-level lock, the contention alarm message carries the page identifier associated with the bucket-level lock.

[0031] In some possible implementations, the evaluation module is specifically used to: calculate the distance between the first used feature and the winning feature; if the distance exceeds a preset threshold, output a first evaluation result, which is used to indicate that the second database resource is an abnormal resource; or, if the distance does not exceed the preset threshold, output a second evaluation result, which is used to indicate that the second database resource is a non-abnormal resource.

[0032] In some possible implementations, when the first database resource includes at least one page in the database, for the first page among the at least one pages, the first view includes one or a combination of the following data: the number of lock queries on the bucket containing the first page, the number of copies of the first page, and the length of the bucket containing the first page.

[0033] In some possible implementations, the second database resource is a hot page.

[0034] In some possible implementations, the evaluation module is specifically used to: perform binary classification on the first usage feature using a logistic regression function and output an evaluation result, wherein the evaluation result is used to indicate whether the second database resource is an anomalous resource.

[0035] In some possible implementations, a feature extraction module is used to query the first view via a selection interface.

[0036] In some possible implementations, the first database resource includes a lock or a page.

[0037] Thirdly, this application provides a database management system, including: a data management device as described in the second aspect and any of its embodiments above.

[0038] Fourthly, this application provides an electronic device, including: a processor and a memory; the processor is coupled to the memory; the processor is configured to read and execute instructions in the memory to implement the database management method as described in the first aspect or any possible embodiment of the first aspect.

[0039] Fifthly, this application provides a computer-readable storage medium storing instructions that, when executed on a computer, perform the database management method as described in the first aspect or any possible implementation thereof.

[0040] Sixthly, this application provides a computer program product that, when executed on a computer, causes the computer to implement the database management method as described in the first aspect or any possible implementation thereof.

[0041] It should be understood that the second to sixth aspects of this application are consistent with the technical solutions of the first aspect of this application, and the beneficial effects achieved by each aspect and the corresponding feasible implementation are similar, so they will not be described again. Attached Figure Description

[0042] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the embodiments of this application or the background art will be described below.

[0043] Figure 1 A schematic diagram of a typical logical architecture of a database system is shown.

[0044] Figure 2 A flowchart illustrating a database management method according to an embodiment of this application is shown;

[0045] Figure 3 An example of dv_spinlocks and dv_page in an embodiment of this application is shown;

[0046] Figure 4 A flowchart illustrating another database management method according to an embodiment of this application is shown;

[0047] Figure 5 A flowchart illustrating another database management method according to an embodiment of this application is shown;

[0048] Figure 6 A schematic diagram of the structure of a database management device according to an embodiment of this application is shown;

[0049] Figure 7 A schematic diagram of the structure of an electronic device according to an embodiment of this application is shown. Detailed Implementation

[0050] The embodiments of this application are described below with reference to the accompanying drawings. In the following description, reference is made to the accompanying drawings, which form part of this application and illustrate specific aspects of the embodiments of this application or to which specific aspects of the embodiments of this application may be used. It should be understood that the embodiments of this application can be used in other aspects and may include structural or logical variations not depicted in the drawings. For example, it should be understood that the disclosure of the described methods can be equally applied to corresponding devices or systems for performing the methods, and vice versa. For example, if one or more specific method steps are described, the corresponding device may include one or more units, such as functional units, to perform the described one or more method steps (e.g., one unit performs one or more steps, or multiple units, each performing one or more of the multiple steps), even if such one or more units are not explicitly described or illustrated in the drawings. On the other hand, for example, if a specific apparatus is described based on one or more units, such as functional units, the corresponding method may include a step to perform the functionality of one or more units (e.g., one step performs the functionality of one or more units, or multiple steps, each performing the functionality of one or more of the multiple units), even if such one or more steps are not explicitly described or illustrated in the drawings. Furthermore, it should be understood that, unless otherwise expressly stated, the features of the various exemplary embodiments and / or aspects described herein may be combined with each other.

[0051] OLTP (Online Transaction Processing) refers to systems with very high transactional requirements. These are typically highly available online systems that primarily handle small transactions and queries. When evaluating such systems, the number of transactions executed per second and the number of structured query language (SQL) statements executed are usually considered. In OLTP systems, a single database often processes hundreds or even thousands of transactions per second, and the number of structured query statements (such as SELECT statements) executed per second can reach thousands or even tens of thousands. Therefore, OLTP systems handle very high concurrency (i.e., high-concurrency scenarios).

[0052] For systems with high concurrency, different users (i.e., process sessions) can access different resources or the same resources, resulting in a large amount of resource consumption. When multiple users access the same resource simultaneously, resource contention may occur. When a sufficient number of users access the same resource, that resource may become a performance bottleneck (also known as a performance blocking point or contention bottleneck). However, whether users are competing, if so, where the competition is occurring, and whether it has reached a performance bottleneck cannot be accurately determined through simple system-level monitoring alone.

[0053] To address this issue, this application provides a database management method that can be applied to a database system (DBS). Figure 1 A schematic diagram of a typical logical architecture of a database system is shown below. Figure 1 As shown, the database system 100 includes a database (DB) 110 and a database management system (DBMS) 120.

[0054] Database 110 is an organized collection of data stored in data storage; that is, a related collection of data organized, stored, and used according to a specific data model. Depending on the data model used to organize the data, data can be divided into several types, such as relational data, graph data, and time series data. Relational data is data modeled using a relational model, usually represented as tables, where rows represent a set of related values ​​for an object or entity. Graph data, simply called a "graph," is used to represent relationships between objects or entities, such as social relationships. Time series data is a series of data columns recorded and indexed in chronological order, used to describe the state changes of an object over time.

[0055] In practical applications, data storage can include, but is not limited to, solid-state drives (SSDs), disk arrays, cloud storage, or other types of non-transitory computer-readable storage media. Those skilled in the art will understand that a database system may include more than […]. Figure 1 The components shown are fewer or more components, or include those with Figure 1 The components shown are different components. Figure 1 Only components more relevant to the implementation disclosed in the embodiments of the present invention are shown.

[0056] The database management system 120 is the core of the database system, serving as system software for organizing, storing, and maintaining data. Clients 130 can access the database 110 through the database management system 120, and database administrators (DBAs) also use the database management system 120 for database maintenance. The database management system 120 provides various functions for clients 130 to create, modify, and query the database. Clients 130 can be applications or user devices. The functions provided by the database management system 120 may include, but are not limited to, the following: (1) Data definition function: The database management system 120 provides a data definition language (DDL) to define the structure of the database 110. The DDL is used to describe the database framework and can be stored in the data dictionary; (2) Data access function: The database management system 120 provides a data manipulation language (DML) to implement basic access operations on the database 110, such as retrieval, insertion, modification and deletion; (3) Database operation management function: The database management system 120 provides data control functions to effectively control and manage the operation of the database 110 to ensure that the data is correct and valid; (4) Database creation and maintenance function: including the loading of initial database data, database dumping, recovery, reorganization, system performance monitoring, analysis and other functions; (5) Database transmission: The database management system 120 provides data transmission to realize communication between the client 130 and the database management system 120, which is usually coordinated with the operating system.

[0057] The database management method provided in the embodiments of this application will be described below in conjunction with the above-described database system.

[0058] Figure 2 A flowchart illustrating a database management method according to an embodiment of this application is shown below. Figure 2 As shown, the database management method 200 may include:

[0059] S201, the database management system obtains statistical data of at least one database resource (i.e., the first database resource).

[0060] It is understandable that a database management system can obtain statistical data of the first database resource in various ways, and this application embodiment does not impose specific limitations. The statistical data of at least one database resource can be the usage data of at least one database resource in the database obtained over a period of time. Here, the usage data of multiple database resources can be counted simultaneously, or the usage data of one database resource can be counted; the specific method can be determined according to actual needs, and this application embodiment does not impose specific limitations.

[0061] For example, a database management system can access the database according to a preset period (such as 1 hour, 5 hours, 1 day, or 1 week) to obtain statistical data on at least one database resource within that period. In this case, the statistical data can be the usage data of at least one database resource within a period. The preset period mentioned here can be understood as a statistical period, maintenance period, or monitoring period set by the database management system for the database. For high-concurrency scenarios, the preset period can also be set to a shorter duration, such as 5 seconds, 1 minute, or 5 minutes. Those skilled in the art can set the duration of the preset period according to actual needs; this application embodiment does not impose specific limitations.

[0062] Alternatively, the database management system can access the database based on the DBA's actions to obtain statistical data on at least one database resource within the current period. For example, if the DBA triggers a contention check on the database management system's front-end interface, the database management system responds by accessing the database and obtaining usage data for at least one database resource from the time of the last contention check until the current moment.

[0063] Furthermore, the database management system can also access the database when a trigger condition is met to obtain statistical data on at least one database resource within the current period. For example, the trigger condition could be a contention bottleneck in the database system or a surge in database access volume. Of course, the trigger condition can be configured by the user according to actual needs, or it can be a default configuration; this application embodiment does not impose specific limitations.

[0064] In some possible implementations, the aforementioned database resources can be database locks (which can also be understood as the critical section corresponding to the lock), pages, etc.

[0065] For example, when the aforementioned database resource is a lock, for the i-th type of lock in a session (let's call it spinlock_i, where i is a positive integer), the usage data of the aforementioned database resource (i.e., the lock usage data) may include at least one of the following features: the number of requests for spinlock_i, the blocking sleep time of spinlock_i, the number of failures to acquire spinlock_i, and the number of successes to acquire spinlock_i. Of course, the lock usage data may also include other usage data for various types of locks, which are not specifically limited in this embodiment.

[0066] Alternatively, when the aforementioned database resource is a page, for the j-th page in a cache pool (let's call it page_j, where j is a positive integer), the usage data of the aforementioned database resource (i.e., the page usage data) may include at least one of the following features: the number of queries (spins) of the bucket-level lock of the bucket where page_j is located, the number of copies (memcpys) of page_j, and the length (bucket_len) of the bucket where page_j is located.

[0067] In practical applications, the identifier of the storage pool where page_j resides (pool ID), the filename of the file to which page_j belongs (file), and the page identifier of page_j (page ID) are required.

[0068] Of course, page usage data may also include other page usage data, but this application embodiment does not specifically limit it.

[0069] It should be noted that when the aforementioned database resources are other resources, the usage data of these database resources may include other data. Those skilled in the art can set them according to actual applications, and the embodiments of this application do not make specific limitations.

[0070] In some possible implementations, in order to collect statistics on the usage of database resources, views (such as lock views, page views, etc.) corresponding to the database resources can be created (or added) in the database. The database management system can obtain the corresponding statistical data by querying different views.

[0071] For example, a lock view (e.g., dv_spinlocks) corresponding to various types of locks can be added to the database. dv_spinlocks is used to query lock usage data in real time. The database management system can then access the database and query dv_spinlocks to obtain statistical data for various types of locks. Alternatively, a page view (e.g., dv_page) corresponding to pages can be added to the database. dv_page is used to statistically analyze the usage data of all pages within the current period. The database management system can then access the database and query dv_page to obtain statistical data for all pages.

[0072] Optionally, dv_spinlocks can be counted for each session, and dv_page can be counted for each cache pool.

[0073] In the embodiments of this application, for example, for an OLTP system, the locks described above may be, but are not limited to, those shown in Table 1 below.

[0074] Table 1

[0075]

[0076] Of course, the above-mentioned locks may also include other types of mutex locks and spin locks in OLTP systems, as well as mutex locks and spin locks in other database systems. This application does not specifically limit the implementation of these locks.

[0077] Therefore, for various locks in the database, `dv_spinlocks` can include statistically obtained lock usage data (also known as lock usage data). For each page in the database, `dv_page` can include statistically obtained page usage data (also known as page usage data). Furthermore, to identify various locks, in addition to lock usage data, `dv_spinlocks` can also include: the session ID of each lock and the lock type; to identify each page, in addition to page usage data, `dv_page` can also include: the pool ID of each page, the filename of the file to which each page belongs, and the page ID.

[0078] For example, Figure 3 An example of dv_spinlocks and dv_page in an embodiment of this application is shown; see [link to relevant documentation]. Figure 3 As shown, Figure 3(a) in the table represents dv_spinlocks. In dv_spinlocks, the lock usage data of 10 types of locks, namely TXN, TXN_LIST, INC_SCN, REDO_BUFFER, COMMIT_QUEUE, CKPT_QUEUE, BUFFER, BUCKET, SPACE, and LOG_FLUSH, are counted in the session with session ID 0. Among them, the spins, sleeps, and failures values ​​of these 10 types of locks are all 0. The requests value of TXN_LIST is 595968, the requests value of REDO_BUFFER is 1, the requests value of CKPT_QUEUE is 2, the requests value of BUFFER is 121, the requests value of BUCKET is 10796, and the requests value of LOG_FLUSH is 1. Figure 3 In (b), dv_page is the cache pool with pool ID 0. The spins value of page 1 (i.e., page ID 1) belonging to file 1 (i.e., file with file ID 1) is 0, the spins value of page 2 belonging to file 1 is 3, the spins value of page 2 belonging to file 3 is 0, and the memcpys value and bucket_len value of page 1 belonging to file 1, page 2 belonging to file 1, and page 2 belonging to file 3 are all 0.

[0079] It should be noted that the above Figure 3 The dv_spinlocks and dv_page in the text are merely examples of lock views and page views, and are not intended to limit lock views and page views.

[0080] In some possible embodiments, when a database management system queries a view corresponding to at least one database resource, it can execute a select statement to obtain statistical data for at least one database resource. For example, a select statement can be found... Figure 3 The options shown are "select * from dv_spinlocks" or "select * from dv_page".

[0081] Following S201, S202 is executed, in which the database management system extracts at least one first usage characteristic of a database resource from the statistical data.

[0082] Understandably, after obtaining usage data of at least one database resource from the database through S201, the database management system can perform feature extraction based on this usage data, that is, extract the specific values ​​of each data in this usage data, so as to obtain the first usage feature of at least one database resource.

[0083] For example, taking the usage data of database resources as lock usage data, the lock usage characteristic F (i.e., the first usage characteristic) of spinlock_i can be obtained by the following formula (1):

[0084] F = (S se α1, α2, α3, ..., α i , ..., α n (1)

[0085] Among them, S se α is the number of current sessions, n is the number of lock types, and α is the number of current sessions. i (i = 1, 2, 3, ..., n) represents the lock usage characteristics of spinlock_i (i.e., the i-th type of lock). c1 i The number of queries (requests value) for spinlock_i, s i The blocking sleep time (sleeps value) of spinlock_i, c2 i The number of times spinlock_i was successfully acquired (spins value), Sa is the total lock blocking sleep time of the thread (which can be understood as the sum of the sleep values ​​of various locks), and S se n, c1 i s i c2 i Both Sa are positive integers.

[0086] Alternatively, taking the usage data of database resources as page usage data as an example, the page usage characteristic F (i.e., the first usage characteristic) of page_j can be obtained by the following formula (3):

[0087] F=(β1,β2,β3,…,β j , ..., β m (2)

[0088] Where, β j (j = 1, 2, 3, ..., m) represents the page usage characteristics of page_j (i.e., the j-th page). cp j cb is the number of times the lock of the bucket containing page_j (i.e., the j-th page) is queried (spins value). jThe total number of lock queries for the bucket containing page_j (which can be understood as the sum of the spins values ​​of page_j), cm j The number of copies of page_j (memcpys value), cc j The length of the bucket containing page_j (bucket_len value), m, cp j cb j cm j and cc j All are positive integers.

[0089] It should be noted that in some possible implementations, lock usage data or page usage data may include one or a combination of the aforementioned features. Therefore, the expressions for lock usage features of various lock types or page usage features of various pages may include the feature value of one or a combination of feature values ​​of multiple features. For example, if the lock usage data of spinlock_i includes requests and spins, then... or If the lock usage data for spinlock_i only includes sleeps, then Of course, lock usage data can include other combinations of features, and the corresponding α i This can include combinations of feature values ​​for these features. Furthermore, if the page usage data for page_j only includes spins, then... If the page usage data for page_j only includes memcpys, then β j = (cm) j If the page usage data for page_j only includes bucket_len, β j =(cc j If the page usage data of page_j includes any combination of spins, memcpys, and bucket_len, then β j It can include cm j and cc j Corresponding combinations. In practical applications, those skilled in the art can set the usage data of database resources according to actual needs, and then obtain the corresponding usage characteristics through S202. This application embodiment does not specifically limit this.

[0090] Next, after feature extraction is completed in S202, S203 is executed. The database management system inputs the first usage features of at least one database resource into the competition evaluation model to obtain the evaluation result. The evaluation result is used to indicate whether one or more database resources (i.e., the second database resource) are abnormal resources (such as abnormal locks or hot pages). Abnormal resources are the database resources where the competition bottleneck occurs.

[0091] Understandably, the database management system uses the first usage feature (e.g., F) of at least one database resource obtained through S202 as input features to the pre-trained competition evaluation model. Based on the evaluation results of the competition evaluation model, it identifies database resources with competition anomalies among at least one database resource. For example, when the input vector is a lock usage feature, the competition evaluation model can identify whether various locks are abnormal locks, that is, identify locks with abnormal competition among various locks; when the input vector is a page usage feature, the competition evaluation model can identify whether each page is a hot page, that is, identify pages with abnormal competition among various pages.

[0092] Optionally, the above-described competition evaluation model is trained based on a second usage feature of at least one database resource. Here, the second usage feature can be a usage feature sample of at least one database resource specifically used to train the competition evaluation model, or it can be a usage feature extracted from historical usage data (or existing usage data) of at least one database resource. Of course, the second usage feature can also take other forms, and this embodiment does not specifically limit it.

[0093] In some possible implementations, if the database resource is locked, the aforementioned competition evaluation model can be a model trained using clustering algorithms such as self-organizing map (SOM), counter propagation (CP) network, adaptive resonance theory (ART) network, and k-means clustering algorithm. In practical applications, the competition estimation model can also be trained and constructed using other clustering algorithms, and this application does not impose specific limitations.

[0094] Optionally, when the above competitive evaluation model is a model built using SOM training, the number of neurons in the output layer can be selected based on experience, or it can be the same as the number of sessions, or multiple neurons can be used to describe each session.

[0095] Alternatively, if the database resource is a page, the above competition evaluation model can be a binary classifier trained using a logistic regression algorithm. In practical applications, the competition estimation model can also be trained and constructed using other binary classification algorithms, and this application embodiment does not impose specific limitations.

[0096] S204, when the evaluation result indicates that one or more database resources are abnormal resources, the database management system outputs a contention alarm message, which is used to indicate that one or more database resources have a contention bottleneck.

[0097] Understandably, the database management system inputs at least one primary usage characteristic (such as F) of a database resource into the competition evaluation model, and the model outputs an evaluation result. If the evaluation result indicates that one or more of the database resources are abnormal resources, the database management system outputs a competition alarm message to indicate that a bottleneck has occurred in the competition for the aforementioned one or more database resources.

[0098] In practice, the output contention alert information can include the identifier of the target database resource. This allows the DBA to accurately locate the database resource experiencing the contention bottleneck based on one or more of these identifiers after seeing the contention alert information. Consequently, the DBA can perform precise optimization on the contention bottleneck, thereby improving the maintainability of the database.

[0099] For example, when one or more of the aforementioned database resources are locks, assuming the evaluation result indicates that spinlock_i is an abnormal lock, the contention alert information can carry the session ID of the session containing spinlock_i and the type value of spinlock_i to accurately alert the DBA that a contention bottleneck has occurred with spinlock_i. Furthermore, the contention alert information can also carry the requests, sleeps, failures, and spins values ​​of spinlock_i. Of course, the contention alert information can also carry other usage data of spinlock_i, as long as it can accurately indicate the spinlock_i experiencing a contention bottleneck; this embodiment does not impose specific limitations.

[0100] Furthermore, when one or more of the aforementioned database resources are pages, assuming the evaluation result indicates that page_j is a hot page, the contention alert information can carry the pool ID, file value, and page ID of page_j to accurately indicate page_j. Further, the contention alert information can also carry the spins value, memcpys value, bucket_len value, etc., of page_j. Of course, the contention alert information can also carry other usage data of page_j, as long as it can accurately indicate the page_j experiencing the contention bottleneck; this application embodiment does not impose specific limitations.

[0101] Furthermore, after S203, the database management method 200 may also include S205, whereby when the evaluation result indicates that one or more of the database resources are non-abnormal resources, the database management system may input the first usage characteristic of the one or more database resources as the second usage characteristic into the competition evaluation model to train the competition evaluation model.

[0102] Understandably, the database management system inputs at least one primary usage characteristic (such as F) of a database resource into the competition evaluation model, and the model outputs the evaluation result. If the evaluation result indicates that one or more of the database resources are non-abnormal resources, meaning that some or all of the database resources do not experience a competition bottleneck, the database management system can use the aforementioned primary usage characteristic to further train the competition evaluation model to improve its accuracy and thus enhance the performance of the database system.

[0103] In this embodiment, the database management system identifies database resources experiencing contention bottlenecks by using a contention assessment model, thus achieving accurate bottleneck location. Furthermore, after locating the bottleneck, the database management system informs the DBA, enabling the DBA to precisely optimize the bottleneck, significantly reducing the DBA's workload and helping them promptly identify problems, thereby improving database maintainability.

[0104] In some possible implementations, to further reduce the workload of DBAs and provide a better user experience, see [link to relevant documentation]. Figure 2 As shown, after S203, the above database management method 200 may further include:

[0105] S206 When the evaluation results indicate that one or more database resources are abnormal resources, the database management system obtains the tuning information corresponding to one or more database resources.

[0106] S205, the database management system outputs contention alarm information and optimization information.

[0107] Here, the above tuning information is used to suggest tuning strategies for one or more database resources.

[0108] For example, if the aforementioned abnormal resource is a checkpoint queue lock, the associated tuning strategy could be adjusting the refresh frequency; if the abnormal resource is a log commit queue lock, the associated tuning strategy could be adjusting the number of lock slots or using group commit. If the abnormal resource is a buffer bucket-level lock, the associated tuning strategy could be adjusting the hash function; if the abnormal resource is a hot page, the associated tuning strategy could be adjusting the page storage density or partitioning the table containing the page; if the table containing the page is a read-only table, it could be set to reside in memory, etc. Of course, different tuning strategies can be associated with different abnormal resources, and this application embodiment does not specifically limit this.

[0109] Understandably, to help DBAs analyze the status of the database system, the database management system can also associate different tuning strategies with different abnormal resources. When the evaluation result of S203 identifies one or more database resources as abnormal resources, in addition to outputting contention alert information as described in S204, the database management system can also output tuning information associated with these one or more database resources. This provides suggestions on the tuning strategies that can be adopted for these one or more abnormal database resources, enabling DBAs to quickly locate performance problems, analyze the system status under load, and provide corresponding tuning solutions based on the contention alert information and tuning information, thereby enhancing the maintainability of the database system.

[0110] For example, if one or more of the above resources are bucket-level locks (SPIN_BUCKET), the contention alarm information output by the database management system can also carry the identifier of the page associated with the SPIN_BUCKET.

[0111] In some possible implementations, in S206, the database management system can output one or more of the aforementioned database resources and their corresponding tuning information in the form of a view.

[0112] The above database management methods will be explained below using database resources as locks and pages as examples.

[0113] Assume the database resource is a lock. Then, Figure 4 This illustration shows a flowchart of another database management method according to an embodiment of this application. The database management method 400 may include:

[0114] S401, the database uses dv_spinlocks to collect usage data for various types of locks.

[0115] S402, the database management system uses the lock usage feature F' to train the SOM model and obtain the competition evaluation model; here, the lock usage feature F' can be understood as the second usage feature in the above embodiment.

[0116] S403, the database management system accesses dv_spinlocks through the select statement to obtain statistical data on various locks;

[0117] S404, the database management system extracts features from the statistical data of various locks to obtain lock usage features F; here, F can be understood as the first usage feature in the above embodiment.

[0118] S405, the database management system uses feature F as input to the lock competition evaluation model, i.e., the SOM model;

[0119] S406, the database management system uses a competition evaluation model to calculate the usage characteristics of various locks (i.e., α1, α2, α3, ..., α4) in lock usage characteristics F. i ..., α n-1 and α n The distance between α and the winning feature point; where, if α i With α i If the distance to the corresponding winning feature exceeds a preset threshold, then execute S407; if α i With α i If the distance to the corresponding winning feature does not exceed the preset threshold, then execute S409.

[0120] S407, the database management system obtains the first evaluation result of the contention evaluation model; for example, the first evaluation result indicates that spinlock_i is an abnormal lock;

[0121] S408, the database management system outputs a contention alarm message to indicate that spinlock_i is an abnormal lock;

[0122] S409, the database management system obtains the second evaluation result of the competition evaluation model; for example, the second evaluation result indicates that spinlock_i is a non-abnormal lock.

[0123] S410, the database management system uses the lock usage feature F of spinlock_i as the lock usage feature F' and returns to S402 to train the competition evaluation model.

[0124] Or, suppose the database resource is a webpage. Then, Figure 5This illustration shows a flowchart of another database management method according to an embodiment of this application. The database management method 500 may include:

[0125] S501, the database uses dv_page to count the usage data of each page in the database.

[0126] S502, the database management system uses the page usage feature F' to train a binary classifier to obtain a competitive evaluation model; here, the page usage feature F' can be understood as the second usage feature in the above embodiment.

[0127] S503, the database management system accesses dv_page through the select statement to obtain statistical data for each page;

[0128] S504, the database management system extracts features from the statistical data of each page to obtain page usage features F; here, F can be understood as the first usage feature in the above embodiment.

[0129] S505, the database management system inputs the page's feature F into the competitive evaluation model, i.e., a binary classifier;

[0130] S506, the database management system uses a competitive evaluation model to evaluate the usage characteristics (i.e., β1, β2, β3, ..., β4) of each page in the page usage characteristics F. j , ..., β m-1 and β m Perform a binary classification; if page_j is an abnormal page, then execute S507; if page_j is a non-abnormal page, then execute S509.

[0131] S507, the database management system obtains the first evaluation result of the competitive evaluation model;

[0132] S508, the database management system outputs a contention alarm message to indicate that page_j is an abnormal page;

[0133] S509, the database management system obtains the second evaluation result of the competitive evaluation model;

[0134] S510, the database management system takes the page usage feature F of page_j as the page usage feature F' and returns to S502 to train the competition evaluation model.

[0135] Based on the same inventive concept, this application also provides a database management device. This device can be the aforementioned database management system, or a chip or system-on-a-chip within the database management system. It can also be a functional module within the database management system used to implement the methods described in any of the above embodiments. This database management device can implement the functions performed by the database management system in any of the above embodiments, and these functions can be implemented by hardware executing corresponding software. The hardware or software here may include one or more modules corresponding to the aforementioned functions.

[0136] Figure 6 A schematic diagram of the structure of a database management device according to an embodiment of this application is shown. See also: Figure 6 As shown, the database management device 600 may include: a feature extraction module 610, used to: obtain statistical data of a first database resource in the database, the statistical data being the usage data of the first database resource obtained through statistics; and extract a first usage feature of the first database resource from the statistical data; an evaluation module 620, used to input the first usage feature into a competitive evaluation model to obtain an evaluation result, the competitive evaluation model being trained based on a second usage feature of the first database resource; and an alarm module 630, used to output a competitive alarm message when the evaluation result indicates that the second database resource is an abnormal resource, the competitive alarm message being used to indicate that a competitive bottleneck has occurred in the second database resource, and the second database resource is part or all of the first database resource.

[0137] In some possible implementations, the alarm module 630 is also used to: obtain tuning information corresponding to the second database resource when the evaluation result indicates that the second database resource is an abnormal resource; output the tuning information, which is used to prompt the tuning strategy associated with the second database resource.

[0138] In some possible implementations, the feature extraction module 610 is specifically used to query the first view in the database corresponding to the first database resource to obtain statistical data, wherein the first view is used to statistically analyze the usage data of the first database resource.

[0139] In some possible implementations, when the first database resource includes at least one type of lock in the database, for the first lock in the at least one type of lock, the first view includes one or a combination of the following data: the number of queries for the first lock, the blocking sleep time of the first lock, the number of times the first lock was failed to be acquired, and the number of times the first lock was successfully acquired.

[0140] In some possible implementations, the second database resource is an exception lock.

[0141] In some possible implementations, when the second database resource is a bucket-level lock, the contention alarm message carries the page identifier associated with the bucket-level lock.

[0142] In some possible implementations, the evaluation module 620 is specifically used to: calculate the distance between the first used feature and the winning feature; if the distance exceeds a preset threshold, output a first evaluation result, which is used to indicate that the second database resource is an abnormal resource; or, if the distance does not exceed the preset threshold, output a second evaluation result, which is used to indicate that the second database resource is a non-abnormal resource.

[0143] In some possible implementations, when the first database resource includes at least one page in the database, for the first page among the at least one pages, the first view includes one or a combination of the following data: the number of lock queries on the bucket containing the first page, the number of copies of the first page, and the length of the bucket containing the first page.

[0144] In some possible implementations, the second database resource is a hot page.

[0145] In some possible implementations, the evaluation module 620 is specifically used to: perform binary classification on the first usage feature using a logistic regression function and output an evaluation result, wherein the evaluation result is used to indicate whether the second database resource is an anomalous resource.

[0146] In some possible implementations, feature extraction module 610 is used to query the first view via a selection interface.

[0147] In some possible implementations, database resources include locks or pages.

[0148] Based on the same inventive concept, embodiments of this application provide a database management system, including: a data management device as described in any of the above embodiments.

[0149] Based on the same inventive concept, embodiments of this application provide an electronic device. Figure 7 This is a schematic diagram illustrating the structure of an electronic device according to an embodiment of this application, such as... Figure 7 As shown, the electronic device 700 uses general-purpose computer hardware, including a processor 701, a memory 702, a bus 703, an input device 704, and an output device 705.

[0150] In some possible implementations, memory 702 may include computer storage media in the form of volatile and / or non-volatile memory, such as read-only memory and / or random access memory. Memory 702 may store operating system, application programs, other program modules, executable code, program data, user data, etc.

[0151] Input device 704 can be used to input commands and information into electronic devices. Input device 704 may be a keyboard or pointing device such as a mouse, trackball, touchpad, microphone, joystick, gamepad, satellite TV antenna, scanner, or similar device. These input devices can be connected to processor 701 via bus 703.

[0152] Output device 705 can be used for electronic devices to output information. In addition to monitors, output device 705 can also be used for other peripheral output devices, such as speakers and / or printing devices. These output devices can also be connected to processor 701 via bus 703.

[0153] Electronic devices can be connected to a network via network interface 706, such as a local area network (LAN). In a networked environment, computer execution instructions stored in the control unit can be stored in a remote storage device, not just locally.

[0154] When the processor 701 in the electronic device executes the executable code or application stored in the memory 702, the electronic device executes the database management method on the database management system side of the above embodiments. For the specific execution process, please refer to the above embodiments, which will not be repeated here.

[0155] Furthermore, the aforementioned memory 702 stores information for implementing... Figure 6 The computer execution instructions for the functions of the feature extraction module 610, evaluation module 620 and alarm module 630. Figure 6 The functions / implementation processes of the feature extraction module 610, evaluation module 620, and alarm module 630 can all be verified through [the following text is incomplete and likely refers to a separate process]. Figure 7 The processor 701 in the memory calls the computer execution instructions stored in the memory 702 to implement the function. For the specific implementation process and function, please refer to the above-mentioned related embodiments.

[0156] Based on the same inventive concept, embodiments of this application provide a computer-readable storage medium storing instructions that, when executed on a computer, are used to perform the database management method as described in any of the above embodiments.

[0157] Based on the same inventive concept, this application provides a computer program product that, when executed on a computer, enables the computer to implement the database management method as described in any of the above embodiments.

[0158] Those skilled in the art will appreciate that the functionality described in conjunction with the various illustrative logic blocks, modules, and algorithmic steps disclosed herein can be implemented in hardware, software, firmware, or any combination thereof. If implemented in software, the functionality described by the various illustrative logic blocks, modules, and steps can be stored or transmitted as one or more instructions or codes on a computer-readable medium and executed by a hardware-based processing unit. The computer-readable medium may comprise a computer-readable storage medium, which corresponds to a tangible medium, such as a data storage medium, or a communication medium that includes any medium facilitating the transfer of a computer program from one place to another (e.g., according to a communication protocol). In this way, the computer-readable medium may substantially correspond to (1) a non-transitory tangible computer-readable storage medium, or (2) a communication medium, such as a signal or carrier wave. The data storage medium may be any available medium accessible by one or more computers or one or more processors to retrieve instructions, code, and / or data structures for implementing the techniques described in this application. A computer program product may comprise a computer-readable medium.

[0159] By way of example and not limitation, such computer-readable storage media may include RAM, ROM, EEPROM, CD-ROM or other optical disc storage devices, magnetic disk storage devices or other magnetic storage devices, flash memory, or any other media that can be used to store desired program code in the form of instructions or data structures and is accessible by a computer. Furthermore, any connection is properly referred to as computer-readable media. For example, if instructions are transmitted from a website, server, or other remote source using coaxial cable, fiber optic cable, twisted pair, digital subscriber line (DSL), or wireless technologies such as infrared, radio, and microwave, then coaxial cable, fiber optic cable, twisted pair, DSL, or wireless technologies such as infrared, radio, and microwave are included in the definition of media. However, it should be understood that the computer-readable storage media and data storage media do not include connections, carrier waves, signals, or other temporary media, but are specifically addressed to non-temporary tangible storage media. As used herein, disks and optical discs include compact optical discs (CDs), laser optical discs, optical discs, digital versatile optical discs (DVDs), and Blu-ray discs, where disks typically reproduce data magnetically, while optical discs reproduce data optically using lasers. The combination of the above items should also be included in the scope of computer-readable media.

[0160] Instructions can be executed by one or more processors, such as digital signal processors (DSPs), general-purpose microprocessors, application-specific integrated circuits (ASICs), field-programmable arrays (FPGAs), or other equivalent integrated or discrete logic circuits. Therefore, the term "processor" as used herein can refer to any of the foregoing structures or any other structures suitable for implementing the techniques described herein. Furthermore, in some aspects, the functionality described in the various illustrative logic blocks, modules, and steps described herein can be provided within dedicated hardware and / or software modules configured for encoding and decoding, or incorporated into combined codecs. Moreover, the techniques can be fully implemented within one or more circuit or logic elements.

[0161] The technology of this application can be implemented in a wide variety of devices or apparatuses, including wireless handheld devices, integrated circuits (ICs), or a set of ICs (e.g., chipsets). The various components, modules, or units described in this application are intended to emphasize functional aspects of the apparatus for performing the disclosed technology, but do not necessarily need to be implemented by different hardware units. In fact, as described above, the various units can be combined with suitable software and / or firmware within a codec hardware unit, or provided via interoperable hardware units (containing one or more processors as described above).

[0162] In the above embodiments, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions in other embodiments.

[0163] The above description is merely an exemplary embodiment of this application, but the scope of protection of this application is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

Claims

1. A database management method, characterized in that, include: Obtain statistical data of the first database resource in the database, wherein the statistical data is the usage data of the first database resource obtained by statistics; Extract the first usage characteristic of the first database resource from the statistical data; The first usage feature is input into the competitive evaluation model to obtain the evaluation result. The competitive evaluation model is trained based on the second usage feature of the first database resource. When the evaluation result indicates that the second database resource is an abnormal resource, a contention alarm message is output. The contention alarm message is used to indicate that a contention bottleneck has occurred in the second database resource. The second database resource is part or all of the first database resource. The alarm message carries the identifier of the second database resource.

2. The method according to claim 1, characterized in that, The method further includes: When the evaluation result indicates that the second database resource is an abnormal resource, the optimization information corresponding to the second database resource is obtained; The tuning information is output, which is used to prompt the tuning strategy associated with the second database resource.

3. The method according to claim 1 or 2, characterized in that, The acquisition of statistical data for the first database resource in the database includes: The statistical data is obtained by querying the first view in the database corresponding to the first database resource, wherein the first view is used to collect usage data of the first database resource.

4. The method according to claim 3, characterized in that, When the first database resource includes at least one type of lock in the database, for the first lock among the at least one type of lock, the first view includes one or a combination of the following features: The number of queries to the first lock, the blocking sleep time of the first lock, the number of times the acquisition of the first lock failed, and the number of times the acquisition of the first lock was successful.

5. The method according to claim 4, characterized in that, The second database resource is an exception lock.

6. The method according to claim 4 or 5, characterized in that, When the second database resource is a bucket-level lock, the contention alarm information carries the page identifier associated with the bucket-level lock.

7. The method according to claim 3, characterized in that, When the first database resource includes at least one page in the database, for the first page among the at least one pages, the first view includes one or a combination of the following features: The number of queries to the lock of the bucket containing the first page, the number of copies of the first page, and the length of the bucket containing the first page.

8. The method according to claim 7, characterized in that, The second database resource is a hot page.

9. The method according to any one of claims 3 to 8, characterized in that, The querying of the first view in the database corresponding to the first database resource includes: The first view is queried using a selection statement.

10. The method according to any one of claims 1 to 9, characterized in that, The step of inputting the first feature into the competitive evaluation model to obtain the evaluation result includes: Calculate the distance between the first used feature and the winning feature; If the distance exceeds a preset threshold, a first evaluation result is output, which indicates that the second database resource is an abnormal resource; or, If the distance does not exceed the preset threshold, a second evaluation result is output, which indicates that the second database resource is a non-abnormal resource.

11. The method according to any one of claims 1 to 9, characterized in that, The step of inputting the first feature into the competitive evaluation model to obtain the evaluation result includes: The first usage feature is binary classified using a logistic regression function, and the evaluation result is output, wherein the evaluation result is used to indicate whether the second database resource is an abnormal resource.

12. The method according to any one of claims 1 to 11, characterized in that, The first database resource includes locks or pages.

13. A database management device, characterized in that, include: The feature extraction module is used to: obtain statistical data of a first database resource in the database, wherein the statistical data is the usage data of the first database resource obtained by statistics; and extract a first usage feature of the first database resource from the statistical data. The evaluation module is used to input the first usage features into the competitive evaluation model to obtain the evaluation result, wherein the competitive evaluation model is trained based on the second usage features of the first database resource; The alarm module is used to output a contention alarm message when the evaluation result indicates a second database resource. The contention alarm message is used to indicate that a contention bottleneck has occurred in the second database resource. The alarm message carries an identifier of the second database resource, which is part or all of the first database resource.

14. The apparatus according to claim 13, characterized in that, The alarm module is further configured to: when the evaluation result indicates that the second database resource is an abnormal resource, obtain the tuning information corresponding to the second database resource; and output the tuning information, which is used to prompt the tuning strategy associated with the second database resource.

15. The apparatus according to claim 13 or 14, characterized in that, The feature extraction module is specifically used to query the first view in the database corresponding to the first database resource to obtain the statistical data, wherein the first view is used to statistically analyze the usage data of the first database resource.

16. The apparatus according to claim 15, characterized in that, When the first database resource includes at least one type of lock in the database, for the first lock in the at least one type of lock, the first view includes one or a combination of the following features: the number of queries for the first lock, the blocking sleep time of the first lock, the number of times the first lock was failed to be acquired, and the number of times the first lock was successfully acquired.

17. The apparatus according to claim 15, characterized in that, When the first database resource includes at least one page in the database, for the first page among the at least one pages, the first view includes one or a combination of the following data: the number of lock queries on the bucket where the first page is located, the number of copies of the first page, and the length of the bucket where the first page is located.

18. The apparatus according to any one of claims 15 to 17, characterized in that, The feature extraction module is used to query the first view through a selection interface.

19. The apparatus according to any one of claims 13 to 18, characterized in that, The evaluation module is specifically used to: calculate the distance between the first used feature and the winning feature; if the distance exceeds a preset threshold, output a first evaluation result, wherein the first evaluation result is used to indicate that the second database resource is an abnormal resource; or, if the distance does not exceed the preset threshold, output a second evaluation result, wherein the second evaluation result is used to indicate that the second database resource is a non-abnormal resource.

20. The apparatus according to any one of claims 13 to 18, characterized in that, The evaluation module is specifically used to: perform binary classification on the first usage feature using a logistic regression function, and output the evaluation result, wherein the evaluation result is used to indicate whether the second database resource is an abnormal resource.

21. The apparatus according to any one of claims 13 to 20, characterized in that, The first database resource includes locks or pages.

22. An electronic device, characterized in that, include: Processor and memory; The processor is coupled to the memory; The processor is configured to read and execute instructions in the memory to implement the database management method as described in any one of claims 1 to 12.

23. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores instructions; when the instructions are executed on a computer, they are used to perform the database management method as described in any one of claims 1 to 12.

Images