CN110781244B

CN110781244B - Method and device for controlling concurrent operation of database

Info

Publication number: CN110781244B
Application number: CN201910859269.8A
Authority: CN
Inventors: 黄欢欢
Original assignee: Alibaba Group Holding Ltd
Current assignee: Alibaba Cloud Computing Ltd
Priority date: 2014-12-03
Filing date: 2014-12-03
Publication date: 2023-06-13
Anticipated expiration: 2034-12-03
Also published as: CN105718474A; CN110781244A; WO2016086800A1; CN105718474B

Abstract

The application discloses a method for controlling concurrent operations of a database, the method comprising: receiving a database operation request aiming at a database; judging whether an index value representing the load pressure of the database is smaller than a preset first threshold value or not; if yes, continuing to process the database operation request and delivering the database operation request to a storage engine for execution; if not, ending the processing of the database operation request. The application also provides a device for controlling the concurrent operation of the database. By adopting the method provided by the application, the load pressure of the database can be finely controlled, the response time for the database operation request is maintained within the acceptable range of the application, and the overall availability of the database is improved.

Description

Method and device for controlling concurrent operation of database

The application is a divisional application of Chinese patent application with the application date of 2014, 12, 03 and the application number of 201410727590.8, and the name of 'a method and a device for controlling concurrent operation of a MySQL database'.

Technical Field

The present application relates to the field of databases, and in particular, to a method for controlling concurrent operations of a database. The application also provides a device for controlling the concurrent operation of the database.

Background

MySQL is an open source relational database management system that increases data access speed and flexibility by storing data in different interrelated data tables rather than placing all of the data in one large warehouse. MySQL supports a variety of database engines in plug-ins, the main stream of which is the InnoDB storage engine supporting the transaction mechanism, and further supports storage engines such as MyISAM, BDB, memory.

The architecture of MySQL databases typically includes two layers, the first being a MySQL server layer, at which all work is done before the MySQL database system processes the underlying data, including: client identity verification, connection management, analysis, optimization, cache query and other operations aiming at database operation requests of the client; the second layer is a storage engine layer (Storage Engine Layer), namely an implementation part of the bottom data access operation, and is composed of multiple storage engines and is responsible for performing operations such as data storage, reading, index establishment and the like.

Typically, database operation requests from clients (e.g., upper layer applications) are random, so there may be a large number of concurrent requests for which the prior art typically uses two ways to guarantee MySQL database availability:

1) Relying on upper layer application throttling for protection, for example: when the upper layer application finds that the response time of the MySQl database exceeds a preset threshold, suspending or slowing down the actions of establishing connection with the MySQL database and sending a database operation request;

2) Relying on the underlying storage engine's own concurrency control mechanism. Taking the storage engine InnoDB as an example, when a thread enters the InnoDB for execution, if the number of threads which are concurrently executed in the InnoDB exceeds the preset concurrency number, the thread waits in a FIFO queue of the InnoDB and cannot enter the InnoDB for direct execution, until the thread is completely executed and exits from the InnoDB, the thread waiting in the FIFO queue is awakened and enters the InnoDB for internal execution.

The two modes can protect the MySQL database to a certain extent, but in practical application, certain defects exist: in the mode 1), because feedback through upper application is needed, the processing time is longer, the load pressure of the MySQL database cannot be relieved in time, and the situation that the current limit does not exert an effect and the MySQL database is crashed possibly occurs; in the mode 2), under the condition that the load pressure of the MySQL database is large to a certain extent, although the storage engine itself has a concurrency control mechanism, because the MySQL Server layer lacks effective self-protection capability, if the scale of concurrency operation exceeds the processing capability of the MySQL Server layer, the MySQL database can not stably provide services, and response time of upper-layer applications can not be responded even in a wide range, so that the usability of the MySQL database can not be effectively ensured.

Disclosure of Invention

The application provides a method for controlling concurrent operation of a database, so as to solve the problem that the database can not stably provide service under the condition of a large number of concurrent operations. The present application additionally provides an apparatus for controlling concurrent operation of a database.

The application provides a method for controlling concurrent operation of a database, comprising the following steps:

receiving a database operation request aiming at a database;

judging whether the database operation request meets a preset unlimited flow condition or not;

if not, judging whether the operation type of the database operation request belongs to one of preset first current limiting types;

if the value of the database is less than a preset first threshold value, judging whether the value of the threads_running variable of the database is less than the preset first threshold value; if the data is smaller than the data, continuing to process the database operation request and delivering the database operation request to a storage engine for execution; and if not, ending the processing of the database operation request.

Optionally, when the result of determining whether the value of the database thread_running variable is smaller than the preset first threshold is "smaller than", before continuing to process the database operation request and passing the database operation request to the storage engine for execution, the following operations are executed:

Judging whether the operation type of the database operation request belongs to one of preset second current limiting types and whether the number of concurrent threads is not smaller than a preset second threshold value;

when the two judging results are yes, distributing the thread corresponding to the database operation request to a first-in first-out queue for waiting;

otherwise, executing the step of continuing to process the database operation request and delivering the database operation request to a storage engine for execution, and adding one to the number of concurrent threads when the operation type of the database operation request belongs to one of preset second current limiting types;

the number of concurrent threads refers to the number of threads whose operation type belongs to one of the preset second current limit types and which are continuously processed and submitted to the database operation request executed by the storage engine.

Optionally, when the result of the determining whether the operation type of the database operation request belongs to one of the preset first current limit types is "not belonging to" the database operation request, before continuing to process the database operation request and passing the database operation request to the storage engine for execution, the following operations are executed:

and judging whether the operation type of the database operation request belongs to one of preset second current limiting types and whether the number of concurrent threads is not smaller than a preset second threshold value.

Optionally, the first current limiting type includes: and (5) inquiring operation.

Optionally, the determining whether the database operation request meets the preset unlimited condition specifically refers to determining whether the database operation request meets any one of the following three conditions:

the initiator of the database operation request has super authority for database operation;

the database transaction to which the operation related to the database operation request belongs is started;

the operation type of the database operation request is one of transaction submission or transaction rollback;

and when the database operation request meets any one of the three conditions, continuing to process the database operation request and delivering the database operation request to a storage engine for execution.

Optionally, when the result of determining whether the database operation request meets the preset unlimited condition is "met", the following operations are performed before the database operation request is continuously processed and is delivered to the storage engine for execution:

Optionally, the second current limiting type includes: query operation, insert operation, update operation, and delete operation.

Optionally, when judging whether the operation type of the database operation request belongs to one of preset second current limiting types and whether the number of concurrent threads is not less than a preset second threshold, firstly executing the following operations:

judging whether the database operation request meets any one of the following three conditions, if yes, turning to the step of continuing to process the database operation request and delivering the database operation request to a storage engine for execution:

the database operation request relates to that a database transaction to which an operation belongs has been started;

the operation type of the database operation request is one of transaction commit, transaction rollback, or database operation related to a data definition language.

Optionally, after the database operation request is executed by the storage engine, the waiting threads in the first-in first-out queue are sequentially awakened to continuously process the database operation request corresponding to the threads, and the database operation request is handed to the storage engine for execution.

Optionally, the method further comprises:

monitoring the waiting time of each thread in the first-in first-out queue;

and when the waiting time is larger than a preset waiting threshold value, returning a response for refusing to execute to an initiator of the database operation request corresponding to the thread, and terminating the running of the thread.

Optionally, the number of the fifo queues is multiple, and threads that need to enter the waiting state are sequentially allocated to different fifo queues.

Optionally, after the step of receiving the database operation request, the following operations are first performed:

judging whether the preset first threshold value is zero or not; if yes, setting the preset first threshold value as the maximum connection number supported by the database;

judging whether the preset first threshold value is smaller than the preset second threshold value or not; if yes, setting the preset first threshold value as the preset second threshold value.

Correspondingly, the application also provides a device for controlling the concurrency operation of the database, which comprises the following steps:

an operation request receiving unit for receiving a database operation request for a database;

a first condition judging unit for judging whether the database operation request meets a preset unlimited condition;

A first current limit type judging unit, configured to judge whether an operation type of the database operation request belongs to one of preset first current limit types when a judgment result of the first condition judging unit is "unsatisfied";

a variable index judging unit, configured to judge whether a value of a thread_running variable of the database is smaller than a preset first threshold value when a judgment result of the first current limit type judging unit is "belonging to";

an operation request processing unit, configured to, when the output of the variable index determining unit is "less than", continue to process the database operation request and transmit the database operation request to a storage engine for execution;

and an operation request ending unit for ending the processing of the database operation request when the output of the variable index judging unit is not less than.

Compared with the prior art, the application has the following advantages:

after receiving a database operation request, the method for controlling the concurrent operation of the database detects an index value representing the load pressure of the database in real time, and when the index value is smaller than a preset first threshold value, the database operation request is continuously processed and is submitted to a storage engine for execution, otherwise, the processing of the database operation request is ended. By adopting the method, under the condition that the database load pressure is high, the processing of the database operation request is directly ended, so that the fine control on the database load is realized, the response time for the database operation request is maintained within the acceptable range of the application, and the availability of the whole database is improved.

Drawings

FIG. 1 is a flow chart of one embodiment of a method of controlling concurrent operation of a database of the present application;

FIG. 2 is a flow chart of a process for limiting current according to threads_running provided in the present embodiment;

FIG. 3 is a flow chart of a process for throttling in FIFO queuing according to the present embodiment;

FIG. 4 is a graph showing the relationship between QPS and the number of active threads in different current limiting modes according to the present embodiment;

fig. 5 is a schematic diagram of an embodiment of an apparatus for controlling concurrent operation of a database according to the present application.

Detailed Description

In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present application. This application is, however, susceptible of embodiment in many other ways than those herein described and similar generalizations can be made by those skilled in the art without departing from the spirit of the application and the application is therefore not limited to the specific embodiments disclosed below.

In the present application, a method for controlling the concurrency operation of a database and an apparatus for controlling the concurrency operation of a database are provided, respectively, and are described in detail in the following embodiments.

Referring to fig. 1, a flowchart of an embodiment of a method for controlling concurrent operations of a database according to the present application includes the following steps:

Step 101: a database operation request is received for a MySQL database.

The database operation request for MySQL database described in the present application refers to an operation request sent from a client (e.g., an upper layer application) to the MySQL database, where the operation request generally includes an SQL statement that requests execution of the MySQL database, for example: query, insert, delete, update, and create, modify, delete, etc. SQL statements for data tables, as well as SQL statements supported by other MySQL databases.

In an implementation, the database operation request is received by a server layer of MySQL, and since MySQL generally adopts a multithreading mechanism, the server layer allocates a thread for processing the database operation request for each received database operation request, where the allocation operation may be to create a new thread or directly obtain an idle thread from a thread buffer pool (thread cache). It will also be appreciated that each database operation request received by the MySQL database is carried by, or has a thread corresponding to, that thread.

Step 102: and judging whether the index value representing the load pressure of the MySQL database is smaller than a preset first threshold value, if so, executing the step 103, otherwise, executing the step 104.

According to the technical scheme, under the condition that the load pressure of the MySQL database is high, the received database operation request can be directly ended, namely: and the processing is not performed, so that the load pressure of the MySQL database is relieved, and the availability of the MySQL database is ensured.

To achieve the above objective, it is first necessary to be able to determine the current load status of MySQL database. In a specific operation, there are many bases that can be used to determine MySQL load conditions, for example: TPS (Transaction Per Second-system throughput), QPS (Query Per Second-rate), RT (Response Time-Response Time), TR (threads_running-number of active Threads), context Switch Per Second (number of thread context switches Per Second), and the like. Some of the indexes are running indexes of a host computer where the MySQL database is located, some of the indexes are considered from the inside of the MySQL database, and the TR is selected as an index for representing the load pressure of the MySQL database from the aspects of universality and reflecting the MySQL load condition as accurately as possible in real time. In other embodiments, the above or other indicators capable of characterizing load pressure conditions of MySQL database may be selected, and the technical solutions of the present application may be implemented as well.

After the database operation request is processed by the server layer of MySQL, it usually enters the storage engine layer (e.g. InnoDB) to execute the actual database operation, and the thread_running variable maintained by the MySQL database is the number of Threads entering the storage engine layer (possibly executing and possibly waiting for the opportunity to acquire execution), which may also be referred to as the number of Threads in the execution state or the number of active Threads, and it may be generally considered that the load pressure of the whole MySQL database is relatively large when the value is relatively large.

The step is to determine whether to continue processing the database operation request according to the value of threads_running, if the value is smaller than the preset first threshold, it is indicated that the load pressure of the MySQL database is still within the processing capability range thereof, so step 103 is executed to continue processing the database operation request, otherwise, it is indicated that the load pressure of the MySQL database may have exceeded the processing capability range thereof, and at this time, a current limiting measure should be taken, namely: execution of step 104 ends the processing of the database operation request.

In a specific example of this embodiment, based on the above-mentioned determination manner, the type of the database operation request or the authority of the initiator is also taken as a consideration of whether to limit the current according to the actual running requirement, so the processing procedure of this step includes the following steps 102-1 to 102-3, and is described below with reference to fig. 2.

Step 102-1: judging whether the database operation request meets the preset unlimited flow condition, if so, turning to step 103 for execution, otherwise, executing step 102-2.

Considering that database operation requests from clients (e.g., upper level applications) may involve various database operations supported by MySQL databases, some of which are special purpose or relatively important operations (e.g., binlog operations related to database synchronization operations) that a user with super rights (i.e., super rights) can perform, such operations may be performed directly to step 103 without taking current limiting measures.

Furthermore, if the underlying storage engine employs InnoDB, then there are some transaction related operations, such as: the commit operation (commit), rollback (rollback) operations, and the various operations contained in the opened transaction may also generally be performed without taking current limiting measures, but rather directly to step 103.

In the above specific example of the present embodiment, the condition of no restriction in this step means:

1) The initiator of the database operation request has super authority for database operation;

2) The database transaction to which the operation related to the database operation request belongs is started;

3) The operation type of the database operation request is one of transaction commit (commit) or transaction rollback (rollback).

When the database operation request satisfies any one of the conditions, the current limit judgment is not performed, and the process goes to step 103 to be executed.

Step 102-2: and judging whether the operation type of the database operation request belongs to one of preset first current limiting types, if so, executing the step 102-3, otherwise, turning to the step 103 for execution.

In an actual business scenario, a large number of operation types of database operation requests are: the types of SQL statements carried in the request are all types of operations involved in DML (data manipulation language) statements, such as: query select, insert, update, delete, etc., these database operation requests are typically considered business-level operation requests; in addition, the database operation request received by MySQL may also have other database-level operation requests, such as: the number of such operation requests is relatively small, for example, operations related to DDL (data definition language) statements or operations related to DCL statements.

In conventional applications, database operation requests at the business level are often a concern for performing current limiting operations, and where the execution time of a query operation may be relatively long, a large number of concurrent query operations may place a relatively large stress on the MySQL database. Thus, considering the execution efficiency of the method and the current limiting effect, the first current limiting type may be set to include only: the query operation, that is to say, if the database operation request is a query operation, goes to step 102-3 for execution, otherwise, goes directly to step 103 for execution without limiting the flow.

Of course, in other embodiments, according to the database operation characteristics of the actual service scenario, the preset first current limiting type may be different from the setting in the embodiment, and the technical solution of the present application may be implemented, which is also within the protection scope of the present application.

Step 102-3: and judging whether the value of the threads_running variable of the MySQL database is smaller than a preset first threshold value, if so, executing the step 103, otherwise, executing the step 104.

The execution proceeds to the step, which is to say that the database operation request belongs to the operation type which can be limited, so the step determines whether to limit the flow by judging the value of the threads_running variable, if the value of the threads_running is smaller than a preset first threshold value, the current load pressure of the MySQL database is still within the processing capacity range, therefore, the flow limit is not needed, the step 103 is executed, and otherwise, the step proceeds to the step 104 to finish the processing of the database operation request.

The above describes a decision process for determining whether a current limit is required based on the value of threads_running, which corresponds to setting a hard upper limit for the threads_running variable, and when the number of active threads exceeds the upper limit, the database operation request will not be processed (see subsequent step 104), thereby enabling the value of threads_running to be maintained substantially within a controllable range.

In a specific implementation process, in order to obtain a better current limiting effect, the embodiment further provides a preferred embodiment, namely: and adopting a two-stage current limiting measure, and further adopting a FIFO queue to limit the current on the basis of the current limiting scheme, so that the availability of the MySQL database can be effectively ensured. In order to distinguish the two types of flow restriction, the above-described flow restriction according to threads_running is referred to as high-level flow restriction, and the flow restriction implemented by using FIFO queues, which will be described below, is referred to as low-level flow restriction. That is, in the case that the high water level throttling does not limit the database operation request, the step 103 may not be directly performed, but the necessary queuing processing is performed through the low water level throttling, so that the load pressure of the MySQL server layer may be relieved in advance.

The low water level flow restriction process includes steps 102-4 to 102-7 as shown below, and is described below with reference to fig. 3.

Step 102-4: and judging whether the database operation request meets the preset condition of not carrying out low water level flow limiting, if so, turning to step 103 for execution, and if not, executing step 102-5.

Similar to the reason described in step 102-1, in order to ensure that some special or important database operation requests can be processed in time and ensure the integrity of the database transaction as much as possible, when the database operation request meets the preset condition of not performing low water level flow limitation, the process goes directly to step 103 for execution, otherwise, step 102-5 is executed to continue to perform low water level flow limitation judgment.

In the above specific example of the present embodiment, the condition that the low water level restriction is not performed in this step means:

3) The operation type of the database operation request is one of transaction commit, transaction rollback, or database operation related to a data definition language.

When the database operation request satisfies any one of the conditions, the process goes to step 103 to execute.

Step 102-5: and judging whether the operation type of the database operation request belongs to one of preset second current limiting types and whether the number of concurrent threads is not less than a preset second threshold value, executing step 102-7 to enter a FIFO queue for waiting when the two judging results are yes, otherwise, executing step 102-6.

Similar to the reason described in step 102-2, the second current limit type in this step mainly refers to a database operation type related to a service layer, and in a specific example of this embodiment, the preset second current limit type includes: query operation, insert operation, update operation, and delete operation. In other embodiments, the second current limit type preset may be different from the setting in the specific example described above.

The number of concurrent threads refers to the number of threads corresponding to the database operation request, the operation type of which belongs to one of preset second current limiting types and is continuously processed. That is, if there is a thread meeting the above-mentioned current-limiting type requirement to continue to perform subsequent processing at the server layer and enter the storage engine layer for execution (including both the case of not being limited by the low-water-level current limit and the case of waking up in the FIFO queue), the number of concurrent threads is increased by one, and when the thread meeting the above-mentioned current-limiting type requirement exits from the storage engine layer, the number of concurrent threads is decreased by one.

In the judgment of this step, if both judgment results are yes, it is indicated that the number of concurrent threads is not less than the preset second threshold, and in order to control the number of concurrent threads within the preset second threshold, it is necessary to go to step 102-7 to perform queuing processing.

Step 102-6: when the operation type of the database operation request belongs to one of preset second current limiting types, the number of concurrent threads is increased by one, and the step 103 is executed.

Step 102-7: threads corresponding to the database operation request are allocated to first-in first-out queues for waiting.

For the database operation request limited in the above determination in step 102-5, the processing cannot be continued, so that the thread corresponding to the database operation request is allocated to a first-in first-out (First In First Out-FIFO) queue for waiting in this step, and after other threads carrying the database operation request exit from the storage engine layer, the thread waiting in the first-in first-out queue is sequentially awakened, and the processing is continued and the execution of the storage engine layer is entered (i.e., the subsequent step 103 is executed).

In general, in a system employing a multithreading mechanism, a CPU executes a plurality of threads in a time-slice round-robin scheduling manner, and when a current time slice ends and starts scheduling of a next thread, a context switch (context switch) process is generally required, which is also time-consuming, and frequent switching operations may affect the stability of the system. By adopting the low-water-level current limiting processing mode, the threads running concurrently on the MySQL server layer can be controlled within a certain number, and possible lock competition between context switching and a large number of threads is reduced, so that the MySQL database can maintain good performance and stability.

In a specific implementation process, in order to prevent threads from waiting for a long time in the FIFO and not being processed due to some unexpected situations, the waiting time of each thread entering the FIFO queue may be monitored, when the waiting time of a certain thread is found to exceed a preset waiting threshold, the operation of the thread is actively terminated, and a response for refusing to execute is returned to an initiator of the database operation request carried by the thread, and after the initiator receives the response, the initiator may send the database operation request again according to a certain policy.

Considering that FIFO queues are shared resources, in order to avoid operation conflicts, threads are usually required to perform locking/unlocking operations when entering or exiting the FIFO queues, and lock contention is necessarily generated under the condition that a large number of threads run concurrently, so that the running efficiency is affected. In order to solve the above-described problems, the present embodiment provides a preferred embodiment, namely: with multiple first-in-first-out queues (multi-FIFOs), threads that need to enter a waiting state are allocated to different first-in-first-out queues in turn according to a round robin (round robin) scheme. By adopting the implementation mode, the shared resource is added, so that the lock contention can be relieved, and the aim of improving the operation efficiency is fulfilled.

The two-stage current limiting mechanism adopted in this embodiment is mainly to perform current limiting according to the first threshold value and the second threshold value. In a specific implementation, these two thresholds may be set before implementing the method, or the following initialization detection and necessary settings may be performed when (or before) receiving a database operation request:

1) Judging whether the preset first threshold value is zero or not; if yes, setting the preset first threshold value as the maximum connection number supported by the MySQL database;

2) Judging whether the preset first threshold value is smaller than the preset second threshold value or not; if yes, setting the preset first threshold value as the preset second threshold value.

In order to enable the high water level flow limit and the low water level flow limit to exert effects, the first threshold is generally set to be larger than the second threshold, and as to what value is specifically set, adjustment can be performed according to actual application scenes and service requirements, and the method and the device are not particularly limited.

Step 103: and continuing to process the database operation request and delivering the database operation request to a storage engine for execution.

If the determination in step 102 indicates that the database operation request is not currently required to be limited, continuing to process the database operation request and delivering the database operation request to a storage engine for execution. Continuing to process the database operation request in this step means that corresponding processing is continued in MySQL server layer, for example: analyzing the database operation request, managing access control, carrying out necessary optimization on the query operation, and selecting each processing branch according to the analysis result: a table manager module, a table modification module, a table maintenance module, a status reporting module, etc. The step of executing the data processing by the storage engine means that after the necessary processing is completed, the thread carrying the database operation request is scheduled to enter the storage engine layer for execution, namely: and performing actual data query, storage, update and other operations. The implementation of this part of the functions may refer to relevant parts of the open source code of the MySQL database, and will not be described here again.

It should be noted that, when the database operation request is executed through the storage engine and exits, if a low water level current limit is adopted in step 102, it should be checked whether there is a thread in a waiting state in the FIFO queue, if so, the waiting threads in the FIFO queue are waken one by one according to the entering order (ensuring that the number of concurrent threads described in the application is within a preset second threshold value), and the waken thread continues to process the database operation request carried by the thread and enters the storage engine for execution; and if the threads in the waiting state are not in the FIFO queue, performing one subtracting operation on the number of concurrent threads, so as to ensure that the number can correctly reflect the number of threads which continue to be processed after the low water level detection and enter a storage engine for execution.

Step 104: and ending the processing of the database operation request.

If the determination result in step 102 indicates that the database operation request needs to be limited currently, it indicates that the load pressure of the MySQL database has exceeded or is about to exceed the processing capability of the MySQL database, and at this time, the MySQL server layer cannot continue to process the database operation request, but directly ends the processing of the database operation request to which the MySQL server layer belongs.

In a specific implementation, ending the processing of the database operation request generally requires terminating the thread carrying the request, and if the thread is created from a thread buffer (thread cache), it is not necessary to destroy the thread, but rather, it is allowed to reenter the thread buffer to wait for the next database operation request to be processed.

After finishing the processing of the database operation request, a response of refusing to execute can be returned to the client (such as an upper layer application) which sends the request, so that the client can know the processing condition of the database operation request in time and carry out subsequent processing according to own strategies.

Thus far, the method for controlling the concurrency operation of the MySQL database provided by the application is described in detail. In a specific implementation, as MySQL is an open source relational database system, the processing of the present technical solution on high water level current limit and low water level current limit can be added on the basis of existing codes, for example: the relevant code for high water level current limiting may be added before the code for sql parsing, the relevant code for low water level current limiting may be added after the sql parsing and before a different processor is selected for processing and is executed by the storage engine according to the parsing result, for example, may be added before mysql_execution_command () is called; of course, processing code for both high water level and low water level restrictions may be added after sql parsing and before execution of the mysql_execution_command () function. The modification of the above embodiment and other embodiments not listed here without departing from the core of the present application are within the scope of protection of the present application, as long as the current limiting function of the technical side of the present application can be implemented, and the availability of MySQL database is ensured.

The embodiment adopts low water level flow limiting based on high water level flow limiting, thereby being capable of obtaining satisfactory implementation effect. The MySQL database supports a multithreading mechanism, and the number of concurrent threads of a MySQL server layer can be controlled within a certain range by adopting high-water-level current limiting, so that the phenomenon that MySQL cannot provide stable service under the condition of a large number of concurrent requests is avoided as much as possible. The low water level flow limit can further guarantee availability of MySQL, and as the second threshold is generally smaller than the first threshold, that is to say, as the load of the database is gradually increased, the low water level flow limit can be acted before the high water level flow limit is acted, and the number of concurrent threads of the MySQL server layer is reduced in a FIFO queuing mode, so that the cost of context switching is reduced, and the availability of the database can be guaranteed before the high water level flow limit is acted, and stable service is provided for upper-layer application.

In the above description, the preferred implementation manner provided in this embodiment may be a high water level flow-limiting scheme or a low water level flow-limiting scheme, which may also be used in other implementation processes, so as to achieve the purpose of guaranteeing the availability of the MySQL database. Specifically, a variable representing the start or the disable of the function can be set for the high water level current limit and the low water level current limit respectively, and the value of the variable is set according to the requirement in practical application, so that the related function is started or disabled; the function may also be directly implemented by using the first threshold or the second threshold described in the present application, for example, when the first threshold is 0, it indicates that the high water level flow restriction is not performed, and when the second threshold is 0, it indicates that the low water level flow restriction is not performed.

In the implementation, only high water level current limit or low water level current limit is adopted, so that the availability of the database can be ensured, and stable service can be provided for upper-layer application. Referring to fig. 4, a graph of QPS (query rate per second) versus the number of active threads in different current limiting modes using the InnoDB storage engine is shown, wherein normal mysql-0 is a curve in which the concurrency control mechanism is not enabled inside the InnoDB, normal mysql-1 is a curve in which the concurrency control mechanism is enabled inside the InnoDB and the maximum concurrency number is set to 32, and patched mysql is a curve in which the low water level current limiting scheme provided in the present application is implemented. As can be seen from the graph, under the condition that the number of active threads is gradually increased, the QPS indexes of the normal mysql-0 and normal mysql-1 curves are obviously reduced to different degrees, and the QPS index of the patched mysql curve adopting the technical scheme of the application is relatively stable.

In summary, the method for controlling concurrent operations of MySQL database provided in the present application detects, in real time, an index value representing load pressure of MySQL database after receiving a database operation request, and when the index value is smaller than a first threshold value set in advance, continues to process the database operation request and sends the database operation request to a storage engine for execution, otherwise, ends processing the database operation request. By adopting the method, under the condition that the load pressure of the MySQL database is high, the processing of the database operation request is directly ended, so that the fine control of the MySQL database load is realized, the response time of each database operation request is ensured to be maintained within the acceptable range of the application, and the overall availability of the MySQL database is improved. In particular, better implementation results can be obtained when the low water level flow limiting scheme is used together.

In the foregoing embodiments, a method for controlling a concurrency operation of a database is provided, and correspondingly, an apparatus for controlling a concurrency operation of a database is also provided. Reference is made to fig. 5, which is a schematic diagram of an embodiment of an apparatus for controlling concurrent operation of a database according to the present application. Since the apparatus embodiments are substantially similar to the method embodiments, the description is relatively simple, and reference is made to the description of the method embodiments for relevant points. The device embodiments described below are merely illustrative.

An apparatus for controlling concurrent operations of a MySQL database of the present embodiment includes: an operation request receiving unit 501, configured to receive a database operation request for a MySQL database; the load pressure judging unit 502 is configured to judge whether an index value representing a load pressure of the MySQL database is smaller than a first threshold value set in advance; an operation request processing unit 503, configured to, when the output of the load pressure judging unit is yes, continue processing the database operation request and transmit it to a storage engine for execution; an operation request ending unit 504 configured to end processing of the database operation request when the output of the load pressure judging unit is no.

Optionally, the apparatus includes:

the concurrent thread judging unit is used for judging whether the operation type of the database operation request belongs to one of preset second current limiting types and whether the number of concurrent threads is not smaller than a preset second threshold value or not when the output of the load pressure judging unit is yes;

a thread waiting unit, configured to allocate a thread corresponding to the database operation request to a first-in first-out queue for waiting when the output of the concurrent thread judging unit is yes;

and the continuous processing unit is used for triggering the operation request processing unit to work when the output of the concurrent thread judging unit is NO, and adding one to the number of the concurrent threads when the operation type of the database operation request belongs to one of preset second current limiting types.

Optionally, the load pressure judging unit is specifically configured to judge whether a value of a threads_running variable of the MySQL database is smaller than a preset first threshold.

Optionally, the apparatus includes:

the first current limiting type judging unit is used for judging whether the operation type of the database operation request belongs to one of preset first current limiting types before judging whether the value of a threads_running variable of the MySQL database is smaller than a preset first threshold value or not;

And the jump control unit is used for triggering the load pressure judging unit to work when the output of the first current limiting type judging unit is yes, and triggering the concurrent thread judging unit to work otherwise.

Optionally, the apparatus includes:

the first condition judging unit is used for judging whether the database operation request meets any one of the following three conditions before judging whether the operation type of the database operation request belongs to one of the preset first current limiting types, if yes, the concurrent thread judging unit is triggered to work, and if not, the first current limiting type judging unit is triggered to work:

the operation type of the database operation request is one of transaction commit or transaction rollback.

Optionally, the concurrent thread judging unit further includes, in addition to a body subunit for implementing the function thereof:

the second condition judging subunit is configured to judge whether the database operation request meets any one of the following three conditions, if yes, trigger the operation request processing unit to work, and if not, trigger the body subunit to work:

Optionally, the apparatus includes:

and the waiting queue awakening unit is used for sequentially awakening the waiting threads in the first-in first-out queue to continuously process the database operation request corresponding to the threads after the database operation request is completely executed by the storage engine and transmitting the database operation request to the storage engine for execution.

Optionally, the apparatus further includes:

a waiting time monitoring unit, configured to monitor waiting time of each thread in the fifo queue;

and the thread termination unit is used for returning a response of refusing execution to an initiator of the database operation request corresponding to the thread and terminating the running of the thread when the waiting time of the thread in the first-in first-out queue is monitored by the waiting time monitoring unit to be larger than a preset waiting threshold value.

Optionally, the number of fifo queues used in the thread waiting unit is multiple, and threads that need to enter the waiting state are sequentially allocated to different fifo queues.

Optionally, the apparatus includes:

the threshold setting unit is used for judging whether the preset first threshold is zero or not after receiving a database operation request; if yes, setting the preset first threshold value as the maximum connection number supported by the MySQL database; judging whether the preset first threshold value is smaller than the preset second threshold value or not; if yes, setting the preset first threshold value as the preset second threshold value.

While the preferred embodiment has been described, it is not intended to limit the invention thereto, and any person skilled in the art may make variations and modifications without departing from the spirit and scope of the present invention, so that the scope of the present invention shall be defined by the claims of the present application.

In one typical configuration, a computing device includes one or more processors (CPUs), input/output interfaces, network interfaces, and memory.

The memory may include volatile memory in a computer-readable medium, random Access Memory (RAM) and/or nonvolatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). Memory is an example of computer-readable media.

1. 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 storage media for a computer 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, which can be used to store information that can be accessed by a computing device. Computer readable media, as defined herein, does not include non-transitory computer readable media (transmission media), such as modulated data signals and carrier waves.

2. It will be appreciated by those skilled in the art that embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application 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.

Claims

1. A method for controlling concurrent operations of a database, comprising:

receiving a database operation request aiming at a database;

if the value of the database is less than a preset first threshold value, judging whether the value of the threads_running variable of the database is less than the preset first threshold value; if the data is smaller than the data, continuing to process the database operation request and delivering the database operation request to a storage engine for execution; if not, ending the processing of the database operation request;

when the result of determining whether the value of the database thread_running variable is smaller than the preset first threshold value is "smaller than", the following operations are executed before the database operation request is continuously processed and is submitted to the storage engine for execution:

2. The method for controlling concurrent operations of a database according to claim 1, wherein when the result of the determining whether the operation type of the database operation request belongs to one of the preset first current limit types is "not belonging", the following operations are performed before continuing to process the database operation request and handing over to a storage engine for execution:

3. The method for controlling concurrent operation of a database of claim 2, wherein the first current limit type comprises: and (5) inquiring operation.

4. The method for controlling concurrent operations of a database according to claim 1, wherein the determining whether the database operation request satisfies a preset unlimited condition specifically refers to determining whether the database operation request satisfies any one of three conditions:

5. The method according to claim 4, wherein when the result of the determining whether the database operation request satisfies a preset unlimited condition is "satisfied", the following operations are performed before continuing to process the database operation request and passing the database operation request to a storage engine for execution:

6. A method for controlling concurrent operation of a database according to any of claims 1-3,5, wherein the second current limit type comprises: query operation, insert operation, update operation, and delete operation.

7. The method for controlling concurrent operations of a database according to any one of claims 1-3,5, wherein when determining whether the operation type of the database operation request belongs to one of the preset second current limit types and the number of concurrent threads is not less than a preset second threshold, first performing the following operations:

8. The method for controlling concurrent operations of a database according to any one of claims 2 to 5, wherein after the database operation request is executed by the storage engine, the waiting threads in the fifo queue are sequentially awakened to continue to process the database operation request corresponding to the thread and are handed to the storage engine for execution.

9. The method for controlling concurrent operation of a database of claim 8, further comprising:

monitoring the waiting time of each thread in the first-in first-out queue;

10. The method for controlling concurrent operations of a database according to claim 8, wherein the fifo is a plurality of threads that need to enter a wait state are sequentially allocated to different fifos.

11. The method for controlling concurrent operations of a database according to claim 1, wherein after the step of receiving a database operation request, the following operations are first performed:

12. An apparatus for controlling concurrent operation of a database, comprising:

an operation request ending unit configured to end processing of the database operation request when the output of the variable index judging unit is "not less than";

the concurrent thread judging unit is used for judging whether the operation type of the database operation request belongs to one of preset second current limiting types and whether the number of concurrent threads is not smaller than a preset second threshold value;

a thread waiting unit, configured to allocate a thread corresponding to the database operation request to a first-in first-out queue for waiting when the results of the above two determinations are yes;

otherwise, a continuous processing unit is used for executing the steps of continuously processing the database operation request and delivering the database operation request to a storage engine for execution, and when the operation type of the database operation request belongs to one of preset second current limiting types, the number of concurrent threads is increased by one;