A method for asynchronously writing data based on a queue and a database

Abstract

The present application relates to the field of database technology, and specifically to a queue-based data asynchronous writing method and database. The method configures a queue for a database server, and after the server completes the parsing of the write statement, stores the parsed data in the queue for asynchronous writing to disk, and returns the first response of temporary successful execution to the client, so that the client can continue to send the write statement to the server, thereby realizing the asynchronization of SQL parsing and disk flushing operations, improving the data writing efficiency, and improving the data writing speed as a whole.

Description

Technical Field

[0001] The present application relates to the technical field of databases, and in particular to a queue-based data asynchronous writing method and a database. Background Art

[0002] In database operations, the data writing process involves multiple key steps, including SQL parsing, generating an execution plan, writing data logs to disk (also known as flushing), and confirming that data is written successfully. When a SQL statement contains multiple data, the database storage engine usually parses the SQL statement and generates an execution plan first, and then writes the data to the disk log file in batch append writing mode, ensuring that all data in memory is written successfully before returning the SQL execution success.

[0003] When writing data to disk, whether writing 4K data or 16M data at a time, the time taken is not much different because it involves a single IO operation. However, if the 16M data is divided into 4096 4K data, a large number of IO operations will be required due to the increase in the number of data items, and the writing time will increase significantly. At present, in order to maintain data consistency, SQL parsing and data flushing are set to serial operations. In this case, when SQL protects multiple data items, it takes a long time for the server to complete the flushing, which is relatively inefficient.

[0004] For example, in a scenario where large amounts of data are written using ETL (Extract-Transform-Load, which extracts, transforms, and loads data from the source to the destination) tools, there will be a large number of SQL statements, and each SQL statement will contain thousands of data records. The current serialized data writing method will affect data writing efficiency and slow down data writing speed. This situation is not allowed in ETL scenarios that place great importance on data transmission speed. Summary of the invention

[0005] In view of this, the present application discloses a queue-based asynchronous data writing method, which is applied to the client of a database, wherein the client corresponds to the server of the database, and the queue is connected to the server in communication. The method comprises: receiving a write request for data to be written; wherein the writing of the data to be written is completed by at least one write statement; in response to the write request, setting the write mode to asynchronous, turning off the automatic disk flushing, and sending a preset number of write statements in the at least one write statement to the server, so that the server parses the preset number of write statements, writes the corresponding target data to be written to the queue to perform asynchronous writing to the disk, and returns a first response of temporary successful execution to the client; in response to receiving the first response, obtaining the preset number of write statements from the remaining write statements and sending them to the server to perform the parsing and the asynchronous writing to the disk operation until all write statements are sent and all data to be written are asynchronously written.

[0006] In some embodiments, in response to completing the sending of all write statements and receiving the first response, the method also includes: setting the write mode to synchronous, resuming automatic disk flushing, so that the server responds to resuming automatic disk flushing, periodically polls the queue and writes all data in the queue to the disk to complete the writing of all the data to be written.

[0007] In some embodiments, in response to completing the writing of all the data to be written, the server returns a second response to the client indicating that all the data has been written successfully; the method also includes: in response to receiving the second response, determining that all the data to be written has been written to prompt the completion of the write request.

[0008] In some embodiments, the server returns a third response of write failure to the client in response to a failure to write any data to be written; the method also includes: in response to receiving the third response, determining that the write request has failed to execute, and re-executing the write request until it succeeds or reaches a preset number of executions.

[0009] In some embodiments, the client records a first correspondence between all write statements corresponding to the write request and the data to be written, the server records a second correspondence between the received write statements and the parsed data to be written, and adds a failed SQL variable for prompting the execution failure SQL in the environment variable mechanism provided by the server. In response to the failure of writing any data to be written, the server returns a third response of write failure to the client; the method also includes: in response to receiving the third response, sending a request to obtain the execution failure SQL to the server, so that the server queries the second correspondence to determine the first failed SQL according to the target data of the failed write, and writes the first failed SQL into the failed SQL variable and returns it to the client; obtaining the first failed SQL recorded by the failed SQL variable in the environment variable mechanism, querying the first correspondence to determine the corresponding rewrite data; generating a first rewrite SQL based on the rewrite data, and sending the first rewrite SQL to the server to execute the rewrite of the rewrite data until the rewrite is successful or a preset number of executions is reached.

[0010] In some embodiments, a SQL parsing unit is pre-deployed in the client, and a SQL generating unit is pre-deployed in the server. A failed SQL variable for prompting the execution of failed SQL is added in the environment variable mechanism provided by the server. In response to the failure of writing any data to be written, the server returns a third response of write failure to the client; the method also includes: in response to receiving the third response, sending a request to obtain the execution failed SQL to the server, so that the server calls the SQL generating unit to generate a second failed SQL according to the target data that failed to be written, and writes the second failed SQL into the failed SQL variable, and returns it to the client; obtains the second failed SQL recorded by the failed SQL variable in the environment variable mechanism, and calls the SQL parsing unit to parse the failed data; generates a second rewrite SQL for all the received failed data, and sends the second rewrite SQL to the server to rewrite the rewrite data until the rewrite is successful or the preset number of executions is reached.

[0011] In some embodiments, an environment variable for controlling whether to automatically flush the disk is added to the environment variable mechanism provided by the server; setting the write mode to asynchronous and turning off automatic flushing includes: calling the environment variable mechanism provided by the server, and setting the environment variable corresponding to the data flushing mode in the environment variable mechanism to the first identifier, so as to set the write mode to asynchronous and turn off automatic flushing; setting the write mode to synchronous and resuming automatic flushing includes: setting the environment variable to the second identifier, so as to set the write mode to synchronous and turn on automatic flushing.

[0012] In some embodiments, the server executes the asynchronous writing to disk, including: monitoring the queue; in response to monitoring that there is any data in the queue or the amount of data reaches a preset threshold, calling an executor to write the data in the queue to disk.

[0013] The present application also proposes a queue-based asynchronous data writing method, which is applied to the server side of a database, wherein the server side corresponds to the client side of the database, and the queue is communicatively connected to the server side; the client side receives a write request for the data to be written; wherein the writing of the data to be written is completed by at least one write statement; the client side responds to the write request, sets the write mode to asynchronous, turns off automatic disk flushing, and sends a preset number of write statements in the at least one write statement to the server side; the method comprises: parsing the preset number of write statements; writing the corresponding target data to be written into the queue to execute asynchronous writing to the disk; returning a first response of temporary execution success to the client side, so that the client side, in response to receiving the first response, obtains the preset number of write statements from the remaining write statements and sends them to the server side to execute the parsing and the asynchronous writing to the disk operation until all write statements are sent and all data to be written are asynchronously written.

[0014] The present application also proposes a database, which includes a client and a server, and the server is communicatively connected to a queue; the client receives a write request for data to be written; wherein the writing of the data to be written is completed by at least one write statement; in response to the write request, the write mode is set to asynchronous, the automatic disk flushing is turned off, and a preset number of write statements in the at least one write statement are sent to the server; the server parses the preset number of write statements, writes the corresponding target data to be written into the queue to execute asynchronous writing to the disk, and returns a first response of temporary success to the client, so that the client, in response to receiving the first response, obtains the preset number of write statements from the remaining write statements and sends them to the server to execute the parsing and the asynchronous writing to the disk operation until the sending of all write statements and the asynchronous writing of all data to be written are completed.

[0015] In the scheme recorded in any of the aforementioned embodiments, by configuring a queue for the database server, after the server completes the parsing of the write statement, the parsed data is stored in the queue for asynchronous writing to the disk, and a first response of temporary successful execution is returned to the client, so that the client can continue to send write statements to the server, thereby realizing the asynchrony of SQL parsing and disk flushing operations, improving data writing efficiency, and improving data writing speed overall.

[0016] It should be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the technical solutions in one or more embodiments of the present application or related technologies, the drawings required for use in the embodiments or related technology descriptions will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in one or more embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0018] The following is a brief introduction to the drawings required for use in the embodiments or related technical descriptions.

[0019] Figure 1 The present invention is a flowchart of a method for asynchronously writing data based on a queue.

[0020] Figure 2 A flowchart of a method for retrieving data is shown in the present application.

[0021] Figure 3 A flowchart of a method for retrieving data is shown in the present application.

[0022] Figure 4 A schematic diagram of the structure of a database illustrated in this application.

[0023] Figure 5 This is a schematic diagram of the device interaction process of a method for asynchronous data writing in this application. DETAILED DESCRIPTION

[0024] The exemplary embodiments will be described in detail below, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are only examples of devices and methods consistent with some aspects of the present application as detailed in the attached claims.

[0025] The terms used in this application are only for the purpose of describing specific embodiments, and are not intended to limit this application. The singular forms of "a", "said" and "the" used in this application and the appended claims are also intended to include plural forms, unless the context clearly indicates other meanings. It should also be understood that the term "and / or" used in this article refers to and includes any or all possible combinations of one or more associated listed items. It should also be understood that the word "if" used in this article, depending on the context, can be interpreted as "at the time of" or "when" or "in response to determining".

[0026] This application proposes a queue-based asynchronous data writing method. This method configures a queue for the database server, and after the server completes the parsing of the write statement, stores the parsed data in the queue for asynchronous writing to the disk, and returns the first response of temporary successful execution to the client, so that the client can continue to send write statements to the server, thereby realizing the asynchronization of SQL parsing and disk flushing operations, improving data writing efficiency, and improving data writing speed overall.

[0027] The following is a description of the embodiments with reference to the accompanying drawings.

[0028] See also Figure 1 , Figure 1 The present invention is a flowchart of a method for asynchronously writing data based on a queue. Figure 1 The illustrated method is applied to a client of a database, the client corresponds to a server of the database, and the queue is connected to the server in communication. The database involved in the present application may be a relational database, a non-relational database, etc., without particular limitation. This article can be illustrated by taking a MySQL database as an example.

[0029] like Figure 1 As shown, the method may include S102-S106.

[0030] S102, receiving a write request for data to be written, and completing writing of the data to be written through at least one write statement.

[0031] The data to be written refers to data that needs to be written into the database.

[0032] The write statement is a statement that matches the database and is used to write data. For example, in a MySQL database, the write statement refers to SQL.

[0033] For example, in an ETL scenario, when there is a need to write data, the ETL tool will send a data write request to the database client. After receiving the write request, the client will parse the data to be written, and then use the SQL generation tool to generate at least one SQL statement, and send it one by one to the database server to complete the writing of all the data to be written.

[0034] S104, in response to the write request, sets the write mode to asynchronous, turns off automatic disk flushing, and sends a preset number of write statements in the at least one write statement to the server, so that the server parses the preset number of write statements, writes the corresponding target data to be written into the queue to perform asynchronous writing to the disk, and returns a first response of temporary execution success to the client.

[0035] In the original mechanism of the database, the disk will be automatically flushed after the SQL parsing is completed. The next SQL will be parsed only after the flushing is successful. This is the reason for the slow data writing speed in the ETL scenario. In order to improve the writing speed, SQL parsing and flushing can be performed asynchronously. In this way, SQL parsing and flushing can be performed at the same time without interfering with each other, thereby improving the overall efficiency of data writing.

[0036] In some ways, an environment variable for controlling whether to automatically flush the disk can be added to the environment variable mechanism provided by the server. When it is necessary to decouple SQL parsing and flushing operations, the environment variable can be modified so that the server can turn off automatic flushing through the modified environment variable to achieve decoupling. When it is necessary to bind SQL parsing and flushing operations, the environment variable can be modified so that the server can turn on automatic flushing through the modified environment variable to achieve binding.

[0037] After turning off automatic disk flushing, the disk will not be flushed after parsing the SQL. Instead, the disk will be flushed according to the instructions issued by the server. By writing disk flushing instructions, the disk flushing logic can be limited to the listening queue. If there is data in the queue, the disk will be flushed, thereby turning disk flushing and SQL parsing into asynchronous operations.

[0038] The preset number can be set according to business requirements, for example, 1, 2 or 3.

[0039] The target data to be written is at least part of the total data, corresponding to the preset number of write statements. For example, the total data includes 1 million data items, and it is expected that the data writing will be completed through 200 SQL statements, each SQL statement corresponding to 5000 data items.

[0040] After receiving the write statement, the server can parse it. After parsing the data, since the automatic disk flushing is currently turned off, the server can be controlled by the written instructions to write the parsed data into the queue. The server can also monitor the queue and flush the disk if there is data in the queue, thus turning the disk flushing and SQL parsing into asynchronous operations. After writing the target data to be written into the queue, the server can return the first response of temporary execution success to the client.

[0041] It is understandable that this temporary execution success can be understood as a temporary success. In fact, the data is only written to the queue but not to the disk. This asynchronous writing is allowed in the ETL scenario because ETL has the characteristic of eventual consistency, that is, if any data writing fails, the operation of writing all data will be re-executed, so it is not necessary to keep the SQL parsing and writing to the disk synchronized.

[0042] S106, in response to receiving the first response, obtaining the preset number of write statements from the remaining write statements and sending them to the server to perform the parsing and the asynchronous writing to disk operations until all write statements are sent and all data to be written are asynchronously written.

[0043] After receiving the first response, the client may consider that the SQL previously sent to the server has been temporarily processed, and may continue to send SQL to the server until all SQLs in the batch are sent. After receiving the SQL, the server will perform operations such as SQL parsing and asynchronous writing to disk in S104 until all data is written to the disk.

[0044] Through the solution recorded in S102-S106, SQL parsing and disk flushing operations can be decoupled on the server side, so that the client can continue to send SQL without waiting for the disk flushing to be completed, thereby improving the overall data writing efficiency and data writing speed.

[0045] In some embodiments, in response to completing the sending of all write statements and receiving the first response, the client can set the write mode to synchronization and resume automatic disk flushing, so that the server responds to resuming automatic disk flushing, periodically polls the queue and writes all data in the queue to the disk to complete the writing of all the data to be written.

[0046] After the client completes sending all write statements and receives the first response of temporary successful execution returned by the server for the last batch of statements, the environment variable can be modified to resume automatic disk flushing. After the server perceives that the environment variable has changed to automatic disk flushing, it can determine that the client has completed sending all write statements, and then periodically poll the queue and write all data in the queue to disk. After all data in the queue is written to disk, it can be determined that all data has been written.

[0047] In some embodiments, in response to completing the writing of all the data to be written, the server returns a second response indicating that all writing was successful to the client.

[0048] In response to receiving the second response, the client determines that all the data to be written have been written, so as to prompt the completion of the write request.

[0049] For example, after the server processes all the data in the queue, it can return a second response to the client indicating that all data has been written successfully. After receiving the response, the client can notify the ETL tool that the batch of data has been written and can process subsequent batches of data.

[0050] In some embodiments, in response to a failure in writing any data to be written, the server returns a third response of write failure to the client.

[0051] In response to receiving the third response, the client determines that the write request has failed to execute, and re-executes the write request until it succeeds or reaches a preset number of execution times.

[0052] For example, if the server fails to write any data, the client can be notified through the third response. The client can re-execute the write request until it succeeds, thereby ensuring that the batch of data is written to the disk.

[0053] In some ways, like Zoo, after data writing fails, the client can perform a rewrite. If the number of recurrences reaches the preset number of executions, it can be considered that there is indeed a problem with this batch of data, and it can be returned to the ETL tool, thus avoiding too many invalid attempts and wasting resources.

[0054] In the previous embodiment, in order to maintain the consistency of all data, ETL needs to rewrite all data after data writing fails, that is, some data that has been written successfully also needs to be rewritten, resulting in resource waste. On the one hand, it is for data consistency, and on the other hand, in order to ensure database performance, the existing database mechanism can only rewrite all data. In order to solve these problems, two solutions can be proposed later.

[0055] In solution one, the client can record the first correspondence between all write statements corresponding to the write request and the data to be written, and the server can record the second correspondence between the received write statements and the parsed data to be written. A failed SQL variable is added to the environment variable mechanism provided by the server to prompt the execution of failed SQL. Therefore, after abnormal data is written in the server, the problematic SQL can be determined according to the second correspondence. The failed SQL can be transmitted to the client through the failed SQL variable. The client then determines the problematic data through the first correspondence, and only rewrites this part of the data. Therefore, it is not necessary to rewrite all the data, which reduces the amount of rewritten data and reduces resource waste. In addition, by adding a failed SQL variable in the environment variable mechanism, the communication mechanism between the database client and the server can be used to make changes, which is simple to implement.

[0056] See also Figure 2 , Figure 2 A flowchart of a method for retrieving data is shown in the present application. Figure 2 The diagram shows the process after the client receives the third response indicating that data writing failed. Figure 2 As shown, the method may include S202-S206.

[0057] S202, in response to receiving the third response, sending a request to the server to obtain the failed SQL, so that the server queries the second corresponding relationship according to the target data that failed to be written to determine the first failed SQL, writes the first failed SQL into the failed SQL variable, and returns it to the client.

[0058] After receiving the third response, the client can further send a request to the server to obtain the failed SQL. The server's own input mechanism can return the data of failed writing. The server can determine the SQL that failed to write based on the maintained second corresponding relationship, and then input the SQL as a parameter into the failed SQL variable.

[0059] The client can determine the failed SQL by calling or obtaining the failed SQL variable.

[0060] S204: Obtain the first failed SQL recorded in the failed SQL variable in the environment variable mechanism, and query the first corresponding relationship to determine corresponding rewrite data.

[0061] In this step, the client can obtain the rewrite data that needs to be rewritten by querying the first corresponding relationship.

[0062] S206: Generate a first rewriting SQL based on the rewriting data, and send the first rewriting SQL to the server to execute the rewriting of the rewriting data until the rewriting is successful or a preset number of executions is reached.

[0063] The client may generate a first rewriting SQL for the rewriting data, and then send it to the server until the rewriting is successful or a preset number of executions is reached.

[0064] Through the solution recorded in S202-S206, the SQL can be rewritten until it fails, reducing the amount of rewritten data.

[0065] For example, for a batch of data, the client generates 100 SQL statements to write data. After receiving the SQL statements sent by the client, the server will parse them and form a second correspondence between the SQL statements and the parsed data. If any data fails to be written, the second correspondence can be queried to determine the corresponding failed SQL statement. For example, SQL1 has a problem.

[0066] If data writing fails, the server will send a third response to the client. After receiving the third response, the client will request the failed SQL statement from the server. After the server determines that SQL1 failed to be written, it can write the failed SQL variable. When the client knows that SQL1 failed to be written, it can query the first corresponding relationship, determine the failed data, and then generate a new SQL statement to rewrite the data. In this way, only the problematic SQL1 can be rewritten, and there is no need to rewrite 100 SQL statements, which improves efficiency and avoids wasting a lot of resources.

[0067] In Solution 2, a SQL parsing unit is pre-deployed in the client, and a SQL generation unit is pre-deployed in the server, and a failed SQL variable is added in the environment variable mechanism provided by the server to prompt the failed SQL execution. In this way, the server can generate SQL only for the data that failed to be written, and notify the client through the failed SQL variable. The client can also parse the SQL to determine the data that failed to be written. In this way, the original communication mechanism between the client and the server can be utilized (the original mechanism can only transmit SQL between the client and the server, not specific data), which is simple to implement. In addition, the client can rewrite only the data that failed to be written, without having to rewrite all the data corresponding to the entire SQL, which further reduces the amount of rewritten data, improves efficiency and reduces resource waste.

[0068] See also Figure 3 , Figure 3 A flowchart of a method for retrieving data is shown in the present application. Figure 3 The diagram shows the process after the client receives the third response indicating that data writing failed. Figure 3As shown, the method may include S302-S306.

[0069] S302, in response to receiving the third response, sending a request for obtaining the failed SQL to the server, so that the server calls the SQL generation unit to generate a second failed SQL according to the target data that failed to be written, and writes the second failed SQL into the failed SQL variable, and returns it to the client.

[0070] In this step, after the client initiates a failed SQL request to the server, the server can use the SQL generation unit to generate a second failed SQL based on the target data that failed to be written and return it to the client. In this way, only the problematic target data can be returned to the client using SQL.

[0071] S304: Obtain the second failed SQL recorded in the failed SQL variable in the environment variable mechanism, and call the SQL parsing unit to parse out the failure data.

[0072] In this step, the client can obtain the second failed SQL from the failed SQL variable, and then use the parsing unit to parse out the failed data.

[0073] S306: Generate a second rewriting SQL for all received failed data, and send the second rewriting SQL to the server to execute rewriting of the rewriting data until the rewriting succeeds or reaches a preset number of execution times.

[0074] The client may receive multiple write-failed data from different servers or different SQLs. The client can reorganize these failed data into one SQL (called the second rewrite SQL) and send it to the server for rewriting until the rewrite succeeds or the preset number of executions is reached. In this way, the failed data can be summarized and rewritten through one SQL, and only the write-failed data is rewritten, which is more efficient and saves resources.

[0075] For example, for a batch of data, the client generates 100 SQL statements to write data. If any data fails to be written, the server can generate SQL statements and return them to the client through the failed SQL statement variable. The client can reorganize the failed write data received within a preset time (such as 3 seconds or 5 seconds, etc.) and generate a rewrite SQL statement to rewrite the data. In this way, only problematic data can be rewritten, which improves efficiency and avoids wasting a lot of resources.

[0076] In some embodiments, an environment variable for controlling whether to automatically refresh the disk can be added to the environment variable mechanism provided by the server, so as to turn on and off the automatic refreshing of the disk.

[0077] In this case, the writing mode is set to asynchronous and the automatic disk flushing is turned off, including:

[0078] The environment variable mechanism provided by the server is called, and the environment variable corresponding to the data disk flushing mode in the environment variable mechanism is set as the first identifier to set the writing mode to asynchronous and turn off automatic disk flushing.

[0079] The step of setting the write mode to synchronous and resuming automatic disk flushing includes:

[0080] The environment variable is set as the second identifier to set the write mode to synchronization and start automatic disk flushing.

[0081] For example, you can deploy an application on the server. The program supports controlling the synchronous or asynchronous writing mode through environment variables, as well as whether to enable the automatic flushing function. Set environment variables: Suppose we set an environment variable named ATUO_FLUSH to control the writing mode. By default, ATUO_FLUSH = on (second flag): The value of this environment variable indicates the use of synchronous writing and the automatic flushing function is turned on, that is, the server executes the flushing function serially after parsing the SQL.

[0082] When the client receives a data write request and needs to write a batch of data to the server, the client can call the environment variable mechanism provided by the server and set ATUO_FLUSH to off (the first flag) to set the write mode to asynchronous and turn off automatic flushing. Thus, SQL parsing and flushing become asynchronous operations.

[0083] After the client sends the last SQL and receives a temporary success response from the server, you can set ATUO_FLUSH to on to turn on automatic disk flushing. The server polls the queue to write all data to disk and responds to the client to confirm that data writing has been completed.

[0084] In this way, the server application can dynamically adjust the synchronization or asynchrony of the write mode and whether to enable the automatic disk flushing function according to the setting of environment variables, thereby optimizing the performance and durability of data writing in different operating environments.

[0085] In some embodiments, the server mentioned in S104 and S106 performs the asynchronous writing to disk, including:

[0086] monitoring the queue;

[0087] In response to monitoring that there is any data in the queue or the amount of data therein reaches a preset threshold, the executor is called to write the data in the queue to the disk.

[0088] After turning off the automatic disk flushing, you need to start the manual disk flushing logic. Manual disk flushing is to let the server monitor the queue through the program. When there is data in the queue or the amount of data reaches a certain preset threshold, it will be written to the disk one by one. This completes the manual disk flushing.

[0089] The present application also proposes a database. The database includes a client and a server. The server is connected to a queue. In the database, by configuring a queue for the database server, after the server completes the parsing of the write statement, the parsed data is stored in the queue for asynchronous writing to the disk, and the first response of temporary successful execution is returned to the client, so that the client can continue to send write statements to the server, thereby realizing the asynchronization of SQL parsing and disk flushing operations, improving the efficiency of data writing, and improving the data writing speed as a whole.

[0090] See also Figure 4 , Figure 4 The schematic diagram of the structure of a database for the present application is shown in FIG. 400 . The database 400 includes a client 410 , a server or a server cluster 420 , and a queue 430 .

[0091] See also Figure 5 , Figure 5 This is a schematic diagram of the device interaction process of a method for asynchronous data writing in this application. Figure 5 In order to store data Figure 4 The diagram below shows the interaction between the various units in the database. Figure 5 As shown, the whole process includes S501-S506.

[0092] The client, S501, receives a write request for data to be written; wherein the writing of the data to be written is completed through at least one write statement.

[0093] S502, in response to the write request, setting the write mode to asynchronous, turning off automatic disk flushing, and sending a preset number of write statements in the at least one write statement to the server.

[0094] The server, S503, parses the preset number of write statements.

[0095] S504, writing the corresponding target data to be written into the queue to perform asynchronous writing to the disk.

[0096] S505: Return a first response of temporary execution success to the client.

[0097] The client, S506, in response to receiving the first response, obtains the preset number of write statements from the remaining write statements and sends them to the server to perform the parsing and the asynchronous writing to disk operations until the sending of all write statements and the asynchronous writing of all data to be written are completed.

[0098] Through the database recorded in S501-S506, SQL parsing and disk flushing operations can be decoupled on the server side, so that the client can continue to send SQL without waiting for the disk flushing to be completed, thereby improving the overall data writing efficiency and data writing speed.

[0099] Figure 5 It is also shown that: the client, S507, in response to completing the sending of all write statements and receiving the first response, sets the write mode to synchronous and resumes automatic disk flushing.

[0100] The server, S508, in response to resuming automatic disk flushing, periodically polls the queue and writes all the data in the queue to the disk, so as to complete writing of all the data to be written.

[0101] S509: In response to completing the writing of all the data to be written, returning a second response indicating that all the data has been written successfully to the client.

[0102] The client, S510, in response to receiving the second response, determines that all the data to be written have been written, so as to prompt the completion of the write request.

[0103] Figure 5 The steps of asynchronously writing data are also illustrated: the server S511 monitors the queue.

[0104] S512: In response to monitoring that there is any data in the queue or the amount of data in the queue reaches a preset threshold, calling an executor to write the data in the queue to a disk.

[0105] For the related description of the aforementioned S501 - S512 , reference may be made to the previous embodiment.

[0106] In some embodiments, in response to a failure in writing any data to be written, the server returns a third response of write failure to the client;

[0107] The client: in response to receiving the third response, determines that the write request fails to execute, and re-executes the write request until it succeeds or reaches a preset number of execution times.

[0108] In some embodiments, the client records a first correspondence between all write statements corresponding to the write request and the data to be written, the server records a second correspondence between the received write statements and the parsed data to be written, and a failed SQL variable for prompting the execution of failed SQL is added in the environment variable mechanism provided by the server.

[0109] In response to a failure in writing any data to be written, the server returns a third response of writing failure to the client; the client:

[0110] In response to receiving the third response, sending a request to the server to obtain the SQL that failed to execute, so that the server queries the second corresponding relationship according to the target data that failed to write to determine the first failed SQL, writes the first failed SQL into the failed SQL variable, and returns it to the client;

[0111] Obtain the first failed SQL recorded in the failed SQL variable in the environment variable mechanism, and query the first corresponding relationship to determine corresponding rewrite data;

[0112] A first rewriting SQL is generated based on the rewriting data, and the first rewriting SQL is sent to the server to execute the rewriting of the rewriting data until the rewriting is successful or a preset number of executions is reached.

[0113] In some embodiments, a SQL parsing unit is pre-deployed in the client, a SQL generating unit is pre-deployed in the server, and a failed SQL variable for prompting the execution of failed SQL is added to the environment variable mechanism provided by the server.

[0114] In response to a failure in writing any data to be written, the server returns a third response of writing failure to the client; the client:

[0115] In response to receiving the third response, sending a request to the server to obtain the SQL that failed to execute, so that the server calls the SQL generation unit to generate a second failed SQL according to the target data that failed to be written, writes the second failed SQL into the failed SQL variable, and returns it to the client;

[0116] Obtain the second failed SQL recorded in the failed SQL variable in the environment variable mechanism, and call the SQL parsing unit to parse out the failed data;

[0117] A second rewriting SQL is generated for all received failed data, and the second rewriting SQL is sent to the server to execute the rewriting of the rewriting data until the rewriting is successful or a preset number of executions is reached.

[0118] In some embodiments, an environment variable for controlling whether to automatically refresh the disk is added in the environment variable mechanism provided by the server; setting the write mode to asynchronous and turning off automatic refreshing of the disk includes:

[0119] Calling the environment variable mechanism provided by the server, setting the environment variable corresponding to the data disk flushing mode in the environment variable mechanism to the first identifier, so as to set the writing mode to asynchronous and turn off automatic disk flushing;

[0120] The step of setting the write mode to synchronous and resuming automatic disk flushing includes:

[0121] The environment variable is set as the second identifier to set the write mode to synchronization and start automatic disk flushing.

[0122] The present application also proposes a queue-based asynchronous data writing method, which is applied to the server of the database. The method configures a queue for the database server, and after the server completes the parsing of the write statement, stores the parsed data in the queue for asynchronous writing to the disk, and returns the first response of temporary successful execution to the client, so that the client can continue to send write statements to the server, thereby realizing the asynchronization of SQL parsing and disk flushing operations, improving the data writing efficiency, and improving the data writing speed overall.

[0123] The server corresponds to the client of the database. The queue is connected to the server in communication; the client receives a write request for data to be written; wherein the writing of the data to be written is completed by at least one write statement; in response to the write request, the client sets the write mode to asynchronous, turns off automatic disk flushing, and sends a preset number of write statements in the at least one write statement to the server;

[0124] The method comprises:

[0125] parsing the preset number of write statements;

[0126] Writing the corresponding target data to be written into the queue to perform asynchronous writing to the disk;

[0127] A first response of temporary successful execution is returned to the client, so that the client, in response to receiving the first response, obtains the preset number of write statements from the remaining write statements and sends them to the server to perform the parsing and the asynchronous writing to disk operations until the sending of all write statements and the asynchronous writing of all data to be written are completed.

[0128] In some embodiments, the client sets the write mode to synchronization in response to completing the sending of all write statements and receiving the first response, and resumes automatic disk flushing. The method further includes:

[0129] In response to resuming automatic disk flushing, the queue is periodically polled and all the data in the queue is written to the disk, so as to complete writing of all the data to be written.

[0130] In some embodiments, the method further comprises:

[0131] In response to completing all writing of the data to be written, a second response indicating that all writing is successful is returned to the client, so that the client determines that all writing of the data to be written has been completed in response to receiving the second response, thereby prompting the completion of the write request.

[0132] In some embodiments, the method further includes: in response to a failure to write any data to be written, returning a third response of write failure to the client, so that the client, in response to receiving the third response, determines that the write request has failed to execute, and re-executes the write request until it succeeds or reaches a preset number of executions.

[0133] In some embodiments, the client records a first correspondence between all write statements corresponding to the write request and the data to be written, the server records a second correspondence between the received write statements and the parsed data to be written, and a failed SQL variable for prompting the execution of failed SQL is added in the environment variable mechanism provided by the server.

[0134] The method further comprises:

[0135] In response to a failure in writing any data to be written, a third response of the write failure is returned to the client, so that the client, in response to receiving the third response, sends a request to the server to obtain the execution failure SQL, so that the server queries the second corresponding relationship to determine the first failure SQL according to the target data that failed to be written, writes the first failure SQL into the failure SQL variable, and returns it to the client; obtains the first failure SQL recorded in the failure SQL variable in the environment variable mechanism, queries the first corresponding relationship to determine the corresponding rewrite data; generates a first rewrite SQL based on the rewrite data, and sends the first rewrite SQL to the server to execute the rewrite of the rewrite data until the rewrite is successful or a preset number of executions is reached.

[0136] In some embodiments, a SQL parsing unit is pre-deployed in the client, and a SQL generating unit is pre-deployed in the server, and a failed SQL variable for prompting the execution of failed SQL is added in the environment variable mechanism provided by the server. The method also includes: in response to the failure of writing any data to be written, returning a third response of writing failure to the client, so that the client sends a request to obtain the execution failed SQL to the server in response to receiving the third response, so that the server calls the SQL generating unit to generate a second failed SQL according to the target data that failed to write, and writes the second failed SQL into the failed SQL variable and returns it to the client; obtains the second failed SQL recorded by the failed SQL variable in the environment variable mechanism, and calls the SQL parsing unit to parse the failed data; generates a second rewrite SQL for all received failed data, and sends the second rewrite SQL to the server to rewrite the rewrite data until the rewrite is successful or the preset number of executions is reached.

[0137] In some embodiments, an environment variable for controlling whether to automatically refresh the disk is added in the environment variable mechanism provided by the server; setting the write mode to asynchronous and turning off automatic refreshing of the disk includes:

[0138] Calling the environment variable mechanism provided by the server, setting the environment variable corresponding to the data disk flushing mode in the environment variable mechanism to the first identifier, so as to set the writing mode to asynchronous and turn off automatic disk flushing;

[0139] The step of setting the write mode to synchronous and resuming automatic disk flushing includes:

[0140] The environment variable is set as the second identifier to set the write mode to synchronization and start automatic disk flushing.

[0141] In some embodiments, the server performs the asynchronous writing to disk, including:

[0142] monitoring the queue;

[0143] In response to monitoring that there is any data in the queue or the amount of data therein reaches a preset threshold, the executor is called to write the data in the queue to the disk.

[0144] The description of the relevant embodiments can refer to the previous embodiments.

[0145] Those skilled in the art will appreciate that one or more embodiments of the present application may be provided as a method, system or computer program product. Therefore, one or more embodiments of the present application may take the form of a complete hardware embodiment, a complete software embodiment or an embodiment combining software and hardware. Moreover, one or more embodiments of the present application may take the form of a computer program product implemented on one or more computer-usable storage media (which may include but are not limited to disk storage, CDROM, optical storage, etc.) containing computer-usable program code.

[0146] The term "and / or" in the present application means at least one of the two. For example, "A and / or B" may include three options: A, B, and "A and B".

[0147] Each embodiment in the present application is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the data processing device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0148] The above describes a specific embodiment of the present application. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the accompanying drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0149] The subject matter described in this application and the embodiments of the functional loading can be implemented in the following: digital electronic circuits, tangibly embodied computer software or firmware, computer hardware that can include the structure disclosed in this application and its structural equivalents, or a combination of one or more of them. The embodiments of the subject matter described in this application can be implemented as one or more computer programs, that is, one or more modules in the computer program instructions encoded on a tangible non-temporary program carrier to be executed by a data processing device or control the loading of a data processing device. Alternatively or additionally, the program instructions can be encoded on an artificially generated propagation signal, such as a machine-generated electrical, optical or electromagnetic signal, which is generated to encode information and transmit it to a suitable receiver device for execution by a data processing device. The computer storage medium can be a machine-readable storage device, a machine-readable storage substrate, a random or serial access memory device, or a combination of one or more of them.

[0150] The processes and logic flows described in this application can be performed by one or more programmable computers executing one or more computer programs to perform corresponding functions by loading according to input data and generating output. The processes and logic flows can also be performed by special-purpose logic circuits, such as FPGAs (field programmable gate arrays) or ASICs (application-specific integrated circuits), and the apparatus can also be implemented as special-purpose logic circuits.

[0151] A computer suitable for executing a computer program may include, for example, a general and / or special purpose microprocessor, or any other type of central processing unit. Typically, the central processing unit will receive instructions and data from a read-only memory and / or a random access memory. The basic components of a computer may include a central processing unit for implementing or executing instructions and one or more memory devices for storing instructions and data. Typically, a computer will also include one or more large-capacity storage devices for storing data, such as a disk, a magneto-optical disk, or an optical disk, or the computer will be loadably coupled to this large-capacity storage device to receive data from it or to transmit data to it, or both. However, a computer does not necessarily have such a device. In addition, a computer may be embedded in another device, such as a mobile phone, a personal digital assistant (PDA), a mobile audio or video player, a game console, a global positioning system (GPS) receiver, or a portable storage device such as a universal serial bus (USB) flash drive, to name a few.

[0152] Computer readable media suitable for storing computer program instructions and data may include all forms of non-volatile memory, media and memory devices, such as semiconductor memory devices (e.g., EPROM, EEPROM and flash memory devices), magnetic disks (e.g., internal hard disks or removable disks), magneto-optical disks, and CD ROM and DVD ROM disks. The processor and memory may be supplemented by, or incorporated in, special purpose logic circuits.

[0153] Although the application includes many specific implementation details, these should not be interpreted as limiting any disclosed scope or the scope of protection claimed, but are mainly used to describe the features of specific disclosed specific embodiments. Certain features described in multiple embodiments in the application can also be implemented in combination in a single embodiment. On the other hand, the various features described in a single embodiment can also be implemented separately in multiple embodiments or implemented with any suitable sub-combination. In addition, although the feature can work in some combinations as described and even initially claim protection, one or more features from the claimed combination can be removed from the combination in some cases, and the claimed combination can point to a variation of a sub-combination or a sub-combination.

[0154] Similarly, although loads are depicted in a particular order in the accompanying drawings, this should not be understood as requiring that these loads be executed in the particular order shown or executed sequentially, or requiring that all illustrated loads be executed to achieve the desired results. In some cases, multitasking and parallel processing may be advantageous. In addition, the separation of various system modules and components in the described embodiments should not be understood as requiring such separation in all embodiments, and it should be understood that the described program components and systems can generally be integrated together in a single software product, or packaged into multiple software products.

[0155] Thus, specific embodiments of the subject matter have been described. Other embodiments are within the scope of the appended claims. In some cases, the actions recited in the claims can be performed in a different order and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the particular order or sequential order shown to achieve the desired results. In some implementations, multitasking and parallel processing may be advantageous.

[0156] The above are only preferred embodiments of one or more embodiments of the present application and are not intended to limit one or more embodiments of the present application. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of one or more embodiments of the present application should be included in the scope of protection of one or more embodiments of the present application.

Claims

1. A queue-based asynchronous data writing method, applied to a client of a database, the client corresponding to the server of the database, characterized in that: The queue is connected to the server in communication; the method comprises: Receiving a write request for data to be written; wherein the writing of the data to be written is completed through at least one write statement; In response to the write request, the write mode is set to asynchronous, the automatic disk flushing is turned off, and a preset number of write statements in the at least one write statement are sent to the server, so that the server parses the preset number of write statements, writes the corresponding target data to be written into the queue to perform asynchronous writing to the disk, and returns a first response of temporary execution success to the client; In response to receiving the first response, the preset number of write statements are obtained from the remaining write statements and sent to the server to perform the parsing and the asynchronous writing to disk operations until all write statements are sent and all data to be written are asynchronously written.

2. The queue-based data asynchronous writing method according to claim 1, characterized in that: In response to completing sending of all write statements and receiving the first response, the method further includes: The write mode is set to synchronous, and automatic disk flushing is resumed, so that the server responds to the resumption of automatic disk flushing by periodically polling the queue and writing all the data in the queue to the disk, so as to complete the writing of all the data to be written.

3. The queue-based data asynchronous writing method according to claim 2, characterized in that: In response to completing the writing of all the data to be written, the server returns a second response of complete writing success to the client; The method further comprises: In response to receiving the second response, it is determined that all the data to be written has been written, so as to prompt the completion of the write request.

4. The queue-based data asynchronous writing method according to claim 2, characterized in that: In response to a failure in writing any data to be written, the server returns a third response of writing failure to the client; The method further comprises: In response to receiving the third response, it is determined that the write request has failed to execute, and the write request is re-executed until it succeeds or reaches a preset number of execution times.

5. The queue-based data asynchronous writing method according to claim 2, characterized in that: The client records a first correspondence between all write statements corresponding to the write request and the data to be written, the server records a second correspondence between the received write statement and the parsed data to be written, and adds a failed SQL variable for prompting the execution of failed SQL in the environment variable mechanism provided by the server. In response to a failure in writing any data to be written, the server returns a third response of writing failure to the client; the method further comprises: In response to receiving the third response, sending a request to the server to obtain the SQL that failed to execute, so that the server queries the second corresponding relationship according to the target data that failed to write to determine the first failed SQL, writes the first failed SQL into the failed SQL variable, and returns it to the client; Obtain the first failed SQL recorded in the failed SQL variable in the environment variable mechanism, and query the first corresponding relationship to determine corresponding rewrite data; A first rewriting SQL is generated based on the rewriting data, and the first rewriting SQL is sent to the server to execute the rewriting of the rewriting data until the rewriting is successful or a preset number of executions is reached.

6. The queue-based data asynchronous writing method according to claim 2, characterized in that: The client pre-deploys an SQL parsing unit, the server pre-deploys an SQL generating unit, and an SQL failure variable for prompting the execution of failed SQL is added to the environment variable mechanism provided by the server. In response to a failure in writing any data to be written, the server returns a third response of writing failure to the client; the method further comprises: In response to receiving the third response, sending a request to the server to obtain the SQL that failed to execute, so that the server calls the SQL generation unit to generate a second failed SQL according to the target data that failed to be written, writes the second failed SQL into the failed SQL variable, and returns it to the client; Obtain the second failed SQL recorded in the failed SQL variable in the environment variable mechanism, and call the SQL parsing unit to parse out the failed data; A second rewriting SQL is generated for all received failed data, and the second rewriting SQL is sent to the server to execute the rewriting of all failed data until the rewriting is successful or a preset number of executions is reached.

7. The queue-based data asynchronous writing method according to claim 2, characterized in that: Adding an environment variable for controlling whether to automatically flush the disk in the environment variable mechanism provided by the server; setting the write mode to asynchronous and turning off automatic flushing of the disk includes: Calling the environment variable mechanism provided by the server, setting the environment variable corresponding to the data disk flushing mode in the environment variable mechanism to the first identifier, so as to set the writing mode to asynchronous and turn off automatic disk flushing; The step of setting the write mode to synchronous and resuming automatic disk flushing includes: The environment variable is set as the second identifier to set the write mode to synchronization and start automatic disk flushing.

8. The queue-based data asynchronous writing method according to claim 1, characterized in that: The server performs the asynchronous writing to disk, including: monitoring the queue; In response to monitoring that there is any data in the queue or the amount of data therein reaches a preset threshold, the executor is called to write the data in the queue to the disk.

9. A queue-based asynchronous data writing method, applied to a server of a database, the server corresponding to a client of the database, characterized in that: The queue is in communication connection with the server; the client receives a write request for data to be written; wherein the writing of the data to be written is completed by at least one write statement; in response to the write request, the client sets the write mode to asynchronous, turns off automatic disk flushing, and sends a preset number of write statements in the at least one write statement to the server; The method comprises: parsing the preset number of write statements; Writing the corresponding target data to be written into the queue to perform asynchronous writing to the disk; A first response of temporary successful execution is returned to the client, so that the client, in response to receiving the first response, obtains the preset number of write statements from the remaining write statements and sends them to the server to perform the parsing and the asynchronous writing to disk operations until the sending of all write statements and the asynchronous writing of all data to be written are completed.

10. A database, comprising a client and a server, characterized in that: The server is connected to the queue for communication; The client receives a write request for data to be written; wherein the writing of the data to be written is completed by at least one write statement; in response to the write request, the write mode is set to asynchronous, the automatic disk flushing is turned off, and a preset number of write statements in the at least one write statement are sent to the server; The server parses the preset number of write statements, writes the corresponding target data to be written into the queue to perform asynchronous writing to disk, and returns a first response of temporary execution success to the client, so that the client, in response to receiving the first response, obtains the preset number of write statements from the remaining write statements and sends them to the server to perform the parsing and the asynchronous writing to disk operations until the sending of all write statements and the asynchronous writing of all data to be written are completed.

Images