Help Center/ TaurusDB/ User Guide/ Log Management/ Managing SQL Audit Logs/ SQL Audit Performance Testing
Updated on 2026-08-13 GMT+08:00
View PDF
Share

SQL Audit Performance Testing

Test Scenarios

This section compares the performance differences between enabling and disabling SQL audit under different workloads (write-only, read-only, and mixed read/write).

Test Environment

The TaurusDB test environment is as follows:

  • Region: AP-Singapore
  • AZ type: multi-AZ
  • Instance type: cluster instance with one primary node and one read replica
  • Instance specifications: 16 vCPUs | 64 GB
  • Elastic Cloud Server (ECS): general computing-plus | c9 | c9.4xlarge.2 | 16 vCPUs | 32 GB. The ECS and DB instance nodes are in the same AZ. Bind an EIP to the ECS because the stress testing tool requires additional compilation tools.

Test Tools

Table 1 Test tools

Tool

Description and Download Method

Version

Sysbench

Sysbench is a modular, multi-threaded benchmark tool developed based on LuaJIT. It is widely used for database performance testing. It evaluates database performance through built-in test models and multi-threaded concurrent operations. See the Sysbench introduction for more details.

Sysbench 1.0.18

Installation commands:

  1. Log in to an ECS and download the Sysbench software package. 

    wget https://codeload.github.com/akopytov/sysbench/zip/refs/tags/1.0.18
yum install -y autoconf libtool mysql mysql-devel vim unzip

  2. Decompress the installation package: 

    unzip 1.0.18

  3. Install the software package. 

    cd sysbench-1.0.18
./autogen.sh
./configure
make
make install
     
    4.  
  4. Query the Sysbench version: 
     
    sysbench --version
     
    Command output:
     
    sysbench 1.0.17
     
    5.  
 
 
Test Metrics
 
     
  • TPS refers to the number of transactions executed per second.
    •  
  • QPS refers to the number of SQL statements executed per second, including INSERT, SELECT, UPDATE, and DELETE statements.
    •  
 
 
 
Test Procedure
 
   
 
     
  • All the tests are performed on an ECS. Replace the number of concurrent threads, connection IP address, connection port, username, and user password with your actual values.
    •  
  • All performance test data (including SQL statements) is automatically generated by Sysbench.
    •  
  • To ensure that Sysbench runs properly under high concurrency (512 to 1,000 threads), increase the value of max_prepared_stmt_count. The recommended value is 1048576. Excessive prepared statements consume memory and can cause out-of-memory (OOM) issues. For an instance with 4 vCPUs and 16 GB of memory, set this parameter to 400000. 
    For details about how to modify parameters, see Modifying Instance Parameters.
    •  
 
 
 
 
  
 
 
 
     
  1. Import data. 
     
       
    1. Create the test database sbtest. 
      mysql -u user -P port -h host -p -e "create database sbtest"
      2.  
    2. Import the test data to the sbtest database. 
      sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=25000 --tables=250 --threads=thread_num oltp_common prepare
      3.  
    3. Verify that the data has been imported. 
      mysql -u user -P port -h host -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'sbtest';select count(*) from sbtest.sbtest1"
       
      Command output:
       
      +----------+
| COUNT(*) |
+----------+
|      250 |
+----------+
+----------+
| COUNT(*) |
+----------+
|    25000 |
+----------+
      4.  
     
    2.  
  2. Run the performance test. The process takes 10 minutes. 
     
    sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=25000 --tables=250 --time=600   --threads=thread_num --percentile=95 --report-interval=1 oltp_write_only run
     
    3.  
  3. Clean up data. 
     
    sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=25000 --tables=250 --threads=thread_num oltp_common cleanup
     
    4.  
 
 
 
 
 
     
  1. Import data. 
     
       
    1. Create the test database sbtest. 
      mysql -u user -P port -h host -p -e "create database sbtest"
      2.  
    2. Import the test data to the sbtest database. 
      sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=25000 --tables=250 --threads=thread_num oltp_common prepare
      3.  
    3. Verify that the data has been imported: 
      mysql -u user -P port -h host -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'sbtest';select count(*) from sbtest.sbtest1"
       
      Command output:
       
      +----------+
| COUNT(*) |
+----------+
|      250 |
+----------+
+----------+
| COUNT(*) |
+----------+
|    25000 |
+----------+
      4.  
     
    2.  
  2. Run the read-only performance test. The process takes about 10 minutes. 
     
    sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=25000 --tables=250 --time=600  --range_selects=0 --skip-trx=1  --threads=thread_num --percentile=95 --report-interval=1 oltp_read_only run
     
    3.  
  3. Clean up data. 
     
    sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=25000 --tables=250 --threads=thread_num oltp_common cleanup
     
    4.  
 
 
 
 
 
     
  1. Import data. 
     
       
    1. Create the test database sbtest. 
      mysql -u user -P port -h host -p -e "create database sbtest"
      2.  
    2. Import the test data to the sbtest database. 
      sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=25000 --tables=250 --threads=thread_num oltp_common prepare
      3.  
    3. Verify that the data has been imported: 
      mysql -u user -P port -h host -p -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'sbtest';select count(*) from sbtest.sbtest1"
       
      Command output:
       
      +----------+
| COUNT(*) |
+----------+
|      250 |
+----------+
+----------+
| COUNT(*) |
+----------+
|    25000 |
+----------+
      4.  
     
    2.  
  2. Run the mixed read/write performance test. The process takes about 10 minutes. 
     
    sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=25000 --tables=250 --time=600   --threads=thread_num --percentile=95 --report-interval=1 oltp_read_write run
     
    3.  
  3. Clean up data. 
     
    sysbench --db-driver=mysql --mysql-host=host --mysql-port=port --mysql-user=user --mysql-password=password --mysql-db=sbtest --table_size=25000 --tables=250 --threads=thread_num oltp_common cleanup
     
    4.  
 
 
 
 
Performance Data
 
A total of 250 tables were used, each containing 25,000 rows. Tests were conducted under different concurrency levels. When concurrency exceeds 128, the CPU reaches its bottleneck.
 
Due to normal system performance fluctuations, slight performance improvements observed after enabling SQL audit fall within expected variance. This does not mean that the audit feature boosts performance.
 
 
 
Mixed Read/Write Performance Test Data
 
 
  Table 2 Mixed read/write performance test data   
  
Mode
  
Table Count
  
Rows per Table
  
Concurrency
  
SQL Audit Enabled (All Options Enabled)
  
SQL Audit Disabled
  
TPS with All Options Enabled
 
vs
 
TPS with SQL Audit Disabled
 
 
  
TPS
  
QPS
  
TPS
  
QPS
 
   
  
Mixed read/write
  
250
  
25000
  
4
  
913.04
  
18260.73
  
910.64
  
18212.79
  
0.26%
 
 
  
8
  
1765.4
  
35307.94
  
1744
  
34879.95
  
1.23%
 
 
  
16
  
3305.48
  
66109.59
  
3356.91
  
67138.11
  
-1.53%
 
 
  
32
  
6000.85
  
120017.01
  
5995.85
  
119916.94
  
0.08%
 
 
  
64
  
10225.39
  
204507.89
  
10628.06
  
212561.11
  
-3.79%
 
 
  
128
  
11777.69
  
235553.71
  
12977.15
  
259542.96
  
-9.24%
 
  
 
 
 Figure 1 TPS in the mixed read/write performance test 
 
 
 Figure 2 QPS in the mixed read/write performance test 
 
 
 
 
Read-Only Performance Test Data
 
 
  Table 3 Read-only performance test data   
  
Mode
  
Table Count
  
Rows per Table
  
Concurrency
  
SQL Audit Enabled (All Options Enabled)
  
SQL Audit Disabled
  
TPS with All Options Enabled
 
vs
 
TPS with SQL Audit Disabled
 
 
  
TPS
  
QPS
  
TPS
  
QPS
 
   
  
Read-only
  
250
  
25000
  
4
  
1579.87
  
25277.86
  
1547.11
  
24753.78
  
2.12%
 
 
  
8
  
3105.53
  
49688.47
  
3050.89
  
48814.18
  
1.79%
 
 
  
16
  
5910.93
  
94574.84
  
5993.17
  
95890.71
  
-1.37%
 
 
  
32
  
10690.48
  
171047.72
  
10470.22
  
167523.58
  
2.10%
 
 
  
64
  
14473.59
  
231577.48
  
14761.66
  
236186.64
  
-1.95%
 
 
  
128
  
16463.74
  
263419.86
  
18041.48
  
288663.68
  
-8.75%
 
  
 
 
 Figure 3 TPS in the read-only performance test 
 
 
 Figure 4 QPS in the read-only performance test 
 
 
 
 
Write-Only Performance Test Data
 
 
  Table 4 Write-only performance test data   
  
Mode
  
Table Count
  
Rows per Table
  
Concurrency
  
SQL Audit Enabled (All Options Enabled)
  
SQL Audit Disabled
  
TPS with All Options Enabled
 
vs
 
TPS with SQL Audit Disabled
 
 
  
TPS
  
QPS
  
TPS
  
QPS
 
   
  
Write-only
  
250
  
25000
  
4
  
1976.92
  
11861.5
  
1974.09
  
11844.55
  
0.14%
 
 
  
8
  
3896.66
  
23379.98
  
3843.34
  
23060.07
  
1.39%
 
 
  
16
  
7347.01
  
44082.04
  
7135.42
  
42812.54
  
2.97%
 
 
  
32
  
12909.03
  
77454.18
  
12617.28
  
75703.7
  
2.31%
 
 
  
64
  
22512.82
  
135076.9
  
22915.74
  
137494.43
  
-1.76%
 
 
  
128
  
39818.79
  
238912.72
  
41480.33
  
248881.96
  
-4.01%
 
  
 
 
 Figure 5 TPS in the write-only performance test 
 
 
 Figure 6 QPS in the write-only performance test 
 
 
 
 
 
 
 Parent Topic: Managing SQL Audit Logs