CN113010495A - Database optimization method and device - Google Patents
Database optimization method and device Download PDFInfo
- Publication number
- CN113010495A CN113010495A CN202110296514.6A CN202110296514A CN113010495A CN 113010495 A CN113010495 A CN 113010495A CN 202110296514 A CN202110296514 A CN 202110296514A CN 113010495 A CN113010495 A CN 113010495A
- Authority
- CN
- China
- Prior art keywords
- database
- stage
- transaction
- monitoring data
- transactions
- 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.)
- Granted
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/217—Database tuning
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/34—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment
- G06F11/3409—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment for performance assessment
- G06F11/3428—Benchmarking
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Computer Hardware Design (AREA)
- Quality & Reliability (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The specification discloses a database optimization method and a database optimization device, monitoring codes pre-embedded in main nodes in a group replication cluster are obtained by aiming at each stage of database transactions, monitoring data of each stage are obtained when the main nodes execute the database transactions, the monitoring data of each stage are stored as a memory file according to transaction identifiers and each stage identifier of the database transactions, efficiency indexes of each stage when the main nodes execute the database transactions are counted according to the stored monitoring data of each stage of a plurality of database transactions, and the database is optimized according to the determined efficiency indexes of each stage. According to the method, the monitoring data are obtained through the monitoring codes embedded in the main node in advance according to the transaction stage, the database is optimized based on the monitoring codes, when the node fails, the position of the fault point can be accurately judged, the database is optimized according to the position of the fault point in time, and the service execution efficiency is improved.
Description
Technical Field
The present disclosure relates to the field of computer technologies, and in particular, to a database optimization method and apparatus.
Background
Currently, MySQL, an open source relational database management system, can generally execute transactions through Group Replication (MGR).
Generally, the MGR executes transactions in a cluster unit, and a server executing the transactions may be referred to as an MGR node, and an MGR cluster is formed by at least three MGR nodes. When one node in the cluster receives the request, the request is analyzed, the transaction corresponding to the request is determined, the transaction corresponding to the request is communicated with other nodes in the cluster according to the transaction corresponding to the request, when more than half of the nodes in the cluster pass the confirmation, each node carries out collision detection, and the transaction is submitted after the confirmation detection passes.
Because communication and collision detection are required between nodes of a cluster to execute a transaction, MGR is more complex than asynchronous replication and semi-synchronous replication, and nodes need to be optimally adjusted to make the cluster more efficient in executing the transaction. A common method for optimizing and adjusting an MGR node is to perform a pressure test on the node, connect the node with a client, enable the node to execute a service, obtain time consumed by the node to execute the service from the client, determine respective performance indexes of the client, a network segment, and the node, and then optimize and adjust a MySQL code in the node according to the determined performance indexes.
However, the MySQL codes in the node are optimized and adjusted only according to the determined performance indexes of the client, the network segment and the node, and the acquisition mode is too coarse, so that the node with higher transaction execution efficiency cannot be adjusted.
Based on this, the present specification provides a database optimization method.
Disclosure of Invention
The present specification provides a database optimization method and apparatus to partially solve the above problems in the prior art.
The technical scheme adopted by the specification is as follows:
the database optimization method provided by the specification comprises the following steps:
monitoring codes corresponding to all stages embedded in a group replication cluster main node are obtained in advance aiming at all stages of database transactions, and monitoring data of all stages are sequentially obtained when the main node executes the database transactions;
monitoring data of each stage is stored as a memory file according to the transaction identifier of the database transaction and the identifier of each stage;
according to the stored monitoring data of each stage of a plurality of database transactions, the efficiency indexes of each stage when the main node executes the database transactions are counted, and the database is optimized according to the determined efficiency indexes of each stage.
Optionally, according to the stored monitoring data of each stage of the plurality of database transactions, counting efficiency indexes of each stage when the master node executes the database transactions, specifically including:
acquiring monitoring data of each stage of a plurality of stored database transactions;
for each database transaction, determining a transaction log of each stage of the database transaction executed by the main node from the stored monitoring data of each stage of the database transactions according to the transaction identifier of the database transaction;
and determining the efficiency index of each stage when the main node executes the database transaction according to the transaction log of each stage when the main node executes each database transaction.
Optionally, for each database transaction, determining, according to the transaction identifier of the database transaction, a transaction log of each stage of the database transaction executed by the master node from the stored monitoring data of each stage of the database transaction, specifically including:
for each database transaction, determining the execution data of each stage of the database transaction executed by the main node from the stored monitoring data of each stage of the database transactions according to the transaction identifier of the database transaction, wherein the execution data at least comprises the stage identifier and the execution time;
and determining a transaction log of the database transaction executed by the main node according to the execution data of each stage of the database transaction executed by the main node.
Optionally, according to the transaction log of each database transaction executed by the master node, counting efficiency indexes of each stage when the master node executes the database transaction, specifically including:
aiming at each stage of database transactions, determining a transaction log of each database transaction corresponding to the stage from the transaction log of each database transaction executed by the main node according to the stage identifier of the stage;
and determining the efficiency index of the database transaction at the stage according to the determined transaction logs.
Optionally, before monitoring data according to each stage of a plurality of stored database transactions and counting efficiency indicators of each stage when the master node executes the database transactions, the method further includes:
receiving an index generation request;
and according to the received index generation request and the stored monitoring data of each stage of the plurality of database transactions, counting the efficiency index of each stage when the main node executes the database transactions.
Optionally, the method further comprises:
and storing the memory file according to a preset time interval.
Optionally, optimizing the database according to the determined efficiency indexes at each stage, specifically including:
and optimizing the database according to the determined efficiency indexes of all the stages and the preset efficiency index threshold of all the stages.
The database optimization device provided by the present specification includes:
the acquisition module is used for sequentially acquiring monitoring data of each stage when the main node executes the database transaction by aiming at each stage of the database transaction in advance and respectively corresponding monitoring codes of each stage embedded in the group replication cluster main node;
the storage module is used for storing the monitoring data of each stage as a memory file according to the transaction identifier of the database transaction and the identifier of each stage;
and the optimization module is used for counting the efficiency indexes of the main node at each stage when executing the database transaction according to the stored monitoring data of each stage of the plurality of database transactions, and optimizing the database according to the determined efficiency indexes at each stage.
The present specification provides a computer-readable storage medium storing a computer program which, when executed by a processor, implements the database optimization method described above.
The electronic device provided by the present specification comprises a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor implements the database optimization method when executing the program.
The technical scheme adopted by the specification can achieve the following beneficial effects:
in the database optimization method provided in this specification, monitoring codes pre-embedded in a master node in a group replication cluster are obtained for each stage of a database transaction, monitoring data of each stage is obtained when the master node executes the database transaction, the monitoring data of each stage is stored as a memory file according to a transaction identifier of the database transaction and each stage identifier, efficiency indexes of each stage when the master node executes the database transaction are counted according to the stored monitoring data of each stage of a plurality of database transactions, and the database is optimized according to the determined efficiency indexes of each stage.
According to the method, the monitoring data are obtained through the monitoring codes embedded in the main node in advance according to the transaction stage, the database is optimized based on the monitoring codes, when the node fails, the position of the fault point can be accurately judged, the database is optimized according to the position of the fault point in time, and the service execution efficiency is improved.
Drawings
The accompanying drawings, which are included to provide a further understanding of the specification and are incorporated in and constitute a part of this specification, illustrate embodiments of the specification and together with the description serve to explain the specification and not to limit the specification in a non-limiting sense. In the drawings:
FIG. 1 is a schematic flow diagram of a database optimization method provided herein;
FIG. 2 is a schematic diagram of the location of each monitoring code in a master node as provided herein;
FIG. 3 is a schematic diagram of a database optimization apparatus provided herein;
fig. 4 is a schematic diagram of an electronic device corresponding to fig. 1 provided in the present specification.
Detailed Description
In order to make the objects, technical solutions and advantages of the present disclosure more clear, the technical solutions of the present disclosure will be clearly and completely described below with reference to the specific embodiments of the present disclosure and the accompanying drawings. It is to be understood that the embodiments described are only a few embodiments of the present disclosure, and not all embodiments. All other embodiments obtained by a person of ordinary skill in the art based on the embodiments in the present specification without any creative effort belong to the protection scope of the present specification.
Generally, when a MGR node is subjected to a stress test, a situation that a performance index of a node side is low may occur, and at this time, it may be determined that the MGR node has a fault and needs to be adjusted. When the performance index of the node end has a problem, the following conditions may be included: the network interaction between the client and the node fluctuates, a disk of a physical device operated by the node fails, and a code program of the MySQL is in error. At this time, only knowing that the performance index of the node end has a problem, it is difficult to determine the cause of the problem of the performance index, and it is necessary to gradually investigate the cause.
The technical solutions provided by the embodiments of the present description are described in detail below with reference to the accompanying drawings.
Fig. 1 is a schematic flow chart of a database optimization method provided in this specification, specifically including the following steps:
s100: monitoring data of each stage when the main node executes the database transaction are sequentially acquired by pre-aiming at each stage of the database transaction and respectively corresponding monitoring codes of each stage embedded in a group replication cluster main node.
In one or more embodiments provided in this specification, the method for database optimization is directed to a scenario in which a node in an MGR cluster receives a transaction processing request and processes a transaction corresponding to the transaction processing request, and a flow of the method for database optimization may be specifically executed by a server of an MGR user, and then a method for the server to execute the database optimization is taken as an example for description.
Generally, when a MGR user executes a transaction through the MGR cluster, each node in the MGR cluster needs to perform communication and collision detection to execute the transaction, and therefore, the MGR has more stages of transaction execution and higher complexity than the conventional asynchronous replication and semi-synchronous replication. If the MGR cluster has too long time to process the transaction corresponding to the transaction processing request, it may be considered that the MGR cluster is not matched with the transaction, and therefore, in order to adjust the MGR cluster that is more matched with the transaction, the performance parameters of each transaction phase in the MGR host node may be obtained, and the MGR host node is optimally adjusted based on the performance parameters of each transaction phase.
Based on this, in one or more embodiments in this specification, monitoring codes corresponding to each phase may be pre-embedded in the MGR master node according to each phase of the database transaction.
Specifically, for each database transaction, the process of the master node executing the database transaction may generally include: the method comprises five parts, namely an analysis phase, a negotiation phase, a conflict detection phase, a log playback phase and a transaction submission phase, wherein the analysis phase is a process that a main node receives a transaction processing request and analyzes the transaction processing request based on an SQL engine. The negotiation phase refers to a process in which the master node communicates with other nodes in the MGR cluster based on a consistency (Paxos) protocol according to the transaction corresponding to the transaction processing request. In the conflict detection stage, in order to avoid that the nodes in the MGR cluster execute different transactions at the same time, and thus data consistency cannot be guaranteed, the process of performing conflict detection on the transactions executed by the nodes is adopted. The log playback phase refers to a process of transferring the log stored in the memory to a disk.
In one or more embodiments provided herein, the stages of the master node performing a database transaction may generally be implemented by calling different code modules. For example, when a transaction request is received, a code module with an analysis function is called first to analyze the database transaction, and after the transaction request is analyzed, a code module with a negotiation function is called again to negotiate according to the transaction corresponding to the transaction request. And taking the part called between the code modules corresponding to each stage as an interface, and acquiring the monitoring data of each stage of the database transaction by acquiring the code running time of each interface.
Therefore, according to each phase of the database transaction executed by the master node, monitoring codes corresponding to each phase can be embedded in the master node in advance, so as to obtain the execution time of each phase when the master node executes the database transaction. As shown in fig. 2.
Fig. 2 is a schematic diagram of monitoring data at each stage when the master node executes a database transaction, where the monitoring data are obtained between the master node and a client, between code modules corresponding to each stage in the master node, and at a position where execution of a code module corresponding to a transaction commit is completed, the monitoring data can be used as interfaces for the master node to execute the database transaction. Therefore, the server pre-embeds monitoring codes at each interface, and in the process of executing the database transaction by the main node, the monitoring data of the database transaction executed by the main node can be determined according to the pre-embedded monitoring codes at each node. The monitoring data is the execution time of the main node executing each stage of the database transaction, and can be determined by the code running time of the front and back interfaces of the code module corresponding to each stage. For example, consider the front-end interface of the conflict monitoring phase as point a and the back-end interface of the conflict monitoring phase as point B. The execution time of the conflict detection phase may be determined by subtracting the code runtime of point a from the code runtime of point B.
S102: and storing the monitoring data of each stage as a memory file according to the transaction identifier of the database transaction and the identifier of each stage.
In one or more embodiments provided in this specification, after the server obtains the monitoring data of each stage when the host node executes the database transaction, the server may further store the monitoring data of each stage as a memory file according to the transaction identifier of the database transaction and the identifier of each stage.
Specifically, the execution time of the master node executing each phase of the database transaction is determined by the database transaction executed by the database and the execution phase of the database transaction. For example, the execution time of each phase when the master node executes the database transaction a is not completely the same as the execution time of each phase when the master node executes the database transaction B, and the execution time of the parsing phase is not the same as the execution time of the negotiation phase when the master node executes the database transaction a. The server may then store the monitoring data for each phase based on the transaction identifier and the phase identifiers for the database transaction.
In one or more embodiments provided in this specification, the data stored in the database is in a non-volatile memory (e.g., a disk), and when the master node executes a database transaction, if the acquired monitoring data is stored in the disk, operations on the disk are increased, threads are occupied, and thus the execution efficiency of the database transaction is reduced. Therefore, in order to avoid the influence on the execution efficiency of the database transaction, when the monitoring data is acquired and stored, the monitoring data can be stored as a memory file. The memory file in this specification refers to a file that is stored in a volatile memory (e.g., a memory) and does not affect the operation of a database on a nonvolatile memory (e.g., a disk). For example, the monitoring data is stored in volatile memory as a temporary file system (tmpfs) file. Volatile memories generally include Static Random Access Memories (SRAMs), Dynamic Random Access Memories (DRAMs), and the like.
Further, the memory of the server is limited, and when a large number of memory files exist in the memory, the efficiency of the primary node executing the database transaction may be affected. Therefore, in order not to affect the execution efficiency of the database transaction, the server may further store the generated memory file in the disk according to a preset storage condition. The storage condition may be to store the memory file according to a preset time interval, or to store the monitoring data of each stage corresponding to the database transaction after the database transaction is executed. A pressure threshold may also be set, and when the database execution pressure is less than the pressure threshold (e.g., queued transactions are less than 5), a transaction is created that adds a system file write to disk. Of course, the specific storage condition can be set according to the requirement, and the specification does not limit this.
S104: according to the stored monitoring data of each stage of a plurality of database transactions, the efficiency indexes of each stage when the main node executes the database transactions are counted, and the database is optimized according to the determined efficiency indexes of each stage.
In one or more embodiments provided in this specification, to ensure the accuracy of the determined efficiency index, after the obtained monitoring data of each stage is stored, the server may obtain the stored monitoring data of each stage of the plurality of database transactions, and according to the stored monitoring data of each stage of the plurality of database transactions, count the efficiency index of each stage when the host node executes the database transaction, and optimize the database according to the determined efficiency index of each stage.
Specifically, first, the server may obtain monitoring data for each phase of a number of stored database transactions.
Secondly, the monitoring data is stored in a file form, the file processing mode is complex, in order to improve the processing efficiency, the server can convert the acquired monitoring data into a log form to be used as a transaction log of each stage of the database transaction, and when the efficiency index of each stage of the database transaction needs to be determined subsequently, the determination is carried out based on the transaction log. Then, for each database transaction, the server may determine, from the stored monitoring data of each phase of the database transactions, a transaction log of each phase of the database transaction executed by the master node according to the transaction identifier of the database transaction.
And then, according to the transaction log of each stage of each database transaction executed by the main node, determining the efficiency index of each stage when the main node executes the database transaction. And finally, after determining the efficiency indexes of each stage when the main node executes the database transaction, the server can optimize the database according to the determined efficiency indexes of each stage.
Further, the efficiency index in this specification may be determined by the execution time of the phase in which the master node executes the database transaction, and therefore, in order to improve the efficiency of generating the efficiency index, the content of the transaction log may include: the phase identification of each phase, the starting time of the phase and the ending time of the phase.
The transaction log may also be formatted as shown in table 1.
| [ date of day]: [ name of buried point]: [ Start time]: [ date of day] |
| [ date of day]: [ name of buried point]: [ end time]: [ date of day] |
| [ date of day]: [ name of buried point]：[cost]: [ duration of time] |
TABLE 1
Thus, when determining the transaction log of each phase of each database transaction, for each database transaction, the server may determine, according to the transaction identifier of the database transaction, execution data for executing each phase of the database transaction by the master node from the stored monitoring data of each phase of the database transactions, where the execution data includes at least a phase identifier and an execution time. After determining the execution data of the database transaction, the server may determine, according to the determined execution data, a transaction log in which the master node executes the database transaction. In the format of each log in table 1, the first [ date ] part may be a prefix for locating a date, the [ buried name ] may be a stage identifier of each stage, the [ start time ] and [ end time ] parts are used with the following [ date ] part to determine a specific start time and a specific end time of a stage, and an execution time, i.e., a duration, of a stage may be determined according to the specific start time and the specific end time of the stage.
In one or more embodiments provided herein, as previously described, the efficiency index for each stage in the present description may be determined based on the execution time of each stage when the master node performs the database transaction. The efficiency index of each stage may be a Top percentage index of each stage, i.e., TP index, or an average index, median index, etc., taking TP90 as an example, TP90 may be regarded as the minimum time consumption for ensuring that 90% of requests can be responded. Thus, for each stage of the database transaction, the server may determine, from the transaction logs of the database transactions executed by the master node according to the stage identifier of the stage, the transaction log of the stage corresponding to each database transaction, and determine the efficiency index of the stage of the database transaction according to each determined transaction log. For example, the total execution time of the database transaction is denoted by tt, the execution time of the analysis phase is denoted by te, the execution time of the negotiation phase is denoted by tp, the execution time of the conflict detection phase is denoted by tc, and the execution time of the log playback phase is denoted by tf. Then according to tt, te, tp, tc, tf of each database transaction, the efficiency index of the database transaction and the efficiency index of each stage can be determined.
Furthermore, before the service is executed through the MGR, the MGR user may still execute the service by MySQL, and the adopted means may be asynchronous replication or semi-synchronous replication. Therefore, for each phase of the database transaction executed by the master node, the server may have a preset efficiency index threshold value of each phase according to the execution efficiency of each phase when the transaction is executed by MySQL in the past. Therefore, the server can optimize the database according to the determined efficiency indexes of all the stages and the preset efficiency index threshold value. For example, the adjustment is performed at a stage below the efficiency index threshold, and the adjustment is not performed at a stage above the efficiency index threshold.
It should be noted that the method for determining the efficiency index threshold may be determined manually according to experience, and the method for optimizing the database according to the determined efficiency index of each stage does not only pass through the efficiency index threshold of each stage, and the specific method for determining the efficiency index threshold and optimizing the database according to the efficiency index of each stage may be set as needed, which is not limited in this specification.
Based on the database optimization method provided in fig. 1, monitoring codes pre-embedded in the master node in the group replication cluster are obtained for each stage of the database transaction, monitoring data of each stage is obtained when the master node executes the database transaction, the monitoring data of each stage is stored as a memory file according to the transaction identifier of the database transaction and each stage identifier, efficiency indexes of each stage when the master node executes the database transaction are counted according to the stored monitoring data of each stage of a plurality of database transactions, and the database is optimized according to the determined efficiency indexes of each stage. According to the method, the monitoring data are obtained through the monitoring codes embedded in the main node in advance according to the transaction stage, the database is optimized based on the monitoring codes, when the node fails, the position of the fault point can be accurately judged, the database is optimized according to the position of the fault point in time, and the service execution efficiency is improved.
In addition, in the prior art, when the master node is subjected to pressure testing, the database also needs to print debugging logs according to the requirements of users, but too many debugging logs printed by the database occupy more resources, so that the pressure testing result is distorted. The method stores the monitoring data in the memory as the memory file without occupying a disk, thereby not influencing the efficiency of the main node for executing the database transaction.
In one or more embodiments provided in this specification, in order to ensure the accuracy of the determined performance index of the master node in each stage of executing the database transaction, in step S104, the server may perform statistics on the efficiency index of the master node in each stage when executing the database transaction according to the transaction log of each database transaction executed by the master node.
Specifically, for each stage of the database transaction, according to the stage identifier of the stage, determining a transaction log of the stage where the master node executes each database transaction from the transaction logs of the stage where the master node executes each database transaction, and according to the transaction log of the stage where the master node executes each database transaction, determining an efficiency index of the stage of the database transaction.
Further, in one or more embodiments provided herein, if there are several stages of monitoring data of database transactions in the server and the master node is executing the database transactions, it is obviously not appropriate to process the monitoring data. Therefore, the server can also receive the index generation request, and then the server can calculate the efficiency index of each phase when the main node executes the database transaction according to the received index generation request and the stored monitoring data of each phase of the database transactions.
It should be noted that, when performing a transaction through the MGR cluster, there are a single master mode and a multi-master mode, where a master node in the single master mode is a fixed node, and a master node in the multi-master mode may be each master node in the cluster. Therefore, in the multi-master mode, each node can apply the database optimization method in the specification to determine the execution efficiency of each stage of the database transaction, and further optimize the database.
In addition, when the master node executes a database transaction, there may be a case where an error occurs in another node in the group of replication clusters in the negotiation stage, which results in a low efficiency index in the negotiation stage, at this time, it is obviously unnecessary to optimize the SQL code of the master node, and thus, when a failure point exists in the negotiation stage among the nodes, the server may further obtain the efficiency index in the negotiation stage of each node in the MGR cluster, so as to perform a troubleshooting on each node according to the obtained efficiency index in the stage, and further determine a specific location of the failure point, such as a disk, an SQL code, a network, and the like.
Based on the same idea, the database optimization method provided above for one or more embodiments of the present specification further provides a corresponding database optimization device, as shown in fig. 3.
Fig. 3 is a schematic diagram of a database optimization apparatus provided in this specification, including:
an obtaining module 200, configured to sequentially obtain, by referring to each stage of a database transaction in advance, monitoring data of each stage when the host node executes the database transaction, by using monitoring codes corresponding to each stage embedded in the group replication cluster host node.
And the storage module 202 is configured to store the monitoring data of each stage as a memory file according to the transaction identifier of the database transaction and the identifier of each stage.
And the optimizing module 204 is configured to count efficiency indexes of the master node at each stage when executing the database transaction according to the stored monitoring data of each stage of the plurality of database transactions, and optimize the database according to the determined efficiency indexes at each stage.
Optionally, the optimization module 204 is specifically configured to obtain stored monitoring data of each stage of a plurality of database transactions, determine, for each stage of a database transaction, a transaction log of the stage corresponding to each database transaction from transaction logs of each database transaction executed by the master node according to the stage identifier of the stage, and determine an efficiency index of the stage of the database transaction according to each determined transaction log.
Optionally, the optimization module 204 is specifically configured to, for each database transaction, determine, according to a transaction identifier of the database transaction, execution data of each phase in which the master node executes the database transaction from stored monitoring data of each phase in a plurality of database transactions, where the execution data at least includes the phase identifier and execution time, and determine, according to the execution data of each phase in which the master node executes the database transaction, a transaction log in which the master node executes the database transaction.
Optionally, the optimization module 204 is specifically configured to, for each phase of the database transaction, determine, from the transaction logs of the phase in which the master node executes the database transactions according to the phase identifier of the phase, a transaction log of the phase in which the master node executes the database transactions, and determine, according to the transaction log of the phase in which the master node executes the database transactions, an efficiency index of the phase in which the database transaction is executed.
Optionally, the optimization module 204 is further configured to receive an index generation request, generate the request according to the received index, and count the efficiency index of each stage when the master node executes the database transaction according to the stored monitoring data of each stage of the plurality of database transactions.
Optionally, the storage module 202 is specifically configured to store the memory file according to a preset time interval.
Optionally, the optimizing module 204 is specifically configured to optimize the database according to the determined efficiency index of each stage and a preset efficiency index threshold of each stage.
The present specification also provides a computer-readable storage medium having stored thereon a computer program operable to execute the database optimization method provided in fig. 1 above.
This specification also provides a schematic block diagram of the electronic device shown in fig. 4. As shown in fig. 4, at the hardware level, the electronic device includes a processor, an internal bus, a network interface, a memory, and a non-volatile memory, and may also include hardware required for other services. The processor reads the corresponding computer program from the non-volatile memory into the memory and then runs the computer program to implement the database optimization method described in fig. 1. Of course, besides the software implementation, the present specification does not exclude other implementations, such as logic devices or a combination of software and hardware, and the like, that is, the execution subject of the following processing flow is not limited to each logic unit, and may be hardware or logic devices.
In the 90 s of the 20 th century, improvements in a technology could clearly distinguish between improvements in hardware (e.g., improvements in circuit structures such as diodes, transistors, switches, etc.) and improvements in software (improvements in process flow). However, as technology advances, many of today's process flow improvements have been seen as direct improvements in hardware circuit architecture. Designers almost always obtain the corresponding hardware circuit structure by programming an improved method flow into the hardware circuit. Thus, it cannot be said that an improvement in the process flow cannot be realized by hardware physical modules. For example, a Programmable Logic Device (PLD), such as a Field Programmable Gate Array (FPGA), is an integrated circuit whose Logic functions are determined by programming the Device by a user. A digital system is "integrated" on a PLD by the designer's own programming without requiring the chip manufacturer to design and fabricate application-specific integrated circuit chips. Furthermore, nowadays, instead of manually making an Integrated Circuit chip, such Programming is often implemented by "logic compiler" software, which is similar to a software compiler used in program development and writing, but the original code before compiling is also written by a specific Programming Language, which is called Hardware Description Language (HDL), and HDL is not only one but many, such as abel (advanced Boolean Expression Language), ahdl (alternate Hardware Description Language), traffic, pl (core universal Programming Language), HDCal (jhdware Description Language), lang, Lola, HDL, laspam, hardward Description Language (vhr Description Language), vhal (Hardware Description Language), and vhigh-Language, which are currently used in most common. It will also be apparent to those skilled in the art that hardware circuitry that implements the logical method flows can be readily obtained by merely slightly programming the method flows into an integrated circuit using the hardware description languages described above.
The controller may be implemented in any suitable manner, for example, the controller may take the form of, for example, a microprocessor or processor and a computer-readable medium storing computer-readable program code (e.g., software or firmware) executable by the (micro) processor, logic gates, switches, an Application Specific Integrated Circuit (ASIC), a programmable logic controller, and an embedded microcontroller, examples of which include, but are not limited to, the following microcontrollers: ARC 625D, Atmel AT91SAM, Microchip PIC18F26K20, and Silicone Labs C8051F320, the memory controller may also be implemented as part of the control logic for the memory. Those skilled in the art will also appreciate that, in addition to implementing the controller as pure computer readable program code, the same functionality can be implemented by logically programming method steps such that the controller is in the form of logic gates, switches, application specific integrated circuits, programmable logic controllers, embedded microcontrollers and the like. Such a controller may thus be considered a hardware component, and the means included therein for performing the various functions may also be considered as a structure within the hardware component. Or even means for performing the functions may be regarded as being both a software module for performing the method and a structure within a hardware component.
The systems, devices, modules or units illustrated in the above embodiments may be implemented by a computer chip or an entity, or by a product with certain functions. One typical implementation device is a computer. In particular, the computer may be, for example, a personal computer, a laptop computer, a cellular telephone, a camera phone, a smartphone, a personal digital assistant, a media player, a navigation device, an email device, a game console, a tablet computer, a wearable device, or a combination of any of these devices.
For convenience of description, the above devices are described as being divided into various units by function, and are described separately. Of course, the functions of the various elements may be implemented in the same one or more software and/or hardware implementations of the present description.
As will be appreciated by one skilled in the art, embodiments of the present invention may be provided as a method, system, or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
In a typical configuration, a computing device includes one or more processors (CPUs), input/output interfaces, network interfaces, and memory.
The memory may include forms of volatile memory in a computer readable medium, Random Access Memory (RAM) and/or non-volatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). Memory is an example of a computer-readable medium.
Computer-readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), Read Only Memory (ROM), Electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), Digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information that can be accessed by a computing device. As defined herein, a computer readable medium does not include a transitory computer readable medium such as a modulated data signal and a carrier wave.
It should also be noted that the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.
As will be appreciated by one skilled in the art, embodiments of the present description may be provided as a method, system, or computer program product. Accordingly, the description may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the description may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
This description may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular abstract data types. The specification may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in both local and remote computer storage media including memory storage devices.
The embodiments in the present specification are described in a progressive manner, and the same and similar parts among the embodiments are referred to each other, and each embodiment focuses on the differences from the other embodiments. In particular, for the system embodiment, since it is substantially similar to the method embodiment, the description is simple, and for the relevant points, reference may be made to the partial description of the method embodiment.
The above description is only an example of the present specification, and is not intended to limit the present specification. Various modifications and alterations to this description will become apparent to those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present specification should be included in the scope of the claims of the present specification.
Claims (10)
1. A method of database optimization, comprising:
monitoring codes corresponding to all stages embedded in a group replication cluster main node are obtained in advance aiming at all stages of database transactions, and monitoring data of all stages are sequentially obtained when the main node executes the database transactions;
monitoring data of each stage is stored as a memory file according to the transaction identifier of the database transaction and the identifier of each stage;
according to the stored monitoring data of each stage of a plurality of database transactions, the efficiency indexes of each stage when the main node executes the database transactions are counted, and the database is optimized according to the determined efficiency indexes of each stage.
2. The method of claim 1, wherein the counting the efficiency index of the master node at each stage when executing the database transaction according to the stored monitoring data at each stage of the database transactions comprises:
acquiring monitoring data of each stage of a plurality of stored database transactions;
for each database transaction, determining a transaction log of each stage of the database transaction executed by the main node from the stored monitoring data of each stage of the database transactions according to the transaction identifier of the database transaction;
and determining the efficiency index of each stage when the main node executes the database transaction according to the transaction log of each stage when the main node executes each database transaction.
3. The method of claim 2, wherein for each database transaction, determining, from the stored monitoring data of each of the plurality of database transactions, a transaction log of each of the database transactions executed by the master node according to the transaction identifier of the database transaction comprises:
for each database transaction, determining the execution data of each stage of the database transaction executed by the main node from the stored monitoring data of each stage of the database transactions according to the transaction identifier of the database transaction, wherein the execution data at least comprises the stage identifier and the execution time;
and determining a transaction log of the database transaction executed by the main node according to the execution data of each stage of the database transaction executed by the main node.
4. The method of claim 2, wherein the counting the efficiency index of the master node at each stage when executing the database transaction according to the transaction log of each database transaction executed by the master node specifically comprises:
aiming at each stage of database transactions, determining a transaction log of each database transaction corresponding to the stage from the transaction log of each database transaction executed by the main node according to the stage identifier of the stage;
and determining the efficiency index of the database transaction at the stage according to the determined transaction logs.
5. The method of claim 1, wherein prior to monitoring data from each stage of a number of stored database transactions, and prior to counting efficiency metrics of each stage of the master node in executing database transactions, the method further comprises:
receiving an index generation request;
and according to the received index generation request and the stored monitoring data of each stage of the plurality of database transactions, counting the efficiency index of each stage when the main node executes the database transactions.
6. The method of claim 1, wherein the method further comprises:
and storing the memory file according to a preset time interval.
7. The method of claim 1, wherein optimizing the database according to the determined efficiency indicators at each stage comprises:
and optimizing the database according to the determined efficiency indexes of all the stages and the preset efficiency index threshold of all the stages.
8. A database optimization apparatus, comprising:
the acquisition module is used for sequentially acquiring monitoring data of each stage when the main node executes the database transaction by aiming at each stage of the database transaction in advance and respectively corresponding monitoring codes of each stage embedded in the group replication cluster main node;
the storage module is used for storing the monitoring data of each stage as a memory file according to the transaction identifier of the database transaction and the identifier of each stage;
and the optimization module is used for counting the efficiency indexes of the main node at each stage when executing the database transaction according to the stored monitoring data of each stage of the plurality of database transactions, and optimizing the database according to the determined efficiency indexes at each stage.
9. A computer-readable storage medium, characterized in that the storage medium stores a computer program which, when executed by a processor, implements the method of any of the preceding claims 1 to 7.
10. An electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor implements the method of any of claims 1 to 7 when executing the program.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110296514.6A CN113010495B (en) | 2021-03-19 | 2021-03-19 | Database optimization method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110296514.6A CN113010495B (en) | 2021-03-19 | 2021-03-19 | Database optimization method and device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN113010495A true CN113010495A (en) | 2021-06-22 |
| CN113010495B CN113010495B (en) | 2023-01-06 |
Family
ID=76403338
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110296514.6A Active CN113010495B (en) | 2021-03-19 | 2021-03-19 | Database optimization method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113010495B (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113553320A (en) * | 2021-07-29 | 2021-10-26 | 上海哔哩哔哩科技有限公司 | Data quality monitoring method and device |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106201826A (en) * | 2016-07-13 | 2016-12-07 | 焦点科技股份有限公司 | A kind of diagnose the big affairs of oracle database and the method for focus affairs |
| US20170177697A1 (en) * | 2015-12-21 | 2017-06-22 | Sap Se | Distributed database transaction protocol |
| CN109101627A (en) * | 2018-08-14 | 2018-12-28 | 交通银行股份有限公司 | heterogeneous database synchronization method and device |
| US20190147097A1 (en) * | 2017-11-15 | 2019-05-16 | International Business Machines Corporation | Data replication in a database environment |
| CN110008224A (en) * | 2019-03-08 | 2019-07-12 | 阿里巴巴集团控股有限公司 | A kind of db transaction processing method and processing device |
| CN111522631A (en) * | 2020-03-23 | 2020-08-11 | 支付宝(杭州)信息技术有限公司 | Distributed transaction processing method, device, server and medium |
| CN112491611A (en) * | 2020-11-25 | 2021-03-12 | 网银在线（北京）科技有限公司 | Fault location system, method, apparatus, electronic device and computer readable medium |
-
2021
- 2021-03-19 CN CN202110296514.6A patent/CN113010495B/en active Active
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20170177697A1 (en) * | 2015-12-21 | 2017-06-22 | Sap Se | Distributed database transaction protocol |
| CN106201826A (en) * | 2016-07-13 | 2016-12-07 | 焦点科技股份有限公司 | A kind of diagnose the big affairs of oracle database and the method for focus affairs |
| US20190147097A1 (en) * | 2017-11-15 | 2019-05-16 | International Business Machines Corporation | Data replication in a database environment |
| CN109101627A (en) * | 2018-08-14 | 2018-12-28 | 交通银行股份有限公司 | heterogeneous database synchronization method and device |
| CN110008224A (en) * | 2019-03-08 | 2019-07-12 | 阿里巴巴集团控股有限公司 | A kind of db transaction processing method and processing device |
| CN111522631A (en) * | 2020-03-23 | 2020-08-11 | 支付宝(杭州)信息技术有限公司 | Distributed transaction processing method, device, server and medium |
| CN112491611A (en) * | 2020-11-25 | 2021-03-12 | 网银在线（北京）科技有限公司 | Fault location system, method, apparatus, electronic device and computer readable medium |
Non-Patent Citations (2)
| Title |
|---|
| 卫孝贤 等: ""多主云数据库的全局事务日志"", 《华东师范大学学报 (自然科学版)》 * |
| 杨建荣: "MySQL DBA工作笔记：数据库管理、架构优化与运维开发", 《中国铁道出版社》 * |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113553320A (en) * | 2021-07-29 | 2021-10-26 | 上海哔哩哔哩科技有限公司 | Data quality monitoring method and device |
| CN113553320B (en) * | 2021-07-29 | 2022-09-02 | 上海哔哩哔哩科技有限公司 | Data quality monitoring method and device |
Also Published As
| Publication number | Publication date |
|---|---|
| CN113010495B (en) | 2023-01-06 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107450979B (en) | Block chain consensus method and device | |
| CN107391628B (en) | Data synchronization method and device | |
| CN107066519B (en) | Task detection method and device | |
| KR20190020105A (en) | Method and device for distributing streaming data | |
| CN110162512B (en) | Log retrieval method, device and storage medium | |
| US8805821B2 (en) | Deferred compilation of stored procedures | |
| CN108599973B (en) | Log association method, device and equipment | |
| CN110297837B (en) | Data updating method and device and data query method and device | |
| CN108459913B (en) | Data parallel processing method and device and server | |
| US20180300147A1 (en) | Database Operating Method and Apparatus | |
| CN113722277A (en) | Data import method, device, service platform and storage medium | |
| CN113868028A (en) | Method for replaying log on data node, data node and system | |
| CN113010495B (en) | Database optimization method and device | |
| CN115002143A (en) | Node election method and device, storage medium and electronic equipment | |
| CN112732427B (en) | Data processing method, system and related device based on Redis cluster | |
| CN113486109A (en) | Data synchronization method and device of heterogeneous database and electronic equipment | |
| CN111522870B (en) | Database access method, middleware and readable storage medium | |
| CN110909023B (en) | Query plan acquisition method, data query method and data query device | |
| CN109597706B (en) | Detection method, device and system for checking difference data | |
| CN110647463A (en) | Method and device for restoring test breakpoint and electronic equipment | |
| CN115438020A (en) | Database resource scheduling method, device, equipment and medium | |
| CN111459474B (en) | Templated data processing method and device | |
| CN109977097B (en) | Network configuration management method and system based on database | |
| CN111274148A (en) | Method and device for detecting SQL code defects | |
| CN113761400A (en) | Access request forwarding method, device and equipment |
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 |