A system for database connection number anomaly detection, upper limit adjustment and usage prediction

Abstract

The application discloses a kind of database connection number anomaly detection, upper limit adjustment and quantity prediction system, including M1 data preprocessing module: for reading the connection number of corresponding database and writing M2 data storage module;M2 data storage module: for receiving data from other modules;M3 connection number threshold calculation module: for calculating observation connection number upper limit threshold, observation connection number lower limit threshold;M4 observation connection number upper limit threshold, observation connection number lower limit threshold, for sending alarm information;M5 design connection number threshold dynamic adjustment module: for adjusting connection number;M6 connection number capacity prediction module: prediction is carried out to future design connection number upper limit threshold.The application can realize the monitoring, deployment and prediction to database connection pool.

Description

Technical Field

[0001] The present invention relates to a system for detecting abnormality of database connection number, adjusting upper limit and predicting usage, which is used in the field of data storage technology. Background Art

[0002] Currently, there is a database connection pool anomaly detection method, which includes the following steps: monitoring database connection acquisition requests; calculating the average connection time and standard deviation of database connection requests, and determining the database connection pool acquisition time anomaly threshold based on the average time and standard deviation; and monitoring the database connection pool anomaly by comparing the current database connection acquisition request acquisition time with the database connection pool acquisition time anomaly threshold. One of the implementation steps is shown in the following example. Figure 1 .

[0003] The above method measures the time it takes to establish a connection in the database connection pool, using this time as a proxy for database performance. In a production environment, database performance is not solely influenced by connection establishment time. A database platform may reject all new connection requests due to connection pool exhaustion, rendering its services inaccessible. In this case, the connection establishment time may not exceed the threshold, and the above method cannot detect this situation. Furthermore, this method can only detect alarms that have already occurred and cannot predict when an alarm might occur in the future.

[0004] In current production environments, monitoring methods for database connection pools within a single database instance do not provide a feasible solution for allocating connections to each database within that instance. This fails to address the issue of rationally allocating connection resources within a database instance, nor does it predict the future usage of each database connection within that instance. Summary of the Invention

[0005] The purpose of the present invention is to overcome the deficiencies of the prior art and provide a system for detecting anomalies in the number of database connections, adjusting the upper limit and predicting usage, which can realize monitoring, allocation and prediction of the database connection pool.

[0006] A technical solution to achieve the above objectives is: a system for detecting database connection anomalies, adjusting upper limits, and predicting usage, including the following modules:

[0007] M1 data preprocessing module: For the database instance connected to this system, it reads the number of connections corresponding to each database in the database instance once every minute, and writes the timestamp, database instance name-database name, and number of used connections as a "database connection number" record into the "database connection number table" in the M2 data storage module of this system;

[0008] M2 data storage module: receive data from other modules, keep the last N days, automatically clean up the part beyond N days;

[0009] M3 connection number threshold calculation module: define "design connection number upper threshold", "design connection number lower threshold", "observation connection number upper threshold" and "observation connection number lower threshold" for each database; in the initial state, for a database, "design connection number upper threshold" is equal to "observation connection number upper threshold"; "design connection number lower threshold" is equal to "observation connection number lower threshold";

[0010] Let A = {a0, a1, …, a n} represent all databases in a database instance, and the design connection number upper threshold for a database instance is T u , and in the initial state, the design connection number upper threshold for each database is

[0011] Assume that the value of the "used connection number" field of the i-th record in the "database connection number" record of a database in the last N days is x i , the average used connection number of this database can be calculated as , and the standard deviation of the used connection number is The sum of the average used connection number and the standard deviation of the used connection number is used as the observation connection number upper threshold The average value minus the standard deviation is used as the observation connection number lower threshold The timestamp, database instance name-database name, observation connection number upper threshold, and observation connection number lower threshold are written as a record into the "database observation connection number threshold table" in the M2 data storage module;

[0012] When the observation connection number upper threshold of a certain database is higher than its design connection number upper threshold and lasts for 3 sampling periods, send an application-level alarm to the M4 alarm module; when the observation connection number lower threshold of a certain database is lower than its design connection number lower threshold and lasts for 3 sampling periods, send an application-level alarm to the M4 alarm module;

[0013] M4 alarm module: define cluster administrators and application administrators, send cluster-level alarms to cluster administrators, and send application-level alarms to application administrators;

[0014] M5 design connection number threshold dynamic adjustment module: limit the dynamic adjustment step of the design connection number upper threshold

[0015] Assume that the database instance accesses the system and initializes at t = 0, at this time

[0016] At a certain moment t i (t i >0), database a x The upper threshold of the number of observed connections is when When , find the minimum value from the upper threshold of the number of observed connections of the remaining databases if and and Then the threshold can be dynamically adjusted: Increase δ and Reduce δ;

[0017] For databases whose design connection limit threshold has changed, the timestamp, database instance name-database name, and design connection limit threshold are written into the "Database Connection Limit Table" in the M2 data storage module;

[0018] Otherwise, it is determined that dynamic threshold adjustment cannot be performed, and a cluster-level alarm is sent to the alarm module;

[0019] M6 connection capacity prediction module: For each database, the observed connection upper limit threshold of the last N days is selected every day, and the designed connection upper limit threshold for each day in the next N days is calculated using a linear regression algorithm. Each predicted database, date, and designed connection upper limit threshold is written as a record in the M2 data storage module; the connection upper limit threshold for each database in the next N days is graphically displayed.

[0020] Furthermore, N is 14 days.

[0021] Furthermore, the M1 data preprocessing module supports docking with different types of database instances. After processing, the connection number of each database in different types of database instances is unified in format.

[0022] The present invention provides a system for detecting anomalies in the number of database connections, adjusting the upper limit, and predicting usage, including: unifying the number of connections of each database in different types of database instances into a unified format, precalculating the number of databases planned to be carried in the database instance and the total capacity of database connections according to the computing resource configuration (memory, number of CPUs) of the database cluster; dividing the total capacity of connections by the number of databases planned to be carried to obtain the initial number of connections planned to be allocated to a single database; defining a "designed upper limit threshold for the number of connections," a "designed lower limit threshold for the number of connections," an "observed upper limit threshold for the number of connections," and an "observed lower limit threshold for the number of connections" for each database in a connection threshold calculation module. For each database, the "observed upper limit threshold for the number of connections" and the "observed lower limit threshold for the number of connections" are monitored, and application-level alarms can be triggered. Dynamic adjustment of the design upper limit threshold for the number of connections of each database in the database instance is implemented in a design connection threshold dynamic adjustment module; using a linear regression method in machine learning to predict the "observed upper limit threshold for the number of connections" of each database in the next 14 days, and performing a visual display. Through this system and the method proposed in this article, the number of connections to each database in a database instance can be monitored, dynamically adjusted, and predicted. This can mainly solve the following problems: 1) the problem of database service interruption due to exhaustion of database instance and database connection resources; 2) the problem of unreasonable allocation of database instance connection numbers; and 3) the problem of unpredictable usage of the connection pool in a database instance in the next 14 days. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Figure 1 This is a flowchart of the existing database connection pool anomaly detection method;

[0024] Figure 2 The present invention is a schematic diagram of the program execution flow of a system for detecting abnormalities in the number of database connections, adjusting the upper limit, and predicting usage. DETAILED DESCRIPTION

[0025] In order to better understand the technical solution of the present invention, the following is a detailed description through specific embodiments:

[0026] See also Figure 2 The present invention provides a system for detecting database connection anomalies, adjusting upper limits, and predicting usage. This system aims to provide a feasible method for dynamically allocating connections within a database instance and a method for predicting the number of connections for each database within the instance over the next 14 days. It specifically includes the following modules:

[0027] M1 data preprocessing module: for the database instance accessing the system, read the connection number of each database in the database instance every 1 minute. M1 data preprocessing module supports different kinds of database instance connection, after processing, the connection number of each database in different types of database instance is unified format, and the timestamp, database instance name-database name, used connection number are written as a "database connection number" record to the "database connection number table" in the M2 data storage module of the system.

[0028] M2 data storage module: receiving data from other modules, retaining the latest N days, and automatically cleaning up the part exceeding N days. N is 14 days.

[0029] M3 connection number threshold calculation module: for each database, define "design connection number upper threshold", "design connection number lower threshold", "observation connection number upper threshold" and "observation connection number lower threshold"; in the initial state, for a database, "design connection number upper threshold" is equal to "observation connection number upper threshold"; "design connection number lower threshold" is equal to "observation connection number lower threshold". Assume that a database instance plans to carry n databases, the value of n is related to the computing resource configuration of the virtual machine where the database instance is located, and it is assumed that each database plans to allocate 1 CPU and 2 GB memory resources, if the computing resources of the virtual machine cannot guarantee the CPU and memory 1:2 relationship, the number of databases is planned according to the insufficient resources. For example, a 16-core 30G memory virtual machine, in order to ensure the stable operation of the business, usually only 15 databases are planned.

[0030] Let A = {a0, a1, …, a n} represent all databases in a database instance, for a database instance, the design connection number upper threshold is T u , in the initial state, the design connection number upper threshold of each database is

[0031] For each database, this method studies all "database connection number" records in the last 14 days. Assume that the value of "used connection number" field of the i-th record in the "database connection number" record of a certain database in the last 14 days is x i , the average value of the used connection number of this database can be calculated as , and the standard deviation of the used connection number is The sum of the average value and the standard deviation of the used connection number is used as the observation connection number upper threshold (Upper threshold) The average value minus the standard deviation is used as the observation connection number lower threshold (Lower threshold) The timestamp, database instance name-database name, upper threshold of observed connection number, and lower threshold of observed connection number are written as a record into the "Database Observed Connection Number Threshold Table" in the M2 data storage module.

[0032] When the upper limit threshold of the observed number of connections of a database is higher than the upper limit threshold of its designed number of connections and lasts for a period of time, an application-level alarm is sent to the M4 alarm module; when the lower limit threshold of the observed number of connections of a database is lower than the lower limit threshold of its designed number of connections and lasts for a period of time, an application-level alarm is sent to the M4 alarm module.

[0033] M4 alarm module: defines cluster administrators and application administrators, sends cluster-level alarms to cluster administrators, and sends application-level alarms to application administrators.

[0034] M5 design connection number threshold dynamic adjustment module: limit the dynamic adjustment step of the upper threshold of the design connection number

[0035] Assume that the database instance is connected to the system and initialized at t = 0.

[0036] At a certain moment t i (t i >0), database a x The upper threshold of the number of observed connections is when When , find the minimum value from the upper threshold of the number of observed connections of the remaining databases if and and Then the threshold can be dynamically adjusted: Increase δ and Reduce δ;

[0037] For databases whose design connection limit threshold has changed, the timestamp, database instance name-database name, and design connection limit threshold are written into the "Database Connection Limit Table" in the M2 data storage module;

[0038] Otherwise, it is determined that dynamic adjustment of the threshold cannot be performed, and a cluster-level alarm is sent to the alarm module.

[0039] M6 connection capacity prediction module: For each database, the observed connection upper threshold value for the past 14 days is selected every day. The designed connection upper threshold value for each day in the next 14 days is calculated using a linear regression algorithm. Each predicted database, date, and designed connection upper threshold value is written as a record into the M2 data storage module. The connection upper threshold value for each database in the next 14 days is graphically displayed.

[0040] Those skilled in the art should recognize that the above embodiments are merely intended to illustrate the present invention and are not intended to limit the present invention. As long as they are within the spirit of the present invention, any changes or modifications to the above embodiments will fall within the scope of the claims of the present invention.

Claims

1. A system for detecting abnormalities in the number of database connections, adjusting upper limits, and predicting usage, characterized in that: Includes the following modules: M1 data preprocessing module: For database instances connected to this system, it reads the number of connections corresponding to each database in the database instance once every sampling period and writes a "Database Connection Number" record with the timestamp, database instance name-database name, and the number of used connections into the "Database Connection Number Table" in the M2 data storage module of this system. M2 data storage module: receives data from other modules, retains the latest N days of data, and automatically cleans up the data older than N days; M3 connection number threshold calculation module: For each database, define the "design connection number upper threshold", "design connection number lower threshold", "observation connection number upper threshold" and "observation connection number lower threshold"; In the initial state, for a database, the "designed connection number upper threshold" is equal to the "observed connection number upper threshold"; the "designed connection number lower threshold" is equal to the "observed connection number lower threshold"; Use the set A={a0,a1,…,a n } represents all databases in a database instance. For a database instance, the upper limit of the number of connections is designed to be T u , in the initial state, the upper limit threshold of the number of connections designed for each database is Assume that the value of the "Number of Used Connections" field in the i-th record of the "Number of Database Connections" record of a database in the past N days is x i , you can calculate the average number of connections used by this database and the standard deviation of the number of used connections The sum of the average number of used connections and the standard deviation of the used connections is used as the upper threshold of the observed connection number. Use the mean minus the standard deviation as the lower limit threshold of the number of observed connections Write the timestamp, database instance name - database name, upper threshold of observed connection number, and lower threshold of observed connection number as a record to the "Database Observed Connection Number Threshold Table" in the M2 data storage module; When the upper threshold of the observed number of connections of a database exceeds the upper threshold of its designed number of connections and lasts for three sampling periods, an application-level alarm is sent to the M4 alarm module; When the lower limit threshold of the number of observed connections of a database is lower than the lower limit threshold of its designed number of connections and lasts for three sampling periods, an application-level alarm is sent to the M4 alarm module; M4 alarm module: defines cluster administrators and application administrators, sends cluster-level alarms to cluster administrators, and sends application-level alarms to application administrators; M5 design connection number threshold dynamic adjustment module: limit the dynamic adjustment step of the upper threshold of the design connection number Assume that the database instance is connected to the system and initialized at t = 0. At a certain moment t i (t i >0), database a x The upper threshold of the number of observed connections is when When , find the minimum value from the upper threshold of the number of observed connections of the remaining databases if and and Then the threshold can be dynamically adjusted: Increase δ and Reduce δ; For databases whose designed upper limit of connection number has changed, the timestamp, database instance name-database name, and designed upper limit of connection number are written into the "Database Connection Number Threshold Table" in the M2 data storage module. Otherwise, it is determined that dynamic threshold adjustment cannot be performed, and a cluster-level alarm is sent to the alarm module; M6 connection capacity prediction module: For each database, the observed connection upper limit threshold of the last N days is selected every day, and the designed connection upper limit threshold for each day in the next N days is calculated using a linear regression algorithm. Each predicted database, date, and designed connection upper limit threshold is written as a record in the M2 data storage module; the connection upper limit threshold for each database in the next N days is graphically displayed.

2. A system for detecting abnormalities in the number of database connections, adjusting upper limits, and predicting usage according to claim 1, characterized in that: N is 14 days.

3. According to the system for detecting anomalies in the number of database connections, adjusting the upper limit, and predicting usage according to claim 1, the M1 data preprocessing module supports docking with different types of database instances. After processing, the connection number of each database in different types of database instances is unified in format.

Images