Database storage optimization method and system, electronic device and storage medium

Abstract

The application provides a database storage optimization method, system, electronic equipment and storage medium. The database storage optimization method comprises: obtaining instance information of a database instance; determining expected optimization benefits of a library table object in the database instance according to the instance information; the expected optimization benefits are expected benefits brought by application of optimization suggestions for a storage strategy of the library table object; outputting corresponding optimization suggestion information to the database instance according to the expected optimization benefits; and the optimization suggestion information is used for suggesting optimization of the storage strategy of the library table object. According to the embodiment of the application, better optimization suggestion information can be given to reduce the storage cost and total cost of ownership of the user.

Description

Technical Field

[0001] This application relates to the field of cloud computing technology, and in particular to a database storage optimization method, system, electronic device, and storage medium. Background Technology

[0002] Analytical data warehouses (also known as databases) are one of the data-intensive applications. In order to accelerate data access, technologies such as solid-state drives (SSDs) for hot storage and full indexing are often used. However, such technologies will significantly increase the user's storage costs and total cost of ownership (TCO). Summary of the Invention

[0003] This application provides a database storage optimization method, system, electronic device, and storage medium to solve the technical problems existing in the prior art.

[0004] In a first aspect, embodiments of this application provide a database storage optimization method, including:

[0005] Retrieve instance information for the database instance;

[0006] Determine the expected optimization benefits of the database table objects in the database instance based on the instance information; the expected optimization benefits are the anticipated benefits brought about by the application of optimization suggestions for the storage strategy of the database table objects.

[0007] Based on the expected optimization benefits, corresponding optimization suggestions are output to the database instance; these suggestions are used to recommend optimization of the storage strategy for database table objects.

[0008] Secondly, embodiments of this application provide a database storage optimization system, including: a database and an optimization control module connected in communication;

[0009] The database is used to store repository table objects and receive at least one of the optimization suggestion information and reversal suggestion information output by the optimization control module;

[0010] The optimization control module is used to execute the method provided in the first aspect of the embodiments of this application on the database in the running or service state.

[0011] Thirdly, embodiments of this application provide an electronic device, including a memory, a processor, and a computer program stored in the memory, wherein the processor implements the method provided in the first aspect of embodiments of this application when executing the computer program.

[0012] Fourthly, embodiments of this application provide a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the method provided in the first aspect of embodiments of this application.

[0013] Compared with the prior art, this application has the following advantages:

[0014] According to the technical solution of this application embodiment, instance information of a database instance can be perceived to determine the expected optimization benefit, which reflects the anticipated benefits after the optimization suggestions are applied. Based on the expected optimization benefit, corresponding optimization suggestion information can be output to the database instance to achieve optimization. Optimization based on the optimization suggestion information can effectively reduce storage costs, thereby reducing the user's total cost of ownership. The database storage optimization method provided in this application embodiment can achieve automatic tuning, that is, automatically provide optimization suggestion information for the storage strategy of database table objects, without requiring manual tuning by data administrators, which can greatly improve tuning efficiency, save labor costs, and further reduce the user's total cost of ownership.

[0015] The above description is only an overview of the technical solution of this application. In order to better understand the technical means of this application, it can be implemented according to the contents of the specification. In order to make the above and other objects, features and advantages of this application more obvious and understandable, specific embodiments of this application are given below. Attached Figure Description

[0016] In the accompanying drawings, unless otherwise specified, the same reference numerals throughout the various drawings denote the same or similar parts or elements. These drawings are not necessarily drawn to scale. It should be understood that these drawings depict only some embodiments according to this application and should not be construed as limiting the scope of this application.

[0017] Figure 1 A flowchart illustrating a database storage optimization method provided in this application embodiment;

[0018] Figure 2 A partial flowchart illustrating another database storage optimization method provided in this application embodiment;

[0019] Figure 3 A schematic diagram of the structural framework of a database storage optimization system provided in this application embodiment; and

[0020] Figure 4 This is a schematic diagram of the structural framework of an electronic device provided in an embodiment of this application. Detailed Implementation

[0021] In the following description, only certain exemplary embodiments are briefly described. As those skilled in the art will recognize, the described embodiments can be modified in various ways without departing from the concept or scope of this application. Therefore, the drawings and description are considered to be exemplary in nature and not restrictive.

[0022] In scenarios using technologies like solid-state drive (SSD) hot storage and full indexing, it's evident that in analytical databases, many table objects (such as data tables and indexes) are intensively accessed within a certain period, and then rarely or never accessed again. For data tables using SSD hot storage, the services involved, such as queries, often exhibit significant temporal locality. Over time, the data table may no longer be a hotspot, or a partition within the table may no longer be a hotspot. Storing the data table in hot storage at this point increases storage costs, leading to a higher total cost of ownership (TCO). Similarly, for indexes, if the corresponding column in a query does not involve aggregation, joins, or filtering, the index on that column is often unnecessary. Storing the index in this case also increases storage costs, further contributing to a higher TCO.

[0023] To reduce storage costs, it's necessary to identify which database table objects will not be accessed or will be accessed very rarely in the future. This allows for the release of storage space or hot storage occupied by these objects. Traditionally, identifying whether a database table object will be accessed in the future relies primarily on the database administrator (DBA). However, many users do not have a database administrator, and adding one increases the total cost of ownership. Even with a database administrator, the workload of data analysis tasks can change constantly. Even experienced database administrators find it difficult to determine which database table objects will not be accessed in the future from a constantly changing workload. Furthermore, database administrators not only need to be fully and promptly familiar with the characteristics of the data analysis task load and data distribution, but also need to balance the benefits of cost reduction with the risk of performance degradation in data analysis tasks when making cost reduction decisions, further increasing the difficulty of identification and decision-making for database administrators.

[0024] To facilitate understanding of the technical solutions of the embodiments of this application, the relevant technologies of the embodiments of this application are described below. The following relevant technologies are optional solutions and can be combined with the technical solutions of the embodiments of this application in any way, and all of them fall within the protection scope of the embodiments of this application.

[0025] This application provides a database storage optimization method, such as... Figure 1As shown, the method may include the following steps: S101, obtaining instance information of a database instance; S102, determining the expected optimization benefit of table objects in the database instance based on the instance information; S103, outputting corresponding optimization suggestion information to the database instance based on the expected optimization benefit. The database instance may be a database in a running or service state, and the table objects may be data structures such as tables and indexes stored in the database instance. The expected optimization benefit is the anticipated benefit after the optimization suggestion for the storage strategy of the table objects is applied; it is an estimated value. In this embodiment, the expected optimization benefit can be used to characterize the reduction in storage costs after the optimization suggestion is applied. The higher the expected optimization benefit, the greater the reduction in storage costs after optimizing the storage strategy of the table objects based on the expected optimization benefit. The optimization suggestion information can be used to suggest optimizing the storage strategy of the table objects. The optimization suggestion information can be output to the database instance in the form of DDL (Data Definition Language) statements or in the form of other instructions that can manipulate table objects.

[0026] The database storage optimization method provided in this application embodiment can perceive the instance information of the database instance to determine the expected optimization benefit, which reflects the anticipated benefits after the optimization suggestions are applied. Based on the expected optimization benefit, corresponding optimization suggestion information can be output to the database instance to achieve optimization. Optimization based on the optimization suggestion information can effectively reduce storage costs, thereby reducing the user's total cost of ownership. The database storage optimization method provided in this application embodiment embodiment can achieve automatic tuning, that is, automatically provide optimization suggestion information for the storage strategy of database table objects, without the need for manual tuning by data administrators, which can greatly improve tuning efficiency, save labor costs, and further reduce the user's total cost of ownership.

[0027] In one implementation, instance information may include the database instance's metadata and its first historical access information. The database instance's metadata may include at least one type of information, such as attribute information, statistical information, and instance configuration information (config) for each table object and all columns within the database instance. The instance configuration information may include global configuration items and values, as well as session-level configuration items and values. The database instance's historical access information may include the time and frequency of access to each table object within the database instance. The first historical access information can be obtained based on the historical data (load) of the data analysis task. The first historical access information may include information on accesses to table objects within a historical time period prior to the current moment. The length and range of this historical time period can be set according to actual needs; for example, the time length can be set to 15 days. The historical data of the data analysis task may include SQL session information, and may also include at least one type of information, such as configuration information, query plans, cost estimation information for the query plans, statistical information on the corresponding operators, and execution information.

[0028] Correspondingly, such as Figure 2 As shown, in step S102 above, determining the expected optimization benefits of database table objects in the database instance based on instance information may include the following steps: S201, predicting the future access probability of database table objects based on metadata and first historical access information; S202, determining the expected cost reduction benefits of database table objects in the database instance based on metadata; S203, determining the expected optimization benefits of database table objects based on the future access probability and the expected cost reduction benefits. The future access probability of a database table object can be the probability that the database table object will be accessed within a certain time period in the future, and the expected cost reduction benefits can be the reduction in storage costs resulting from the expected saving of storage space. Steps 201 and 202 can be executed sequentially or simultaneously. Figure 2 The sequence of steps shown is for illustrative purposes only and is not intended to be limiting.

[0029] Metadata can include the amount of storage space occupied by each database table object and the price per unit of storage space. The price per unit of storage space can be pre-set based on actual conditions. Multiplying the amount of storage space occupied by each database table object by the price per unit of storage space yields the expected cost reduction benefit for each database table object. Expected cost reduction benefits can also include the benefits from reduced database maintenance operations. For example, after optimizing the storage strategy of database table objects, the objects may be deleted. In this case, no update, check, or other maintenance operations are needed in the database, thereby further saving computing resources and energy consumption, further increasing cost reduction benefits.

[0030] Research has revealed that the use of database table objects, such as data tables and indexes, exhibits both temporal locality and periodicity. This means that historical access patterns are strongly correlated with future access patterns, and historically accessed database table objects also have a certain probability (greater than 0) of being accessed in the future. Based on this finding, in the database storage optimization method provided in this application, given historical access information, namely the aforementioned first historical access information, a relatively accurate prediction of future access probability can be made. Based on the prediction results and the expected cost reduction benefits, the expected optimization benefits of database table objects can be obtained with high accuracy, thereby improving the optimization effect and quality of database table object storage space.

[0031] In one implementation, step S201, predicting the future access probability of a database table object based on metadata and first historical access information, may include: inputting the metadata and first historical access information into a prediction model, and predicting the future access probability of the database table object using the prediction model. The prediction model may be pre-trained based on metadata and second historical access information of database table objects in the database instance. The second historical access information was generated earlier than the first historical access information. For example, the second historical access information may include information on accesses to the database table object within a second historical time period prior to the current time, and the first historical access information may include information on accesses to the database table object within a first historical time period prior to the second historical time period.

[0032] Based on the above approach, the database storage optimization method provided in this application embodiment can predict the probability of future access based on a prediction model obtained from machine learning. This decouples the optimization process from the kernel of the database engine, eliminating the need to occupy kernel resources. Consequently, it does not rely on the kernel functions of the database engine or the What-If capability (an optimization capability) in the optimizer. This makes it more versatile, applicable to both optimizers with and without What-If capability.

[0033] Before inputting metadata and first historical access information into the prediction model, the metadata and first historical access information can be cleaned and processed to form time series pattern data, and then input into the prediction model. That is, the metadata and first historical access information input into the prediction model can be time series pattern data.

[0034] In one example, training the predictive model may involve: cleaning and processing metadata and second historical access information to form time-series pattern data; performing alignment, aggregation, and correlation processing on this data to form training data; and training the predictive model based on this training data. In another example, training the predictive model may involve: cleaning and processing metadata and second historical access information to form time-series pattern data; performing multi-dimensional alignment, aggregation, and correlation processing on this data to form training data and validation data. The training data can be generated earlier than the validation data. The predictive model can be trained based on the training data, and the effectiveness of the trained predictive model can be validated based on the validation data. Training the predictive model can be performed once or multiple times. For example, historical access information can be collected periodically as second historical access information to train the predictive model. The interval between periodic training sessions can be set according to actual needs.

[0035] In the above examples, the alignment process for time-series pattern data can include: normalizing metadata and second historical access information at the time granularity. The aggregation process for time-series pattern data can include: aggregating metadata and second historical access information according to different time granularities, such as 1 hour, 12 hours, 24 hours, 1 day, 2 days, 3 days, 7 days, etc., and the aggregation form can include at least one form such as count, average, minimum / maximum value, summation, etc. The association process for time-series pattern data can include: connecting the aligned content of metadata and second historical access information to combine data with a larger amount of information.

[0036] In one implementation, the prediction model is a posterior Bayesian distribution model. Posterior Bayesian distribution models have high accuracy and require less cloud computing resources. Furthermore, they also perform well in terms of model complexity and generalization, exhibiting a balanced performance across various aspects. Using a posterior Bayesian distribution model to predict the probability of future visits can yield relatively accurate prediction results with less computation.

[0037] The posterior Bayesian distribution model can fit historical access information and predict the corresponding probability distribution. Its principle is illustrated in the following expression:

[0038] p(y_{new}|Y)=\integral_{-\inf}^{\inf}p(y_{new}|\theta)p(\theta|Y)d\theta

[0039] In this expression, Y represents the observed data. In the prediction scenario of this application embodiment, Y can represent the time series distribution of historical access information; y_{new} represents the new data to be predicted. In the prediction scenario of this application embodiment, y_{new} can represent whether the database table object will be accessed in the future. 0 can represent not being accessed, and 1 can represent being accessed; p(y_{new}|Y) represents the distribution of unobserved data predicted given the observed data. In the probability prediction scenario of this application embodiment, p(y_{new}|Y) can represent predicting whether a certain database table object will be accessed in the future, given the time series distribution of historical access information; \theta represents... Let $\integral_{-\inf}^{\inf}$ represent the model parameters; $p(y_new|\theta)$ represents the distribution of observed values ​​of the new data $y_{new}$ to be predicted given the model parameters; after obtaining the temporal distribution of observed data $Y$, such as the historical access information in the embodiments of this application, the uncertainty of the model parameter $\theta$ can be characterized by the posterior probability $p(\theta|Y)$, where $p(\theta|Y)$ represents the distribution of the unknown model parameter $\theta$ estimated using the observed data $Y$; $d\theta$ represents the marginalization of the unknown model parameter $\theta$. In the above expression, when there is observed data $Y$, the unknown model parameter $\theta$ will be integrated away, so $p(y_{new}|Y)$ can be independent of the unknown model parameter $\theta$.

[0040] In other implementations, the prediction model may be a time series model or other prediction models based on Bayesian posterior probability.

[0041] In one embodiment, the database storage optimization method provided in this application may further include: obtaining optimization rule information; the optimization rule information includes the optimization range, such as information on the range of database table objects to be optimized, and the optimization rule information may be pre-input or real-time input by the user according to the actual situation, and the input optimization rule may be stored in the database instance for collection, and the instance information may include the optimization rule information.

[0042] Correspondingly, in step S102 above, predicting the expected optimization benefit of database table objects in the database instance based on instance information includes: determining the target database table objects to be optimized based on the optimization scope; and determining the expected optimization benefit of the target database table objects based on instance information. For example, for three data tables A, B, and C, if the user-input optimization scope is C, meaning only C is optimized and A and B cannot be optimized, then only the expected optimization benefit for C is determined. The specific method for determining the expected optimization benefit of target database table objects based on instance information can be found in the preceding related content.

[0043] Based on this implementation method, the database optimization storage method provided in this application can determine the target database table object to be optimized based on the optimization range input by the user, determine the expected optimization benefit only for the target database table object, and then output optimization suggestion information only for the target database table object to optimize the storage space of the target database table object. In this way, database table objects within the range specified by the user can be optimized to meet the user's optimization needs and reduce the amount of computation.

[0044] In another embodiment, the database storage optimization method provided in this application may further include: obtaining optimization rule information and obtaining an optimization benefit threshold based on the optimization rule information. The optimization rule information may be pre-input or real-time input by the user according to actual conditions. The input optimization rules may be stored in a database instance for collection. The instance information may include the optimization rule information. The optimization rules may include a preset optimization benefit threshold or cost reduction preference information. The optimization benefit threshold may be determined based on the preset optimization benefit threshold or cost reduction preference information in the optimization rule information. For example, the preset optimization benefit threshold input by the user may be used as the optimization benefit threshold in this application embodiment. Alternatively, the user's cost reduction preference may be determined based on the cost reduction preference information. For example, when reducing storage costs, the user may choose to prioritize reducing storage costs or consider the performance fallback of data analysis tasks (e.g., query tasks) while reducing storage costs. A matching optimization benefit threshold may be determined based on the user's cost reduction preference.

[0045] Correspondingly, if the optimization rule information includes an optimization benefit threshold, in step S103 above, outputting corresponding optimization suggestion information to the database instance based on the expected optimization benefit may include: determining the expected optimization benefit of each database table object that is greater than the optimization benefit threshold, and using it as the target expected optimization benefit; and outputting optimization suggestion information for the storage strategy of the database table object corresponding to the target expected optimization benefit.

[0046] Based on this implementation method, the database optimization storage method provided in this application can filter out database table objects with high expected optimization benefits based on a preset optimization benefit threshold input by the user, and output optimization suggestion information for the storage strategies of the database table objects with high expected optimization benefits, so that the storage strategies of the database table objects with high expected optimization benefits can be optimized. Alternatively, it can automatically determine the optimization benefit threshold that can achieve the corresponding preference based on the cost reduction preference information input by the user, and then filter out database table objects with high expected optimization benefits based on the optimization benefit threshold, and output optimization suggestion information for the storage strategies of the database table objects with high expected optimization benefits, so that the storage strategies of the database table objects with high expected optimization benefits can be optimized. After optimization based on the optimization suggestion information, storage costs can be significantly reduced and personalized optimization needs of users can be met.

[0047] In other embodiments, the optimization rule information may include at least one of the optimization scope, preset optimization benefit threshold, and cost reduction preference information. Correspondingly, the database storage optimization method provided in this application embodiment can perform the operation corresponding to at least one of the optimization scope, preset optimization benefit threshold, and cost reduction preference information. The operation corresponding to each piece of information can be referred to the relevant description above.

[0048] In one implementation, the database table object in this application embodiment may include at least one of a data table and an index. Correspondingly, in step S103 above, the optimization suggestion information output for the storage strategy of the database table object corresponding to the target expected optimization benefit may include information of at least one of the following optimization suggestions: Optimization suggestion one, move the data table corresponding to the target expected optimization benefit from the hot storage space (or hot storage space, hot storage area) to the cold storage space; Optimization suggestion two, delete the index corresponding to the target expected optimization benefit. The target expected optimization benefit can be determined based on any of the aforementioned implementation methods; the hot storage space, also called the hot storage space or hot storage area, can be the storage space of a solid-state drive (SSD), and the data stored in the hot storage space can be called hot storage data. Hot storage can meet the needs of high-performance access and improve query performance; the cold storage space, also called the cold storage space or cold storage area, can be the storage space of a hard disk drive (HDD), and the data stored in the cold storage space can be called cold storage data. Cold storage is a more economical storage method that can save storage costs.

[0049] Based on this implementation method, the database storage optimization method provided in this application can output different optimization suggestions for different database table objects to achieve different optimization operations. When the database table object is a data table, the optimization suggestions for the data table can automatically move the data table with greater expected optimization benefits from the hot storage space to the cold storage space, thereby reducing the occupation of the hot storage space by the data table with greater expected optimization benefits, achieving hot and cold stratification, and reducing storage costs. When the database table object is an index, the optimization suggestions for the index can automatically delete the index with greater expected optimization benefits, thereby reducing the occupation of the index on the storage space and reducing storage costs.

[0050] In one implementation, the optimization suggestion information output for the storage strategy of the database table objects corresponding to the target expected optimization benefit may include: outputting optimization suggestion information sequentially for the storage strategy of the database table objects corresponding to each target expected optimization benefit in descending order. This ensures that each optimization based on the optimization suggestion information optimizes only the database table object corresponding to the current largest expected optimization benefit, thereby minimizing storage costs.

[0051] In this embodiment, the expected optimization benefit can be expressed as a score. Then, optimization suggestions can be output for the storage strategy of the corresponding database table object in descending order of the expected optimization benefit scores.

[0052] In one implementation, step S103 above, outputting corresponding optimization suggestion information to the database instance based on the expected optimization benefit, may include: outputting corresponding optimization suggestion information to the database instance under specified operation and maintenance conditions based on the expected optimization benefit; for example, under specified operation and maintenance conditions, optimization suggestion information may be output sequentially for the storage strategy of the database table objects corresponding to each expected optimization benefit in descending order, and the specified operation and maintenance conditions may be provided by operation and maintenance rule information.

[0053] Users can pre-enter or input maintenance rules information in real time. The maintenance conditions provided in these rules can include at least one of the following: a time window for database version upgrades, a time window for capacity expansion, etc. For example, if the database version is to be upgraded between 2 AM and 4 AM, optimization suggestions can be output to the database instance outside of this time window to avoid impacting database maintenance operations. The user-inputted maintenance rules information can be stored in the database instance for data collection; the instance information can include the maintenance rules information.

[0054] In one embodiment, the database storage optimization method provided in this application may further include: monitoring the performance of the database instance to determine whether the database instance has experienced performance regression; if the database instance experiences performance regression, outputting undo suggestion information to the database instance, the undo suggestion information can be used to suggest undoing the performed optimization operations; otherwise, maintaining the current state. For example, if the database instance experiences performance regression, undo suggestion information can be output to the database instance, and the database instance can undo the optimization operations based on the undo suggestion information. The undo suggestion information may also be in the form of a DDL statement.

[0055] In one example, after moving the data table corresponding to the expected optimization benefit from the hot storage space to the cold storage space, access to this data table can only be done in the cold storage space. Access in the cold storage space is slower, causing a performance regression in the database instance. In another example, after deleting the index corresponding to the expected optimization benefit, if access to the corresponding original information through that index is needed again, since there is no index available, the original information corresponding to the index must be accessed directly. This access is slow, causing a performance regression in the database instance.

[0056] Undoing optimization operations can include at least one of the following: moving the data table corresponding to the expected optimization benefit from the hot storage space to the cold storage space, or deleting the index corresponding to the expected optimization benefit. For example, for performance regressions occurring during data table access, the data table corresponding to the expected optimization benefit can be moved from the cold storage space back to the hot storage space, speeding up access to the data table and improving the performance of the database instance again. For performance regressions occurring during index access, the index can be recreated, speeding up access to the original information corresponding to the index and improving the performance of the database instance again, thus eliminating the performance regression.

[0057] Monitoring performance and undoing performance rollbacks can balance performance and storage costs. Storage costs can be reduced as much as possible without performance rollback, thus avoiding sacrificing database instance performance to reduce storage costs.

[0058] In one implementation, in step S101 above, obtaining the instance information of the database instance can be performed periodically. That is, the instance information of the database instance can be obtained periodically, and the acquisition period can be set according to actual needs. The instance information obtained each time can be persisted for easy access to the instance information in a timely manner later.

[0059] In one implementation, a database instance can optimize or abandon the storage strategy of a table object based on optimization suggestions. In one example, upon receiving optimization suggestions, the database instance can directly optimize the storage strategy of the table object based on those suggestions. In another example, upon receiving optimization suggestions, the database instance can display the suggestions to the user, allowing the user to decide whether to ultimately perform the optimization. Upon receiving confirmation from the user, the database instance optimizes the storage strategy of the table object. In yet another example, upon receiving optimization suggestions, the database instance can choose, based on its own selection mechanism, whether to optimize the storage strategy of the table object based on the suggestions, or which part of the suggestions to optimize. Referring to these examples, upon receiving a cancellation suggestion, the database instance can directly cancel the optimization operation, cancel the optimization operation in response to a user's confirmation, or choose, based on its own selection mechanism, whether to perform a cancellation operation based on the cancellation suggestion, or which part of the cancellation suggestion to perform the cancellation operation.

[0060] Based on the same technical concept, embodiments of this application also provide a database storage optimization system, which may include: a database connected in communication and an optimization control module.

[0061] The database can be used to store repository table objects and receive at least one of the optimization suggestion information and revocation suggestion information output by the optimization control module. The optimization control module can be used to execute any of the database storage optimization methods provided in the embodiments of this application on the database in a running or service state.

[0062] The optimization control module may include an information collection submodule, a revenue determination submodule, and an optimization submodule.

[0063] The information acquisition submodule is used to obtain instance information of the database instance; the benefit determination submodule is used to determine the expected optimization benefit of the database table objects in the database instance based on the instance information; and the optimization submodule is used to optimize the storage strategy of the database table objects based on the expected optimization benefit. Here, the expected optimization benefit is the anticipated benefit after the optimization suggestions for the storage strategy of the database table objects are applied, and the optimization suggestion information is used to suggest optimizing the storage strategy of the database table objects.

[0064] In one implementation, instance information includes metadata of the database instance and the database instance's first historical access information. Correspondingly, such as... Figure 3 As shown, the revenue determination submodule 320 may include a first prediction unit 321, a second prediction unit 322, and a revenue determination unit 323.

[0065] The first prediction unit 321 can be used to predict the future access probability of a database table object based on metadata and first historical access information; the second prediction unit 322 can be used to predict the expected cost reduction benefit of the database table object based on metadata; and the benefit determination unit 323 can be used to determine the expected optimization benefit of the database table object based on the future access probability and the expected cost reduction benefit.

[0066] In one implementation, the first prediction unit 321 can be used to: input metadata and first historical access information into a prediction model, and predict the future load probability of a database table object through the prediction model. The prediction model can be pre-trained based on metadata and second historical access information of database table objects in the database instance. The prediction model can be a posterior Bayesian distribution model.

[0067] In one implementation, based on the first prediction unit 321, the second prediction unit 322, and the profit determination unit 323, the profit determination module 320 may further include a training data generation unit. The training data generation unit may be used to generate training data based on metadata and second historical access information, or to generate training data and validation data.

[0068] The first prediction unit 321 can be used to: input metadata and first historical access information into a prediction model, and predict the probability of future load on a database table object through the prediction model. The prediction model can be pre-trained based on metadata and second historical access information of database table objects in the database instance, where the second historical access information is generated later than the first historical access information.

[0069] In one embodiment, the present application may further include an information acquisition module, which can be used to acquire optimization rule information. The optimization rule information may include at least one of optimization scope, preset optimization benefit threshold and cost reduction preference information.

[0070] When the optimization rule information includes an optimization scope, the benefit determination unit 320 can be used to determine the target database table object to be optimized based on the optimization scope, and to determine the expected optimization benefit of the target database table object based on the instance information. When the optimization rule information includes a preset optimization benefit threshold or cost reduction preference information, the optimization submodule 330 can be used to determine the expected optimization benefit of each database table object that is greater than the optimization benefit threshold, and use this as the target expected optimization benefit. Optimization suggestion information is then output for the storage strategy of the database table object corresponding to the target expected optimization benefit. The optimization benefit threshold can be determined based on a preset optimization benefit threshold and cost reduction preference information.

[0071] In one implementation, when outputting optimization suggestion information for the storage strategy of the database table object corresponding to the target expected optimization benefit, the optimization submodule 330 can be used to output optimization suggestion information for the storage strategy of the database table object corresponding to each target expected optimization benefit in descending order.

[0072] In one implementation, when outputting corresponding optimization suggestion information to the database instance based on the expected optimization benefits, the optimization submodule 330 can be used to output corresponding optimization suggestion information to the database instance under specified operation and maintenance conditions based on the expected optimization benefits; the specified operation and maintenance conditions are provided by operation and maintenance rule information. In this embodiment, the optimization rule information and operation and maintenance rule information can be collected from the database instance by the information collection submodule 310.

[0073] In one implementation, the database table object includes at least one of a data table and an index. Correspondingly, the optimization submodule 330 can be used to perform at least one of the following operations: transferring the data table corresponding to the target expected optimization benefit from the hot storage space to the cold storage space; deleting the index corresponding to the target expected optimization benefit.

[0074] In one implementation, such as Figure 3 As shown, the above-mentioned optimization control module may also include a monitoring submodule 340, which can be used to monitor the performance of the database instance and determine whether the database instance has experienced performance regression; if the database instance experiences performance regression, it outputs undo suggestion information to the database instance; the undo suggestion information can be used to suggest undoing the optimization operations that have been performed.

[0075] In one example, refer to Figure 3 The information acquisition submodule 310 can collect instance information of the database instance and output the instance information to the benefit determination submodule 320. The information acquisition submodule 310 can also persist the acquired instance information. The first prediction unit 321 in the benefit determination submodule 320 can predict the future access probability of the database table objects in the database instance based on the metadata in the instance information and the first historical access information. The second prediction unit 322 in the benefit determination submodule 320 can predict the expected cost reduction benefit of the database table objects based on the metadata in the instance information. The benefit determination unit 323 can determine the expected optimization benefit of the database table objects based on the future access probability of the database table objects, the expected cost reduction benefit, and the optimization rule information. The optimization submodule 330 can output corresponding optimization suggestion information to the database instance based on the expected optimization benefit. The monitoring module 340 can monitor the performance of the database instance, determine whether the database instance has experienced performance regression, and output reversal suggestion information to the database instance when performance regression occurs.

[0076] The functions of each module, submodule, and unit in the system provided in this application embodiment can be found in the corresponding description in the above method, and they have corresponding beneficial effects, which will not be repeated here.

[0077] Based on the same technical concept, embodiments of this application also provide an electronic device, such as... Figure 4 As shown, the electronic device includes a memory 401 and a processor 402. The memory 401 stores a computer program that can run on the processor 402. When the processor 402 executes the computer program, it implements any of the methods provided in the embodiments of this application. The number of memories 401 and processors 402 can be one or more.

[0078] The electronic device also includes:

[0079] Communication interface 403 is used to communicate with external devices and perform data exchange and transmission.

[0080] If the memory 401, processor 402, and communication interface 403 are implemented independently, they can be interconnected via a bus to communicate with each other. This bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. This bus can be divided into address bus, data bus, control bus, etc. For ease of representation, Figure 4 The bus is represented by a single thick line, but this does not mean that there is only one bus or one type of bus.

[0081] Optionally, in a specific implementation, if the memory 401, processor 402, and communication interface 403 are integrated on a single chip, then the memory 401, processor 402, and communication interface 403 can communicate with each other through an internal interface.

[0082] This application provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements any of the methods provided in this application.

[0083] This application also provides a chip, which includes a processor for calling and executing instructions stored in a memory, causing a communication device equipped with the chip to perform any of the methods provided in this application.

[0084] This application also provides a chip, including: an input interface, an output interface, a processor, and a memory. The input interface, output interface, processor, and memory are connected through an internal connection path. The processor is used to execute code in the memory. When the code is executed, the processor is used to execute any of the methods provided in the application.

[0085] It should be understood that the aforementioned processor can be a Central Processing Unit (CPU), or other general-purpose processors, Digital Signal Processors (DSPs), Application Specific Integrated Circuits (ASICs), Field-Programmable Gate Arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. General-purpose processors can be microprocessors or any conventional processor. It is worth noting that the processor can be a processor supporting Advanced Reduced Instruction Set Machines (ARM) architecture.

[0086] Further, optionally, the aforementioned memory may include read-only memory and random access memory. The memory may be volatile memory or non-volatile memory, or may include both. Non-volatile memory may include read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), or flash memory. Volatile memory may include random access memory (RAM), which serves as an external cache. By way of example, but not limitation, many forms of RAM are available. Examples include Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), Synchronous DRAM (SDRAM), Double Data Rate SDRAM (DDR SDRAM), Enhanced Synchronous DRAM (ESDRAM), Synchronous Link DRAM (SLDRAM), and Direct Rambus RAM (DR RAM).

[0087] In the above embodiments, implementation can be achieved, in whole or in part, through software, hardware, firmware, or any combination thereof. When implemented in software, it can be implemented, in whole or in part, as a computer program product. A computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions according to this application are generated. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transferred from one computer-readable storage medium to another.

[0088] In the description of this specification, the references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of this application. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples. Moreover, without contradiction, those skilled in the art can combine and integrate the different embodiments or examples described in this specification, as well as the features of those different embodiments or examples.

[0089] Furthermore, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include at least one of that feature. In the description of this application, "a plurality of" means two or more, unless otherwise explicitly specified.

[0090] Any process or method described in the flowchart or otherwise herein can be understood as representing a module, segment, or portion of code comprising one or more executable instructions for implementing a particular logical function or process. Furthermore, the scope of the preferred embodiments of this application includes additional implementations in which functions may be performed not in the order shown or discussed, including substantially simultaneously or in reverse order depending on the functionality involved.

[0091] The logic and / or steps described in the flowchart or otherwise herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0092] It should be understood that various parts of this application can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system. All or part of the steps of the methods in the above embodiments can be implemented by a program instructing related hardware, the program being stored in a computer-readable storage medium, which, when executed, includes one or a combination of the steps of the method embodiments.

[0093] Furthermore, the functional units in the various embodiments of this application can be integrated into a processing module, or each unit can exist physically separately, or two or more units can be integrated into a module. The integrated module can be implemented in hardware or as a software functional module. If the integrated module is implemented as a software functional module and sold or used as an independent product, it can also be stored in a computer-readable storage medium. This storage medium can be a read-only memory, a disk, or an optical disk, etc.

[0094] The above description is merely an exemplary embodiment of this application, but the scope of protection of this application is not limited thereto. Any person skilled in the art can easily conceive of various variations or substitutions within the technical scope described in this application, and these should all 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 storage optimization method, characterized in that, include: Retrieve instance information for the database instance; The instance information includes the metadata of the database instance and the first historical access information of the database instance; Determining the expected optimization benefit of a database table object in the database instance based on the instance information specifically includes: inputting the metadata and the first historical access information into a prediction model; predicting the future access probability of the database table object using the prediction model, wherein the prediction model is pre-trained based on the metadata and the second historical access information of the database table object in the database instance, the second historical access information being generated later than the first historical access information; predicting the expected cost reduction benefit of the database table object based on the metadata; and determining the expected optimization benefit of the database table object based on the future access probability and the expected cost reduction benefit; wherein the expected optimization benefit is the expected benefit brought about by the application of optimization suggestions for the storage strategy of the database table object. Based on the expected optimization benefits, corresponding optimization suggestions are output to the database instance; the optimization suggestions are used to recommend optimization of the storage strategy for the database table objects.

2. The database storage optimization method according to claim 1, characterized in that, The prediction model is a posterior Bayesian distribution model.

3. The database storage optimization method according to any one of claims 1-2, characterized in that, Also includes: Obtain optimization rule information; The optimization rule information includes the optimization range; The step of predicting the expected optimization benefit of the database table objects in the database instance based on the instance information includes: The target database table objects that need to be optimized are determined based on the optimization scope. The expected optimization benefit of the target database table object is determined based on the instance information.

4. The database storage optimization method according to any one of claims 1-2, characterized in that, Also includes: Obtain optimization rule information; The optimization rule information includes at least one of the following: a preset optimization benefit threshold and cost reduction preference information; The step of outputting corresponding optimization suggestion information to the database instance based on the expected optimization benefit includes: Determine the expected optimization benefit of each database table object that is greater than the optimization benefit threshold, and use it as the target expected optimization benefit. The optimized revenue threshold is determined based on the preset optimized revenue threshold or the cost reduction preference information; The system outputs optimization suggestions for the storage strategy of the database table objects corresponding to the target expected return.

5. The database storage optimization method according to claim 4, characterized in that, The database table object includes at least one of data tables and indexes; The optimization suggestion information output for the storage strategy of the database table objects corresponding to the expected optimization benefit includes at least one of the following optimization suggestions: The data table corresponding to the expected optimized return is moved from the hot storage space to the cold storage space. Delete the index corresponding to the expected optimization benefit of the target.

6. The database storage optimization method according to claim 4, characterized in that, The optimization suggestion information output for the storage strategy of the database table object corresponding to the target expected optimization benefit includes: Optimization suggestions are output for the storage strategies of the database table objects corresponding to each target's expected optimization benefit, in descending order of expected optimization benefit.

7. The database storage optimization method according to any one of claims 1-2, characterized in that, The step of outputting corresponding optimization suggestion information to the database instance based on the expected optimization benefit includes: Based on the expected optimization benefits, corresponding optimization suggestions are output to the database instance under specified operation and maintenance conditions; the specified operation and maintenance conditions are provided by operation and maintenance rules information.

8. The database storage optimization method according to any one of claims 1-2, characterized in that, Also includes: The performance of the database instance is monitored to determine whether the database instance has experienced performance regression. In the event of a performance rollback in the database instance, an undo suggestion message is output to the database instance. The cancellation suggestion information is used to suggest canceling the optimization operation that has already been performed.

9. A database storage optimization system, characterized in that, include: The database and optimization control module for communication connections; The database is used to store repository table objects and receive at least one of the optimization suggestion information and revocation suggestion information output by the optimization control module; The optimization control module is used to execute the database storage optimization method according to any one of claims 1-8 on the database in the running or service state.

10. An electronic device, characterized in that, The system includes a memory, a processor, and a computer program stored on the memory, wherein the processor, when executing the computer program, implements the database storage optimization method according to any one of claims 1-8.

11. A computer-readable storage medium, characterized in that, The system contains a computer program that, when executed by a processor, implements the database storage optimization method according to any one of claims 1-8.

Images