CN100543746C - The system and method for a kind of fulfillment database system Automatic Optimal - Google Patents
The system and method for a kind of fulfillment database system Automatic Optimal Download PDFInfo
- Publication number
- CN100543746C CN100543746C CNB2007100875304A CN200710087530A CN100543746C CN 100543746 C CN100543746 C CN 100543746C CN B2007100875304 A CNB2007100875304 A CN B2007100875304A CN 200710087530 A CN200710087530 A CN 200710087530A CN 100543746 C CN100543746 C CN 100543746C
- Authority
- CN
- China
- Prior art keywords
- data
- database systems
- performance
- database
- optimality
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Expired - Fee Related
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
- Stored Programmes (AREA)
Abstract
The invention discloses the system of a kind of fulfillment database system Automatic Optimal, this system comprises: acquisition module and expert module.Described acquisition module is gathered the related data that influences the Database Systems performance and is offered expert module; Described expert module, the analysis of data collected Database Systems performance by acquisition module provides is optimized the database system configuration according to the principle of optimality.The invention also discloses the method for a kind of fulfillment database system Automatic Optimal.Use the present invention, can realize Database Systems are carried out long-term Automatic Optimal comprehensively.
Description
Technical field
The present invention relates to database field, the system and method for particularly a kind of fulfillment database system Automatic Optimal.
Background technology
Data base administration is as a kind of data management mode that makes data have integrality, security and sharing, by being applied in each field more and more widely.Data base administration can use the data base set application system of unifying simply to describe.The various data that application system need be called all are stored in the tables of data of Database Systems, when need carrying out such as operations such as inquiry, deletion, insertion, modifications to these data, application system files a request to Database Systems, Database Systems provide various services at database at request for application system by intrasystem database server, and database server can provide service for application system at a plurality of databases.This process also can be sketched into application system and by the visit to Database Systems the data that are stored in the Database Systems be operated.The data base set application system of unifying need be operation platform with operating system all.
Database Systems can realize by Structured Query Language (SQL) (SQL) the operation of data.SQL is the standardized language that is used for defining object and service data in Database Systems, Database Systems are carried out inquiry, renewal, insertion and the deletion of data by operation SQL, when wherein moving SQL and carrying out the inquiry of data, be that the index that the data list of Database Systems is set up improves efficiency data query by reference.
The configuration of Database Systems generally includes use, optimizer and the database server relevant configuration etc. of disk performance, index configurations, lock, wherein the relevant configuration of database server comprises memory configurations, cache arrangement and CPU configuration etc., is very important ingredient in the Database Systems configuration.The configuration of above-mentioned Database Systems directly influences the performances such as inquiry velocity, operating speed and operational efficiency of Database Systems.Usually when the software product exploitation of application system, meeting is configured every index of the Database Systems that this application systems software need be used, the default configuration value of Database Systems when using as reality, but the performance of Database Systems is not optimum under default configuration.When generally being divided into exploitation, the optimization of Database Systems optimizes and field optimizing, optimizing during exploitation is in the software product performance history of application system, every configuration to Database Systems is optimized at common situation, and field optimizing is after the software product issue of application system, every configuration to Database Systems is optimized at the concrete condition of site of deployment, improves the performance of Database Systems.Therefore field optimizing is more meaningful for the practical application of Database Systems.
After the software product issue of application system, in application, need Database Systems are disposed, it is the database that installation database server, foundation will be used, finishing the back in this process all can not change such as disk performance and software program code, therefore the field optimizing for Database Systems generally is primarily aimed at the database server relevant configuration, promptly comprise index configurations, cache arrangement, memory configurations etc., and optimizer carries out.
At present, the field optimizing at Database Systems mainly contains following three kinds of modes:
The first, the field optimizing of Database Systems mainly makes by hand and carries out, and it is on-the-spot that the Database Engineer arrives Database Systems operations, the running status of manual Query Database system, and the every configuration according to the storehouse system is optimized according to running status and empirical log.The shortcoming of this mode is to optimize to rely on and manually carry out, to tuning personnel technical ability requirement height, take time and effort the cost height, and tuning personnel can only carry out tracking in very short time to Database Systems, can not be according to the dynamic tuning of comprehensive situation, and then obtain best tuning method.If the user has changed a kind of application habit and application scenarios, also need to carry out again again the artificial optimization.
The second, the function of the automatic tuning of integrated memory in Database Systems, the internal memory that Database Systems have been distributed that can be limited is adjusted.The shortcoming of this mode is, can only adjust at the internal memory that Database Systems have been distributed, and can not adjust at other configurations of Database Systems, and have only some Database Systems that this technology is provided, and do not have versatility.
Three, utilize the optimizer of Database Systems inside that SQL directly is optimized, realize optimization the database system performance.The defective of this mode is, optimizer can only carry out limited optimization according to Database Systems inside available data table and index, can't accomplish comprehensive optimization to index, to cooperate SQL to improve the performance of Database Systems, and can only not have semantic optimization according to SQL, can't accomplish comprehensive optimization according to applicable cases.
As seen, above-mentioned three kinds of modes that the database system for field is optimized all can not realize according to the site of deployment situation Database Systems being carried out long-term Automatic Optimal comprehensively.
Summary of the invention
The embodiment of the invention provides the system of a kind of fulfillment database system Automatic Optimal, and this system can realize according to the site of deployment situation Database Systems being carried out long-term Automatic Optimal comprehensively.
The embodiment of the invention provides the method for a kind of fulfillment database system Automatic Optimal, and this method can realize according to the site of deployment situation Database Systems being carried out long-term Automatic Optimal comprehensively.
The embodiment of the invention provides the system of a kind of fulfillment database system Automatic Optimal, and this system comprises: acquisition module and expert module;
Described acquisition module is gathered the related data that influences the Database Systems performance and is offered expert module;
Described expert module, the analysis of data collected Database Systems performance by acquisition module provides is optimized the correspondence configuration that causes the decline of Database Systems performance according to the principle of optimality;
Comprise detection module in the described expert module, be used to detect the Database Systems performance after the optimization, if detecting optimization back database performance descends on the contrary, the configuration that autonomous learning Database Systems performance descends, upgrade the principle of optimality, and with all configuration restores of Database Systems configuration status behind last the optimization.
The embodiment of the invention provides the method for a kind of fulfillment database system Automatic Optimal, and this method comprises:
A, collection influence the related data of Database Systems performance;
B, the performance by the Data Analysis Data Base system of gathering in the steps A are optimized the correspondence configuration that causes the Database Systems performance and descend according to the principle of optimality;
Database Systems performance after C, inspection are optimized, if the Database Systems performance that detects after the optimization descends on the contrary, the configuration that autonomous learning Database Systems performance descends, in the principle of optimality, search the principle of optimality that causes the relevant configuration correspondence that the Database Systems performance descends of autonomous learning gained, deletion or revise the principle of optimality find, and with all configuration restores of Database Systems configuration status behind last the optimization.
The system of the fulfillment database system Automatic Optimal that the embodiment of the invention provides, the acquisition module collection influences the related data of Database Systems performance, the performance situation of the Data Analysis Data Base system that expert module provides according to acquisition module, automatically start optimization, realize the comprehensive long-term Automatic Optimal of Database Systems to the database system configuration.
The method of the fulfillment database system Automatic Optimal that the embodiment of the invention provides, by gathering the related data that influences the Database Systems performance, the performance situation of analytical database system starts the optimization to the database system configuration automatically, realizes the comprehensive long-term Automatic Optimal of Database Systems.
Description of drawings
Fig. 1 is the system architecture synoptic diagram of embodiment of the invention fulfillment database system Automatic Optimal;
Fig. 2 is the synoptic diagram that concerns of the system of embodiment of the invention fulfillment database system Automatic Optimal and Database Systems, application system;
Fig. 3 is the embodiment of the invention data base set same process synoptic diagram of automatic optimization of data base system system of unifying;
Fig. 4 is the method better embodiment process flow diagram of embodiment of the invention fulfillment database system Automatic Optimal;
Fig. 5 is the process flow diagram of the method for embodiment of the invention fulfillment database system Automatic Optimal based on system shown in Figure 1.
Embodiment
For the purpose, technical scheme and the advantage that make the embodiment of the invention is clearer,, the embodiment of the invention is further described below in conjunction with accompanying drawing.
The system of the fulfillment database system Automatic Optimal that the embodiment of the invention provides, this system comprises acquisition module and expert module.Acquisition module is used to gather the related data that influences the Database Systems performance, and the data of gathering are offered expert module.The Data Analysis Data Base system performance that provides by acquisition module is provided expert module, according to the principle of optimality database system configuration is optimized.
The method of the fulfillment database system Automatic Optimal that the embodiment of the invention provides, this method is at first gathered the related data that influences the Database Systems performance, pass through the performance of the Data Analysis Data Base system of collection then, the database system configuration is optimized according to the principle of optimality.
At first, the system of the fulfillment database system Automatic Optimal that the embodiment of the invention is provided is described in detail.The system architecture synoptic diagram of the fulfillment database system Automatic Optimal that Fig. 1 provides for the embodiment of the invention, this system comprises: acquisition module 101 and expert module 102, wherein acquisition module 101 comprises SQL service data acquisition module 103 and performance data collection module 104.
Acquisition module 101 is used to gather the related data that influences the Database Systems performance and offers expert module 102.In the embodiment of the invention, the above-mentioned related data that influences the Database Systems performance can comprise: the performance data of Database Systems and/or operating system and/or application system, and/or the SQL service data of Database Systems, acquisition module 101 is gathered above-mentioned all data as a kind of better embodiment.In the case, may further include SQL service data acquisition module 103 and performance data collection module 104 in the acquisition module 101.
Performance data collection module 104 is used for the performance data of acquisition operations system, application system and Database Systems, the data of gathering is offered expert module 102 use.In the embodiment of the invention, the performance data that performance data collection module 104 is gathered comprises: the unify internal memory of application system of operating system memory distributed data, data base set uses the CPU of data, operating system to take data, the data base set high-speed cache that the CPU of application system takies data and Database Systems of unifying to use data.Application system all is operating platform with operating system because data base set is unified, performance data acquisition module 104 can provide by operating system in the embodiment of the invention application programming interfaces (API) or tool software, obtain the operating system memory distributed data, the unify internal memory of application system of acquisition operations system, data base set uses data; By API or the tool software that operating system provides, the unify CPU of application system of acquisition operations system, data base set takies data.Data acquisition module 104 uses data by the API acquisition database system cache that Database Systems provide in addition.The embodiment of the invention as a kind of better embodiment, also can only be gathered one or more performance datas of gathering as performance data collection module 104 in the above-mentioned data with above-mentioned acquisition mode.
SQL service data acquisition module 103 is used for the SQL service data of acquisition database system, the data of gathering is offered expert module 102 use.In the embodiment of the invention, the data that SQL service data acquisition module 103 is gathered comprise: to carry out SQL statement tabulation that the frequency height is an order item, to be the tables of data tabulation of order item and the operating position data of tables of data index when carrying out SQL with visiting frequency height, update frequency height.SQL service data acquisition module 103 also can only be gathered in the above-mentioned data one or more as the SQL service data.
Above-mentioned performance data collection module 104 and SQL service data acquisition module 103, the mode of image data can be timing acquiring, and the time interval of collection promptly specifically is set according to actual needs.The embodiment of the invention with collecting performance data and SQL service data as a kind of better embodiment, also can a collecting performance data or only gather the SQL service data, can apply in a flexible way according to the needs of actual optimization.
Expert module 102, the performance data that is used for operating system, application system and Database Systems that receptivity data acquisition module 104 provides, receive the database system-SQL service data that SQL service data acquisition module 103 provides, by analyzing the above-mentioned data that receive, analysis draws the Database Systems performance curve, according to the principle of optimality, the database system configuration is optimized.Expert module 102 can descend the Database Systems performance, the trigger condition of You Huaing to start with, promptly when the Database Systems performance of Database Systems performance curve reflection descended, expert module 102 was optimized causing the correspondence configuration that the Database Systems performance descends according to the principle of optimality.
In the embodiment of the invention, the Database System Optimization rule of setting comprises following several:
1) the Memory Allocation principle of optimality, this rule has specifically described the foundation that Memory Allocation is optimized, if promptly the maximum distribution value of operating system memory is greater than the unify maximum use value of application system internal memory of operating system, data base set, distribute the maximum use value of its internal memory to Database Systems, if the maximum distribution value of operating system memory is weighted to Database Systems storage allocation use value less than the unify maximum use value of application system internal memory of operating system, data base set according to applicable cases.Use this principle of optimality, need the internal memory of acquisition operations system, application system and Database Systems to use data, the embodiment of the invention is API or the tool software that provides by operating system, obtain the operating system memory distributed data, the unify internal memory of application system of acquisition operations system, data base set uses data.
2) CPU allocation optimized rule, this rule have specifically described the foundation of CPU allocation optimized, promptly take all factors into consideration applicable cases, CPU value of taking of weighting configuration database system, operating system and application system.Use this principle of optimality, need the CPU of acquisition operations system, application system and Database Systems to take data, the embodiment of the invention is API or the tool software that provides by operating system, and the CPU of acquisition operations system, application system and Database Systems takies data.
3) cache optimization rule, this rule has specifically described the foundation of the cache optimization of Database Systems, promptly according to the actual conditions of using, for Database Systems are distributed the high-speed cache use value.Use this principle of optimality, need the high-speed cache of acquisition database system to use data, the high-speed cache of the API acquisition database system that provides by Database Systems in the embodiment of the invention uses data.
4) the index principle of optimality, this rule has specifically described the foundation that index is optimized, if promptly exist certain index to use mistake or lack the situation that index causes the Database Systems performance to descend in the Database Systems, when Database Systems are idle, create this index again, if exist certain index not use all the time or frequency of utilization is lower and the situation of data space off-capacity, delete this index.Use this principle of optimality, need the index of acquisition database system to use data, gather index by the SQL service data of acquisition database system in the embodiment of the invention and use data.
5) the SQL statement principle of optimality, this rule has specifically described the foundation that SQL statement is optimized, if promptly there is the problem of writing, cause its index not have correct situation about using, then according to the predefined semantic SQL statement of adjusting owing to the higher SQL statement of frequency of utilization.Use this principle of optimality, need the SQL service data of acquisition database system.
Above-mentioned 1)-5) a kind of better embodiment of providing for the embodiment of the invention of the principle of optimality in actual applications, also can be selected several the principles of optimality as Database Systems in the above-mentioned principle of optimality according to the actual optimization needs.
The principle of optimality at above-mentioned every Database Systems, the performance data of operating system, application system and Database Systems that expert module 102 analytical performance data acquisition modules 104 provide, and the database system-SQL service data that provides of SQL service data acquisition module 103, obtain the Database Systems performance curve, according to the above-mentioned principle of optimality, the database system configuration is optimized.This principle of optimality can be stored in the expert module 102, also can be stored in the memory module that links to each other with expert module 102, can adopt different situations to realize as required.
The system of the fulfillment database system Automatic Optimal that the above embodiment of the invention provides, the performance data of SQL service data acquisition module 103 and performance data collection module 104 acquisition database systems, application system and operating system and the SQL service data of database, the mode of collection can be timing acquiring; Expert module 102 obtains the Database Systems performance curve by analyzing the above-mentioned data that collect, and according to the principle of optimality, the database system configuration is optimized, and starting optimized conditions can be that the Database Systems performance descends.This system can carry out long-term follow to the database system performance, carries out comprehensive Automatic Optimal according to the principle of optimality that is provided with, and need not artificial participation, can adapt to the situation that change takes place for application scenarios and application habit.
The system of the fulfillment database system Automatic Optimal that the embodiment of the invention provides can further include detection module in the expert module 102, is used for the performance after the Database System Optimization is detected.If this detection module detects the performance of optimizing the back Database Systems and is improved, can also carry out new optimization according to the said system working method again by undo, if the Database Systems performance descended on the contrary after this detection module detected and optimizes, configuration with the decline of autonomous learning Database Systems performance, in the principle of optimality, upgrade the principle of optimality cause the relevant configuration correspondence that the Database Systems performance descends, and with all configuration restores of Database Systems configuration status behind last the optimization.Therefore, in the system of the fulfillment database system Automatic Optimal that the embodiment of the invention provides, the principle of optimality can dynamically update, and the foundation of renewal is exactly that detection module is to optimizing the testing result of back database performance.When Database Systems were carried out optimizing the first time, the principle of optimality of use was to optimize to begin the principle of optimality of setting before, and in the optimization after optimizing for the first time, the principle of optimality of use is the principle of optimality after upgrading in this suboptimization the last time optimization before.
After further comprising detection module in the expert module 102, after the system of the fulfillment database system Automatic Optimal that the embodiment of the invention provides carries out Automatic Optimal to the configuration of Database Systems each time, all will check the lifting situation of optimizing back Database Systems performance, if therefore the performance of Database Systems does not get a promotion according to expection after Automatic Optimal, can recover the configuration status after the last time is successfully optimized automatically, can not impact Database Systems.And, detection module in the expert module 102 can cause the relevant configuration that the Database Systems performance descends by autonomous learning, upgrade these principles of optimality, make the principle of optimality constantly obtain revising, thereby prevent the situation that may cause performance to descend after the Database System Optimization.
Fig. 2 shows the system of embodiment of the invention fulfillment database system Automatic Optimal and the relation of Database Systems and application system, and promptly application system is by this accessing database system of Automatic Optimal system.
The Automatic Optimal system of fulfillment database system that Database Systems, application system and the embodiment of the invention provide all is the platform operation with operating system, and the operation in the operating system is all divided according to process.The system of the fulfillment database system Automatic Optimal that the embodiment of the invention provides can be a detached process, can be same process with application system also.Fig. 3 shows the same process synoptic diagram of application system and automatic optimization of data base system system.In actual applications, the process of automatic optimization of data base system system also can be the single process that is different from application system, and for example Fig. 2 can represent that also automatic optimization of data base system system and application system are different processes.
The method of the fulfillment database system Automatic Optimal that the embodiment of the invention is provided is elaborated below, and Fig. 4 shows the method better embodiment flow process of the fulfillment database system Automatic Optimal that the embodiment of the invention provides, and this flow process comprises:
Step 401: the SQL service data of the performance data of acquisition database system, operating system, application system and Database Systems.
In this step, the mode of image data can for: the time interval of image data is set according to actual needs, carries out the timing data collection again according to setting interval.The configuring condition of each data all has direct relation with the performance of Database Systems.The mode of above-mentioned image data is a kind of better embodiment, also can gather one or more analysis foundations as follow-up optimization of above-mentioned data according to practical situations.
Step 402: the Data Analysis Data Base system performance by gathering is optimized the database system configuration according to the principle of optimality.
In this step, the data of gathering in the analytical procedure 401, the Database Systems performance curve of corresponding these data configurations of acquisition.According to the principle of optimality, the Database Systems relevant configuration is optimized.The condition that optimize to start can for: the Database Systems performance descends.
The method of the fulfillment database system Automatic Optimal that the embodiment of the invention provides can be optimized the database system configuration according to performance data that collects and SQL service data, and optimization can be carried out according to the principle of optimality.According to the method, realize the comprehensive long-term Automatic Optimal of Database Systems be need not special messenger's maintenance optimization, can reduce maintenance cost.If consider the Database Systems performance after optimizing is detected, may further include after the described step 402 and detect the performance of optimizing the back Database Systems.
The method better embodiment of the fulfillment database system Automatic Optimal that the invention described above embodiment provides can be applied in the system shown in Figure 1, Fig. 5 shows the flow process of the method for embodiment of the invention fulfillment database system Automatic Optimal based on Fig. 1 system, may further comprise the steps after flow process begins:
Step 501: the unify performance data of application system of performance data collection module acquisition operations system, data base set offers expert module.
In this step, API or tool software that the performance data collection module provides by operating system obtain the operating system memory distributed data, and the unify internal memory of application system of acquisition operations system, data base set uses data; By API or the tool software that operating system provides, the unify CPU of application system of acquisition operations system, data base set takies data; By the API that Database Systems provide, the acquisition database system cache is used data.The configuration of these performance datas all has a direct impact the performance of Database Systems.Can set in advance the time interval of image data, according to the above-mentioned data of time interval timing acquiring.
The SQL service data of step 502:SQL service data acquisition module acquisition database system offers expert module.
In this step, the collection of SQL service data acquisition module is to carry out SQL statement tabulation that the frequency height is an order item, to be the tables of data tabulation of order item and the operating position data of tables of data index when carrying out SQL with visiting frequency height, update frequency height.The SQL service data has a direct impact the performance of Database Systems.Can set in advance the time interval of an image data, according to the above-mentioned data of real-time collection of this time interval.
The execution sequence of above-mentioned steps 501 and step 502 can also have following two kinds of situations.First kind is step 501 and step 502 exchange execution sequence, i.e. the SQL service data of acquisition database system of SQL service data acquisition module elder generation, performance data collection module be the unify performance data of application system of acquisition operations system, data base set again.Second kind be step 501 with step 502 can executed in parallel, promptly with the SQL service data of SQL service data acquisition module acquisition database system, and performance data collection module acquisition operations system, data base set unify the performance data of application system can executed in parallel.The change of the step 501 of foregoing description and step 502 execution sequence can not impact the performance evaluation of follow-up data storehouse system.
Step 503: the performance data that expert module provides by the performance data acquisition module, and the SQL service data that provides of SQL service data acquisition module, the analytical database system performance is optimized according to the configuration of the principle of optimality to Database Systems.
In this step, the performance data that expert module analytical performance data acquisition module provides, and the SQL service data that provides of SQL service data acquisition module obtain the correlated performance curve of Database Systems.According to the analysis result to database performance, expert module is optimized the configuration of Database Systems, and according to the principle of optimality, wherein starting optimized conditions can descend for: Database Systems performance during optimization.The principle of optimality described in this step can dynamically update after initial setting up, that is to say that the employed principle of optimality of optimization flow process of Database Systems is the initial optimization rules that are provided with before flow process begins for the first time, optimizing for the first time the employed principle of optimality in the optimization flow process of the Database Systems each time after the flow process, all is the last principle of optimality of upgrading in the flow process of optimizing before this suboptimization flow process.The concrete update method of the principle of optimality is described in detail in subsequent step.
Step 504: the detection module in the expert module is checked the Database Systems performance after optimizing.
In this step, the detection module in the expert module behind the Database Systems configuration optimization, the Database Systems performance after self-verifying is optimized.
Step 505: whether the database performance that detects in the determining step 504 promotes, and gets a promotion if optimize back Database Systems performance, and then the Returning process initial state continues execution in step 506 if optimization back Database Systems performance descends.
In this step, if the testing result in the step 504 is to optimize back Database Systems performance to be improved, then expert module can undo.Returning process initial state in this step is meant, continues to be optimized according to the performance of the described flow process of step 501～step 505 to Database Systems after this step.If testing result is to optimize back Database Systems performance to descend on the contrary, then continue execution in step 506.
Step 506: the configuration that the detection module autonomous learning Database Systems performance in the expert module descends, renewal causes the principle of optimality of the relevant configuration correspondence of Database Systems performance decline in the principle of optimality.
In this step, the detection module in the expert module checks out that the Database Systems performance after the optimization descends on the contrary, and then autonomous learning causes the relevant configuration that the Database Systems performance descends.In the principle of optimality that is provided with, find the principle of optimality of the relevant configuration institute foundation that causes that the Database Systems performance descends, revise or delete this principle of optimality.
Step 507: the configuration status the detection module in the expert module is optimized all configuration restores of Database Systems to the last time after, Returning process initial state.
In this step, detection module in the expert module disposes the configuration status that all returns to after the last time is optimized with all of Database Systems, described Returning process initial state is meant that expert module all returns to all configurations of Database Systems after the configuration status behind last the optimization, continuation is carried out new optimization according to step 501～step 506, and the employed principle of optimality is the principle of optimality after upgrading in this flow process in the new optimization flow process.
Above-mentioned steps 506 and step 507 also can exchange execution sequence, be that detection module in the expert module is earlier with all configuration restores of Database Systems configuration status behind last the optimization, autonomous learning causes the relevant configuration that the Database Systems performance descends again, delete or revise the corresponding principle of optimality of this configuration, Returning process initial state after the renewal principle of optimality, continuation is carried out new optimization according to step 501～step 506, and the employed principle of optimality is the principle of optimality after upgrading in this flow process in the new optimization flow process.
The method better embodiment of the embodiment of the invention fulfillment database system Automatic Optimal that above-mentioned steps 501～step 507 is described is based on the flow process of system shown in Figure 1, realization is to the long-term of database system performance and comprehensively optimization, need not the special messenger and be optimized maintenance, reduce maintenance cost, Database Systems Performance Detection behind each the optimization, with the configuration status of Database Systems configuration restore behind last the optimization, prevent when guarantee optimizing back Database Systems performance and descending owing to optimize the influence that causes the Database Systems performance to descend and cause.
The method and system of the fulfillment database system Automatic Optimal that the embodiment of the invention provides, can be at various Database Systems, for example Sybase ASE Database Systems, MS SQL Server Database Systems, IBM DB2 Database Systems or oracle database system etc.
Above-described specific embodiment; purpose of the present invention, technical scheme and beneficial effect are further described; institute is understood that; the above only is specific embodiments of the invention; and be not intended to limit the scope of the invention; within the spirit and principles in the present invention all, any modification of being made, be equal to replacement, improvement etc., all should be included within protection scope of the present invention.
Claims (8)
1, the system of a kind of fulfillment database system Automatic Optimal is characterized in that this system comprises: acquisition module and expert module;
Described acquisition module is gathered the related data that influences the Database Systems performance and is offered expert module;
Described expert module, the analysis of data collected Database Systems performance by acquisition module provides is optimized the correspondence configuration that causes the decline of Database Systems performance according to the principle of optimality;
Comprise detection module in the described expert module, be used to detect the Database Systems performance after the optimization, if detecting optimization back database performance descends on the contrary, the configuration that autonomous learning Database Systems performance descends, upgrade the principle of optimality, and with all configuration restores of Database Systems configuration status behind last the optimization.
2, the system as claimed in claim 1, it is characterized in that, the described related data that influences the Database Systems performance comprises: the performance data of Database Systems and/or operating system and/or application system, and/or database system structure query language SQL service data; Described acquisition module comprises performance data collection module and SQL service data acquisition module;
Described performance data collection module, the performance data of acquisition database system and/or operating system and/or application system offers expert module with the data of gathering;
Described SQL service data acquisition module, the SQL service data of acquisition database system offers expert module with the data of gathering.
3, the method for a kind of fulfillment database system Automatic Optimal is characterized in that this method comprises:
A, collection influence the related data of Database Systems performance;
B, the performance by the Data Analysis Data Base system of gathering in the steps A are optimized the correspondence configuration that causes the Database Systems performance and descend according to the principle of optimality;
Database Systems performance after C, inspection are optimized, if the Database Systems performance that detects after the optimization descends on the contrary, the configuration that autonomous learning Database Systems performance descends, in the principle of optimality, search the principle of optimality that causes the relevant configuration correspondence that the Database Systems performance descends of autonomous learning gained, deletion or revise the principle of optimality find, and with all configuration restores of Database Systems configuration status behind last the optimization.
4, method as claimed in claim 3 is characterized in that, the described related data that influences the Database Systems performance comprises: the performance data of Database Systems and/or operating system and/or application system, and/or the SQL service data of Database Systems; The related data that the described collection of steps A influences the Database Systems performance is:
Is set, the performance data of timing acquiring Database Systems and/or operating system and/or application system, and/or the SQL service data of Database Systems the acquisition interval time.
5, method as claimed in claim 4, it is characterized in that, the performance data of described Database Systems and/or operating system and/or application system comprises: the internal memory of Database Systems uses data, central processor CPU to take data, high-speed cache uses data, and/or internal memory distributed data, the CPU of operating system take data, and/or the internal memory of application system uses data, CPU to take data; The performance data of described acquisition database system and/or operating system and/or application system is:
The application programming interfaces API or the tool software that provide by operating system obtain the operating system memory distributed data, and the unify internal memory of application system of acquisition operations system, data base set uses data; By API or the tool software that operating system provides, the unify CPU of application system of acquisition operations system, data base set takies data; By the API that Database Systems provide, the acquisition database system cache is used data.
6, method as claimed in claim 4, it is characterized in that, the SQL service data of described Database Systems comprises: to carry out the SQL statement tabulation that the frequency height is an order item, and/or be the tables of data tabulation of order item, and/or the operating position data of tables of data index when carrying out SQL with visiting frequency height, update frequency height.
As claim 3 or 4 described methods, it is characterized in that 7, step B is described by the Data Analysis Data Base system performance of gathering in the steps A to be:
Data by the steps A collection obtain the Database Systems performance curve, by the performance of tracing analysis Database Systems.
8, method as claimed in claim 3, it is characterized in that, the described principle of optimality comprises the Memory Allocation principle of optimality, and/or central processor CPU allocation optimized rule, and/or cache optimization rule, and/or the index principle of optimality, and/or the SQL statement principle of optimality, the wherein said Memory Allocation principle of optimality, when the internal memory distribution maximal value of defining operation system is unified application system internal memory maximum use value sum greater than operating system, data base set, for Database Systems are distributed the maximum use value of its internal memory; When the internal memory distribution maximal value of operating system is unified application system internal memory maximum use value sum less than operating system, data base set, be Database Systems weight assignment internal memory use value according to applicable cases;
Described CPU allocation optimized rule, definition is the CPU quantity that Database Systems, application system and operating system configuration take according to application need;
Described cache optimization rule, definition are the high-speed cache use value of Database Systems configuration needs according to application need;
When there are index use mistake in the described index principle of optimality, definition or lack index, when Database Systems are idle, create this index again; Exist index not use all the time or frequency of utilization not low, and during the data space off-capacity, delete this index;
The described SQL statement principle of optimality, definition when causing its index correctly not use, are again write this SQL statement according to the SQL semanteme owing to the high SQL statement of frequency of utilization writes.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CNB2007100875304A CN100543746C (en) | 2007-03-16 | 2007-03-16 | The system and method for a kind of fulfillment database system Automatic Optimal |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CNB2007100875304A CN100543746C (en) | 2007-03-16 | 2007-03-16 | The system and method for a kind of fulfillment database system Automatic Optimal |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN101059810A CN101059810A (en) | 2007-10-24 |
| CN100543746C true CN100543746C (en) | 2009-09-23 |
Family
ID=38865916
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CNB2007100875304A Expired - Fee Related CN100543746C (en) | 2007-03-16 | 2007-03-16 | The system and method for a kind of fulfillment database system Automatic Optimal |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN100543746C (en) |
Families Citing this family (30)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101778400B (en) * | 2010-01-08 | 2012-06-27 | 哈尔滨工业大学 | Database-based telephone traffic analysis and prediction system and telephone traffic prediction method using same |
| CN101882147B (en) * | 2010-05-12 | 2012-07-25 | 北京航空航天大学 | Curve data storage device and method |
| CN101901270A (en) * | 2010-08-05 | 2010-12-01 | 上海酷吧信息技术有限公司 | Internal memory database method supporting mass memory |
| CN102163232B (en) * | 2011-04-18 | 2012-12-05 | 国电南瑞科技股份有限公司 | SQL (Structured Query Language) interface implementing method supporting IEC61850 object query |
| CN102508963A (en) * | 2011-11-02 | 2012-06-20 | 哈尔滨工业大学 | Parametric weapon combat efficiency analysis system based on simulation and analysis method thereof |
| CN103164455B (en) * | 2011-12-15 | 2016-08-03 | 百度在线网络技术（北京）有限公司 | The optimization method and device of data base |
| CN103186603B (en) * | 2011-12-29 | 2016-04-27 | 中国移动通信集团浙江有限公司 | Determine that SQL statement is on the method for the impact of the performance of key business, system and equipment |
| CN103514227B (en) * | 2012-06-29 | 2016-12-21 | 阿里巴巴集团控股有限公司 | A kind of method and device of more new database |
| CN102999604B (en) * | 2012-11-20 | 2017-07-28 | 北京奇虎科技有限公司 | The detection method and device of a kind of database performance |
| CN104714984A (en) * | 2013-12-17 | 2015-06-17 | 中国移动通信集团湖南有限公司 | Database optimization method and device |
| CN103793526A (en) * | 2014-02-24 | 2014-05-14 | 浪潮电子信息产业股份有限公司 | Method for monitoring SQL statement performance |
| CN103984726B (en) * | 2014-05-16 | 2017-03-29 | 上海新炬网络信息技术有限公司 | A kind of local correction method of data base's implement plan |
| CN105320679B (en) * | 2014-07-11 | 2019-05-24 | 中国移动通信集团重庆有限公司 | A kind of tables of data indexed set symphysis is at method and device |
| CN104503974B (en) * | 2014-11-17 | 2017-07-18 | 杭州斯凯网络科技有限公司 | A kind of relational database automatic optimization method based on cloud platform |
| CN104615696B (en) * | 2015-01-23 | 2018-05-01 | 国家电网公司 | A kind of 95598 knowledge base system and building method |
| CN104965861B (en) * | 2015-06-03 | 2018-06-22 | 上海新炬网络信息技术股份有限公司 | A kind of data access monitoring device |
| WO2017015792A1 (en) * | 2015-07-24 | 2017-02-02 | Cai Yongcheng Eddy | Sql performance recommendations and scoring |
| CN106407830B (en) * | 2015-07-29 | 2020-01-21 | 阿里巴巴集团控股有限公司 | Cloud-based database detection method and device |
| CN105138621B (en) * | 2015-08-14 | 2019-03-26 | 浪潮电子信息产业股份有限公司 | A kind of Sybase ASE database configuration optimization system and method |
| CN106909485B (en) * | 2015-12-23 | 2020-10-23 | 伊姆西Ip控股有限责任公司 | Method and apparatus for determining causes of storage system performance degradation |
| CN106202403B (en) * | 2016-07-11 | 2020-03-20 | 浪潮通用软件有限公司 | Method for creating database index through dynamic analysis |
| US9753745B1 (en) * | 2016-08-17 | 2017-09-05 | TCL Research America Inc. | System and method for system function-flow optimization utilizing application programming interface (API) profiling |
| CN106709005B (en) * | 2016-12-23 | 2020-11-24 | 北京奇虎科技有限公司 | Method, device and system for processing redundant index in database system |
| CN107341203A (en) * | 2017-06-22 | 2017-11-10 | 北京北信源软件股份有限公司 | The access control and optimiged index method and apparatus of a kind of distributed search engine |
| CN108519949A (en) * | 2018-04-12 | 2018-09-11 | 郑州云海信息技术有限公司 | A kind of test method, system and the device of ORACLE databases pressure |
| CN109885623A (en) * | 2019-01-20 | 2019-06-14 | 成都飞机工业（集团）有限责任公司 | A kind of database automatic monitoring method |
| CN111694816B (en) * | 2020-06-15 | 2024-04-23 | 中国工商银行股份有限公司 | Processing method and device for optimizing database table |
| CN112988786B (en) * | 2021-05-12 | 2022-04-05 | 北京优炫软件股份有限公司 | Database maintenance method and device |
| CN112925804B (en) * | 2021-05-12 | 2021-08-03 | 北京优炫软件股份有限公司 | Database maintenance method and device |
| CN115640274A (en) * | 2021-07-19 | 2023-01-24 | 中兴通讯股份有限公司 | Method, device and storage medium for dynamically adjusting database model |
-
2007
- 2007-03-16 CN CNB2007100875304A patent/CN100543746C/en not_active Expired - Fee Related
Also Published As
| Publication number | Publication date |
|---|---|
| CN101059810A (en) | 2007-10-24 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN100543746C (en) | The system and method for a kind of fulfillment database system Automatic Optimal | |
| CN100451989C (en) | Software testing system and testing method | |
| US8688622B2 (en) | Methods and systems for loading data into a temporal data warehouse | |
| US7610264B2 (en) | Method and system for providing a learning optimizer for federated database systems | |
| CN100478944C (en) | Automatic task generator method and system | |
| US7797286B2 (en) | System and method for externally providing database optimizer statistics | |
| CN102479225B (en) | Distributed data analyzing and processing method and system | |
| US20030158842A1 (en) | Adaptive acceleration of retrieval queries | |
| CN104620239A (en) | Adaptive query optimization | |
| US20130191370A1 (en) | System and Method for Querying a Data Stream | |
| CN102117306A (en) | Method and system for monitoring ETL (extract-transform-load) data processing process | |
| GB2460532A (en) | Metadata driven data capture for a temporal data warehouse | |
| CN105069134A (en) | Method for automatically collecting Oracle statistical information | |
| US20210124738A1 (en) | Utilizing neural network and machine learning models to generate a query after migrating data from a source data structure to a target data structure | |
| CN103559300A (en) | Data query method and device | |
| CN102750384A (en) | Device and method for acquiring data from multidatabase engine | |
| CN102148848B (en) | Data management method and system | |
| US11475011B2 (en) | Pruning cutoffs for database systems | |
| Du et al. | Dynamic scheduling with process migration | |
| CN117271481B (en) | Automatic database optimization method and equipment | |
| CN102693174B (en) | Object broker database constraint-oriented backup method | |
| CN101895426B (en) | Data acquisition method and system in network resource evaluation | |
| CN101158976A (en) | Method and system for data-base recording enquire preprocess | |
| Bowman et al. | SQL Anywhere: A holistic approach to database self-management | |
| CN115544073A (en) | Cloud native database application load autonomous optimization method and device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C14 | Grant of patent or utility model | ||
| GR01 | Patent grant | ||
| CF01 | Termination of patent right due to non-payment of annual fee |
Granted publication date: 20090923 Termination date: 20160316 |
|
| CF01 | Termination of patent right due to non-payment of annual fee |