On-line analytical processing (OLAP) database-oriented server-side assembled batch high-concurrency import method and system

Abstract

The invention discloses a server-side batched high-concurrency import method and system for an OLAP database, and the method comprises the steps: multiplexing a multi-client-side concurrent import request into a single internal transaction and data version at a server side, introducing a configurable synchronous / asynchronous dual-mode submission mode, guaranteeing the data reliability in combination with WAL in an asynchronous mode, and enabling the data reliability to be higher than that in the server side; meanwhile, preprocessing statement cache is supported to reduce SQL analysis overhead; according to the method, the number of transactions and versions is greatly reduced through active batch gathering of the server side, the CPU, memory and IO pressure of the system is reduced, the data consistency and reliability are guaranteed, meanwhile, the import requirements of high throughput and low delay are considered, and the overall performance and stability of the OLAP database in a high-concurrency write-in scene are effectively improved.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a server-side batch high-concurrency import method and system for OLAP databases. Background Technology

[0002] In OLAP (Online Analytical Processing) database systems, existing technologies typically employ a serial or simple concurrent processing approach when handling high-concurrency data import scenarios. This approach involves creating an independent transaction for each import request, generating a new data version, and executing a complete SQL parsing. Another common practice is to first write the data to the server-side memory buffer and then asynchronously batch-flush it to persistent storage according to a predetermined strategy.

[0003] While existing technologies employing client-side synchronous waiting for flush completion ensure strong data consistency and reliability, each import request requires an independent transaction, resulting in an excessive number of transactions. Furthermore, clients cannot effectively reuse server-side memory buffers and processing contexts. In truly high-concurrency write scenarios, this approach leads to a massive number of small transactions and data versions within the database system. This not only drastically increases transaction management overhead but also triggers a surge in IO and CPU pressure during subsequent data compaction operations, severely limiting the system's overall throughput and scalability.

[0004] Therefore, in response to the problems mentioned above, this invention proposes a server-side batch high-concurrency import method and system for OLAP databases. Summary of the Invention

[0005] To overcome the problems of excessive transactions and version numbers and high system resource pressure in existing high-concurrency import schemes, this invention proposes a server-side batch high-concurrency import method and system for OLAP databases. This method significantly reduces internal system overhead by intelligently aggregating and reusing concurrent requests from multiple clients on the server side and introducing an optional asynchronous write-ahead log mechanism.

[0006] The technical solution of this invention is: a server-side batch high-concurrency import method for OLAP databases, comprising the following steps: S1, the database server receives concurrent data import requests from multiple clients; S2, the server performs batch processing on multiple concurrent import requests received, reusing the import operations of multiple clients into an internal logical import task. This internal import task corresponds to a unique database transaction and a data version. When the server receives the first client import request, it creates an internal import task, a corresponding data queue, and an associated WAL file. For other client import requests that arrive later, it directly reuses the data queue and WAL file of the already created internal import task to append data. S3: The server processes the internal import tasks according to the preset import mode. In synchronous mode, the batched data is submitted to the database storage layer as a whole transaction, and a successful import response is returned to the corresponding client after the transaction is successfully committed, ensuring that the data is immediately visible. In asynchronous mode, the batched data is first written to the Write-Ahead Log (WAL) and persisted. Then, a successful import response is immediately returned to the client. At this time, the data only exists in the WAL and the memory queue and has not yet formed a formal storage file or version. The server background process asynchronously submits the data in the WAL to the database storage layer according to the preset submission conditions and makes the data visible after submission. S4, in asynchronous mode, the server background process continuously monitors the data queue corresponding to the internal import task. When the preset time interval threshold or the cumulative data volume reaches the preset size threshold, the data submission operation is triggered. This threshold is configurable. S5, for write requests using the INSERT INTO VALUES statement via JDBC, the front-end node of the server supports the prepared statement feature of the MySQL protocol, which caches the first received SQL statement and its generated execution plan in session-level memory. Subsequent import requests in the same session directly reuse the cached execution plan, avoiding repeated SQL parsing and plan generation.

[0007] Preferably, the synchronous mode is suitable for scenarios that require high data consistency and need to be queried immediately after import; the asynchronous mode is suitable for high-frequency write scenarios that are sensitive to write latency and allow eventual consistency.

[0008] This invention proposes a server-side batch high-concurrency import system for OLAP databases, comprising: The client interface module is used to receive concurrent data import requests from multiple clients; The request scheduling and batching module is used to reuse multiple concurrent import requests into an internal import task and manage the corresponding data queue and transaction context. When creating an internal import task, it assigns a globally unique transaction identifier and version identifier to it. The mode processing module is used to call the corresponding processing logic according to the configured synchronous or asynchronous mode; The WAL management module is used to manage the writing and persistence of data in asynchronous mode. The asynchronous commit engine monitors commit conditions in the background and triggers the data to be persisted from the memory queue to the storage layer and the transaction to be committed when the conditions are met. This asynchronous commit module is coupled with the backend storage module of the database and is responsible for converting the data from the memory queue into segment files in columnar storage format and updating the metadata so that the new data is visible to queries. The execution plan caching module, located in the front-end node, is used to cache the SQL of prepared statements and their execution plans for reuse in subsequent sessions.

[0009] The beneficial effects of this invention are: 1. This invention solves the problem of linear growth in the number of transactions and versions in high-concurrency scenarios by actively aggregating and batching concurrent import requests from multiple clients on the server side, reusing them as a single internal transaction and a unique data version. This significantly reduces the computational, memory, and I / O overhead of the database system in transaction management, metadata maintenance, and background data compression, and greatly improves the overall throughput and scalability of the system.

[0010] 2. This invention introduces a dual-mode import mechanism that can be configured for both synchronous and asynchronous operations, and provides reliability assurance for the asynchronous mode through WAL. This enables the system to flexibly take into account the different needs of different business scenarios for data consistency, visibility, and write latency, and achieves the optimization goal of meeting both low-latency, high-throughput writing and strong consistency real-time query while ensuring that data is not lost.

[0011] 3. This invention combines PreparedStatement support with session-level execution plan caching, effectively avoiding the overhead of repetitive SQL parsing and optimization in high-frequency, small-batch insert scenarios, and specifically reducing the CPU and memory pressure on the front-end node, thereby further improving the system's processing efficiency and stability during ultra-high concurrency writes. Attached Figure Description

[0012] Figure 1 The diagram shown is a schematic representation of the system framework of this invention. Figure 2 The diagram shown is a schematic representation of the asynchronous mode process of this invention. Detailed Implementation

[0013] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are some embodiments of the present invention, but not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0014] Please see Figure 1 The present invention provides an embodiment: In this embodiment, the client interface module is responsible for listening to the network port, receiving and parsing standard data import requests from multiple clients. This module does not perform substantive processing on the request content, but only completes the parsing, verification and forwarding of the protocol, and passes the legitimate request payload to the downstream request scheduling and batching module. Its design supports high-concurrency connections and can effectively buffer burst traffic.

[0015] In this embodiment, the request scheduling and batching module maintains a global import task scheduler. When an import request is received from the client interface module, the scheduler first checks whether there is an active and reusable internal import task. If not, a new internal import task is created immediately. This task is a logical entity whose key attributes include a globally unique transaction ID, a globally unique data version number, an in-memory blocking queue (for temporarily storing data to be processed), and a write-ahead log (WAL) file associated with it in asynchronous mode. If an active internal import task already exists, the data of the new request is directly appended to the BlockQueue corresponding to that task. In this way, multiple concurrent import requests from different clients and different sessions are batched into the same internal task on the server side, thus sharing the same transaction and version at the underlying level. This module is also responsible for managing the lifecycle of the internal task, including creation, state maintenance, and triggering the final commit based on instructions from the mode processing module.

[0016] In this embodiment, the pattern processing module provides a configurable import strategy, mainly including two modes: In synchronous mode, this module waits for the internal import tasks in the request scheduling and batching modules to meet the commit conditions. Then, it instructs the system to commit all the data accumulated in the BlockQueue as a complete database transaction to the backend storage module. Only after the transaction is successfully persisted and a new data version is generated does the schema processing module notify the client interface module to return an "import successful" response to all associated clients. This mode ensures strong data consistency and immediate visibility.

[0017] In asynchronous mode, the execution path of this module differs. Once data is written to the BlockQueue, it immediately instructs the WAL management module to sequentially and persistently write the changes (or original data) to the WAL file. As soon as the WAL write is successful, the mode processing module immediately notifies the client interface module to return a "request received" response to the client, without waiting for the data to actually be stored in main storage. The actual commit action is delegated to the asynchronous commit module to execute in the background. This mode significantly reduces the perceived latency for the client and is suitable for throughput-priority scenarios.

[0018] In this embodiment, the WAL management module is specifically designed for asynchronous mode and is responsible for managing the write-ahead log. Its main responsibilities include: (1) Assign and manage independent WAL segment files for each internal import task; (2) Write data change records efficiently in an append-only manner; (3) Ensure the persistence of WAL writes; (4) After the asynchronous commit engine completes the data commit, safely clean up or archive the committed WAL logs; The introduction of WAL is key to ensuring that data is not lost in asynchronous mode. Even if the server crashes unexpectedly, uncommitted data can be recovered by replaying WAL after restarting.

[0019] In this embodiment, the asynchronous submission module continuously monitors all internal import tasks in asynchronous mode. Its core function is to check whether each task meets preset submission conditions. These conditions typically include time conditions and data volume conditions, specifically: (1) The time condition is whether the set time window has been reached since the internal task was created or last submitted. The default value is 10 seconds.

[0020] (2) The data volume condition is whether the accumulated data volume in the current BlockQueue has reached the set threshold, which is 64MB by default.

[0021] When any of the conditions are met, the asynchronous commit module retrieves all data from the task's BlockQueue, calls the backend storage engine's interface to convert the data into the database's inherent columnar storage format, updates the relevant metadata, and finally commits the transaction. After a successful commit, the new data becomes visible to queries, and the WAL management module is notified to clean up the corresponding logs.

[0022] In this embodiment, the execution plan caching module is located on the front-end node of the database, primarily optimizing high-frequency, small-batch writes such as INSERT INTO VALUES using interfaces like JDBC. When a client uses the PreparedStatement of the MySQL protocol, the first executed SQL statement undergoes a complete parsing, semantic analysis, and query planning process. This module caches the text of the SQL statement and its generated physical execution plan object within the Session's memory space. When the same PreparedStatement is executed again in the same session, the system directly hits the cache, skipping the repetitive and expensive SQL parsing and optimizer planning stages, thereby significantly reducing the CPU consumption of the front-end node.

[0023] Please participate Figure 2 In this embodiment, the asynchronous import process in asynchronous mode is described in detail: (1) Client 1 initiates an import request R1, which arrives at the client interface module of the server. The request is forwarded to the request scheduling and batching module. The module checks and finds that there are no active internal import tasks. So it creates a new task T1, generates a transaction ID and version number for it, and initializes an empty BlockQueue Q1 and WAL file W1.

[0024] (2) The data requested by R1 is appended to Q1. At the same time, the mode processing module (asynchronous mode) instructs the WAL management module to append the data change record of R1 to the WAL file W1. After confirming that W1 has been written to persistent storage, the server returns a success response to client 1 through the client interface module. At this time, the write operation is completed for client 1, but the data is not visible in the main storage of the database.

[0025] (3) While task T1 is still active, other clients 2, 3, etc., successively send requests R2, R3, etc. These requests are all routed to the same internal task T1 by the request scheduling and batching module, and the data is appended to Q1 in sequence, and the change records are also appended to the WAL file W1. Each client receives a response immediately after its data is written to the WAL.

[0026] (4) The asynchronous commit module in the background continuously monitors task T1. Assume that after R5 arrives, the amount of data accumulated in Q1 reaches the preset threshold of 64MB. The asynchronous commit module immediately triggers the commit operation. It locks Q1, retrieves all the data (from R1 to R5) at once, hands it over to the backend storage module, generates a new Segment file S1, updates the metadata, marks the version number as committed and visible, and finally notifies the WAL management module that the data in the W1 file has been persisted and can be cleaned up or marked as recyclable.

[0027] (5) After T1 is submitted, the request scheduling and batching module sets its status to complete. When the next new client request R6 arrives, the module will create a new internal task T2 and start a new batching cycle.

[0028] In this embodiment, the synchronous mode process is as follows: After the request is batched into the internal task, the client will not receive a response immediately. Instead, it is triggered by the application layer or configuration. The mode processing module instructs the system to commit all the data in the current BlockQueue as a transaction. After the commit is successful, all waiting clients are notified at once.

[0029] This invention provides Embodiment 1: This example demonstrates and verifies a basic asynchronous batch import process. The application is in a real-time monitoring system where approximately 5,000 monitoring metrics from hundreds of data acquisition terminals need to be written to the OLAP database every second for real-time dashboard display and short-term retrospective analysis. The write latency requirement is low (<500ms), and data must be visible within a few seconds.

[0030] This example uses asynchronous mode, with a commit time interval of 5 seconds and a data size threshold of 32MB. The client uses a simple INSERT statement to write data.

[0031] The operation is as follows: S1, the data acquisition end concurrently sends data to the database server.

[0032] S2: The server creates an internal task when the first request arrives, and all subsequent requests are accumulated into this task.

[0033] With S3, each request's data is typically responded to within 10 milliseconds after being written to the memory queue and WAL.

[0034] S4's background asynchronous submission engine triggers a batch submission every 5 seconds (or when the data accumulates to 32MB), generating a version of all the data collected from the past 5 seconds and writing it to disk.

[0035] Effect: For the client experience, the end-to-end latency for each write request is extremely low, consistently at the tens of milliseconds level, meeting real-time requirements. For the server load, this invention reduces 5000 writes per second (corresponding to 5000 transactions and versions) to approximately 0.2 commits per second (once every 5 seconds, corresponding to one transaction and version), reducing transaction management overhead, version metadata management overhead, and subsequent compaction pressure by two orders of magnitude. Furthermore, thanks to WAL (Write-Ahead Logging), even if the server crashes before a batch commit, it can still recover all data that responded to clients within those 5 seconds after restarting, ensuring no data loss.

[0036] This invention provides Embodiment 2: This example demonstrates the implementation and verification of a synchronous workflow. It is applied in a financial batch processing system, where a large amount of detailed transaction data needs to be imported from the business database to the OLAP database for rapid verification and summarization at the end of each day. The import process must be consistent, and an accuracy verification query must be performed immediately after each batch of data is imported. The import task itself needs to be completed as quickly as possible.

[0037] This example uses synchronous mode, with the data size threshold for submission set to 256MB and no time interval specified. The client uses JDBC's PreparedStatement for batch insertion.

[0038] The operation is as follows: S1, the application establishes a connection to the database and creates a PreparedStatement object.

[0039] S2, when this statement is executed for the first time, the execution plan caching module of the front-end node will parse and cache its execution plan.

[0040] In S3, the application reads source data in a loop, adds parameters continuously through PreparedStatement, and executes the requests. These execution requests are aggregated into a single internal task on the server side.

[0041] S4: When the accumulated data volume reaches 256MB, the request scheduling and batch processing module triggers a synchronous commit. The entire 256MB of data is committed as a single transaction, and upon successful commit, all associated client calls return success.

[0042] S5 allows the application to immediately initiate a verification query on this 256MB of data.

[0043] S6. Repeat steps 3-5 until all data has been imported.

[0044] Effect: This invention, through batch processing, compresses millions of transaction commits corresponding to hundreds of millions of records into hundreds of commits, significantly reducing the inherent overhead of transaction commits. Simultaneously, execution plan caching avoids repeated parsing and optimization of each INSERT statement. In a session importing hundreds of millions of records, only the first execution incurs parsing overhead; subsequent billions of executions directly utilize the cache, resulting in an estimated reduction of over 60% in FE node CPU utilization. Furthermore, this invention rigorously guarantees the atomicity of each batch of data and its immediate verifiability after import, meeting the stringent accuracy requirements of financial systems.

[0045] Comparative Example 1 provided by the present invention: This example verifies the effectiveness of the invention through a comparative experiment. The experiment was conducted on a standard OLAP database cluster, which includes 3 front-end nodes and 6 back-end storage nodes. The test data is a simulated e-commerce user behavior log schema.

[0046] There are two sets of comparison examples. Comparison example 1 is the independent transaction synchronous import method. This method treats each INSERT request as an independent transaction, and the client synchronously waits for the transaction to be successfully committed before returning.

[0047] Comparative Example 2 is a simple asynchronous buffered import method. This method first writes the data to the memory buffer of the storage node, and then a background thread periodically scans and flushes all the data in the buffer to the disk. The client returns immediately after writing the data to the memory buffer.

[0048] (1) In this example, 100 concurrent clients are set up to continuously send a single record of 1KB size, with a total data volume of 100GB. The results are shown in Table 1.

[0049] Table 1. Basic High-Concurrency Write Results

[0050] As shown in Table 1, the asynchronous mode of this invention is close to Comparative Example 2 (without WAL) in both throughput and latency, but provides the reliability guaranteed by WAL, whereas Comparative Example 2 would lose data under failure. Meanwhile, the transaction / version number of the asynchronous mode of this invention is much lower than that of Comparative Example 1. The synchronous mode of this invention achieves the highest throughput through larger batch sizes (in this experiment, due to high concurrency, the actual batch size is triggered according to the data volume threshold, resulting in larger batch sizes), while reducing the number of transactions to an extremely low level, and achieving better latency than Comparative Example 1. The CPU utilization in the front-end stage is the lowest, which is due to the batching effect under high concurrency and the potential cache reuse effect.

[0051] (2) In this example, there are 500 concurrent clients, each sending 10 records, about 10KB, to simulate a high-frequency reporting environment of massive sensor data. The results are shown in Table 2.

[0052] Table 2 Results of Ultra-High Frequency Small Batch Writing

[0053] As shown in Table 2, prior art A caused system metadata management to crash due to transaction explosion. The asynchronous mode of the present invention has basically the same number of transactions as Comparative Example 2, but the P99 latency is slightly higher. This is a small overhead of writing to WAL, which is exchanged for reliability. In terms of compaction time, the present invention improves compaction efficiency by nearly 50% due to better internal version management and more regular batch processing.

[0054] Enabling prepared statement caching (+preprocessing) significantly improves throughput and reduces latency, highlighting the immense value of the execution plan caching module in scenarios with extremely high frequency and small statements.

[0055] The above are merely preferred embodiments of the present invention and are not intended to limit the present invention in any other way. Any person skilled in the art may make changes or modifications to the above-disclosed technical content to create equivalent embodiments that can be applied to other fields. However, any simple modifications, equivalent changes, and modifications made to the above embodiments based on the technical essence of the present invention without departing from the scope of the present invention shall still fall within the protection scope of the present invention.

Claims

1. A server-side batch high-concurrency import method for OLAP databases, characterized in that: It includes the following steps: S1, the database server receives concurrent data import requests from multiple clients; S2, the server performs batch processing on multiple concurrent import requests received, reusing the import operations of multiple clients into an internal logical import task. This internal import task corresponds to a unique database transaction and a data version. S3: The server processes the internal import tasks according to the preset import mode. In synchronous mode, the batched data is submitted to the database storage layer as a whole transaction, and a successful import response is returned to the corresponding client after the transaction is successfully committed, ensuring that the data is immediately visible. In asynchronous mode, the batched data is first written to the WAL and persisted, and then an import success response is immediately returned to the client. The server background process asynchronously submits the data in the WAL to the database storage layer according to the preset submission conditions, and makes the data visible after submission. S4. In asynchronous mode, the server background process continuously monitors the data queue corresponding to the internal import task. When the preset time interval threshold is reached or the cumulative data volume reaches the preset size threshold, the data submission operation is triggered.

2. The server-side batch high-concurrency import method for OLAP databases according to claim 1, characterized in that: For write requests using the INSERT INTO VALUES statement via JDBC, the server-side front-end node supports the PreparedStatement feature of the MySQL protocol, which caches the first received SQL statement and its generated execution plan in a session-level memory cache. Subsequent import requests in the same session directly reuse the cached execution plan to avoid repeated SQL parsing and plan generation.

3. The server-side batch high-concurrency import method for OLAP databases according to claim 1, characterized in that: In step S2, when the server receives the first client import request, it creates an internal import task, a corresponding data queue, and an associated WAL file. For subsequent client import requests, the server directly reuses the data queue and WAL file of the created internal import task to append data.

4. The server-side batch high-concurrency import method for OLAP databases according to claim 1, characterized in that: The data queue is a blocking queue in the memory of the server backend process, used to temporarily store batch data to be processed; the WAL file is used to ensure data reliability in asynchronous mode before the data is persisted to the main storage.

5. The server-side batch high-concurrency import method for OLAP databases according to claim 1, characterized in that: The synchronous mode is suitable for scenarios that require high data consistency and need to be queried immediately after import; the asynchronous mode is suitable for high-frequency write scenarios that are sensitive to write latency and allow eventual consistency.

6. The server-side batch high-concurrency import method for OLAP databases according to claim 1, characterized in that: In step S3, in asynchronous mode, when returning a response to the client, the data only exists in the WAL and the memory queue.

7. The server-side batch high-concurrency import method for OLAP databases according to claim 1, characterized in that: In step S4, the time interval threshold and size threshold of the submission conditions are configurable.

8. A server-side batch high-concurrency import system for OLAP databases, based on any one of claims 1-7, characterized in that... Including: The client interface module is used to receive concurrent data import requests from multiple clients; The request scheduling and batching module is used to reuse multiple concurrent import requests into an internal import task and manage the corresponding data queue and transaction context. The mode processing module is used to call the corresponding processing logic according to the configured synchronous or asynchronous mode; The WAL management module is used to manage the writing and persistence of data in asynchronous mode. The asynchronous commit module is used to monitor commit conditions in the background and trigger the data to be persisted from the memory queue to the storage layer and the transaction to be committed when the conditions are met. The execution plan caching module, located in the front-end node, is used to cache the SQL of prepared statements and their execution plans for reuse in subsequent sessions.

9. The server-side batch high-concurrency import system for OLAP databases according to claim 8, characterized in that: When creating an internal import task, the request scheduling and batching module assigns a globally unique transaction identifier and version identifier to it.

10. The server-side batch high-concurrency import system for OLAP databases according to claim 8, characterized in that: The asynchronous submission module is coupled to the backend storage module of the database and is responsible for converting data from the memory queue into segment files in columnar storage format and updating metadata so that the new data is visible to queries.

Images