Skip to main content

KlustronDB_storage vs PostgreSQL OLTP Test

KlustronDBAbout 3 min

KlustronDB_storage vs PostgreSQL OLTP Test

1 Introduction to KlustronDB_storage

KlustronDB_storage is a database storage server optimized by Klustron based on Percona MySQL 8.0.26. As a Klustore instance of the KlustronDB distributed database, we have made extensive performance enhancements to Percona MySQL, filling in gaps in its disaster recovery and error handling for XA transaction processing, and adding some functions required by the overall KlustronDB cluster, including fullsync replication and update/delete...returning statements.

2 Test Environment

Testing software:

  • sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3 (AWS cloud environment))

  • sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3) (local deployment environment)

Server Configuration:

  • PostgreSQL and KlustronDB_storage are each deployed on a separate machine: Amazon i3.4xlarge (CPU 8 cores 16 Threads, memory: 122G, storage: 2 x 1900 NVMe SSDs) (AWS cloud environment)

  • PostgreSQL and KlustronDB_storage are both deployed on the same server (CPU: 16 cores 32 threads, Memory: 64GB, Storage: 1 NVMe SSD) (local deployment environment)

Software Version:

  • Postgresql：PostgreSQL 14.2 onx86_64-pc-linux-gnu

  • KlustronDB_storage:8.0.26-16-KlustronDB_storage

Database parameter configuration:

PostgreSQL：

shared_buffers = 32768MB
wal_level = replica
fsync = on         
synchronous_commit = on       
wal_sync_method = fdatasync 
full_page_writes = on

KlustronDB_storage:

innodb_buffer_pool_size  32768MB
inndo_flush_at_trx_commit=1
sync_binlog=1
innodb_use_fdatasync = 1
Test background:

PostgreSQL and KlustronDB_storage use the default installation configuration, only adjusting the memory parameters and the several parameters mentioned above. Throughout the entire testing process, PostgreSQL and KlustronDB_storage did not perform any optimization.

3 Test Data

Testing software:

  • sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3 (AWS cloud environment))

  • sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3) (local deployment environment)

Sysbench test scenarios:
Scene 1: oltp_write_only

Each transaction performs the following four operations: execute_index_updates(), execute_non_index_updates(), execute_delete_inserts()

Scenario 2: oltp_update_index

Each transaction performs the following 1 operation: execute_index_updates()

Scenario 3: oltp_update_non_index

Each transaction performs the following one operation: execute_non_index_updates()

Scenario 4: oltp_read_write.lua

Each transaction performs the following 7 operations: execute_simple_ranges(), execute_sum_ranges(), execute_order_ranges(), execute_distinct_ranges(), execute_index_updates(), execute_non_index_updates(), execute_delete_inserts()

The SQL statements corresponding to each operation are as follows:
sum_ranges = {
      "SELECT SUM(k) FROMsbtest%u WHERE id BETWEEN ? AND ?",
       t.INT, t.INT},
order_ranges = {
      "SELECT c FROMsbtest%u WHERE id BETWEEN ? AND ? ORDER BY c",
       t.INT, t.INT},
distinct_ranges = {
      "SELECT DISTINCT cFROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c",
      t.INT, t.INT},
index_updates = {
      "UPDATE sbtest%uSET k=k+1 WHERE id=?",
      t.INT},
non_index_updates = {
      "UPDATE sbtest%uSET c=? WHERE id=?",
      {t.CHAR, 120}, t.INT},
deletes = {
      "DELETE FROMsbtest%u WHERE id=?",
      t.INT},
inserts = {
      "INSERT INTOsbtest%u (id, k, c, pad) VALUES (?, ?, ?, ?)",
      t.INT, t.INT, {t.CHAR,120}, {t.CHAR, 60}}
Test data volume:

--tables=18 --table-size=10000000

Table occupies operating system storage space: 36 G
Test script:

The threads of the test sysbench range from 64 to 900, with each thread scenario running for 10 minutes, and each scenario tested continuously for 140 minutes.

KlustronDB_storage:

sysbench /usr/local/share/sysbench/oltp_write_only.lua--db-driver=mysql --mysql-host=172.31.41.115 --mysql-port=6001 --mysql-user=pgx --mysql-password=pgx_pwd--mysql-db=vpgtest --tables=18 --table-size=10000000 --report-interval=10--threads=64 --time=600  run
 
sysbench/usr/local/share/sysbench/oltp_update_index.lua --db-driver=mysql --mysql-host=172.31.41.115  --mysql-port=6001 --mysql-user=pgx--mysql-password=pgx_pwd --mysql-db=vpgtest --tables=18 --table-size=10000000--report-interval=10 --threads=64 --time=600  run 

sysbench/usr/local/share/sysbench/oltp_update_non_index.lua  --db-driver=mysql--mysql-host=172.31.41.115 --mysql-port=6001 --mysql-user=pgx --mysql-password=pgx_pwd--mysql-db=vpgtest --tables=18 --table-size=10000000 --report-interval=10--threads=64 --time=600  run

Threads range: 64-128-192-......900

PostgreSQL:

sysbench/usr/local/share/sysbench/oltp_read_write.lua --db-driver=pgsql--pgsql-host=172.31.44.208 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres--pgsql-db=postgres --tables=18 --table-size=10000000 --report-interval=10--threads=64  --time=600 run
 
sysbench /usr/local/share/sysbench/oltp_update_index.lua--db-driver=pgsql --pgsql-host=172.31.44.208 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres--pgsql-db=postgres --tables=18 --table-size=10000000 --report-interval=10--threads=64  --time=600 run

sysbench/usr/local/share/sysbench/oltp_update_non_index.lua --db-driver=pgsql--pgsql-host=172.31.44.208 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=postgres--pgsql-db=postgres --tables=18 --table-size=10000000 --report-interval=10--threads=640  --time=600  run

Threads range: 64-128-192-......900

4 AWS Cloud Environment Test Results

oltp_write_only test

oltp_update_index test

oltp_update_non_index test

5 Local Deployment Test Results

oltp_write_only test

oltp_update_index test

oltp_update_non_index test

oltp_read_write test

6 Test Results and Summary

1. In OLTP write-only, OLTP read-write, and OLTP update_index scenarios, KlustronDB_storage significantly outperforms PostgreSQL in performance.

It should be emphasized that whenever PostgreSQL updates any indexed field, it needs to insert a new index entry in all indexes pointing to the new version of the data row, at which point HOT update cannot take effect.

Therefore, the performance of update_index will lag far behind MySQL.

In actual production systems, updates to indexed columns are very common, especially since the IO consumption caused by Vacuum increases significantly, so PostgreSQL's general write performance is relatively poor.

2. In the OLTP update_non_index scenario, PostgreSQL's TPS performance is higher than KlustronDB_storage, but the 95th percentile delay is also higher than KlustronDB_storage. This indicates that in scenarios where the updated field is not an indexed field, PostgreSQL can achieve HOT updates for most rows by keeping heap pages partially empty. This means there is no need to insert index rows, and the new version of the row can be written directly on the same heap page as the old row. As a result, the average QPS is 5%–30% higher than KlustronDB_storage.

However, from the test results, it can be seen that PostgreSQL's QPS and latency fluctuate quite a bit, because the latency of those update statements that cannot do HOT updates will also increase significantly, which in turn causes PostgreSQL's 95% latency to be about 10% to 40% higher than MySQL's.

Since in most practical usage scenarios it is unavoidable to update index fields, and even for statements that do not update index fields, HOT updates cannot be guaranteed to occur frequently (HOT updates can only happen when no index fields are updated and the heap page has enough space to store the new version of the updated row), this performance advantage of PostgreSQL has too narrow a scope.

3. PostgreSQL experiences noticeable latency jitter during dynamic load changes, while the performance curve of KlustronDB_storage is relatively stable.

END

Copyright © KlustronDB
Copyright © 2026 KlustronDB