A database adjustment method, device, electronic device and storage medium

Abstract

An embodiment of the present invention provides a database adjustment method, apparatus, electronic device, and storage medium. The method includes: allocating a query request to a database, which can be allocated to the database for processing so that the query request is processed; by determining the current processing data volume and operating status of the database, the current processing data volume and operating status of the database can be obtained, and it can be determined whether it is necessary to increase or decrease the database for processing analytical requests; according to the current processing data volume and operating status of the database, adjusting the number of sub-databases in the database can improve the processing speed of analytical requests and avoid excessive occupation of database storage space.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and particularly to a database adjustment method, apparatus, electronic device, and computer-readable storage medium. Background Art

[0002] With the rapid development of computer technology, the volume of business databases is increasing, resulting in an increasing demand for queries. When the database processes different types of query requests simultaneously, the processing speed of some query requests is relatively slow, leading to a relatively slow overall processing speed of the database. Moreover, each time a certain type of query is performed on the same data in the database, it needs to be stored once, causing excessive occupation of the database storage space. Summary of the Invention

[0003] The purpose of the embodiments of the present invention is to provide a database adjustment method, apparatus, electronic device, and storage medium to improve the speed of the database in processing query requests and avoid excessive occupation of the database storage space. The specific technical solutions are as follows:

[0004] In the first aspect of the present invention, a database adjustment method is first provided, including:

[0005] Allocating query requests to the database; the query requests include transactional requests or analytical requests;

[0006] Determining the current processing data volume and operating state of the database;

[0007] Adjusting the number of sub-databases in the database according to the current processing data volume and operating state of the database.

[0008] Optionally, the operating state includes the number of slow queries per minute on average and the slow query mode;

[0009] The step of determining the current processing data volume and operating state of the database includes:

[0010] Determining the number of slow queries per minute on average; the slow query is a query request that takes more time than a preset second threshold;

[0011] In the slow queries, if the proportion of analytical requests is greater than a preset third threshold, determining the slow query mode as an analytical query.

[0012] Optionally, the step of allocating query requests to the database includes:

[0013] Allocating query requests to a second sub-database for processing transactional requests and analytical requests;

[0014] The step of adjusting the number of sub - databases in the database according to the current processing data volume and running status of the database includes:

[0015] When the current processing data volume of the database is greater than a preset first threshold, and the average number of slow queries per minute of the database is greater than a preset fourth threshold, and the pattern of the slow queries conforms to analytical queries, create a first sub - database for processing analytical requests in the database and copy all the data in the second sub - database to the first sub - database.

[0016] Optionally, the step of allocating query requests to the database includes:

[0017] Allocate query requests to the first sub - database for processing analytical requests and the second sub - database for processing transactional requests;

[0018] The step of adjusting the number of sub - databases in the database according to the current processing data volume and running status of the database includes:

[0019] When the current processing data volume of the database is less than or equal to the preset first threshold, or the average number of slow queries per minute of the database is less than or equal to the preset fourth threshold, or the pattern of the slow queries conforms to analytical queries, delete the first sub - database for processing analytical requests in the database.

[0020] Optionally, the step of allocating query requests to the database includes:

[0021] When the first sub - database and the second sub - database are running simultaneously, determine whether the query request is a transactional request or an analytical request;

[0022] When the query request is an analytical request, allocate the query request to the first sub - database for processing analytical requests.

[0023] Optionally, after the step of creating a first sub - database for processing analytical requests in the database and copying all the data in the second sub - database to the first sub - database when the current processing data volume of the database is greater than a preset first threshold, and the average number of slow queries per minute of the database is greater than a preset fourth threshold, and the pattern of the slow queries conforms to analytical queries, further includes:

[0024] When it is detected that there is incremental data in the second sub - database, synchronize the incremental data of the second sub - database to the first sub - database in real - time.

[0025] Optionally, when the first sub-database and the second sub-database are running simultaneously, the step of determining whether the query request is a transactional request or an analytical request includes:

[0026] If the cost required for the second sub-database to process the query request is less than the cost required for the first sub-database to process the query request, determine that the query request is a transactional request;

[0027] If the cost required for the second sub-database to process the query request is greater than the cost required for the first sub-database to process the query request, determine that the query request is an analytical request.

[0028] In the second aspect of the implementation of the present invention, there is also provided a database adjustment device, including:

[0029] An allocation module, configured to allocate query requests to the database; the query requests include transactional requests or analytical requests;

[0030] A determination module, configured to determine the current processing data volume and the running state of the database;

[0031] An adjustment module, configured to adjust the number of sub-databases in the database according to the current processing data volume and the running state of the database.

[0032] Optionally, the running state includes the number of slow queries per minute on average and the slow query mode;

[0033] The determination module includes:

[0034] A first determination sub-module, configured to determine the number of slow queries per minute on average; the slow query is a query request whose time consumption exceeds a preset second threshold;

[0035] A second determination sub-module, configured to determine that the slow query mode is an analytical query if the proportion of analytical requests in the slow queries is greater than a preset third threshold.

[0036] Optionally, the allocation module includes:

[0037] A first allocation sub-module, configured to allocate query requests to the second sub-database for processing transactional requests and analytical requests;

[0038] The adjustment module includes:

[0039] Create a sub-module, which is used to create a first sub-database for processing analytical requests in the database and copy all the data in the second sub-database to the first sub-database when the current processed data volume of the database is greater than a preset first threshold, the average number of slow queries per minute of the database is greater than a preset fourth threshold, and the pattern of the slow queries conforms to analytical queries.

[0040] Optionally, the allocation module includes:

[0041] A second allocation sub-module, which is used to allocate query requests to a first sub-database for processing analytical requests and a second sub-database for processing transactional requests;

[0042] The adjustment module includes:

[0043] A deletion sub-module, which is used to delete the first sub-database for processing analytical requests in the database when the current processed data volume of the database is less than or equal to a preset first threshold, or the average number of slow queries per minute of the database is less than or equal to a preset fourth threshold, or the pattern of the slow queries conforms to analytical queries.

[0044] Optionally, the allocation module includes:

[0045] A third determination sub-module, which is used to determine whether the query request is a transactional request or an analytical request when the first sub-database and the second sub-database are running simultaneously;

[0046] A third allocation sub-module, which is used to allocate the query request to the first sub-database for processing analytical requests when the query request is an analytical request.

[0047] Optionally, the device further includes:

[0048] A synchronization module, which is used to synchronize the incremental data of the second sub-database to the first sub-database in real time when it detects that there is incremental data in the second sub-database.

[0049] Optionally, the third determination sub-module includes:

[0050] A fourth determination sub-module, which is used to determine that the query request is a transactional request if the cost required for the second sub-database to process the query request is less than the cost required for the first sub-database to process the query request;

[0051] A fifth determination sub-module, which is used to determine that the query request is an analytical request if the cost required for the second sub-database to process the query request is greater than the cost required for the first sub-database to process the query request.

[0052] In yet another aspect of the implementation of the present invention, there is also provided a computer-readable storage medium storing instructions which, when run on a computer, cause the computer to execute any one of the above-described database adjustment methods.

[0053] In yet another aspect of the implementation of the present invention, there is also provided a computer program product containing instructions which, when run on a computer, cause the computer to execute any one of the above-described database adjustment methods.

[0054] A database adjustment method, apparatus, electronic device and storage medium provided by an embodiment of the present invention can process a query request by allocating the query request to a database, where the query request includes a transactional request or an analytical request, so that the query request can be processed; by determining the current processing data volume and running state of the database, the current processing data volume and running state of the database can be obtained, and it can be determined whether to increase or decrease the sub-databases for processing analytical requests; according to the current processing data volume and running state of the database, adjusting the number of sub-databases in the database can improve the processing speed of analytical requests and avoid over-occupying the database storage space at the same time. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art.

[0056] Figure 1 It is a flowchart of the steps of a database adjustment method provided in an embodiment of the present invention;

[0057] Figure 2 It is a flowchart of the steps of another database adjustment method provided in an embodiment of the present invention;

[0058] Figure 3 It is a schematic flowchart of a database adjustment process;

[0059] Figure 4 It is a schematic flowchart of a process for a hybrid transaction analysis database system to process query requests;

[0060] Figure 5 It is a block diagram of the structure of a database adjustment apparatus provided in an embodiment of the present invention;

[0061] Figure 6 It is a block diagram of an electronic device provided in an embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0062] The following will describe the technical solutions in the embodiments of the present invention with reference to the drawings in the embodiments of the present invention.

[0063] In view of the problems that the database mentioned in the above background art processes different types of query requests simultaneously, resulting in slow request processing speed and waste of storage space due to storing the same data multiple times, the present invention provides a database adjustment method. By allocating query requests to the database and adjusting the number of sub-databases in the database according to the current processed data volume and running state of the database, the waste of storage space can be reduced while efficiently processing a large number of transactional requests and large-scale analytical requests.

[0064] In order to reduce the waste of storage space while efficiently processing a large number of transactional requests and large-scale analytical requests, the present invention creates a system that can include a client, a database, and a controller. The client is mainly used for allocating query requests; the database is mainly used for processing query requests; the controller is mainly used for adjusting the number of sub-databases.

[0065] Refer to Figure 1 , which shows a step flowchart of a database adjustment method provided in an embodiment of the present invention, and specifically may include the following steps:

[0066] Step 101, allocate a query request to the database; the query request includes a transactional request or an analytical request;

[0067] In an embodiment of the present invention, when there is a query request to be processed on the client, the client allocates the query request to the database for processing. When the database receives the query request, it can first selectively cache the query request according to the actual situation. For a database system with frequent read and write operations, using a query cache may reduce the processing efficiency of queries. In this case, the query request may not be cached. Secondly, the database can parse the received query request, preprocess the query request, and formulate a corresponding query execution plan. Next, the database can call the storage engine to query data according to the execution plan and return the result to the client.

[0068] Among them, the query request may include a transactional request and an analytical request. The transactional request generally only needs to process current or short-term data and requires a relatively short processing time. The analytical request needs to analyze by integrating current data and a large amount of historical data and requires a relatively longer processing time compared to the transactional request.

[0069] Step 102, determine the current processed data volume and running state of the database;

[0070] In an embodiment of the present invention, during the process of the database processing query requests, there may be a situation where there are many query requests and the processing efficiency of analytical requests is relatively low. At this time, it may be considered to create a database specifically for processing analytical requests.

[0071] Therefore, in order to determine whether a database for processing analytical requests needs to be created or deleted, it is necessary to determine the number of query requests currently received by the database and the processing speed of analytical requests, that is, the current data processing volume and operating status of the database.

[0072] Among them, the database can be detected in real time to obtain the current data processing volume of the database; the operating status of the database can include the speed at which the database processes query requests, and the currently running database can be detected to determine its speed of processing query requests.

[0073] Step 103, adjust the number of sub-databases in the database according to the current data processing volume and operating status of the database.

[0074] In an embodiment of the present invention, in order to efficiently process large-scale analytical requests, the number of sub-databases in the database can be adjusted according to the current data processing volume and operating status of the database. Specifically, a first sub-database for processing analytical requests can be created in the database. In order to reduce waste of storage space, the first sub-database for processing analytical requests can be deleted in the database according to the current data processing volume and operating status of the database.

[0075] Specifically, when the number of query requests currently received by the database is large and the processing speed of the current analytical requests of the database is relatively slow, a first sub-database for processing analytical requests can be created in the database. When the number of query requests currently received by the database is small, or the processing speed of the current analytical requests is relatively fast, the first sub-database for processing analytical requests can be deleted in the database.

[0076] Through the database adjustment method of the embodiment of the present invention, query requests are allocated to the database, the current data processing volume and operating status of the database are determined, and the number of sub-databases in the database is adjusted according to the current data processing volume and operating status of the database, so that in the case of a large number of query requests and a relatively slow processing speed of analytical requests, the processing efficiency of analytical requests can be improved; in the case of a small number of query requests or a relatively fast processing speed of analytical requests, the first sub-database is deleted to reduce waste of storage space. This database adjustment method has a fast processing speed for analytical requests and reduces waste of storage space, solving the problems of low processing efficiency of analytical requests and waste of storage space.

[0077] Referring to Figure 2 , a step flowchart of another database adjustment method provided in an embodiment of the present invention is shown, which specifically may include the following steps:

[0078] Step 201, allocate a query request to the database; the query request includes a transactional request or an analytical request.

[0079] In an embodiment of the present invention, when there is a query request to be processed on the client, the client allocates the query request to the database for processing. Among them, the query request may include a transactional request and an analytical request. The transactional request generally only needs to process current or short-term data, and the required processing time is relatively short. The analytical request needs to comprehensively analyze current data and a large amount of historical data, and the required processing time is relatively long compared to the transactional request.

[0080] Specifically, when at least one client starts, it can first initiate registration to the controller, and the controller records the current client information in the statistics library to distinguish different clients. The client information may include a client identifier, such as a client ID, a client IP, a parameter indicating whether the client is online, and a parameter indicating whether the client has added an analytical request. After the client receives a query request, the controller can send the current status information of the database and the rules for judging the query request type to the client. Then, the client can allocate the query request to the database for processing according to the current status information of the database and the ability of the database to process the query request.

[0081] In an embodiment of the present invention, the step of allocating a query request to the database includes:

[0082] S11, allocate a query request to a second sub-database for processing transactional requests and analytical requests;

[0083] The client can allocate a query request to the second sub-database according to the current database status information. The current database status information may include two situations. One situation may be that only the second sub-database is running currently, and the other situation may be that the first sub-database and the second sub-database are running simultaneously currently. If the current database status information is that only the second sub-database is running, allocate the query request including both transactional requests and analytical requests to the second sub-database for processing.

[0084] In an embodiment of the present invention, in the state where only the second sub-database is running, when the client has a query request to be processed, it can allocate the query request to the second sub-database for processing transactional requests and analytical requests.

[0085] In an embodiment of the present invention, the step of allocating a query request to the database includes:

[0086] S21, allocate a query request to a first sub-database for processing analytical requests and a second sub-database for processing transactional requests;

[0087] In an embodiment of the present invention, when the client has a query request to process, it can allocate the query request to a first sub-database for processing analytical requests and a second sub-database for processing transactional requests. The client can allocate the query request according to the current database status information and the ability of the database to process different types of requests.

[0088] The ability of the database to process different types of requests is one of the considerations for selecting a sub-database. By comparing the ability of the database to process different types of requests, the query request is allocated to the sub-database with a stronger ability to process the query request. Specifically, the ability of the first sub-database and the second sub-database to process analytical requests can be compared. When the current database status information is that the first sub-database and the second sub-database are both running, and the ability of the first sub-database to process analytical requests is stronger than that of the second sub-database to process analytical requests, the analytical request is allocated to the first sub-database for processing, and the transactional request is allocated to the second sub-database for processing.

[0089] In an embodiment of the present invention, the step of allocating a query request to the database includes:

[0090] S31. When the first sub-database and the second sub-database are both running, determine whether the query request is a transactional request or an analytical request;

[0091] When the status of the database is that the first database and the second sub-database are both running, the client can first determine whether the received query request is a transactional request or an analytical request. After determining the type of the query request, according to the type of the query request, the transactional request is sent to the second sub-database for processing transactional requests, and the analytical request is sent to the first sub-database for processing analytical requests.

[0092] S32. When the query request is an analytical request, allocate the query request to the first sub-database for processing analytical requests.

[0093] When the client determines that the received query request is an analytical request, the analytical request can be sent to the first sub-database for processing analytical requests, so that the first sub-database processes the analytical request.

[0094] In an embodiment of the present invention, the step of determining whether the query request is a transactional request or an analytical request when the first sub-database and the second sub-database are both running includes:

[0095] S41. If the cost required for the second sub-database to process the query request is less than the cost required for the first sub-database to process the query request, determine that the query request is a transactional request;

[0096] After receiving a query request, the client can determine the type of the query request in order to decide whether to allocate the query request to the first sub-database or the second sub-database for processing. The type of the query request is determined by calculating and comparing the costs required for the first sub-database and the second sub-database to process the query request.

[0097] The cost required for a sub-database to process a query request is related to factors such as the average row and column size of the sub-database, the estimated number of rows, the I / O overhead for scanning data, and the number of columns involved in the query. Specifically, the cost required for a sub-database to process a query request can be represented by C, and can be calculated by the following formula:

[0098] C (first sub-database) = S tuple × N tupl × f scan

[0099] C (second sub-database) = sum (j＝1～m) (S col_j × N tuple × f scan )

[0100] Where

[0101] S tuple / col_j : The average row or column size (which can be estimated based on the statistical information of the table size);

[0102] N tuple : The estimated number of rows (which can be estimated based on the statistical information of the table size);

[0103] F scan : The I / O overhead for scanning data, usually a fixed value;

[0104] m: The number of columns involved in the query (which can be obtained by parsing the SQL syntax).

[0105] Specifically, after the client calculates the costs required for the first sub-database and the second sub-database to process the received query request, if the cost required for the second sub-database to process the query request is less than the cost required for the first sub-database, it can be determined that the query request is a transactional request;

[0106] S42. If the cost required for the second sub-database to process the query request is greater than the cost required for the first sub-database to process the query request, determine that the query request is an analytical request.

[0107] Specifically, after the client calculates the costs of the first sub-database and the second sub-database for processing the received query request, if the cost required for the second sub-database to process the query request is greater than the cost required for the first sub-database, it can be determined that the query request is an analytical request, and the query request is assigned to the first sub-database for processing.

[0108] Step 202, determine the current processing data volume and operating status of the database.

[0109] In an embodiment of the present invention, during the process of the database processing query requests, there may be a situation where there are many query requests and the processing efficiency of analytical requests is relatively low. At this time, a database specifically used for processing analytical requests can be considered to be created.

[0110] Therefore, in order to determine whether to create or delete a database for processing analytical requests, it is necessary to determine the number of query requests currently received by the database and the processing speed of analytical requests, that is, the current processing data volume and operating status of the database.

[0111] Among them, the database can be detected in real time to obtain the current processing data volume of the database; the operating status of the database can include the speed at which the database processes query requests. The currently running database can be detected to determine its speed of processing query requests.

[0112] The current processing data volume of the database can be the number of query requests that the database currently needs to process. When only the second sub-database is running, the current processing data volume of the database can be the number of query requests currently processed by the second sub-database; when the first sub-database and the second sub-database are running simultaneously, the current processing data volume of the database can be the total number of query requests currently processed by the first sub-database and the second sub-database.

[0113] The current processing data volume of the database can be collected by the controller. The controller stores the collected current processing data volume of the database in the statistical database. When it is necessary to determine whether to create or delete the database, the current processing data volume of the database is extracted from the statistical database.

[0114] In an embodiment of the present invention, the step of determining the current processing data volume and operating status of the database includes:

[0115] S51, determine the number of slow queries per minute on average; the slow query is a query request that takes more time than a preset second threshold;

[0116] The operating state may include the number of slow queries per minute on average and the pattern of slow queries. The number of slow queries per minute on average of the database can be collected by a controller, and the controller stores the collected number of slow queries per minute on average of the database in a statistics database. When it is necessary to determine whether to create or delete a database, the number of slow queries per minute on average of the database is extracted from the statistics database.

[0117] The slow query may be a query request that takes more time than a preset second threshold. The slow query may be a transactional request that takes more time than a preset second threshold, or an analytical request that takes more time than a preset second threshold. For example, the second threshold may be 1s, 5s, 10s, etc., and the present invention does not limit this.

[0118] S52, in the slow query, if the proportion of analytical requests occupied is greater than a preset third threshold, determine that the pattern of the slow query is an analytical query.

[0119] Since the slow query may include transactional requests and analytical requests, the pattern of the slow query may include transactional queries and analytical queries. When the proportion of analytical requests occupied is greater than a preset third threshold, it can be determined that the pattern of the slow query is an analytical query. For example, the third threshold may be 1 / 2, 2 / 3, etc., and the present invention does not limit this.

[0120] Step 203, adjust the number of sub-databases in the database according to the current processing data volume and the operating state of the database.

[0121] In an embodiment of the present invention, in order to efficiently process a large number of analytical requests, the number of sub-databases in the database can be adjusted according to the current processing data volume and the operating state of the database. Specifically, a first sub-database for processing analytical requests can be created in the database. In order to reduce waste of storage space, the first sub-database for processing analytical requests can be deleted in the database according to the current processing data volume and the operating state of the database.

[0122] Specifically, when the number of query requests currently received by the database is large, and the processing speed of the current analytical requests of the current database is relatively slow, a first sub-database for processing analytical requests can be created in the database. When the number of query requests currently received by the database is small, or the processing speed of the current analytical requests is relatively fast, the first sub-database for processing analytical requests can be deleted in the database.

[0123] In an embodiment of the present invention, the step of adjusting the number of sub-databases in the database according to the current processing data volume and the operating state of the database includes:

[0124] S61, when the current processed data volume of the database is greater than a preset first threshold, and the average number of slow queries per minute of the database is greater than a preset fourth threshold, and the pattern of the slow queries conforms to analytical queries, create a first sub-database for processing analytical requests in the database and copy all the data in the second sub-database to the first sub-database in full volume.

[0125] In an embodiment of the present invention, when the current processed data volume of the database is greater than a preset first threshold, and the average number of slow queries per minute of the database is greater than a preset fourth threshold, and the pattern of the slow queries conforms to analytical queries, create a first sub-database for processing analytical requests in the database, and all the data in the second sub-database can be copied to the first sub-database in full volume so that the first sub-database analyzes the data according to the analytical requests. For example, the first threshold may be 1GB, 5GB, 100GB, 500GB, etc., and the present invention does not limit this. The fourth threshold may be 100, 200, 500, etc., and the present invention does not limit this. For example, when the current processed data volume of the database is greater than 500GB, and the average number of slow queries per minute of the database is greater than 100, and the pattern of the slow queries conforms to analytical queries, create a first sub-database for processing analytical requests in the database and copy all the data in the second sub-database to the first sub-database in full volume.

[0126] In an embodiment of the present invention, the step of adjusting the number of sub-databases in the database according to the current processed data volume and the running state of the database includes:

[0127] S71, when the current processed data volume of the database is less than or equal to a preset first threshold, or the average number of slow queries per minute of the database is less than or equal to a preset fourth threshold, or the pattern of the slow queries conforms to analytical queries, delete the first sub-database for processing analytical requests in the database.

[0128] In an embodiment of the present invention, in order to reduce the waste of storage space, the first sub-database for processing analytical requests can be deleted in the database according to the current processed data volume and the running state of the database.

[0129] Specifically, during the process of the first sub-database processing an analytical request, when the current processed data volume of the database is less than or equal to a preset first threshold, or the average number of slow queries per minute of the database is less than or equal to a preset fourth threshold, or the pattern of the slow queries conforms to an analytical query, the first sub-database used for processing the analytical request is deleted in the database. For example, when the current processed data volume of the database is less than or equal to 500 GB, or the average number of slow queries per minute of the database is less than or equal to 100, or the pattern of the slow queries conforms to an analytical query, the first sub-database used for processing the analytical request is deleted in the database.

[0130] Step 204, when it is detected that there is incremental data in the second sub-database, the incremental data of the second sub-database is synchronously updated to the first sub-database in real time.

[0131] In an embodiment of the present invention, after the first sub-database is created, all the data of the second sub-database can be fully copied to the first sub-database for use when the first database processes analytical requests. During the operation of the first sub-database, if it is detected that there is incremental data in the second sub-database, the incremental data can be synchronously updated to the first sub-database, so that the first sub-database can analyze the newly added data in a timely manner when processing analytical requests, ensuring that the analysis results are more comprehensive and accurate.

[0132] In a specific implementation, the first sub-database can first use an engine to perform a full copy of the data in the second sub-database, and then use the engine to monitor the changes in the data of the second sub-database in real time. When incremental data appears, the incremental data is synchronously updated in the first sub-database. For example, the first sub-database can use the MaterializeMySQL engine, and MaterializeMySQL supports full copy and incremental synchronization. When the database engine is first created, a full copy of the data in the second sub-database can be performed, and then incremental data synchronization is performed by monitoring the real-time binlog log changes of the second sub-database.

[0133] As a specific example of the present invention, Figure 3 is a schematic diagram of a database adjustment process, as shown in Figure 3As shown, the second sub-database can be MySQL, which is a relational database management system; the first sub-database can be ClickHouse, which is a columnar database management system for online analysis. First, in the state where only MySQL is running, it can be determined whether the condition for adding a database is met. If the condition for adding a database is met, the ClickHouse database can be added; if the condition for adding a database is not met, the state where only MySQL is running is maintained. Next, in the state where MySQL and ClickHouse are running simultaneously, it can be determined whether the condition for reducing a database is met. If the condition for reducing a database is met, the ClickHouse database can be deleted; if the condition for reducing a database is not met, the state where MySQL and ClickHouse are running simultaneously is maintained.

[0134] As a specific example of the present invention, Figure 4 is a schematic flowchart of a hybrid transactional and analytical database system for processing query requests, as Figure 4 shown, the second sub-database can be MySQL, and the first sub-database can be ClickHouse. First, in step 401, when the client starts, it can first initiate registration to the controller, and the controller records the current client information in the statistics database; in step 402, the controller sends the current status information of the database and the rules for judging the query request type to the client. Then, the client distributes the query request to MySQL and / or ClickHouse for processing according to the current status information of the database and the cost required for the database to process the query request. In step 403, ClickHouse can synchronize the data of MySQL in real time. Finally, in step 404, the controller can collect information such as the current processed data volume, the average number of slow queries per minute, and the slow query pattern of MySQL and ClickHouse and store them in the statistics database.

[0135] Through the database adjustment method of the embodiments of the present invention, a query request is allocated to the database, the current processing data volume and the running state of the database are determined, and according to the current processing data volume and the running state of the database, the number of sub-databases in the database is adjusted, so that when there are many query requests and the processing speed of analytical requests is slow, the processing efficiency of analytical requests can be improved; when there are few query requests or the processing speed of analytical requests is fast, the first sub-database is deleted to reduce the waste of storage space. When it is detected that there is incremental data in the second sub-database, the incremental data of the second sub-database is synchronously updated to the first sub-database in real time, so that the first sub-database can analyze the newly added data in time when processing analytical requests, ensuring that the analysis results are more comprehensive and accurate. This database adjustment method has a fast processing speed for analytical requests and reduces the waste of storage space, solving the problems of low processing efficiency of analytical requests and wasted storage space.

[0136] Refer to Figure 5 , which shows the structural block diagram of a database adjustment device provided in the embodiments of the present invention, and specifically may include the following modules:

[0137] An allocation module 501, configured to allocate a query request to the database; the query request includes a transactional request or an analytical request;

[0138] A determination module 502, configured to determine the current processing data volume and the running state of the database;

[0139] An adjustment module 503, configured to adjust the number of sub-databases in the database according to the current processing data volume and the running state of the database.

[0140] Optionally, the running state includes the number of slow queries per minute on average and the slow query mode;

[0141] The determination module includes:

[0142] A first determination sub-module, configured to determine the number of slow queries per minute on average; the slow query is a query request that takes more time than a preset second threshold;

[0143] A second determination sub-module, configured to determine that the slow query mode is an analytical query if the proportion of analytical requests in the slow queries is greater than a preset third threshold.

[0144] Optionally, the allocation module includes:

[0145] A first allocation sub-module, configured to allocate a query request to a second sub-database for processing transactional requests and analytical requests;

[0146] The adjustment module includes:

[0147] Create a sub-module to create a first sub-database for processing analytical requests in the database and copy all the data in the second sub-database to the first sub-database when the current processed data volume of the database is greater than a preset first threshold, the average number of slow queries per minute in the database is greater than a preset fourth threshold, and the pattern of the slow queries conforms to analytical queries.

[0148] Optionally, the allocation module includes:

[0149] A second allocation sub-module for allocating query requests to a first sub-database for processing analytical requests and a second sub-database for processing transactional requests;

[0150] The adjustment module includes:

[0151] A deletion sub-module for deleting the first sub-database for processing analytical requests in the database when the current processed data volume of the database is less than or equal to a preset first threshold, or the average number of slow queries per minute in the database is less than or equal to a preset fourth threshold, or the pattern of the slow queries conforms to analytical queries.

[0152] Optionally, the allocation module includes:

[0153] A third determination sub-module for determining whether the query request is a transactional request or an analytical request when the first sub-database and the second sub-database are running simultaneously;

[0154] A third allocation sub-module for allocating the query request to the first sub-database for processing analytical requests when the query request is an analytical request.

[0155] Optionally, the device further includes:

[0156] A synchronization module for synchronizing the incremental data of the second sub-database to the first sub-database in real time when incremental data is detected in the second sub-database.

[0157] Optionally, the third determination sub-module includes:

[0158] A fourth determination sub-module for determining that the query request is a transactional request if the cost required for the second sub-database to process the query request is less than the cost required for the first sub-database to process the query request;

[0159] A fifth determination sub-module for determining that the query request is an analytical request if the cost required for the second sub-database to process the query request is greater than the cost required for the first sub-database to process the query request.

[0160] Through the database adjustment device according to the embodiments of the present invention, a query request is allocated to the database, the current processing data volume and the running state rate of the database are determined, and according to the current processing data volume and the running state of the database, a first sub-database for processing analytical requests is created or deleted in the database, so that in the case of a large number of query requests and a slow processing speed of analytical requests, the processing efficiency of analytical requests can be improved; in the case of a small number of query requests or a fast processing speed of analytical requests, the first sub-database is deleted, reducing the waste of storage space. This database adjustment method has a fast processing speed for analytical requests and reduces the waste of storage space, solving the problems of low processing efficiency of analytical requests and waste of storage space.

[0161] For the device embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and for the relevant parts, refer to the partial description of the method embodiments.

[0162] Embodiments of the present invention also provide an electronic device, as Figure 6 shown, including a processor 601, a communication interface 602, a memory 603, and a communication bus 604, where the processor 601, the communication interface 602, and the memory 603 complete communication with each other through the communication bus 604,

[0163] The memory 603 is used to store a computer program;

[0164] When the processor 601 is used to execute the program stored in the memory 603, the following steps are implemented:

[0165] Allocate a query request to the database; the query request includes a transactional request or an analytical request;

[0166] Determine the current processing data volume and the running state of the database;

[0167] Adjust the number of sub-databases in the database according to the current processing data volume and the running state of the database.

[0168] Optionally, the running state includes the number of slow queries per minute on average and the slow query mode;

[0169] The step of determining the current processing data volume and the running state of the database includes:

[0170] Determine the number of slow queries per minute on average; the slow query is a query request that takes more time than a preset second threshold;

[0171] In the slow query, if the proportion of analytical requests is greater than a preset third threshold, determine that the mode of the slow query is an analytical query.

[0172] Optionally, the step of allocating query requests to the database includes:

[0173] Allocate query requests to a second sub-database for processing transactional requests and analytical requests;

[0174] The step of adjusting the number of sub-databases in the database according to the current processing data volume and operating status of the database includes:

[0175] When the current processing data volume of the database is greater than a preset first threshold, and the average number of slow queries per minute of the database is greater than a preset fourth threshold, and the mode of the slow query conforms to an analytical query, create a first sub-database for processing analytical requests in the database and copy all the data in the second sub-database to the first sub-database.

[0176] Optionally, the step of allocating query requests to the database includes:

[0177] Allocate query requests to a first sub-database for processing analytical requests and a second sub-database for processing transactional requests;

[0178] The step of adjusting the number of sub-databases in the database according to the current processing data volume and operating status of the database includes:

[0179] When the current processing data volume of the database is less than or equal to a preset first threshold, or the average number of slow queries per minute of the database is less than or equal to a preset fourth threshold, or the mode of the slow query conforms to an analytical query, delete the first sub-database for processing analytical requests in the database.

[0180] Optionally, the step of allocating query requests to the database includes:

[0181] When the first sub-database and the second sub-database are running simultaneously, determine whether the query request is a transactional request or an analytical request;

[0182] When the query request is an analytical request, allocate the query request to the first sub-database for processing analytical requests.

[0183] Optionally, after the step of creating a first sub-database for processing analytical requests in the database and copying all the data in the second sub-database to the first sub-database when the current processed data volume of the database is greater than a preset first threshold, the average number of slow queries per minute of the database is greater than a preset fourth threshold, and the pattern of the slow queries conforms to analytical queries, the method further includes:

[0184] When it is detected that there is incremental data in the second sub-database, the incremental data of the second sub-database is synchronously copied to the first sub-database in real time.

[0185] Optionally, the step of determining whether the query request is a transactional request or an analytical request when the first sub-database and the second sub-database are running simultaneously includes:

[0186] If the cost required for the second sub-database to process the query request is less than the cost required for the first sub-database to process the query request, it is determined that the query request is a transactional request;

[0187] If the cost required for the second sub-database to process the query request is greater than the cost required for the first sub-database to process the query request, it is determined that the query request is an analytical request.

[0188] The communication bus mentioned in the above terminal may be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. This communication bus can be divided into an address bus, a data bus, a control bus, etc. For the sake of convenience of representation, only a thick line is used in the figure, but it does not mean that there is only one bus or one type of bus.

[0189] The communication interface is used for communication between the above terminal and other devices.

[0190] The memory may include a Random Access Memory (RAM), and may also include a non-volatile memory, such as at least one disk memory. Optionally, the memory may also be at least one storage device located far from the aforementioned processor.

[0191] The above-mentioned processor may be a general-purpose processor, including a Central Processing Unit (CPU for short), a Network Processor (NP for short), etc.; it may also be a Digital Signal Processor (DSP for short), an Application Specific Integrated Circuit (ASIC for short), a Field-Programmable Gate Array (FPGA for short), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0192] In another embodiment provided by the present invention, there is also provided a computer-readable storage medium storing instructions, which when run on a computer, cause the computer to execute any one of the database adjustment methods described in the above embodiments.

[0193] In another embodiment provided by the present invention, there is also provided a computer program product containing instructions, which when run on a computer, cause the computer to execute any one of the database adjustment methods described in the above embodiments.

[0194] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware, or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the processes or functions described in the embodiments of the present invention are generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The computer instructions may be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions may be transmitted from one website, computer, server, or data center to another website, computer, server, or data center in a wired manner (such as coaxial cable, optical fiber, Digital Subscriber Line (DSL)) or a wireless manner (such as infrared, wireless, microwave, etc.). The computer-readable storage medium may be any available medium that can be accessed by a computer or a data storage device such as a server, data center, etc. that includes one or more integrated available media. The available medium may be a magnetic medium (such as a floppy disk, hard disk, magnetic tape), an optical medium (such as a DVD), or a semiconductor medium (such as a Solid State Disk (SSD)).

[0195] It should be noted that in this text, relational terms such as first and second are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the term "comprising", "including" or any other variant thereof is intended to cover non-exclusive inclusion, such that a process, method, article or device comprising a series of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article or device. Without further limitation, an element defined by the statement "comprising an..." does not exclude the presence of additional identical elements in the process, method, article or device comprising the element.

[0196] Each embodiment in this specification is described in a related manner. For the same or similar parts among the embodiments, reference can be made to each other, and the differences between each embodiment and other embodiments are emphasized. In particular, for system embodiments, since they are basically similar to method embodiments, the description is relatively simple, and reference can be made to the relevant parts of the method embodiments for the relevant content.

[0197] The above are only the preferred embodiments of the present invention and are not intended to limit the protection scope of the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principle of the present invention are included in the protection scope of the present invention.

Claims

1. A database adjustment method, characterized in that, the method includes: Allocating query requests to the database; the query requests include transactional requests or analytical requests; Determining the current processing data volume and operating status of the database; Adjusting the number of sub-databases in the database according to the current processing data volume and operating status of the database; The operating status includes the number of slow queries per minute on average and the pattern of slow queries; The step of determining the current processing data volume and operating status of the database includes: Determining the number of slow queries per minute on average; the slow query is a query request that takes more time than a preset second threshold; Among the slow queries, if the proportion of analytical requests is greater than a preset third threshold, determining the pattern of the slow queries as analytical queries.

2. The method according to claim 1, characterized in that, the step of allocating query requests to the database includes: Allocating query requests to a second sub-database for processing transactional requests and analytical requests; The step of adjusting the number of sub-databases in the database according to the current processing data volume and operating status of the database includes: When the current processing data volume of the database is greater than a preset first threshold, and the number of slow queries per minute on average of the database is greater than a preset fourth threshold, and the pattern of the slow queries conforms to analytical queries, creating a first sub-database for processing analytical requests in the database and copying all the data in the second sub-database to the first sub-database.

3. The method according to claim 1, characterized in that, the step of allocating query requests to the database includes: Allocating query requests to a first sub-database for processing analytical requests and a second sub-database for processing transactional requests; The step of adjusting the number of sub-databases in the database according to the current processing data volume and operating status of the database includes: When the current processing data volume of the database is less than or equal to a preset first threshold, or the number of slow queries per minute on average of the database is less than or equal to a preset fourth threshold, or the pattern of the slow queries conforms to analytical queries, deleting the first sub-database for processing analytical requests in the database.

4. The method according to claim 1, characterized in that, the step of allocating query requests to the database includes: When the first sub-database and the second sub-database are running simultaneously, determining whether the query request is a transactional request or an analytical request; When the query request is an analytical request, allocating the query request to the first sub-database for processing analytical requests.

5. The method according to claim 2, characterized in that, after the step of creating a first sub-database for processing analytical requests in the database and copying all the data in the second sub-database to the first sub-database when the current processing data volume of the database is greater than a preset first threshold, and the number of slow queries per minute on average of the database is greater than a preset fourth threshold, and the pattern of the slow queries conforms to analytical queries, further includes: When it is detected that there is incremental data in the second sub-database, the incremental data of the second sub-database is synchronously transferred to the first sub-database in real time.

6. The method according to claim 4, wherein, the step of determining whether the query request is a transactional request or an analytical request when the first sub-database and the second sub-database are running simultaneously includes: if the cost required for the second sub-database to process the query request is less than the cost required for the first sub-database to process the query request, determining that the query request is a transactional request; if the cost required for the second sub-database to process the query request is greater than the cost required for the first sub-database to process the query request, determining that the query request is an analytical request.

7. A database adjustment device, wherein, the device includes: an allocation module, configured to allocate query requests to the database; the query requests include transactional requests or analytical requests; a determination module, configured to determine the current processed data volume and the running state of the database; an adjustment module, configured to adjust the number of sub-databases in the database according to the current processed data volume and the running state of the database; the running state includes the number of slow queries per minute on average and the slow query pattern; the determination module includes: a first determination sub-module, configured to determine the number of slow queries per minute on average; the slow query is a query request whose elapsed time exceeds a preset second threshold; a second determination sub-module, configured to determine that the slow query pattern is an analytical query if the proportion of analytical requests among the slow queries is greater than a preset third threshold.

8. An electronic device, wherein, it includes a processor, a communication interface, a memory, and a communication bus. Among them, the processor, the communication interface, and the memory communicate with each other through the communication bus; the memory is used to store a computer program; the processor, when executing the program stored on the memory, implements the method steps described in any one of claims 1-6.

9. A computer-readable storage medium, on which a computer program is stored, wherein, when the program is executed by a processor, it implements the method described in any one of claims 1-6.

Images