Introduction

The server_audit_plugin employs synchronous, per-event logging, causing performance bottlenecks. Individual file writes for each log entry result in significant I/O overhead, especially in large database environments.

Detailed Description (Solution)

A dedicated logging thread is implemented to reduce the main audit plugin thread's wait time from synchronous write operations.The main audit plugin thread now pushes messages into a thread-safe queue, which efficiently manages concurrent access. Upon each push, it checks if the queue has reached its capacity. If the queue is full, the main thread signals the logger thread to flush and process the messages. This approach ensures efficient logging by reducing the performance hit from frequent thread creation and maintaining smooth, high-throughput operations in write-heavy applications. To further guarantee security, we introduced an additional system variable that allows users to set a log buffer time. This enhancement enables the audit plugin to periodically poll the buffer queue. If the queue is not empty, it will flush the messages at specified intervals. This approach ensures that during high throughput periods, the buffer size will be exceeded and flushed accordingly. Conversely, during low throughput periods, users are guaranteed to receive their logs after a certain period. This dual-variable system provides a more adaptive and efficient logging mechanism, accommodating fluctuations in database activity while maintaining optimal performance and security.

Summary of Changes

Buffered Logging:

• Implement an in-memory buffer to reduce disk I/O by batching log writes.

Asynchronous Logging:

• Delegate logging to a dedicated logger thread for independent log handling,
  minimizing wait times for main audit threads

Dynamic Buffer Management:

• Add log_buffer_size and log_buffer_time system variables for user-customizable
  flushing based on data volume or time, adapting to varying workloads.

Confirm Performance Improvement:

• An average of 15% speed improvements confirmed in benchmark tests, demonstrating the
  effectiveness of these logging enhancements in high-load environments.

Additional Information

System Variables Added

************
Note: this section is outdated. Ref here and MDEV-36956 for the actual implemented variables.
************

server_audit_log_buffer_size

Description: When server_audit_log_buffer_size is set, it determines the maximum number of log messages
the buffer can hold. If the buffer reaches its limit, it flushes the logs and writes them to the file. The default value is 1, which means logs are directly written without buffering. Adjusting this value allows for batch logging, improving performance by reducing the overhead of writing each log message individually.

Commandline: --server-audit-log-buffer-size=value

Scope: Global

Dynamic: Yes

Data Type: numeric

Default Value: 1

Range: 1 to 9999

server_audit_log_buffer_time

Description: When server_audit_log_buffer_time is set, it determines how often, in milliseconds, the queue of log messages flushes itself. The default value is 1000 ms (1 second). This longer interval is due to the initial server_audit_log_buffer_size being 1, meaning logs are directly written without significant buffering. Adjusting this value allows for fine-tuning how frequently the logs are written, balancing between real-time logging and performance optimization.

Commandline: --server-audit-log-buffer-time=value

Scope: Global

Dynamic: Yes

Data Type: numeric

Default Value: 1000

Range: 1000 to 3600000 (1 hour)

Results

Below are the benchmarking results comparing three implementations: the original buffer size only, the default audit plugin, and the combination of buffer size and buffer time.

For these tests, the buffer size was set to 100, and the buffer time was set to 1000 ms (1 second).

Benchmarking with 64 Threads: Performance Improvements Compared to Default

Benchmarking with 256 Threads: Performance Improvements Compared to Default

Update After Feedback

After receiving feedback from Upstream, I was advised against adding additional threads since MariaDB already has built-in timer threads that can handle asynchronous time-based logging. Consequently, size-based logging will now operate synchronously. Utilizing the thr_time class, I have successfully implemented this approach. Below are the updated benchmarking results, comparing this implementation to the default audit plugin.

Below are the results for New Implementation set to Default Configurations. The `log_buffer_size` is set to 1 here to mimic instant logging.

64 Threads

256 Threads

512 Threads

Below are the results for New Implementation set to Buffered Configurations. The `log_buffer_size` is set to 1000 and `log_buffer_time` was set to 20 seconds here to mimic buffered logging.

64 Threads

256 Threads

512 Threads

We observe that instant logging with this implementation performs almost as efficiently as the default implementation. However, significant performance improvements are achieved when using the buffer.

Note: We were unable to benchmark time-based buffered logging because improvements or regressions can only be detected with high throughput, which can only test size-based buffering.