CN115757055B - Database unified operation and maintenance and comprehensive performance scoring system based on Spring Cloud - Google Patents
Database unified operation and maintenance and comprehensive performance scoring system based on Spring Cloud Download PDFInfo
- Publication number
- CN115757055B CN115757055B CN202211283205.6A CN202211283205A CN115757055B CN 115757055 B CN115757055 B CN 115757055B CN 202211283205 A CN202211283205 A CN 202211283205A CN 115757055 B CN115757055 B CN 115757055B
- Authority
- CN
- China
- Prior art keywords
- database
- service
- performance
- management unit
- sql
- 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.)
- Active
Links
- 238000012423 maintenance Methods 0.000 title claims abstract description 33
- 230000004044 response Effects 0.000 claims abstract description 7
- 230000003068 static effect Effects 0.000 claims abstract description 7
- 238000007726 management method Methods 0.000 claims description 60
- 238000012544 monitoring process Methods 0.000 claims description 49
- 230000006870 function Effects 0.000 claims description 46
- 238000000034 method Methods 0.000 claims description 10
- 238000004458 analytical method Methods 0.000 claims description 8
- 238000012986 modification Methods 0.000 claims description 6
- 230000004048 modification Effects 0.000 claims description 6
- 238000013523 data management Methods 0.000 claims description 4
- 238000013475 authorization Methods 0.000 claims description 3
- 238000003745 diagnosis Methods 0.000 claims description 3
- 230000010354 integration Effects 0.000 claims description 3
- 238000010606 normalization Methods 0.000 claims description 3
- 238000005070 sampling Methods 0.000 claims description 3
- 238000007792 addition Methods 0.000 claims description 2
- 238000012217 deletion Methods 0.000 claims description 2
- 230000037430 deletion Effects 0.000 claims description 2
- 238000012795 verification Methods 0.000 claims description 2
- 239000003054 catalyst Substances 0.000 claims 1
- 230000008859 change Effects 0.000 abstract description 4
- 230000002159 abnormal effect Effects 0.000 abstract description 2
- 230000007246 mechanism Effects 0.000 abstract description 2
- 230000001960 triggered effect Effects 0.000 abstract description 2
- 230000008569 process Effects 0.000 description 4
- 238000012545 processing Methods 0.000 description 3
- 238000004364 calculation method Methods 0.000 description 2
- 238000004891 communication Methods 0.000 description 2
- 238000013461 design Methods 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 2
- 238000011156 evaluation Methods 0.000 description 2
- 239000002699 waste material Substances 0.000 description 2
- WVCHIGAIXREVNS-UHFFFAOYSA-N 2-hydroxy-1,4-naphthoquinone Chemical compound C1=CC=C2C(O)=CC(=O)C(=O)C2=C1 WVCHIGAIXREVNS-UHFFFAOYSA-N 0.000 description 1
- 101000822425 Arthrobacter sp. (strain KUJ 8602) Guanidinobutyrase Proteins 0.000 description 1
- 238000013473 artificial intelligence Methods 0.000 description 1
- 230000003139 buffering effect Effects 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- BTCSSZJGUNDROE-UHFFFAOYSA-N gamma-aminobutyric acid Chemical compound NCCCC(O)=O BTCSSZJGUNDROE-UHFFFAOYSA-N 0.000 description 1
- 230000036541 health Effects 0.000 description 1
- 230000003993 interaction Effects 0.000 description 1
- 230000002688 persistence Effects 0.000 description 1
- 230000000007 visual effect Effects 0.000 description 1
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a system for unified operation and maintenance of a database and scoring comprehensive performance based on Spring Cloud, which comprises a basic service module, a background service module and a foreground service module, wherein the system can comprehensively monitor the performance of the database so as to know the performance of the database on key indexes such as database connection number, SQL response time, storage capacity and the like in real time; the corresponding problem management unit provides an alarm and problem management mechanism, and once some indexes of the database are abnormal, the alarm and problem management can be immediately triggered to remind a user of timely focusing, so that the operation safety of the database is ensured. Thousands of databases or even tens of thousands of databases can be managed on a large scale, and convenience and efficiency of operation and maintenance work are greatly improved. In addition, the operation and maintenance system has good expansibility, and can conveniently support expansion of other types of databases. The invention also provides a database comprehensive performance scoring system based on Spring Cloud, which performs comprehensive scoring on the performance of the DB2 database in a performance management unit of a foreground service module, and because the value of each factor changes dynamically, the dynamic change can reflect whether the state of each factor is stable or not, so that the dynamic weight is increased on the basis of the static weight, and the performance condition of the DB2 database can be reflected more accurately.
Description
Technical Field
The invention belongs to the field of artificial intelligence, and particularly relates to a database unified operation and maintenance and comprehensive performance scoring system based on Spring Cloud.
Background
Under the background of big data age, large-scale data is accumulated rapidly in various fields, and the phenomenon is particularly remarkable in enterprises such as banks, telecom and the like. In the enterprises, the data volume is large, the types of the used database systems are various, the databases comprise the traditional databases such as Oracle and DB2, the novel databases such as openGauss, tiDB, the characteristics of the different databases are different, the use and maintenance methods are different, and the databases are incompatible, so that the unprecedented challenges are brought to maintenance staff of the database systems.
Typically, different micro services have different network addresses, and external clients may need to invoke multiple service interfaces to fulfill a service requirement. If the client communicates directly with the micro-service, there are problems in that 1) the client requests different micro-services multiple times, increasing the complexity of the client; 2) The cross-domain request exists, and the processing is relatively complex under a certain scene; 3) Authentication is complex, and each service needs independent authentication; 4) Difficult to reconstruct, which may require repartitioning of the microservices as the project iterates, would be difficult to implement if the clients were to communicate directly with the microservices; 5) Some micro-services may use other protocols and direct access may be difficult.
Drawings
FIG. 1 is a diagram showing the structure of a database unified operation and maintenance and comprehensive performance scoring system based on Spring Cloud.
Fig. 2 is a calculation formula and weight of each performance factor of the DB2 database.
Disclosure of Invention
Based on the problems existing in the prior art, in order to improve the efficiency and convenience of database management, the invention provides a database unified operation and maintenance and comprehensive performance scoring system based on Spring Cloud, which comprises a basic service module, a background service module and a foreground service module, wherein the basic service module provides basic operation and maintenance functions of service registration, configuration, routing, tracking and monitoring for other modules, the background service module is used as a background service providing interface for the foreground service module, the foreground service module is used by platform users, the database operation and maintenance management function is provided, and operation and maintenance management requirements are realized in different foreground service units according to different classifications, and the system is characterized in that:
1. the basic service module comprises a service registration center, a configuration center, a micro service gateway, a service tracking unit and a service monitoring unit;
(1) A service registry, in which all programs for providing services need to be registered and can be found and called by other service modules;
(2) A configuration center, which provides global configuration for all micro services, and other micro services can use the global configuration as a configuration client;
(3) The micro-service gateway is arranged between the client and the server;
(4) The service tracking unit is used for visually displaying the calling relation between the micro services and realizing asynchronous distributed tracking;
(5) The service monitoring unit is responsible for sampling and storing data and displaying data;
2. the background service module comprises a CMDB service unit, an SQL execution service unit, a monitoring data acquisition service unit and a monitoring data access service unit, wherein
(1) The CMDB service unit provides the integration of the display of the CMDB data and the distributed database and provides the CMDB information required by other service modules;
(2) The SQL execution service unit comprises the following functions:
1) A single SQL executing function is provided to realize SQL inquiry of the target library and return results;
2) A multi-SQL execution function, which provides a single target DB with a plurality of SQL execution functions, and returns a result set to a caller after all SQL is executed;
3) A multi-SQL batch execution function for executing a plurality of SQL to a single target DB and recording an execution result;
4) Acquiring a DBversion function, and providing an interface to acquire a version of a target DB;
5) Acquiring a field type obtained by single SQL execution, executing SQL according to provided information to a target DB, acquiring a result, recording the types of all fields, and returning the result to a caller;
6) Providing a switch for access control, and executing a management and control function by the database SQL;
(3) The monitoring data acquisition service unit periodically performs data acquisition on the monitored database, sends SQL (structured query language) to be executed for acquiring data to the SQL execution service to be executed on the target library, returns a result set to the monitoring data acquisition service, and simultaneously takes the monitoring data acquisition service as a producer of kafka data, places the acquired data into the kafka and simultaneously comprises a kafka data consumer;
(4) The monitoring data access service unit displays a chart of the front-end page;
3. the foreground service module comprises a resource management unit, a performance management unit, a basic operation and maintenance unit, a data management unit, a metadata management unit, a problem management unit, a user and authority management unit and a configuration management unit, and is used by a platform user;
(1) The resource management unit displays the state, architecture, load and capacity of the database, and can jump to a single database page, a logic database page, a topological graph page, a performance page and a capacity page of the database.
(2) The performance management unit performs performance management on performance index monitoring, performance analysis, performance diagnosis and performance trend analysis, and scores the overall situation performance.
(3) The basic operation and maintenance unit provides example start-stop, master-slave switching, parameter modification, backup, user establishment, user authorization (white list opening) and export backup of the logic library for the database.
(4) The data management unit analyzes the capacity space usage size, the total capacity space size, the capacity increment ratio, the capacity increment amplitude, the large table, the hot table and the index-free table from the physical library and the logical library.
(5) The metadata management unit provides a distributed database physical library table distribution condition query and a table structure comparison function between physical tables and a physical library information query.
(6) The problem management unit establishes a problem item standard, and after comparing the problem item standard with the standard, the problem management unit finds out the unreasonable or optimized state in the database and sets the unreasonable or optimized state as a problem; after the problem is generated, tracking and recording the attribution and the status of the problem, and continuously tracking the whole life cycle of the problem.
(7) The user and authority management unit comprises a user configuration subunit, a role configuration subunit and an operation record subunit, wherein the user configuration subunit realizes the addition, deletion and verification operation of the user, can configure the role of the user and configures a viewable database; the character configuration subunit realizes the operations of adding, deleting and checking characters, can configure the authority possessed by the characters and configures a viewable database; the operation recording subunit implements a viewing function.
(8) The configuration management unit comprises a physical library configuration subunit, a template configuration subunit, a system configuration subunit and a monitoring configuration subunit, wherein the physical library configuration subunit selects a monitored database and provides a modification function; the template configuration subunit modifies configuration information of the physical library in batches; the system configuration subunit configures system information; the monitoring configuration subunit checks the current database and the monitoring item state, and restores the normal state.
The database unified operation and maintenance system based on Spring Cloud can comprehensively monitor the performance of the database so as to know the performance of the database on key indexes such as the database connection number, SQL response time, storage capacity and the like in real time; the corresponding problem management unit provides an alarm and problem management mechanism, and once some indexes of the database are abnormal, the alarm and problem management can be immediately triggered to remind a user of timely focusing, so that the operation safety of the database is ensured. Thousands of databases or even tens of thousands of databases can be managed on a large scale, and convenience and efficiency of operation and maintenance work are greatly improved. In addition, the operation and maintenance system has good expansibility, and can conveniently support expansion of other types of databases.
The invention also provides a database comprehensive performance scoring system based on Spring Cloud, which comprehensively scores the performance of the DB2 database in a performance management unit of a foreground service module, and is characterized in that the performance factors of the DB2 database are SQL response time, effective reading proportion, waiting time duty ratio, buffer pool hit rate, average operation line number, package Cache hit rate and Catalo respectivelyg Cache hit rate and sorting overflow rate, and the performance factor is expressed as e i Wherein i is more than or equal to 1 and less than or equal to 8, and the corresponding score is v i The corresponding static weight is s i . Factor e i The last K historical values of (2) are recorded as e ij J is more than or equal to 1 and less than or equal to K, K is a natural number more than 1, e i Is the historical mean value of (1)
e i Standard deviation M for stability of (2) i The representation is made of a combination of a first and a second color,
m for obtaining various performance factors i Thereafter, M is i Normalization, calculate e i Dynamic weight d of (2) i ,
e i Corresponding comprehensive weight w i Is that
w i ＝λs i +(1-λ)d i ,
Where λ is a real number within the interval [0,1 ].
Finally, obtaining the comprehensive performance score V of the DB2 database as
Because the value of each factor changes dynamically, the dynamic change can reflect whether the state of each factor is stable or not, so that the dynamic weight is increased on the basis of the static weight, and the performance condition of the DB2 database can be reflected more accurately.
Detailed Description
The system design is based on layering and modularization theory of Spring Cloud architecture, and the system comprises a basic service module, a background service module and a foreground service module, and the basic functions of the modules are as follows:
1. the basic service module comprises a service registration center, a configuration center, a micro service gateway, a service tracking unit and a service monitoring unit; the basic service module provides basic operation and maintenance functions such as service registration, configuration, routing, tracking, monitoring and the like for the foreground service module and the background service module based on the Spring Cloud framework.
(1) Service registry
Is a registry and discovery center of micro services where all programs that provide services need to be registered and can be invoked by other service discoveries. Services registered in the service registry maintain a surviving relationship in the service center through heartbeat. The service registry may use a high availability architecture and technology use Spring Cloud Eureka.
(2) Configuration center
Global configurations, such as username-password to connect to a local library, etc., are provided for all micro-services so that other micro-services can use these as configuration clients. If the configuration of the configuration server is modified, the configuration server can inform all the monitoring clients to update through the message bus, so that all the service clients do not need to be restarted, and the technology used by the service configuration is mainly Spring Cloud Config.
(3) Micro service gateway
Different micro-services typically have different network addresses, and external clients may need to invoke interfaces for multiple services to fulfill a business need. If the client communicates directly with the microservice, the following problems may exist:
1) The client can request different micro services for many times, so that the complexity of the client is increased;
2) The cross-domain request exists, and the processing is relatively complex under a certain scene;
3) Authentication is complex, and each service needs independent authentication;
4) Difficult to reconstruct, which may be difficult to implement if the client communicates directly with the microservices as the project iterates, may require repartitioning the microservices;
5) Some micro-services may use other protocols and direct access may be difficult.
The above problems can be solved by means of a micro-service gateway. The micro service gateway is an intermediate layer between the client and the server, all external requests can pass through the micro service gateway first, so that the client only needs to interact with the gateway without directly calling an interface of a specific micro service, and the micro service gateway is convenient to monitor, easy to authenticate and capable of reducing interaction times between the client and each micro service.
(4) Service tracking unit
In the micro-service architecture, how to clearly record the call links of services by calling each other among numerous micro-services is a problem to be solved. Meanwhile, for various reasons, when cross-process service call fails, an operation and maintenance person hopes to locate the problem by checking the call relation between the log and the check service, the service tracking unit adopts a Spring Cloud Sleuth component to solve the micro-service tracking problem, and the call relation between the micro-services can be intuitively displayed by combining Zipkin, so that asynchronous distributed tracking is realized by using Kafka.
(5) Service monitoring unit
The service monitoring unit consists of Prometheus and Grafana, the former is responsible for sampling data and storing the data; the latter shows the data.
2. Background service module
These services are used primarily as background services providing interfaces for foreground services, with which the average user does not need to interact directly.
(1) CMDB service unit
The CMDB service provides the integration of the display of the CMDB data and the distributed database, realizes the display of the detailed information and the statistical information of the physical library from the aspects of an application system, the physical library and the logical library, simultaneously realizes the management of the logical library and provides the CMDB information required by other services. Has the following basic functions:
1) Providing physical library information statistics in terms of application system, operating system angle, physical library angle, logical library angle
2) And displaying the basic information of the application system, the physical library and the logical library.
3) And exporting application system, physical library and logical library data.
4) The CRUD of the logical library is manually managed.
5) The Api is provided externally with the CMDB information.
(2) The SQL execution service unit mainly comprises the following functions:
1) The single SQL execution function is used for realizing SQL query on the target library and returning results, and can control all accesses to the target library by using the API as a unique entry, and is mainly applied to real-time data query of pages.
2) The multi-SQL execution function is used for providing a single target DB with a plurality of SQL executions, and is mainly applied to a fetch program, and a result set is returned to a caller after all SQL executions are completed.
3) And the multi-SQL batch execution function is that a function of executing a plurality of SQL codes on a single target DB and recording an execution result is provided, the transaction is supported, and the whole transaction rollback when failure occurs can be set to ensure the consistency of the whole transaction.
4) And the DBversion acquisition function is to provide an interface for acquiring the version of the target DB, recording a large version, a small version and a patch version and returning the large version, the small version and the patch version to the caller.
5) And acquiring the field types obtained by single SQL execution, namely providing an interface to execute SQL according to the provided information to a target DB, acquiring a result, recording the types of all the fields, and returning the result to a caller.
6) And providing a switch for access control, performing a management and control function on the database SQL, carrying an identifier in the parameter, and returning a unified error code if the database has access rights and the SQL operator can execute the SQL in the target library if the database has the access rights.
(3) Monitoring data acquisition service unit
The monitoring data acquisition service unit periodically performs data acquisition on the monitored database, the SQL execution service required to be executed by the acquired data is sent to the target library to be executed, a result set is returned to the monitoring data acquisition service unit, meanwhile, the monitoring data acquisition service unit can also serve as a producer of kafka data, the acquired data is put into the kafka to be used for a plurality of times conveniently, and the monitoring data acquisition service unit comprises a kafka data consumer and has the main function of performing data persistence on the acquired data and storing the data into the database.
The monitoring data acquisition service unit supports various database type monitoring, and currently supports Oracle, DB2, mySQL, SQL Server, HANA, postgreSQL, mongoDB, redis, shinDB, oceanBase, openGauss, DAMENG, tiDB, GBase and the like.
The sources of the data acquisition items in the monitoring data acquisition service unit can be divided into the following two types:
1) Built-in acquisition item, namely built-in monitoring item of operation and maintenance platform, and built-in system.
2) The user can add the new monitoring item by user definition, necessary SQL information and the like are needed to be provided, and the visual page is simply configured.
(4) Monitoring data access service unit
The monitoring data access service unit is mainly designed for a chart displayed on a front-end page. If the chart type of the front page belongs to the standard chart type, setting a data source in the monitoring data access service directly, inquiring results from the data source to a local library or calling SQL execution service to a target library, wherein the supported data source comprises DB2, oracle, mySQL, ES, hadoop and the like, and forming a chart to be displayed at a designated position of a designated page through configuration.
3. Front desk service module
These services are truly available to platform users, providing specific database operation and maintenance management functions, and the operation and maintenance management requirements are implemented in different foreground service modules according to different classifications.
(1) Resource management unit
The resource management unit is used for managing the resources of the database, and mainly manages the database in the current system, and has some basic overview display on the state, architecture, load, capacity and the like of the database. The method can jump to a database single-library page, a logic library page of a logic library, a topological graph page of a framework, a performance page and a capacity page. And providing quick operation functions such as SQL execution, basic operation and maintenance, report forms and the like.
(2) Performance management unit
The performance management unit is mainly used for managing the performance of the database, and comprises performance index monitoring, performance analysis, performance diagnosis, performance trend analysis and the like, and the performance of the database is integrally grasped from overall condition performance scoring to specific index item analysis.
(3) Capacity management unit
The capacity management unit is mainly used for capacity management of the database and comprises the steps of analyzing the use size of the capacity space, the total size of the capacity space, the capacity increase proportion and the capacity increase amplitude from different angles of a physical library and a logical library, and analyzing database objects such as a large table, a hot table, an index-free table and the like.
(4) Basic operation and maintenance unit
The basic operation and maintenance unit provides example start-stop, master-slave switching, parameter modification, backup, user establishment, user authorization (white list opening) and export backup of the logic library for the database.
(5) Metadata management unit
The metadata management unit provides a distributed database physical library table distribution condition query function, a physical table structure comparison function and a physical library information query. The operation and maintenance personnel can visually inquire the overall condition of the current distributed database through the module, view the basic information of the data object of the physical database, and the metadata information is the real-time inquiry result of the access target database.
(6) Problem management unit
By setting up standards of some problem items, after comparing and judging with the standards, the problem management unit finds out the state in the database (middleware, buffering, etc.) which is in a unreasonable or needs to be optimized, and sets the state as a problem; after the problem is generated, tracking and recording the attribution and the status of the problem, namely whether to process, how to process, whether to shut down the knowledge and the method applied in the process, and continuously tracking the whole life cycle of the problem. Knowledge of the manner and application of problem resolution, as knowledge assets are continually accumulated and maintained, may also be used for learning and tracking. In the process of processing the problems, the communication between the developer and the operation and maintenance personnel is enhanced, and the communication cost is reduced.
(7) Subscriber management unit
The user management unit can realize user configuration, role configuration and operation record.
1) User configuration, namely adding, deleting and checking the user, configuring the roles of the user, and configuring the viewable database.
2) And configuring the roles, namely performing the operations of adding, deleting and checking the roles, configuring the rights possessed by the role locks, and configuring the viewable database.
3) The operation record has only a viewing function.
(8) Configuration management unit
The configuration management unit may implement:
1) And (3) configuring a physical library, namely selecting a monitored database and providing a modification function.
2) Template configuration, which is a function of simplifying physical library configuration. Configuration information of the physical library can be modified in batches, and user operation is facilitated.
3) The system configuration is used for configuring some system information, namely sending end information for sending short message information, sending end information for sending mail information and SNMP information.
4) And monitoring configuration, namely checking the current database and the monitoring item state and recovering the normal state. Built-in monitoring items can be checked, and custom monitoring items can be added, modified and set.
In the performance management unit, the platform may comprehensively score the primary type of database performance, including DB2, oracle, and MySQL in particular.
Taking the DB2 database as an example, the performance score comprehensively considers the following 8 factors:
1) SQL response time is the most intuitive index for evaluating the performance of a database system, and almost all performance problems are reflected on SQL response time. Typically in an OLTP database, the average response time of SQL should be on the order of milliseconds, so 10ms is set to 60 minutes and grid lines in our evaluation system. This index is significant, we set its weight to 20%.
2) Effective READ proportion-effective READ proportion, also called RS proportion (ROWS_READ and ROWS_RETURNED), should actually include ROWS_UPDATED and ROWS_DELETE), which reflects the health of the index in the database. Ideally, the effective read ratio should be 1, i.e., the database engine only reads the data that the application needs to return, with little waste. The low effective read rate means that the database wastes a large amount of CPU to scan additional data, while potentially affecting the buffer pool hit rate and increasing the number of physical reads. The effective read ratio is a critical performance evaluation index, and we set the weight to 20%.
3) Latency ratio-latency, i.e., the ratio of times that a database server waits, including IO wait, lock wait, LATCH wait, etc., reflects the time that a database spends due to some bottleneck. Ideally the latency ratio should be 0 and a large index means that this database presents a serious bottleneck. Latency duty cycle is also a very important indicator of the performance of the reaction database, which we set to 20%.
4) Buffer Chi Mingzhong rate for OLTP database systems, the buffer pool can greatly reduce the number of physical reads, relieve the I/O pressure of the database server, and is particularly important for transaction systems with high read/write ratios. The low hit rate of the buffer pool may be due to a low effective read ratio, or the buffer pool itself may be set too small. In practice we find that even though the buffer pool hit rate of an SQL statement reaches 95%, the time spent by the remaining 5% of physical reads can account for more than 90% of the total execution time of the SQL. The buffer Chi Mingzhong rate had a more significant impact on database performance, we set its weight to 10%.
5) Average number of operations for OLTP databases, the ideal average number of operations should be 1, i.e., one SQL statement is used to operate on only one piece of data. Too high means that one SQL can operate multiple pieces of data at the same time, which may cause problems of execution efficiency and concurrency; too low indicates that a significant portion of the SQL statement is performing no operation. The average number of rows of operations reflects the application design level, which we set to 10% weight.
6) The Package Cache hit rate is that the Package Cache in the DB2 database is used for storing the execution plan after SQL statement compiling, and a large number of dynamic statements in the Package Cache low-specification database do not use a variable binding mode when executing, which means that the same SQL statement needs to be recompiled each time executing. In a highly concurrent transaction system, the Package Cache also has some impact on performance, we set its weight to 10%.
7) Catalog Cache hit ratio the Catalog Cache is the memory area of the DB2 database used to Cache the data dictionary information. A low hit rate of the Cache affects performance, but the probability of occurrence is low, we set its weight to 5%.
8) Ordering overflow rate-ordering overflow means that there is insufficient memory to complete the ordering actions required by the SQL statement, which can bring additional Merge actions and disk IOs, thereby affecting the SQL performance of the request ordering. Ordering overflow affects local SQL statement performance, and we set its weight to 5%.
Fig. 2 shows the calculation formulas and static weights of the respective factors, where v represents the variable values of the respective factors, a and b are coefficients, and reference values are given, log10 () represents a base 10 logarithmic function, abs () represents an absolute value function, and pow () represents a function of a power operation. The table above gives both the variable values for each factor and the corresponding score values. In fact, because the value of each factor can change dynamically, the dynamic change can reflect whether the state of each factor is stable or not. Therefore, on the basis of the static weight, the dynamic weight is additionally considered, so that the performance condition of the database can be accurately reflected.
The above factors are expressed as e i Wherein i is more than or equal to 1 and less than or equal to 8, and the corresponding score is v i The corresponding static weight is denoted s i . Factor e i The last K historical values of (2) are recorded as e ij J is more than or equal to 1 and less than or equal to K, wherein K is a natural number greater than 1. From this, e i The historical mean value of (2) is:
factor e is expressed by standard deviation i Stable conditions of (2), namely:
obtaining stability M of each factor i Thereafter, M is i Normalization, calculating factor e i Is a dynamic weight of:
by combining the above analysis, factor e can be obtained i The corresponding comprehensive weight:
w i ＝λs i +(1-λ)d i ,
where λ is a real number within the interval [0,1 ].
Finally, the comprehensive performance score of the DB2 database can be obtained as follows:
besides being applied to a DB2 database, the method can also be applied to databases such as Oracle, mySQL and the like, and proper performance parameters are set according to the characteristics of the database in actual use.
Claims (1)
1. The system comprises a basic service module, a background service module and a foreground service module, wherein the basic service module provides basic operation and maintenance functions of service registration, configuration, routing, tracking and monitoring for other modules, the background service module is used as a background service providing interface for the foreground service module, the foreground service module is used by platform users and provides a database operation and maintenance management function, and operation and maintenance management requirements are realized in different foreground service units according to different classifications, and the system is characterized in that:
the basic service module comprises a service registration center, a configuration center, a micro service gateway, a service tracking unit and a service monitoring unit;
(1) A service registry, in which all programs for providing services need to be registered and can be found and called by other service modules;
(2) A configuration center, which provides global configuration for all micro services, and other micro services can use the global configuration as a configuration client;
(3) The micro-service gateway is arranged between the client and the server;
(4) The service tracking unit is used for visually displaying the calling relation between the micro services and realizing asynchronous distributed tracking;
(5) The service monitoring unit is responsible for sampling and storing data and displaying data;
the background service module comprises a CMDB service unit, an SQL execution service unit, a monitoring data acquisition service unit and a monitoring data access service unit, wherein
(1) The CMDB service unit provides the integration of the display of the CMDB data and the distributed database and provides the CMDB information required by other service modules;
(2) The SQL execution service unit comprises the following functions:
1) A single SQL executing function is provided to realize SQL inquiry of the target library and return results;
2) A multi-SQL execution function, which provides a single target DB with a plurality of SQL execution functions, and returns a result set to a caller after all SQL is executed;
3) A multi-SQL batch execution function for executing a plurality of SQL to a single target DB and recording an execution result;
4) Acquiring a DBversion function, and providing an interface to acquire a version of a target DB;
5) Acquiring a field type obtained by single SQL execution, executing SQL according to provided information to a target DB, acquiring a result, recording the types of all fields, and returning the result to a caller;
6) Providing a switch for access control, and executing a management and control function by the database SQL;
(3) The monitoring data acquisition service unit periodically performs data acquisition on the monitored database, sends SQL (structured query language) to be executed for acquiring data to the SQL execution service to be executed on the target library, returns a result set to the monitoring data acquisition service, and simultaneously takes the monitoring data acquisition service as a producer of kafka data, places the acquired data into the kafka and simultaneously comprises a kafka data consumer;
(4) The monitoring data access service unit displays a chart of the front-end page;
the foreground service module comprises a resource management unit, a performance management unit, a basic operation and maintenance unit, a data management unit, a metadata management unit, a problem management unit, a user and authority management unit and a configuration management unit, wherein the module is used by a platform user;
(1) The resource management unit displays the state, architecture, load and capacity of the database and can jump to a single database page, a logic database page, a topological graph page, a performance page and a capacity page of the database;
(2) The performance management unit performs performance management on performance index monitoring, performance analysis, performance diagnosis and performance trend analysis, and scores the overall situation performance;
(3) The basic operation and maintenance unit provides example start-stop, master-slave switching, parameter modification, backup, user establishment, user authorization (white list opening) and export backup of the logic library for the database;
(4) The data management unit analyzes the capacity space usage size, the total capacity space size, the capacity increment proportion, the capacity increment amplitude, the large table, the hot table and the index-free table from the physical library and the logical library;
(5) The metadata management unit provides a distributed database physical library table distribution condition query function, a physical table structure comparison function and a physical library information query;
(6) The problem management unit establishes a problem item standard, and after comparing the problem item standard with the standard, the problem management unit finds out the unreasonable or optimized state in the database and sets the unreasonable or optimized state as a problem; after the problem is generated, tracking and recording the attribution and the status of the problem, and continuously tracking the whole life cycle of the problem;
(7) The user and authority management unit comprises a user configuration subunit, a role configuration subunit and an operation record subunit, wherein the user configuration subunit realizes the addition, deletion and verification operation of the user, can configure the role of the user and configures a viewable database; the character configuration subunit realizes the operations of adding, deleting and checking characters, can configure the authority possessed by the characters and configures a viewable database; the operation record subunit realizes a checking function;
(8) The configuration management unit comprises a physical library configuration subunit, a template configuration subunit, a system configuration subunit and a monitoring configuration subunit, wherein the physical library configuration subunit selects a monitored database and provides a modification function; the template configuration subunit modifies configuration information of the physical library in batches; the system configuration subunit configures system information; the monitoring configuration subunit checks the current database and the monitoring item state and restores the normal state;
comprehensively scoring the performance of a DB2 database in a performance management unit of a foreground service module, wherein the performance factors of the DB2 database are SQL response time, effective reading proportion, waiting time duty ratio, buffer pool hit rate, average operation line number, package Cache hit rate, catalyst Cache hit rate and sorting overflow rate, and the performance factors are expressed as e i Wherein i is more than or equal to 1 and less than or equal to 8, and the corresponding score is v i The corresponding static weight is s i The method comprises the steps of carrying out a first treatment on the surface of the Factor e i The last K historical values of (2) are recorded as e ij J is more than or equal to 1 and less than or equal to K, K is a natural number more than 1, e i Is the historical mean value of (1)
e i Standard deviation M for stability of (2) i The representation is made of a combination of a first and a second color,
m for obtaining various performance factors i Thereafter, M is i Normalization, calculate e i Dynamic weight d of (2) i ,
e i Corresponding comprehensive weight w i Is that
w i ＝λs i +(1-λ)d i ，
Wherein λ is a real number within the interval [0,1 ];
finally, obtaining the comprehensive performance score V of the DB2 database as
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211283205.6A CN115757055B (en) | 2022-10-19 | 2022-10-19 | Database unified operation and maintenance and comprehensive performance scoring system based on Spring Cloud |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211283205.6A CN115757055B (en) | 2022-10-19 | 2022-10-19 | Database unified operation and maintenance and comprehensive performance scoring system based on Spring Cloud |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN115757055A CN115757055A (en) | 2023-03-07 |
| CN115757055B true CN115757055B (en) | 2023-10-27 |
Family
ID=85354002
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211283205.6A Active CN115757055B (en) | 2022-10-19 | 2022-10-19 | Database unified operation and maintenance and comprehensive performance scoring system based on Spring Cloud |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115757055B (en) |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR20040027270A (en) * | 2003-03-22 | 2004-04-01 | (주)오알솔루션즈코리아 | Method for monitoring database system |
| US7984073B1 (en) * | 2004-07-09 | 2011-07-19 | Crossroads Systems, Inc. | System and method for providing service management in a distributed database system |
| CN105843904A (en) * | 2016-03-23 | 2016-08-10 | 江苏太湖云计算信息技术股份有限公司 | Monitoring alarm system for database operation performance |
| CN108388503A (en) * | 2018-02-13 | 2018-08-10 | 中体彩科技发展有限公司 | Data-base performance monitoring method, system, equipment and computer readable storage medium |
| WO2018233037A1 (en) * | 2017-06-20 | 2018-12-27 | 平安科技（深圳）有限公司 | Database integration testing method, device, server, and storage medium |
| CN112527887A (en) * | 2020-12-02 | 2021-03-19 | 中国农业银行股份有限公司 | Visual operation and maintenance method and device applied to Gbase database |
| CN114625597A (en) * | 2020-12-21 | 2022-06-14 | 亚信科技（中国）有限公司 | Monitoring operation and maintenance system, method and device, electronic equipment and storage medium |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6772411B2 (en) * | 2000-12-01 | 2004-08-03 | Bmc Software, Inc. | Software performance and management system |
-
2022
- 2022-10-19 CN CN202211283205.6A patent/CN115757055B/en active Active
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR20040027270A (en) * | 2003-03-22 | 2004-04-01 | (주)오알솔루션즈코리아 | Method for monitoring database system |
| US7984073B1 (en) * | 2004-07-09 | 2011-07-19 | Crossroads Systems, Inc. | System and method for providing service management in a distributed database system |
| CN105843904A (en) * | 2016-03-23 | 2016-08-10 | 江苏太湖云计算信息技术股份有限公司 | Monitoring alarm system for database operation performance |
| WO2018233037A1 (en) * | 2017-06-20 | 2018-12-27 | 平安科技（深圳）有限公司 | Database integration testing method, device, server, and storage medium |
| CN108388503A (en) * | 2018-02-13 | 2018-08-10 | 中体彩科技发展有限公司 | Data-base performance monitoring method, system, equipment and computer readable storage medium |
| CN112527887A (en) * | 2020-12-02 | 2021-03-19 | 中国农业银行股份有限公司 | Visual operation and maintenance method and device applied to Gbase database |
| CN114625597A (en) * | 2020-12-21 | 2022-06-14 | 亚信科技（中国）有限公司 | Monitoring operation and maintenance system, method and device, electronic equipment and storage medium |
Non-Patent Citations (2)
| Title |
|---|
| 中国计算机用户协会（政务信息化分会）赵进延.数字政府 服务社会.北京：知识产权出版社,2019,第310-316页. * |
| 本社.探索与创新 13.北京：中国统计出版社,2011,第471-472页. * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN115757055A (en) | 2023-03-07 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| JP6266630B2 (en) | Managing continuous queries with archived relations | |
| CN102375731B (en) | Coding-free integrated application platform system | |
| US7761556B2 (en) | Performance monitoring within an enterprise software system | |
| US10642647B2 (en) | Concurrent queueing and control command feedback loop in unified automation platforms | |
| CN109690524A (en) | Data Serialization in distributed event processing system | |
| EP3543866A1 (en) | Resource-efficient record processing in unified automation platforms for robotic process automation | |
| CN109656963B (en) | Metadata acquisition method, apparatus, device and computer readable storage medium | |
| CN111367989B (en) | Real-time data index calculation system and method | |
| CN111782672B (en) | Multi-field data management method and related device | |
| CN115757055B (en) | Database unified operation and maintenance and comprehensive performance scoring system based on Spring Cloud | |
| CN117149873A (en) | Data lake service platform construction method based on flow batch integration | |
| US11615061B1 (en) | Evaluating workload for database migration recommendations | |
| Babu et al. | DESH: Database evaluation system with hibernate ORM framework | |
| CN116521664A (en) | Data monitoring method and device for data warehouse, computing equipment and storage medium | |
| Xu et al. | Banian: a cross-platform interactive query system for structured big data | |
| CN107590199B (en) | Memory-oriented multithreading database design method | |
| Lin et al. | Design of Distributed Timing Job Scheduling System for Data Analysis Platform | |
| Lin et al. | Design of Human Resource Salary Management System for State-owned Enterprises Based on SSH Architecture | |
| EP4152173B1 (en) | Data digital decoupling of legacy systems | |
| CN110928938B (en) | Interface middleware system | |
| Silva | Data modeling with NoSQL: How, when and why | |
| Ozawa et al. | A Hybrid Event-Processing Architecture based on the Model-driven Approach for High Performance Monitoring | |
| Taina | Problem classes in intelligent network database design | |
| CN113919167A (en) | Fine evaluation method for new energy power generation performance | |
| Miao | Application of Cloud Computing in Information Management System in the Era of Big Data |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |