CN112363812A

CN112363812A - Database connection queue management method based on task classification and storage medium

Info

Publication number: CN112363812A
Application number: CN202011284651.XA
Authority: CN
Inventors: 刘刚; 王刚; 赵山; 王阳
Original assignee: Inspur Cloud Information Technology Co Ltd
Current assignee: Inspur Cloud Information Technology Co Ltd
Priority date: 2020-11-17
Filing date: 2020-11-17
Publication date: 2021-02-12
Anticipated expiration: 2040-11-17
Also published as: CN112363812B

Abstract

The invention discloses a database connection queue management method based on task classification and a storage medium, belonging to the technical field of databases, aiming at solving the technical problem of how to solve the access performance problem of a database when facing massive concurrent connection, improving the operation efficiency of accessing the database in a thread pool connection management mode and ensuring the realization of more efficient and more refined database request queue scheduling, and the technical scheme is as follows: the method is based on a MySQL database, classifies client connection requests according to request types in a thread pool operation mode, and schedules different requests of a user in a reasonable and efficient mode to give the best operation practice to the user; the method comprises the following specific steps: s1, monitoring a data request; s2: request task grading: requesting a task queue and adjusting the dynamic priority; and S3, dynamically expanding and contracting the thread groups in parallel on site.

Description

Database connection queue management method based on task classification and storage medium

Technical Field

The invention relates to the technical field of databases, in particular to a database connection queue management method based on task classification and a storage medium.

Background

Under the current internet environment with mass flow, a database is used as bottom-layer data summarizing and access supporting software of an application system, the high-efficiency throughput rate of the database is the basic requirement of database software and the technical direction of continuous improvement, and particularly, how to schedule client requests in an optimal mode when the database faces mass client connection so as to achieve the optimal performance of the database is a technical subject of the database software.

Currently, the conventional technology of the MySQL database is to allocate a thread to each connection request of a client for processing, and for a MySQL instance, a thread is used to execute a statement in each connection of the client, generally, as the number of concurrent connections increases, the total performance of the instance increases. However, when the number of concurrencies exceeds a certain number, the total performance of the instance decreases as the number of connections continues to increase, and the reason for the decrease in performance is mainly two reasons:

firstly, MySQL creates a thread for each connection request, and as the number of threads rises, frequent thread context switching is caused, and the cache hit rate of a CPU is reduced;

secondly, a large number of threads can cause the contention of the shared resources to be intensified, and the lock acquisition for protecting the shared resources can become a great performance bottleneck.

Some improved versions of the MySQL database allow the server to maintain good performance even with a large number of client connections by introducing a dynamic thread pool. The server, by managing client connection requests using a thread pool, greatly reduces the number of concurrent threads, which reduces context switching and hot lock contention for threads. The use of the thread pool has the most obvious influence on OLTP workload (relatively short CPU constraint query), and in the known performance test, when mass data connection is faced, the performance index shown by the thread pool connection management model is obviously superior to that of a single-connection single-thread running mode.

Currently, the known thread pool connection management is implemented as two client connection request task queues, namely a high priority queue and a low priority queue, wherein the two queues queue respectively according to the state of the request task, and when the client request task is:

firstly, a client connection request is in a transaction;

the client connection request is holding a table lock;

the client side connection request table locking mode has effective value;

the client connects and requests to hold the metadata lock;

the client connection request is requesting a global read lock;

sixthly, the client connection request is requesting a backup lock.

In the above states, the task is added to the high priority queue, so that the client connects to the fast completion request and releases the lock of the occupied shared resource, while the ordinary client requests are added to the low priority queue by default. By scheduling two task queues with different priorities, the overall execution efficiency of the task queues is improved to a certain extent. When the MySQL database runs in a thread pool connection management mode, priority scheduling of client requests can be improved to a certain extent, but the MySQL database does not distinguish types of client connections, does not care about specific operation types of client requests, and cannot finely distinguish different types of operations which are short in time consumption, small in occupied shared resources, long in time consumption and large in occupied shared resources, so that the overall performance of the thread pool is not expressed in an optimal state.

In summary, there is no method for distinguishing the client connection requests according to the operation types to achieve the best performance in the prior art. Therefore, how to solve the problem of access performance when the database faces massive concurrent connection, improve the operating efficiency of accessing the database in a thread pool connection management mode, and ensure that more efficient and more precise scheduling of the database request queue is a problem to be solved urgently at present.

Disclosure of Invention

The technical task of the invention is to provide a database connection queue management method and a storage medium based on task classification, so as to solve the problem of access performance of a database when the database faces massive concurrent connections.

The technical task of the invention is realized in the following way, a database connection queue management method based on task classification, the method is based on MySQL database, under the thread pool operation mode, the connection request of the client is classified according to the request type, and different requests of the user are dispatched in a reasonable and efficient way, so as to give the best operation practice to the user; the method comprises the following specific steps:

s1, monitoring a data request;

s2: request task grading: requesting a task queue and adjusting the dynamic priority;

and S3, dynamically expanding and contracting the thread groups in parallel on site.

Preferably, the data request monitoring in step S1 is as follows:

s101, loading a thread pool connection management function in a plug-in mode when a MySQL database is started, and waiting for connection and access of a client after a connection thread pool is started;

s102, connecting a thread pool to construct thread groups, wherein the number of the thread groups is consistent with the number of CPU cores of the running host, so that the maximum computing capability of a CPU is exerted;

s103, after the client is connected, obtaining a thread group number to be added to the connection through the digital-analog operation of the unique digital identifier connected with the client and a bus thread group, and adding the client to a corresponding thread group;

s104, distinguishing a management link and a data link;

s105, after the client side is connected and added into the task queue, the independent monitoring thread of the thread group starts to poll data requests connected with all the client sides in the thread group;

s106, after the monitoring thread reads the client connection request, judging whether the client connection is in one or more states of a transaction, a lock holding a shared resource and a global lock being requested according to the state of the client connection:

if yes, the priority of the data request is considered as high priority, and the data request is added into a high priority queue;

if not, adding other data requests into the common priority queue;

s107, judging whether the service is stopped:

if yes, ending;

if not, jumping to step S105.

Preferably, each thread group sets a maximum parallel thread number, and each thread group is assigned with an independent thread for monitoring data requests connected with all clients in the thread group.

Preferably, the distinguishing between the management link and the data link in step S104 is as follows:

s10401, adding the client connection to different task queues of the thread group according to the type of the client connection;

s10402, judging the connection type:

firstly, when the connection type is a management link, directly adding the link into a high-priority queue;

and secondly, when the connection type is a common data link, adding the link into a low-priority queue.

Preferably, the task queue request and the dynamic priority adjustment in step S2 are specifically as follows:

s201, a monitoring thread previews request data connected with all clients in a low-priority queue, only checks a data type field carried in a request data head, and does not read the request data;

s202, performing secondary classification on the request data according to the viewed request data types, wherein the secondary classification is divided into five operation types;

s203, judging whether the priority needs to be adjusted:

if yes, executing step S204;

if not, jumping to the step S205;

s204, dynamically adjusting the priority of the three types of secondary task queues according to different service scenes;

s205, judging whether the service is stopped:

if yes, ending;

if not, jumping to step S201.

Preferably, the five operation types in step S202 are specifically as follows:

(1) and management operation: operations such as query and modification (SET) variables, query running States (SHOW) and the like except for the following three types of operations, wherein the operations are generally operations with extremely low overhead, are executed firstly, and the data requests of the type are directly added into a high-priority queue;

(2) and transaction type operation: when the operation request is a transaction operation, such as various DDL statements and definite transaction starting statements, the operation occupies more shared resources, but the occurrence frequency of the operation is low, and the execution of higher priority is ensured, and the operation is added into a transaction operation queue;

(3) and updating operation: when the operation request is an automatically submitted UPDATE (INSERT, UPDATE, DELETE) operation, such as most DML statements except SELECT, the type of statement will occupy relatively few shared resources, but the occurrence frequency is generally medium, and it should be ensured that the medium priority is executed, and the medium priority is added to the UPDATE operation queue;

(4) and query operation: when the operation type is a query type operation, such as a SELECT statement, the type statement occupies the least shared resources, but has the highest occurrence frequency and possibly longer execution time, and should ensure the execution with lower priority, add the query operation into a query operation queue;

(5) and long task operation: when the operation type is a task which consumes a long time, such as export (DUMP), Copy (CLONE) and the like, the statement of the type can monopolize one thread for a long time, then the task is directly executed, the thread of the task is not counted in the number of concurrent threads of the thread group, and the quota of the number of normal working threads of the thread group is ensured not to be occupied.

Preferably, the priority of the transaction operation queue, the update operation queue and the query operation queue is transaction operation queue > update operation queue > query operation queue.

Preferably, in step S3, the parallel live dynamic scaling of the thread group is specifically as follows:

s301, after client request data are distributed to proper queues, each thread group immediately starts a working thread to process data requests according to priorities, and each task request in a high-priority queue is processed firstly;

s302, after the high-priority queue is processed, the thread group requests the priority to process the secondary task queue according to the preset tertiary queue;

s303, judging the number of waiting requests and the number of parallel threads of the task queue:

firstly, if the waiting number of the task queue is large and the duration time exceeds a threshold value, increasing the number of parallel threads of the thread group;

and secondly, if the waiting number of the task queue is small, the number of parallel threads is large, and the duration time exceeds a threshold value, recovering idle threads of the thread group, and reducing the number of the parallel threads.

An electronic device, comprising: a memory and at least one processor;

wherein the memory stores computer-executable instructions;

the at least one processor executes computer-executable instructions stored by the memory to cause the at least one processor to perform the method for task classification-based database connection queue management as described above.

A computer-readable storage medium having stored thereon computer-executable instructions, which, when executed by a processor, implement a task classification-based database connection queue management method as described above.

The database connection queue management method and the storage medium based on task classification have the following advantages that:

compared with the existing database thread pool connection management mode, the invention optimizes and improves the database from multiple angles, shows excellent performance indexes, solves the problem of access performance of the database in the face of massive concurrent connection, and improves the operation efficiency of accessing the database in the thread pool connection management mode;

secondly, the connection requests of the client are classified according to the request types based on the MySQL database in a thread pool operation mode, so that the overall throughput rate of the database is improved, different requests of users are dispatched in a reasonable and efficient mode, the best operation practice is provided for the users, optimization, promotion and transformation are performed on the original implementation, and the MySQL database has strong practicability and innovation;

the invention solves the problem of quick recovery when the database load is abnormal by distinguishing the client connection type and preferentially processing the management type connection link request;

according to the method, the client side connection request is received, the independent and stable monitoring thread analysis request mode is adopted, the complexity and the performance overhead of the back-and-forth switching of roles of the monitoring thread and the task processing thread are avoided, the monitoring thread is concentrated on the classification of the client side request tasks, and the problem that the database monitoring task cannot be processed in time at the first time is solved;

after the connection request of the client arrives, two levels of task queues of a high-priority queue and a low-priority queue and a queue classified according to operation types are adopted, so that the task request is more finely scheduled, and the running performance of a thread pool connection management mode is further improved;

the invention provides a solution for smoothly improving the performance in the time dimension and smoothly recycling idle resources by adopting a mode of time delay expansion and time delay contraction after the number of parallel connections of the thread pool reaches the existing limit, so that the performance accumulation performance of the thread pool connection management for a long time is further improved;

the invention ensures that the data request of the management operation is firstly processed and the high priority of the management operation by distinguishing the management link and the data link, thereby leading a database administrator to intervene in the operation of the database at the first time, further carrying out necessary adjustment on the operation state of the database and ensuring the healthy and efficient operation of the database;

according to the invention, through the independent monitoring thread, according to a single responsibility principle, the monitoring thread is only used for rapidly processing the data requests connected with all the clients in the thread group, so that the data requests connected with all the clients can be read and distributed to the task queue at the first time;

the invention distinguishes different request types through two stages of task queues, dynamically adjusts the priority of the three types of queues according to the operation scene and the service requirement of the user, and can finely adjust the performance of the database by a flexible and effective strategy so as to exert the optimal performance of the thread pool connection management;

the invention can rapidly cope with the sudden flow increase by dynamically expanding and reducing the parallel thread number of the thread group, and timely release the resources in idle time, so that the overall performance of the database can be kept smooth and output.

Compared with the prior art, the invention provides a more excellent and efficient method for processing massive database connection requests.

Drawings

The invention is further described below with reference to the accompanying drawings.

FIG. 1 is a block diagram of a data request snooping process;

FIG. 2 is a flow diagram of a request task hierarchy process;

FIG. 3 is a flow diagram of parallel thread dynamic scaling.

Detailed Description

The task classification-based database connection queue management method and storage medium according to the present invention will be described in detail with reference to the accompanying drawings and specific embodiments.

Example 1:

the invention relates to a database connection queue management method based on task classification, which is based on a MySQL database, classifies client connection requests according to request types in a thread pool operation mode, and schedules different requests of users in a reasonable and efficient manner to give optimal operation practice to the users; the method comprises the following specific steps:

s1, monitoring a data request;

As shown in fig. 1, the data request snooping in step S1 in this embodiment is as follows:

s102, connecting a thread pool to construct thread groups, wherein the number of the thread groups is consistent with the number of CPU cores of the running host, so that the maximum computing capability of a CPU is exerted; and each thread group is provided with a maximum parallel thread number, and is simultaneously allocated with an independent thread for monitoring data requests connected with all clients in the thread group.

s104, distinguishing a management link and a data link; the method comprises the following specific steps:

s10402, judging the connection type:

if not, adding other data requests into the common priority queue;

s107, judging whether the service is stopped:

if yes, ending;

if not, jumping to step S105.

As shown in fig. 2, the task queue request and dynamic priority adjustment in step S2 in this embodiment are as follows:

s202, performing secondary classification on the request data according to the viewed request data types, wherein the secondary classification is divided into five operation types; the method comprises the following specific steps:

S203, judging whether the priority needs to be adjusted:

if yes, executing step S204;

if not, jumping to the step S205;

s205, judging whether the service is stopped:

if yes, ending;

if not, jumping to step S201.

In this embodiment, the priority is transaction operation queue > update operation queue > query operation queue.

As shown in fig. 3, in step S3, the details of the parallel field dynamic expansion of the thread group in the present embodiment are as follows:

s302, after the high-priority queue is processed, the thread group requests the priority to process the secondary task queue according to the preset tertiary queue; because of the possibility of more queued data requests, each thread group allows a certain number of parallel threads to process multiple tasks simultaneously, but when the number of tasks backlogged in each thread group for a period of time is too large, the integrity of the thread pool is affected. If the thread group is in the full-bin running state all the time, after a period of time, the maximum parallel thread number is dynamically expanded to process more tasks in parallel after a preset threshold value is exceeded.

Example 2:

an embodiment of the present invention further provides an electronic device, including: a memory and at least one processor;

wherein the memory stores computer-executable instructions;

the at least one processor executing the computer-executable instructions stored by the memory causes the at least one processor to perform a method of database connection queue management based on task classification as in any one of the embodiments.

Example 3:

the embodiment of the invention also provides a computer-readable storage medium, wherein a plurality of instructions are stored, and the instructions are loaded by the processor, so that the processor executes the database connection queue management method based on task classification in any embodiment of the invention. Specifically, a system or an apparatus equipped with a storage medium on which software program codes that realize the functions of any of the above-described embodiments are stored may be provided, and a computer (or a CPU or MPU) of the system or the apparatus is caused to read out and execute the program codes stored in the storage medium.

In this case, the program code itself read from the storage medium can realize the functions of any of the above-described embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

Examples of the storage medium for supplying the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (e.g., CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-R database connection queue management method based on task classification and storage medium M, DVD-RW, DVD + RW), a magnetic tape, a nonvolatile memory card, and a ROM. Alternatively, the program code may be downloaded from a server computer via a communications network.

Further, it should be clear that the functions of any one of the above-described embodiments may be implemented not only by executing the program code read out by the computer, but also by causing an operating system or the like operating on the computer to perform a part or all of the actual operations based on instructions of the program code.

Further, it is to be understood that the program code read out from the storage medium is written to a memory provided in an expansion board inserted into the computer or to a memory provided in an expansion unit connected to the computer, and then causes a CPU or the like mounted on the expansion board or the expansion unit to perform part or all of the actual operations based on instructions of the program code, thereby realizing the functions of any of the above-described embodiments.

Finally, it should be noted that: the above embodiments are only used to illustrate the technical solution of the present invention, and not to limit the same; while the invention has been described in detail and with reference to the foregoing embodiments, it will be understood by those skilled in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some or all of the technical features may be equivalently replaced; and the modifications or the substitutions do not make the essence of the corresponding technical solutions depart from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A database connection queue management method based on task classification is characterized in that the method is based on a MySQL database, classifies client connection requests according to request types in a thread pool operation mode, and schedules different requests of users in a reasonable and efficient manner to give optimal operation practices to the users; the method comprises the following specific steps:

s1, monitoring a data request;

2. The method for managing the database connection queue based on task classification as claimed in claim 1, wherein the data request snooping in step S1 is as follows:

s104, distinguishing a management link and a data link;

if not, adding other data requests into the common priority queue;

s107, judging whether the service is stopped:

if yes, ending;

if not, jumping to step S105.

3. The method as claimed in claim 2, wherein each thread group has a maximum parallel thread count, and each thread group is assigned an independent thread for monitoring data requests connected to all clients in the thread group.

4. The task classification-based database connection queue management method according to claim 2 or 3, wherein the distinguishing between the management link and the data link in step S104 is as follows:

s10402, judging the connection type:

5. The method for managing a database connection queue according to claim 1, wherein the task queue request and the dynamic priority adjustment in step S2 are as follows:

s203, judging whether the priority needs to be adjusted:

if yes, executing step S204;

if not, jumping to the step S205;

s205, judging whether the service is stopped:

if yes, ending;

if not, jumping to step S201.

6. The task classification-based database connection queue management method according to claim 5, wherein the five operation types in step S202 are specifically as follows:

(1) and management operation: the operation with low overhead is executed firstly, and the data request is directly added into a high-priority queue;

(2) and transaction type operation: when the operation request is a transaction operation, ensuring high-priority execution with an explicit transaction starting statement, and adding the operation request into a transaction operation queue;

(3) and updating operation: when the operation request is an updating operation which is automatically submitted, the execution of medium priority is ensured, and the operation request is added into an updating operation queue;

(4) and query operation: when the operation type is query operation, ensuring low-priority execution, and adding the operation type into a query operation queue;

(5) and long task operation: when the operation type is a task which consumes long time for exporting and copying, the statement of the type can monopolize a thread for a long time, then the task is directly executed, and the thread of the task is not counted in the number of concurrent threads of a thread group.

7. The method of claim 6, wherein the priorities of the transaction operation queue, the update operation queue and the query operation queue are transaction operation queue > update operation queue > query operation queue.

8. The method for managing database connection queues based on task classification as claimed in claim 1, wherein the thread group parallel field dynamic scaling in step S3 is specifically as follows:

9. An electronic device, comprising: a memory and at least one processor;

wherein the memory stores computer-executable instructions;

the at least one processor executing the computer-executable instructions stored by the memory causes the at least one processor to perform the method of task classification-based database connection queue management of any of claims 1-8.

10. A computer-readable storage medium having stored thereon computer-executable instructions which, when executed by a processor, implement a task classification-based database connection queue management method as claimed in claims 1 to 8.