Klustron-Storage vs. PostgreSQL OLTP Testing

1 Introduction to Klustron-Storage

Klustron-Storage is a database storage server optimized by Zetuo Technology based on Percona-mysql-8.0.26. It serves as the storage node for the Klustron distributed database. We have made significant performance enhancements to Percona-mysql, filled the gaps in its disaster recovery and error handling capabilities in XA transaction processing, and added some features required by the Klustron cluster as a whole, including fullsync replication and support for statements like update/delete...returning.

2 Test Environment

Test 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 Klustron-Storage are deployed on the same server: Amazon i3.4xlarge (CPU 8 cores 16 threads, memory: 122G, storage: 2x 1900 NVMe SSD) (AWS Cloud Environment)

• PostgreSQL and Klustron-Storage are both deployed on the same server (CPU 16 cores 32 threads, memory: 64G, storage: 1x NVMe SSD) (Local Deployment Environment)

Software Versions:

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

• Klustron-Storage:8.0.26-16-Klustron-Storage

Database Parameter Configuration:

PostgreSQL：

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

Klustron-Storage:

innodb_buffer_pool_size  32768MB
inndo_flush_at_trx_commit=1
sync_binlog=1
innodb_use_fdatasync = 1

Test Background:

PostgreSQL and Klustron-Storage are using default installation configurations with only adjustments made to memory parameters and the mentioned parameters. Throughout the entire testing process, there have been no optimization actions taken on PostgreSQL and Klustron-Storage.

3 Test Data

Test Software:

• ssysbench 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 Testing Scenarios:

Scenario One: oltp_write_only

Each transaction performs the following 4 types of operations: execute_index_updates(), execute_non_index_updates(), execute_delete_inserts()

Scenario Two: oltp_update_index

Each transaction performs the following 1 type of operation: execute_index_updates()

Scenario Three: oltp_update_non_index

Each transaction performs the following 1 type of operation: execute_non_index_updates()

Scenario Four: oltp_read_write.lua

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

SQL Statements Corresponding to Each Operation:

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

Space Occupied by Tables in the Operating System: 36 GB

Test Script:

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

Klustron-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 

Thread 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

Thread 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 the OLTP write-only, oltp-read-write, and OLTP update_index scenarios, Klustron-Storage performs significantly better than PostgreSQL.

It's important to emphasize that whenever PostgreSQL updates any indexed field, it needs to insert new index rows in all indexes pointing to the new version of the data row. In such cases, HOT updates cannot be utilized effectively.

Therefore, the performance of update_index operations lags significantly behind MySQL.

In real-world production systems, updating indexed columns is a common occurrence. Particularly due to the increased I/O consumption caused by Vacuum, PostgreSQL's general write performance is relatively poor.

2. In the OLTP update_non_index scenario, PostgreSQL's tps performance is higher than Klustron-Storage. However, the 95 percent delay is also higher than Klustron-storage. This indicates that in scenarios where fields are updated but not indexed, PostgreSQL can achieve better performance due to HOT updates. In HOT updates, most rows can be updated without inserting new index rows. Therefore, it achieves 5% to 30% higher QPS compared to Klustron-Storage on average.

However, the test results show that PostgreSQL's QPS and latency have significant fluctuations. This is because the delay for those update statements that cannot perform HOT updates will increase significantly. This, in turn, leads to PostgreSQL's 95% latency being higher than MySQL by around 10% to 40%.

Since most real-world use cases involve updating indexed fields, and even for statements that do not update indexed fields, HOT updates cannot be guaranteed to occur with high probability (HOT Updates can only happen when no indexed fields are updated, and the heap page has enough space to store the new version of the updated row). Therefore, PostgreSQL's performance advantage in such scenarios is limited.

3. PostgreSQL exhibits significant latency jitter during dynamic load changes, while Klustron-Storage's performance curve remains relatively stable.

END