A method and system for high-concurrency log storage and automatic rotation based on embedded SQLite
By setting up a log access buffer layer and WAL mode in embedded SQLite, combined with connection pooling and exponential backoff retry mechanism, high-concurrency log storage and automatic rotation are achieved, solving the problems of low write throughput and rotation blocking in log storage in embedded devices, and improving the availability and resource utilization efficiency of the system.
Patent Information
- Application Number
- CN202611504776.6
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-09-24
- Publication Date
- 2026-11-17
AI Technical Summary
Existing log storage solutions in embedded devices suffer from low write throughput, blocking of foreground writes by round-robin operations, database file bloat, and the inability of round-robin strategies to adapt to load fluctuations.
Log batches are aggregated using a log access buffer layer, WAL mode is enabled, and a connection pool and exponential backoff retry mechanism are combined. Non-blocking round-robin is achieved through a control table, and background threads perform asynchronous archiving and fragment recycling.
It significantly improved write throughput, ensured the continuous availability of log services, and solved the problems of database file bloat and the adaptability of the rotation strategy.
Smart Images

Figure CN123115257A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of embedded system data storage technology, specifically to a high-concurrency log storage and automatic rotation method and system based on embedded SQLite. Background Technology
[0002] In embedded devices such as storage arrays and servers, event logs (such as kernel events, user operation audits, and hardware sensor alarms) typically need to be persistently stored in a local database for fault location and security auditing. SQLite, as a lightweight embedded relational database, is widely used in these devices due to its lack of a separate service process, zero configuration, single-file storage, and excellent cross-platform compatibility.
[0003] Currently, the commonly used SQLite-based log storage solutions in the industry typically work as follows: multiple log producers (such as event forwarding daemons, BMC acquisition clients, local alarm modules, etc.) send logs to a centralized log writing daemon via inter-process communication methods (such as Unix domain sockets or message queues), or each producer directly opens the SQLite database file and performs write operations. At the write level, existing solutions mostly use the sqlite3_exec interface or pre-compiled SQL statements to execute INSERT INTO evt_log (time, level, content) VALUES (?, ?, ?), and each time a record is inserted, an implicit transaction is started, and the database lock is released immediately after commit. To avoid conflicts between multiple writers, the application layer usually uses file locks or mutexes to ensure that only one writer is active at a time, or serializes all write requests through a single queue. Regarding log volume control, existing solutions typically start a timer (e.g., triggered hourly) in the write daemon process to execute the statement `DELETE FROM evt_log WHERE id IN (SELECT id FROM evt_log ORDER BY id ASC LIMIT N)`, deleting the oldest N records in batches to keep the total number of records below a preset threshold. Furthermore, to reclaim the disk space occupied by deleted records, some solutions periodically execute the `VACUUM` command to rebuild the database files.
[0004] However, the aforementioned existing technical solutions have the following significant drawbacks in practical applications:
[0005] First, the write throughput is low. Because a disk synchronization operation is performed separately for each log insertion (depending on the PRAGMA synchronous setting), and multiple producers competing for database write locks leads to a large number of SQLITE_BUSY errors and retry overhead, actual tests show that the existing solution's write capacity is usually less than 100 records per second, which is difficult to meet the log recording needs during sudden peak periods.
[0006] Second, rotation operations can cause write interruptions. When executing a DELETE statement to delete a large number of old records, SQLite locks the entire database table, blocking all write operations during this period. The blocking time is proportional to the number of rows deleted. For example, deleting 10,000 records may block writes for hundreds of milliseconds. For systems that need to log 24 / 7, such blocking may lead to business timeouts or log buffer overflows.
[0007] Third, the problem of database file bloat is prominent. Frequent deletion operations generate a large number of free page fragments in the database file, and the size of the SQLite database file does not automatically shrink as records are deleted. Even if the VACUUM command is executed periodically, this operation requires copying the entire database file, which requires twice the amount of free space on the disk as the original file, and the database is unavailable during the execution process. This is often unacceptable for embedded devices with already limited storage resources.
[0008] Fourth, fixed-period polling strategies cannot adapt to load fluctuations. During peak log writing periods, scheduled deletion may not be triggered in time, leading to database record limits or even fullness; while during idle log writing periods, frequent rotation and deletion operations waste system resources.
[0009] Fifth, the existing solution does not fully utilize the batch write mechanism. Committing each log entry separately results in excessive disk I / O operations, failing to leverage SQLite's batch transaction commit capability to reduce disk synchronization overhead. Summary of the Invention
[0010] To address these issues, this invention provides a method and system for high-concurrency log storage and automatic rotation based on embedded SQLite, in order to solve the technical problems of severe concurrent write conflicts, rotation operations blocking foreground writes, easy expansion of database files, and the inability of rotation strategies to adapt to load fluctuations in existing technologies.
[0011] To achieve the above objectives, the embodiments of the present invention provide the following technical solutions:
[0012] According to a first aspect of the present invention, a method for high-concurrency log storage and automatic rotation based on embedded SQLite is provided, the method comprising:
[0013] Set up a log access buffer layer on the log producer side to aggregate multiple logs into log batches according to a quantity threshold or time window;
[0014] Obtain a database connection from the SQLite connection pool, enable WAL mode, start an explicit transaction, and write the log batch to the currently active log table using a single INSERT statement containing a list of multiple values.
[0015] When a write return lock conflict error occurs, an exponential backoff strategy is used to perform a limited number of retries. If the retry fails, the log batch is written to the fault persistence queue for background replay.
[0016] After each successful batch write, the record count in the control table is atomically updated, and it is determined whether the record count of the currently active table has reached the preset rotation threshold.
[0017] If the rotation threshold is reached, a new table is created, and subsequent writes are switched to the new table by atomically updating the active table name field in the control table, thus achieving non-blocking rotation.
[0018] The original active table is renamed and placed into the asynchronous archive queue, and the background thread performs deletion or compression of archives according to the system configuration.
[0019] A background thread periodically monitors the proportion of free pages in the currently active table. When the proportion of free pages exceeds a preset threshold and the system load is lower than a preset load threshold, a fragment reclamation operation is triggered.
[0020] Furthermore, the quantity threshold is 200 entries, and the time window is 100 milliseconds; when the number of log entries accumulated in the buffer layer reaches the quantity threshold or the time since the last refresh reaches the time window, the log batch is triggered to commit.
[0021] Furthermore, the initial waiting time of the exponential backoff strategy is 5 milliseconds, and the waiting time increases exponentially after each retry until it reaches a maximum of 100 milliseconds, with a maximum of 10 retries; the fault persistence queue is a local file, which is periodically scanned and rewritten by an independent background replay thread, and the corresponding fault record is deleted after successful writing.
[0022] Furthermore, the atomic update of the active table name field in the control table is performed using a single UPDATE statement. After the active table name field is updated, all subsequent write operations determine the target table by reading the value of the active table name field. The structure of the new table is exactly the same as the original active table, containing the same field definitions and index structure.
[0023] Furthermore, the system configuration includes a direct deletion mode, a compressed archive mode, and a retain N archive mode; in the compressed archive mode, the VACUUM INTO command is used to compress the data of the original active table into a new database file and then replace the original file; in the retain N archive mode, the N most recent archive tables are retained and the earlier archive tables are deleted.
[0024] Furthermore, the free page ratio is obtained by executing PRAGMA freelist_count to get the number of free pages and calculating it in combination with the total number of pages in the database; the preset ratio threshold is 20%, and the preset load threshold is CPU utilization of 30%; the fragmentation reclamation operation is executed using the VACUUM command or the VACUUM INTO command.
[0025] According to a second aspect of the present invention, a high-concurrency log storage and automatic rotation system based on embedded SQLite is provided, the system comprising:
[0026] The log access buffer module is deployed on each log producer end. It is used to aggregate multiple logs into a log batch according to a quantity threshold or time window, and submit the log batch to the database writing module when the trigger condition is met.
[0027] The database connection management module is used to maintain the SQLite connection pool, enable WAL mode, and allocate a database connection for each write request.
[0028] The adaptive write module is used to execute batch insert statements through the database connection. When encountering lock conflicts, it adopts an exponential backoff strategy to perform a limited number of retries, and writes the log batches to the fault persistence queue after the retry fails.
[0029] The control table management module is used to maintain a control table containing active table names and record counts, and atomically update the record counts after each batch write is successful.
[0030] The dual-table rotation control module is used to create a new table and switch subsequent writes to the new table by atomically updating the active table name field of the control table when the record count in the control table reaches a preset rotation threshold.
[0031] The asynchronous archiving module is used to rename the original active table and then asynchronously perform deletion or compression archiving operations;
[0032] The fragmentation reclamation module is used to periodically monitor the proportion of free pages in the currently active tables and trigger fragmentation reclamation operations when the proportion of free pages exceeds a preset threshold and the system load is lower than a preset load threshold.
[0033] Furthermore, in the database connection management module, each write thread holds an independent SQLite database connection, each connection has a busy_timeout of 5000 milliseconds, and the explicit transaction is started through the BEGIN IMMEDIATE statement.
[0034] Furthermore, in the dual-table rotation control module, the rotation threshold is measured using either the total number of records or the size of the database file, and the rotation threshold is dynamically adjusted according to the current write rate during system operation.
[0035] Furthermore, the asynchronous archiving module and the fragmentation recycling module run in a low-priority background thread and are executed concurrently with the foreground write operation; before performing the fragmentation recycling operation, the fragmentation recycling module first queries the current system CPU utilization and disk utilization through the operating system interface, and only triggers execution when both the CPU utilization and the disk utilization are lower than their respective preset thresholds.
[0036] The embodiments of the present invention have the following advantages:
[0037] This invention employs a log access buffer layer to aggregate multiple logs into batches for batch writing based on quantity thresholds or time windows. It enables SQLite's WAL mode, combined with connection pooling and exponential backoff retry mechanisms, to support concurrent writing across multiple connections. A control table records the currently active table name and record count; when the record count reaches a threshold, a new table is dynamically created and the control table is atomically updated, switching writing to the new table for non-blocking rotation. A background thread asynchronously archives old tables and adaptively triggers fragment reclamation based on system load and the proportion of free pages. This invention is applicable to embedded products such as storage arrays and network devices that require 24 / 7 uninterrupted log recording, significantly improving write throughput and ensuring continuous log service availability. Attached Figure Description
[0038] To more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are merely exemplary, and those skilled in the art can derive other embodiments based on the provided drawings without creative effort.
[0039] The structures, proportions, sizes, etc. illustrated in this specification are only for the purpose of assisting those skilled in the art in understanding and reading the content disclosed herein, and are not intended to limit the conditions under which the present invention can be implemented. Therefore, they have no substantial technical significance. Any modifications to the structure, changes in the proportions, or adjustments to the size, without affecting the effects and objectives that the present invention can produce, should still fall within the scope of the technical content disclosed in the present invention.
[0040] Figure 1 This is a schematic diagram of the logical structure of a high-concurrency log storage and automatic rotation system based on embedded SQLite, provided in an embodiment of the present invention.
[0041] Figure 2 This is a flowchart illustrating a high-concurrency log storage and automatic rotation method based on embedded SQLite, provided in an embodiment of the present invention. Detailed Implementation
[0042] The following specific embodiments illustrate the implementation of the present invention. Those skilled in the art can easily understand other advantages and effects of the present invention from the content disclosed in this specification. Obviously, the described embodiments are only some, not all, of the embodiments of the present invention. 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.
[0043] refer to Figure 1 This invention discloses a high-concurrency log storage and automatic rotation system based on embedded SQLite. The system includes a log access buffer module deployed on each log producer, used to aggregate multiple logs into log batches according to a quantity threshold or time window, and submit the log batches to the database write module when a trigger condition is met. In this embodiment, each log producer integrates a log access buffer module.
[0044] The database connection management module is used to maintain the SQLite connection pool, enable WAL (Write-Ahead Logging) mode, and allocate a database connection for each write request. In this embodiment, the connection pool maintains 5 database connections, each write thread holds an independent SQLite database connection, and each connection has a busy_timeout of 5000 milliseconds.
[0045] The adaptive write module is used to execute batch insert statements through the database connection. When encountering lock conflicts, it adopts an exponential backoff strategy to perform a limited number of retries, and writes the logs in batches to the fault persistence queue after the retry fails.
[0046] The control table management module is used to maintain a control table containing active table names and record counts, and atomically update the record counts after each batch write is successful.
[0047] Atomic update refers to the update operation being executed as an indivisible unit, without any partial completion.
[0048] The dual-table rotation control module is used to create a new table and switch subsequent writes to the new table by atomically updating the active table name field in the control table when the record count in the control table reaches a preset rotation threshold.
[0049] The asynchronous archiving module is used to rename the original active table and then asynchronously perform deletion or compression archiving operations.
[0050] The fragmentation reclamation module is used to periodically monitor the proportion of free pages in the currently active tables and trigger fragmentation reclamation operations when the proportion of free pages exceeds a preset threshold and the system load is lower than a preset load threshold.
[0051] In this embodiment, upon system startup, a control table named `log_control` is first created in the SQLite database. This control table contains the following fields: `key` (primary key, string type), `active_table` (string type, recording the name of the currently active table), `row_count` (integer type, recording the total number of records in the currently active table), and `update_time` (timestamp type, recording the last update time). Simultaneously, the system creates the first active log table named `evt_log_active`. This table contains the following fields: `id` (integer type, auto-incrementing primary key), `time` (integer type, log generation timestamp), `level` (integer type, log level), and `content` (text type, log content). Indexes are created on the `time` and `level` fields to accelerate queries. The initial value of the `active_table` field in the control table is set to `evt_log_active`, and the initial value of the `row_count` field is set to 0.
[0052] Corresponding to the aforementioned high-concurrency log storage and automatic rotation system based on embedded SQLite, this invention also discloses a high-concurrency log storage and automatic rotation method based on embedded SQLite. The following details a high-concurrency log storage and automatic rotation method based on embedded SQLite disclosed in this invention, in conjunction with the aforementioned high-concurrency log storage and automatic rotation system based on embedded SQLite.
[0053] refer to Figure 2This invention discloses a method for high-concurrency log storage and automatic rotation based on embedded SQLite.
[0054] The high-concurrency write process in this embodiment includes the following steps:
[0055] Step S101: The log enters the buffer layer.
[0056] Each log producer (event forwarding daemon, BMC acquisition client, and local alarm module) calls a unified log writing interface after generating a log, passing each log entry to its internal log access buffer module. This log access buffer module maintains a cache queue in memory to temporarily store received log entries. The log format is a preset structured format, including the log level, log generation time, log content, and the name of the module that generated the log. For example, a log entry might be formatted as: info 2026-08-17 14:32:15.123456 event_daemon: disk / dev / sdatemperature normal.
[0057] Step S102: Determine whether the batch write trigger condition has been met.
[0058] The log access buffer module continuously monitors the number of log entries accumulated in the cache queue and records the time elapsed since the last refresh. Batch writing is triggered when any of the following conditions are met:
[0059] (1) The number of log entries accumulated in the cache queue reaches a preset threshold. In this embodiment, the threshold is preferably 200 entries.
[0060] (2) The time elapsed since the last refresh reaches a preset time window. In this embodiment, the time window is preferably 100 milliseconds.
[0061] If neither of the above two conditions is met, the buffer module continues to accumulate logs, waiting for the next trigger condition to arrive.
[0062] Step S103: Obtain a database connection from the connection pool and start a transaction.
[0063] When a batch write operation is triggered, the log access buffer module submits the entire batch of logs (e.g., 200 records) in the cache queue to the adaptive write module. The adaptive write module obtains an idle SQLite database connection from the connection pool maintained by the database connection management module. In this embodiment, five database connections are pre-created in the connection pool. Each connection has executed the `PRAGMA journal_mode=WAL` command upon establishment, enabling WAL mode; simultaneously, each connection has executed the `PRAGMA busy_timeout=5000` command, setting the lock wait timeout to 5000 milliseconds.
[0064] The adaptive write module initiates an explicit transaction by executing the BEGIN IMMEDIATE statement upon obtaining a database connection. The BEGIN IMMEDIATE statement immediately attempts to acquire a write lock on the database; if it cannot acquire the lock, it waits, thus avoiding the situation where the lock is already held when executing subsequent insert statements.
[0065] Step S104: Perform batch insertion.
[0066] The adaptive write module constructs batch insert statements. In this embodiment, assuming the currently active table is named evt_log_active and there are 200 log entries to be written in the cache queue, the following SQL statement is constructed:
[0067] INSERT INTO evt_log_active (time, level, content) VALUES (?, ?, ?),(?, ?, ?), ... (a total of 200 parameters)
[0068] This batch insert statement is compiled using the pre-compiled sqlite3_prepare_v2 interface, then all parameters are bound through the sqlite3_bind series of interfaces, and finally the sqlite3_step is called to execute the insert. By carrying a batch of 200 log entries in a single SQL statement, the number of interactions with the SQLite engine and disk synchronizations is significantly reduced.
[0069] Step S105: Determine the write result and handle lock conflicts.
[0070] After executing sqlite3_step, the adaptive write module checks the return code:
[0071] (1) If SQLITE_DONE is returned, it means that the batch insertion was successfully executed and proceed to step S106.
[0072] (2) If SQLITE_BUSY or SQLITE_LOCKED is returned, it means that the database is locked by other writers, and proceed to step S105a to perform exponential backoff retry.
[0073] Step S105a: Index backoff retry.
[0074] When an SQLITE_BUSY or SQLITE_LOCKED error is encountered, the adaptive write module initiates an exponential backoff retry mechanism. This mechanism retryes according to the following strategy: the initial wait time is 5 milliseconds, and the wait time increases exponentially after each retry (i.e., 5 milliseconds for the first retry, 10 milliseconds for the second, 20 milliseconds for the third, 40 milliseconds for the fourth, and 100 milliseconds for the fifth and subsequent retryes), with a maximum of 10 retries. After each wait period, sqlite3_step is re-executed.
[0075] If the operation is successful within 10 retries, proceed to step S106.
[0076] If the SQLITE_BUSY or SQLITE_LOCKED is still returned after 10 retries, proceed to step S105b.
[0077] Step S105b: Write to the fault persistence queue.
[0078] If the retries fail after 10 attempts, the adaptive write module writes the batch of 200 log entries to a local fault persistence queue. In this embodiment, the fault persistence queue is implemented as a local file / var / log / failed.sql. Each failed log entry is recorded line by line in JSON format, along with a timestamp of when it was generated and the reason for the retry failure.
[0079] Simultaneously, the system starts a separate background replay thread. This thread scans the fault file every 30 seconds, and if there are log records to be replayed, it attempts to re-execute the write operation. After a successful write, the corresponding record is deleted from the fault file. This mechanism ensures that logs are not lost under extreme pressure.
[0080] Step S106: Commit the transaction and update the control table.
[0081] After successful batch insertion, the adaptive write module executes a COMMIT statement to commit the transaction and release the database lock. Subsequently, the record count in the control table is atomically updated by the control table management module. Specifically, the following SQL statement is executed:
[0082] UPDATE log_control SET row_count = row_count + 200, update_time =CURRENT_TIMESTAMP WHERE key = 'log_config'
[0083] This update operation increments the row_count field value in the control table by the number of rows written in this batch (200). Because this UPDATE statement is executed as an atomic operation in SQLite, there will be no issue of count errors caused by multiple writers updating simultaneously.
[0084] Step S107: Release the database connection.
[0085] After the write operation is complete, the adaptive write module releases the database connection back to the connection pool for use by other write requests.
[0086] The lockless dual-meter rotation process in this embodiment includes the following steps:
[0087] Step S201: Check whether the record count has reached the rotation threshold.
[0088] After each successful batch write and update of the control table, the dual-table rotation control module reads the row_count value in the control table and determines whether it has reached or exceeded the preset rotation threshold. In this embodiment, the rotation threshold is preset to 1 million records. That is, rotation is triggered when row_count >= 1,000,000.
[0089] In this embodiment, the round-robin operation is performed by the write thread that currently triggers the round-robin condition. In other embodiments, it can also be handled by a separate round-robin thread, triggered by a signal or queue notification.
[0090] Step S202: Create a new table.
[0091] When rotation is determined to be necessary, the dual-table rotation control module executes a table creation statement to create a new log table. The name of the new table is generated according to a preset naming rule. In this embodiment, the new table is named evt_log_active_new, and its table structure is exactly the same as the original active table, containing the same field definitions (id, time, level, content) and the same index structure (time field index and level field index).
[0092] The following is an example of a table creation statement:
[0093] CREATE TABLE evt_log_active_new (id INTEGER PRIMARY KEYAUTOINCREMENT, time INTEGER, level INTEGER, content TEXT)
[0094] CREATE INDEX idx_time_new ON evt_log_active_new (time)
[0095] CREATE INDEX idx_level_new ON evt_log_active_new (level)
[0096] Step S203: Atomic update control table to switch active table.
[0097] After the new table is created, the dual-table rotation control module executes an atomic update statement to switch the active table name in the control table to the new table and reset the record count to 0:
[0098] UPDATE log_control SET active_table = 'evt_log_active_new', row_count= 0, update_time = CURRENT_TIMESTAMP WHERE key = 'log_config'
[0099] This UPDATE statement is executed as a single transaction in SQLite, with an execution time in the milliseconds. After the statement completes, all subsequent write operations will automatically obtain the evt_log_active_new value when reading the active_table field in the control table, thus writing the subsequent logs to the new table. Because this switchover operation is atomic and extremely short, the foreground write operations are completely unaware of it.
[0100] Step S204: Rename the original active table.
[0101] After the atomic switch is completed, the dual-table rotation control module renames the original active table (which has now been replaced by the new table) to an archive table. In this embodiment, the renaming operation is as follows:
[0102] ALTER TABLE evt_log_active RENAME TO evt_log_archive_20260817_143215
[0103] 20260817_143215 is the timestamp (in YYYYMMDD_HHMMSS) when the rotation was triggered. This ALTER TABLE RENAME operation only modifies the table name record in the SQLite internal system table, consumes almost no time, and does not involve data copying or moving.
[0104] Step S205: Place the archive table into the asynchronous queue.
[0105] After the renaming is complete, the dual-table rotation control module adds the name of the archive table (evt_log_archive_20260817_143215) to the asynchronous archive queue and then returns immediately. The rotation thread does not wait for subsequent archiving processing, and foreground write operations continue to write logs to the new table evt_log_active_new. At this point, the entire rotation process is complete, without any write blocking.
[0106] The asynchronous archiving and fragmentation recycling process in this embodiment includes the following steps:
[0107] Step S301: The background thread retrieves the archive table name from the queue.
[0108] A low-priority background archiving thread is created upon system startup. This thread continuously listens to the asynchronous archiving queue, retrieving new archive table names from the queue for processing. This background thread runs at low priority and will not preempt CPU resources from foreground write operations.
[0109] Step S302: Execute the archiving policy according to the configuration.
[0110] The background archiving thread determines the processing strategy for the archived tables based on the system configuration. In this embodiment, the system provides three configurable archiving strategies:
[0111] (1) Direct deletion mode: Execute the statement DROP TABLE evt_log_archive_20260817_143215 to directly delete the archive table and immediately release the disk space.
[0112] (2) Compact Archive Mode: Execute the command VACUUM INTO 'compact.db' to compress the data of the archived tables into a new compact database file, then replace the original file and delete the original tables. The VACUUM INTO command will rebuild the database file, remove free pages and fragments, and generate a compact file with a size close to the actual amount of data.
[0113] (3) Retain N archive modes: Retain the most recent N archive tables and delete earlier archive tables. For example, if configured to retain the most recent 5 archives, the system will retain the latest 5 archive tables and delete the 6th and earlier archive tables.
[0114] In this embodiment, the system is configured to "retain 3 archive modes," meaning that the archive tables generated in the three most recent rotations are always retained, while earlier archive tables are automatically deleted. This retains recent logs for troubleshooting while controlling the total storage space usage.
[0115] Step S303: Monitor the proportion of free pages and trigger fragmentation reclamation.
[0116] In addition to processing the archive table, the background archiving thread also periodically (every 10 minutes in this embodiment) performs fragmentation monitoring. Specifically, it executes the following command on the currently active table to obtain the number of free pages:
[0117] PRAGMA freelist_count
[0118] Additionally, the total number of database pages can be obtained using the following command:
[0119] PRAGMA page_count
[0120] The background archiving thread calculates the free page ratio as: number of free pages / total number of pages × 100%. If this ratio exceeds a preset threshold (20% in this embodiment), the current system load is further checked.
[0121] System load is checked by obtaining the current CPU utilization through the operating system interface. In this embodiment, CPU utilization is calculated by reading the / proc / stat file. If the current CPU utilization is lower than a preset load threshold (30% in this embodiment), the system is considered to be in an idle state, suitable for performing fragmentation reclamation.
[0122] When the proportion of free pages exceeds 20% and CPU utilization is below 30%, the background archiving thread performs a fragmentation reclamation operation on the currently active tables. In this embodiment, the fragmentation reclamation operation is performed using the VACUUM command. The VACUUM command rebuilds the entire database file, reorganizes the data into contiguous storage, releases all free pages, and brings the database file size close to the actual data volume.
[0123] Since the garbage collection operation is performed in a low-priority background thread and is only triggered when the system load is low, it does not have a significant impact on foreground write operations.
[0124] Although the present invention has been described in detail above with general descriptions and specific embodiments, modifications or improvements can be made to it, which will be obvious to those skilled in the art. Therefore, all such modifications or improvements made without departing from the spirit of the present invention fall within the scope of protection claimed by the present invention.
Claims
1. A method for high-concurrency log storage and automatic rotation based on embedded SQLite, characterized in that, The method includes: Set up a log access buffer layer on the log producer side to aggregate multiple logs into log batches according to a quantity threshold or time window; Obtain a database connection from the SQLite connection pool, enable WAL mode, start an explicit transaction, and write the log batch to the currently active log table using a single INSERT statement containing a list of multiple values. When a write return lock conflict error occurs, an exponential backoff strategy is used to perform a limited number of retries. If the retry fails, the log batch is written to the fault persistence queue for background replay. After each successful batch write, the record count in the control table is atomically updated, and it is determined whether the record count of the currently active table has reached the preset rotation threshold. If the rotation threshold is reached, a new table is created, and subsequent writes are switched to the new table by atomically updating the active table name field in the control table, thus achieving non-blocking rotation. The original active table is renamed and placed into the asynchronous archive queue, and the background thread performs deletion or compression of archives according to the system configuration. A background thread periodically monitors the proportion of free pages in the currently active table. When the proportion of free pages exceeds a preset threshold and the system load is lower than a preset load threshold, a fragment reclamation operation is triggered.
2. The method for high-concurrency log storage and automatic rotation based on embedded SQLite as described in claim 1, characterized in that, The quantity threshold is 100-500 records, preferably 200 records, and the time window is 50-200 milliseconds, preferably 100 milliseconds; when the number of log records accumulated in the buffer layer reaches the quantity threshold or the time since the last refresh reaches the time window, the log batch is triggered to commit.
3. The high-concurrency log storage and automatic rotation method based on embedded SQLite as described in claim 1, characterized in that, The initial waiting time for the exponential backoff strategy is 5 milliseconds. After each retry, the waiting time increases exponentially until it reaches a maximum of 100 milliseconds. There is an upper limit to the number of retries. The fault persistence queue is a local file, which is periodically scanned and rewritten by an independent background replay thread. After a successful write, the corresponding fault record is deleted.
4. The high-concurrency log storage and automatic rotation method based on embedded SQLite as described in claim 1, characterized in that, The atomic update of the active table name field in the control table is performed using a single UPDATE statement. After the active table name field is updated, all subsequent write operations determine the target table by reading the value of the active table name field. The structure of the new table is exactly the same as the original active table, containing the same field definitions and index structure.
5. The high-concurrency log storage and automatic rotation method based on embedded SQLite as described in claim 1, characterized in that, The system configuration includes direct deletion mode, compressed archive mode, and retain N archive modes; in the compressed archive mode, the VACUUM INTO command is used to compress the data of the original active table into a new database file and then replace the original file. In the "Retain N Archives" mode, the N most recent archive tables are retained and the earlier archive tables are deleted.
6. The method for high-concurrency log storage and automatic rotation based on embedded SQLite as described in claim 1, characterized in that, The proportion of free pages is obtained by executing PRAGMA freelist_count to get the number of free pages and combining it with the total number of pages in the database; the fragmentation reclamation operation is executed using the VACUUM command or the VACUUM INTO command.
7. A high-concurrency log storage and automatic rotation system based on embedded SQLite, characterized in that, The system includes: The log access buffer module is deployed on each log producer end. It is used to aggregate multiple logs into a log batch according to a quantity threshold or time window, and submit the log batch to the database writing module when the trigger condition is met. The database connection management module is used to maintain the SQLite connection pool, enable WAL mode, and allocate a database connection for each write request. The adaptive write module is used to execute batch insert statements through the database connection. When encountering lock conflicts, it adopts an exponential backoff strategy to perform a limited number of retries, and writes the log batches to the fault persistence queue after the retry fails. The control table management module is used to maintain a control table containing active table names and record counts, and atomically update the record counts after each batch write is successful. The dual-table rotation control module is used to create a new table and switch subsequent writes to the new table by atomically updating the active table name field of the control table when the record count in the control table reaches a preset rotation threshold. The asynchronous archiving module is used to rename the original active table and then asynchronously perform deletion or compression archiving operations; The fragmentation reclamation module is used to periodically monitor the proportion of free pages in the currently active tables and trigger fragmentation reclamation operations when the proportion of free pages exceeds a preset threshold and the system load is lower than a preset load threshold.
8. A high-concurrency log storage and automatic rotation system based on embedded SQLite as described in claim 7, characterized in that, In the database connection management module, each write thread holds an independent SQLite database connection, each connection has a busy_timeout of 5000 milliseconds, and the explicit transaction is started by the BEGIN IMMEDIATE statement.
9. A high-concurrency log storage and automatic rotation system based on embedded SQLite as described in claim 7, characterized in that, In the dual-table rotation control module, the rotation threshold is measured using either the total number of records or the size of the database file, and the rotation threshold is dynamically adjusted based on the current write rate during system operation.
10. A high-concurrency log storage and automatic rotation system based on embedded SQLite as described in claim 7, characterized in that, The asynchronous archiving module and the fragmentation recycling module run in low-priority background threads and are executed concurrently with the foreground write operation. Before performing the fragmentation recycling operation, the fragmentation recycling module first queries the current system CPU utilization and disk utilization through the operating system interface, and only triggers execution when both the CPU utilization and the disk utilization are lower than their respective preset thresholds.
