Klustron vs. Greenplum TPCC Performance Comparison Results

Objective:

This article aims to perform a performance comparison between Klustron-1.2 and Greenplum-6.24.5 using the industry-standard TPCC performance testing tool (BenchmarkSQL) and draw performance comparison conclusions.

Test Environment 1: (Greenplum)

Greenplum Version: 6.24.5-rhel8-x86_64

Node Type	IP	Port
Master	192.168.0.20	5432
Seg1 Node	192.168.0.21	6000
Seg2 Node	192.168.0.22	6000

Operating Environment: CentOS 8.5 Linux 64-bit, CPU: AMD Ryzen 9 7950X 16-Core Processor, Memory: 128GB, Storage: M.2 NVME SSD PCIE 4 2TB Solid State Drive

Test Environment 2: (Klustron)

Node Type	IP	Port
Computing Node	192.168.0.20	47001
Shard1 Master Node	192.168.0.21	57003
Shard2 Master Node	192.168.0.22	57005
XPanel	192.168.0.20	40580

Operating Environment: CentOS 8.5 Linux 64-bit, CPU: AMD Ryzen 9 7950X 16-Core Processor, Memory: 128GB, Storage: M.2 NVME SSD PCIE 4 2TB Solid State Drive

​

Test Environment 3: (BenchmarkSQL)

BenchmarkSQL: v5.0

Deployment Mode: Single Node

Key Parameters:

warehouses=50
	terminals=100
	runMins=30
	newOrderWeight=45
	paymentWeight=43
	orderStatusWeight=4
	deliveryWeight=4
	stockLevelWeight=4

Operating Environment: CentOS 8.5 Linux 64-bit, CPU: AMD Ryzen 9 7950X 16-Core Processor, Memory: 128GB, Storage: M.2 NVME SSD PCIE 4 2TB Solid State Drive

IP: 192.168.0.19

Note: The Linux environment where BenchmarkSQL is located must have R installed; otherwise, the test results report will not display image analysis results.

01 Greenplum Performance Testing

1.1. Test Preparation

1.1.1. Greenplum Installation Process

Details omitted.

1.1.2. Parameter Adjustment (Performed on the Master Node)

gpconfig -c shared_buffers -v 32GB
gpconfig -c statement_timeout -v 6000000
gpconfig -c lock_timeout -v 1200000
gpconfig -c log_min_duration_statement -v 1200000
gpconfig -c effective_cache_size  -v  64GB
gpconfig -c work_mem   -v  64MB
gpconfig -c wal_buffers -v 64MB
gpconfig -c autovacuum -v false
gpconfig -c optimizer -v off
gpstop -M fast
gpstart

1.1.3. Adjusting BenchmarkSQL Configuration to Suit Greenplum Database Architecture

Modify the BenchmarkSQL table creation script (partitioning tables).

cd benchmarksql5.0/run/sql.common

cat << EOF > tableCreates.sql
create table bmsql_config (
  cfg_name    varchar(30) primary key,
  cfg_value   varchar(50)
);
create table bmsql_warehouse (
  w_id        integer   not null,
  w_ytd       decimal(12,2),
  w_tax       decimal(4,4),
  w_name      varchar(10),
  w_street_1  varchar(20),
  w_street_2  varchar(20),
  w_city      varchar(20),
  w_state     char(2),
  w_zip       char(9)
) DISTRIBUTED BY (w_id);

create table bmsql_district (
  d_w_id       integer       not null,
  d_id         integer       not null,
  d_ytd        decimal(12,2),
  d_tax        decimal(4,4),
  d_next_o_id  integer,
  d_name       varchar(10),
  d_street_1   varchar(20),
  d_street_2   varchar(20),
  d_city       varchar(20),
  d_state      char(2),
  d_zip        char(9)
) DISTRIBUTED BY (d_w_id);

create table bmsql_customer (
  c_w_id         integer        not null,
  c_d_id         integer        not null,
  c_id           integer        not null,
  c_discount     decimal(4,4),
  c_credit       char(2),
  c_last         varchar(16),
  c_first        varchar(16),
  c_credit_lim   decimal(12,2),
  c_balance      decimal(12,2),
  c_ytd_payment  decimal(12,2),
  c_payment_cnt  integer,
  c_delivery_cnt integer,
  c_street_1     varchar(20),
  c_street_2     varchar(20),
  c_city         varchar(20),
  c_state        char(2),
  c_zip          char(9),
  c_phone        char(16),
  c_since        timestamp,
  c_middle       char(2),
  c_data         varchar(500)
) DISTRIBUTED BY (c_w_id);

create sequence bmsql_hist_id_seq;

create table bmsql_history (
  hist_id  integer,
  h_c_id   integer,
  h_c_d_id integer,
  h_c_w_id integer,
  h_d_id   integer,
  h_w_id   integer,
  h_date   timestamp,
  h_amount decimal(6,2),
  h_data   varchar(24)
) DISTRIBUTED BY (h_c_w_id);

create table bmsql_new_order (
  no_w_id  integer   not null,
  no_d_id  integer   not null,
  no_o_id  integer   not null
) DISTRIBUTED BY (no_w_id);

create table bmsql_oorder (
  o_w_id       integer      not null,
  o_d_id       integer      not null,
  o_id         integer      not null,
  o_c_id       integer,
  o_carrier_id integer,
  o_ol_cnt     integer,
  o_all_local  integer,
  o_entry_d    timestamp
) DISTRIBUTED BY (o_w_id);

create table bmsql_order_line (
  ol_w_id         integer   not null,
  ol_d_id         integer   not null,
  ol_o_id         integer   not null,
  ol_number       integer   not null,
  ol_i_id         integer   not null,
  ol_delivery_d   timestamp,
  ol_amount       decimal(6,2),
  ol_supply_w_id  integer,
  ol_quantity     integer,
  ol_dist_info    char(24)
) DISTRIBUTED BY (ol_w_id);

create table bmsql_item (
  i_id     integer      not null,
  i_name   varchar(24),
  i_price  decimal(5,2),
  i_data   varchar(50),
  i_im_id  integer
);

create table bmsql_stock (
  s_w_id       integer       not null,
  s_i_id       integer       not null,
  s_quantity   integer,
  s_ytd        integer,
  s_order_cnt  integer,
  s_remote_cnt integer,
  s_data       varchar(50),
  s_dist_01    char(24),
  s_dist_02    char(24),
  s_dist_03    char(24),
  s_dist_04    char(24),
  s_dist_05    char(24),
  s_dist_06    char(24),
  s_dist_07    char(24),
  s_dist_08    char(24),
  s_dist_09    char(24),
  s_dist_10    char(24)
) DISTRIBUTED BY (s_w_id);

alter table bmsql_warehouse add constraint warehouse_pkey primary key (w_id);
alter table bmsql_district add constraint district_pkey   primary key (d_w_id, d_id);
alter table bmsql_customer add constraint customer_pkey  primary key (c_w_id, c_d_id, c_id);
create index bmsql_customer_idx1  on  bmsql_customer (c_w_id, c_d_id, c_last, c_first);
alter table bmsql_oorder add constraint oorder_pkey  primary key (o_w_id, o_d_id, o_id);
create unique index bmsql_oorder_idx1  on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
alter table bmsql_new_order add constraint new_order_pkey  primary key (no_w_id, no_d_id, no_o_id);
alter table bmsql_order_line add constraint order_line_pkey  primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
alter table bmsql_stock add constraint stock_pkey  primary key (s_w_id, s_i_id);
alter table bmsql_item add constraint item_pkey  primary key (i_id);
EOF

mv indexCreates.sql indexCreates.sql_old
mv foreignKeys.sql foreignKeys.sql.bak

Edit: /home/gpadmin/benchmarksql-5.0/run/sql.postgres/extraHistID.sql, change
alter table bmsql_history add primary key (hist_id);
to:
alter table bmsql_history add primary key (hist_id,h_c_w_id);

mv /home/kunlun/benchmarksql-5.0/run/sql.postgres/buildFinish.sql /home/kunlun/benchmarksql-5.0/run/sql.postgres/buildFinish.sql.bak 

1.1.4. Creating Test User and Database

psql -h 192.168.0.20 -p5432 -d gpdw   #Greenplum creates the gpdw database upon installation
create user bench with password ‘bench’;
create database bench with owner bench ;
alter user bench with superuser;

1.1.5. Modifying benchmarksql5.0/run/props.pg Parameters

db=bench
driver=org.postgresql.Driver
conn=jdbc:postgresql://192.168.0.20:5432/bench
user=bench
password=bench

warehouses=50
loadWorkers=50

terminals=100
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=30
//Number of total transactions per minute
limitTxnsPerMin=0

//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=false

//The following five values must add up to 100
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1
osCollectorSSHAddr=gpadmin@192.168.0.21
osCollectorDevices=net_enp4s0 blk_ nvme1n1

Note: Configure resource monitoring for one of the storage nodes running Greenplum, set up resource monitoring in the BenchmarkSQL configuration file (props.pg) to collect load parameters related to CPU, storage IO, and network IO during stress testing. Key parameter: osCollectorSSHAddr=gpadmin@192.168.0.21 #Requires configuring SSH keyless access.

osCollectorDevices=net_enp4s0 blk_ nvme1n1 #Adjust names according to the target machine's network interface and storage device.

1.1.6. Generating Test Data

cd benchmarksql5.0/run/
./runDatabaseBuild.sh props.pg

1.1.7. Performing Analysis on Test Data Tables and Columns, Collect Statistics

Copy tableCreates.sql to the Master node and create a script named ana.sh in the current directory as follows:

for i in `cat ./tableCreates.sql | grep 'create table' | grep -v partition | awk '{print $3}'`
do
        echo "select * from $i limit 1;" > a.sql
        clomun=`psql -h 192.168.0.20 -p 5432 -U bench bench  -f a.sql | sed -n 1p | sed 's/|/,/g'`
        analyze="analyze $i($clomun)"
        echo $analyze
        echo $analyze > tmp.sql
        psql -h 192.168.0.20 -p 5432 -U bench bench  -f tmp.sql
done

Run this script to perform statistical analysis on all test tables.

1.2. Running the Tests

Execute runBenchMark.sh props.pg to run the tests. After the stress test is completed, a directory similar to my_result_2023-07-25_112947 will be generated in the current directory. During the test run, Greenplum in this test scenario continuously outputs deadlock warning messages, as shown in the screenshot below:

1.3. Test Report Generation and Result Interpretation

Execute generateReport.sh <my_result_directory> to generate an HTML analysis report tailored to the current stress test configuration (warehouses=50, terminals=100, runMins=30). Key output information obtained in the report file is as follows:

Overall tpmC:	1555.73
Overall tpmTotal:	3468.83

The load monitoring status of the Greenplum cluster storage node 1's host machine is as follows:

02 Klustron Performance Testing

2.1. Test Preparation

2.1.1. Installing Klustron

Details omitted.

Cluster parameters set for testing are attached.

2.1.2. Adjusting Parameters for Computing and Storage Nodes

Key Parameters of the Computing Node (PG):

shared_buffers = 34359738368   (%25 RAM)
statement_timeout=6000000    
mysql_read_timeout=1200  
mysql_write_timeout=1200    
lock_timeout=1200000      
log_min_duration_statement=1200000
effective_cache_size = 68719476736   (%50 RAM)
work_mem  = 8MB   
wal_buffers = ‘64MB’
autovacuum=false
  
psql -h 192.168.0.20 -p 47001 -U abc postgres
alter system set shared_buffers='32GB';
alter system set statement_timeout=6000000;
alter system set mysql_read_timeout=1200;
alter system set mysql_write_timeout=1200;
alter system set lock_timeout=1200000;
alter system set log_min_duration_statement=1200000;
alter system set effective_cache_size = '64GB';
alter system set work_mem  = '8MB';
alter system set wal_buffers='64MB';
alter system set autovacuum=false;

alter system set log_min_messages='DEBUG1' ;  #Activate when SQL debugging is needed
alter system set enable_sql_log=on;  #Activate when SQL debugging is needed


pg_ctl restart -D /nvme2/kunlun/server_datadir/47001

Key Parameters of the Storage Node (MYSQL):

innodb_buffer_pool_size=32*1024*1024*1024
lock_wait_timeout=1200    
innodb_lock_wait_timeout=1200    
fullsync_timeout=1200000 
enable_fullsync=false
innodb_flush_log_at_trx_commit=2
sync_binlog=0
max_binlog_size=1*1024*1024*1024

mysql -h 192.168.0.21 -P 57003 -upgx -ppgx_pwd
mysql -h 192.168.0.22 -P 57005 -upgx -ppgx_pwd
set global innodb_buffer_pool_size=32*1024*1024*1024;
set global lock_wait_timeout=1200;
set global innodb_lock_wait_timeout=1200;    
set global fullsync_timeout=1200000; 
set global enable_fullsync=false;
set global innodb_flush_log_at_trx_commit=2;
set global sync_binlog=0;
set global max_binlog_size=1*1024*1024*1024;

set global enable_fullsync=off;  #All shard master nodes turn off full synchronization
set enable_fullsync=off;    #All shard master nodes turn off full synchronization

set global general_log=on ;   #Activate when SQL debugging is needed

Set Failover to 10800 seconds (Xpanel)

Metadata cluster MySQL MGR 3-node parameter adjustments:

export LD_LIBRARY_PATH=/nvme2/kunlun/instance_binaries/storage/57001/Klustron-storage-1.2.1/lib:$LD_LIBRARY_PATH ; 
/nvme2/kunlun/instance_binaries/storage/57001/Klustron-storage-1.2.1/bin/mysql --connect-expired-password -S/nvme2/kunlun/storage_logdir/57001/mysql.sock -uroot -proot 
set global innodb_buffer_pool_size=1*1024*1024*1024;     (Default 128M)

2.1.3. Modifying BenchmarkSQL Configuration to Adapt to Klustron Data Architecture

Modify the BenchmarkSQL table creation script (partitioning and specifying shards).

cd benchmarksql5.0/run/sql.common

cat << EOF > tableCreates.sql
create table bmsql_config (
  cfg_name    varchar(30) primary key,
  cfg_value   varchar(50)
);
create table bmsql_warehouse (
  w_id        integer   not null,
  w_ytd       decimal(12,2),
  w_tax       decimal(4,4),
  w_name      varchar(10),
  w_street_1  varchar(20),
  w_street_2  varchar(20),
  w_city      varchar(20),
  w_state     char(2),
  w_zip       char(9)
) partition by hash(w_id);
create table bmsql_warehouse_0 partition of bmsql_warehouse for values with (MODULUS 6, REMAINDER 0) with (shard = 1);
create table bmsql_warehouse_1 partition of bmsql_warehouse for values with (MODULUS 6, REMAINDER 1) with (shard = 1);
create table bmsql_warehouse_2 partition of bmsql_warehouse for values with (MODULUS 6, REMAINDER 2) with (shard = 1);
create table bmsql_warehouse_3 partition of bmsql_warehouse for values with (MODULUS 6, REMAINDER 3) with (shard = 2);
create table bmsql_warehouse_4 partition of bmsql_warehouse for values with (MODULUS 6, REMAINDER 4) with (shard = 2);
create table bmsql_warehouse_5 partition of bmsql_warehouse for values with (MODULUS 6, REMAINDER 5) with (shard = 2);
create table bmsql_district (
  d_w_id       integer       not null,
  d_id         integer       not null,
  d_ytd        decimal(12,2),
  d_tax        decimal(4,4),
  d_next_o_id  integer,
  d_name       varchar(10),
  d_street_1   varchar(20),
  d_street_2   varchar(20),
  d_city       varchar(20),
  d_state      char(2),
  d_zip        char(9)
) partition by hash(d_w_id);
create table bmsql_district_0 partition of bmsql_district for values with (MODULUS 6, REMAINDER 0) with (shard = 1);
create table bmsql_district_1 partition of bmsql_district for values with (MODULUS 6, REMAINDER 1) with (shard = 1);
create table bmsql_district_2 partition of bmsql_district for values with (MODULUS 6, REMAINDER 2) with (shard = 1);
create table bmsql_district_3 partition of bmsql_district for values with (MODULUS 6, REMAINDER 3) with (shard = 2);
create table bmsql_district_4 partition of bmsql_district for values with (MODULUS 6, REMAINDER 4) with (shard = 2);
create table bmsql_district_5 partition of bmsql_district for values with (MODULUS 6, REMAINDER 5) with (shard = 2);
create table bmsql_customer (
  c_w_id         integer        not null,
  c_d_id         integer        not null,
  c_id           integer        not null,
  c_discount     decimal(4,4),
  c_credit       char(2),
  c_last         varchar(16),
  c_first        varchar(16),
  c_credit_lim   decimal(12,2),
  c_balance      decimal(12,2),
  c_ytd_payment  decimal(12,2),
  c_payment_cnt  integer,
  c_delivery_cnt integer,
  c_street_1     varchar(20),
  c_street_2     varchar(20),
  c_city         varchar(20),
  c_state        char(2),
  c_zip          char(9),
  c_phone        char(16),
  c_since        timestamp,
  c_middle       char(2),
  c_data         varchar(500)
) partition by hash(c_w_id);
create table bmsql_customer_0 partition of bmsql_customer for values with (MODULUS 6, REMAINDER 0) with (shard = 1);
create table bmsql_customer_1 partition of bmsql_customer for values with (MODULUS 6, REMAINDER 1) with (shard = 1);
create table bmsql_customer_2 partition of bmsql_customer for values with (MODULUS 6, REMAINDER 2) with (shard = 1);
create table bmsql_customer_3 partition of bmsql_customer for values with (MODULUS 6, REMAINDER 3) with (shard = 2);
create table bmsql_customer_4 partition of bmsql_customer for values with (MODULUS 6, REMAINDER 4) with (shard = 2);
create table bmsql_customer_5 partition of bmsql_customer for values with (MODULUS 6, REMAINDER 5) with (shard = 2);
create sequence bmsql_hist_id_seq;
create table bmsql_history (
  hist_id  integer,
  h_c_id   integer,
  h_c_d_id integer,
  h_c_w_id integer,
  h_d_id   integer,
  h_w_id   integer,
  h_date   timestamp,
  h_amount decimal(6,2),
  h_data   varchar(24)
) partition by hash(h_c_w_id);
create table bmsql_history_0 partition of bmsql_history for values with (MODULUS 6, REMAINDER 0) with (shard = 1);
create table bmsql_history_1 partition of bmsql_history for values with (MODULUS 6, REMAINDER 1) with (shard = 1);
create table bmsql_history_2 partition of bmsql_history for values with (MODULUS 6, REMAINDER 2) with (shard = 1);
create table bmsql_history_3 partition of bmsql_history for values with (MODULUS 6, REMAINDER 3) with (shard = 2);
create table bmsql_history_4 partition of bmsql_history for values with (MODULUS 6, REMAINDER 4) with (shard = 2);
create table bmsql_history_5 partition of bmsql_history for values with (MODULUS 6, REMAINDER 5) with (shard = 2);
create table bmsql_new_order (
  no_w_id  integer   not null,
  no_d_id  integer   not null,
  no_o_id  integer   not null
) partition by hash(no_w_id);
create table bmsql_new_order_0 partition of bmsql_new_order for values with (MODULUS 6, REMAINDER 0) with (shard = 1);
create table bmsql_new_order_1 partition of bmsql_new_order for values with (MODULUS 6, REMAINDER 1) with (shard = 1);
create table bmsql_new_order_2 partition of bmsql_new_order for values with (MODULUS 6, REMAINDER 2) with (shard = 1);
create table bmsql_new_order_3 partition of bmsql_new_order for values with (MODULUS 6, REMAINDER 3) with (shard = 2);
create table bmsql_new_order_4 partition of bmsql_new_order for values with (MODULUS 6, REMAINDER 4) with (shard = 2);
create table bmsql_new_order_5 partition of bmsql_new_order for values with (MODULUS 6, REMAINDER 5) with (shard = 2);
create table bmsql_oorder (
  o_w_id       integer      not null,
  o_d_id       integer      not null,
  o_id         integer      not null,
  o_c_id       integer,
  o_carrier_id integer,
  o_ol_cnt     integer,
  o_all_local  integer,
  o_entry_d    timestamp
) partition by hash(o_w_id);
create table bmsql_oorder_0 partition of bmsql_oorder for values with (MODULUS 6, REMAINDER 0) with (shard = 1);
create table bmsql_oorder_1 partition of bmsql_oorder for values with (MODULUS 6, REMAINDER 1) with (shard = 1);
create table bmsql_oorder_2 partition of bmsql_oorder for values with (MODULUS 6, REMAINDER 2) with (shard = 1);
create table bmsql_oorder_3 partition of bmsql_oorder for values with (MODULUS 6, REMAINDER 3) with (shard = 2);
create table bmsql_oorder_4 partition of bmsql_oorder for values with (MODULUS 6, REMAINDER 4) with (shard = 2);
create table bmsql_oorder_5 partition of bmsql_oorder for values with (MODULUS 6, REMAINDER 5) with (shard = 2);
create table bmsql_order_line (
  ol_w_id         integer   not null,
  ol_d_id         integer   not null,
  ol_o_id         integer   not null,
  ol_number       integer   not null,
  ol_i_id         integer   not null,
  ol_delivery_d   timestamp,
  ol_amount       decimal(6,2),
  ol_supply_w_id  integer,
  ol_quantity     integer,
  ol_dist_info    char(24)
) partition by hash(ol_w_id);
create table bmsql_order_line_0 partition of bmsql_order_line for values with (MODULUS 6, REMAINDER 0) with (shard = 1);
create table bmsql_order_line_1 partition of bmsql_order_line for values with (MODULUS 6, REMAINDER 1) with (shard = 1);
create table bmsql_order_line_2 partition of bmsql_order_line for values with (MODULUS 6, REMAINDER 2) with (shard = 1);
create table bmsql_order_line_3 partition of bmsql_order_line for values with (MODULUS 6, REMAINDER 3) with (shard = 2);
create table bmsql_order_line_4 partition of bmsql_order_line for values with (MODULUS 6, REMAINDER 4) with (shard = 2);
create table bmsql_order_line_5 partition of bmsql_order_line for values with (MODULUS 6, REMAINDER 5) with (shard = 2);
create table bmsql_item (
  i_id     integer      not null,
  i_name   varchar(24),
  i_price  decimal(5,2),
  i_data   varchar(50),
  i_im_id  integer
) with (shard = all);
create table bmsql_stock (
  s_w_id       integer       not null,
  s_i_id       integer       not null,
  s_quantity   integer,
  s_ytd        integer,
  s_order_cnt  integer,
  s_remote_cnt integer,
  s_data       varchar(50),
  s_dist_01    char(24),
  s_dist_02    char(24),
  s_dist_03    char(24),
  s_dist_04    char(24),
  s_dist_05    char(24),
  s_dist_06    char(24),
  s_dist_07    char(24),
  s_dist_08    char(24),
  s_dist_09    char(24),
  s_dist_10    char(24)
) partition by hash(s_w_id);
create table bmsql_stock_0 partition of bmsql_stock for values with (MODULUS 6, REMAINDER 0) with (shard = 1);
create table bmsql_stock_1 partition of bmsql_stock for values with (MODULUS 6, REMAINDER 1) with (shard = 1);
create table bmsql_stock_2 partition of bmsql_stock for values with (MODULUS 6, REMAINDER 2) with (shard = 1);
create table bmsql_stock_3 partition of bmsql_stock for values with (MODULUS 6, REMAINDER 3) with (shard = 2);
create table bmsql_stock_4 partition of bmsql_stock for values with (MODULUS 6, REMAINDER 4) with (shard = 2);
create table bmsql_stock_5 partition of bmsql_stock for values with (MODULUS 6, REMAINDER 5) with (shard = 2);
alter table bmsql_warehouse add constraint warehouse_pkey primary key (w_id);
alter table bmsql_district add constraint district_pkey   primary key (d_w_id, d_id);
alter table bmsql_customer add constraint customer_pkey  primary key (c_w_id, c_d_id, c_id);
create index bmsql_customer_idx1  on  bmsql_customer (c_w_id, c_d_id, c_last, c_first);
alter table bmsql_oorder add constraint oorder_pkey  primary key (o_w_id, o_d_id, o_id);
create unique index bmsql_oorder_idx1  on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
alter table bmsql_new_order add constraint new_order_pkey  primary key (no_w_id, no_d_id, no_o_id);
alter table bmsql_order_line add constraint order_line_pkey  primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
alter table bmsql_stock add constraint stock_pkey  primary key (s_w_id, s_i_id);
alter table bmsql_item add constraint item_pkey  primary key (i_id);
EOF
mv indexCreates.sql indexCreates.sql_old
mv foreignKeys.sql foreignKeys.sql.bak

修改：/home/kunlun/benchmarksql-5.0/run/sql.postgres/extraHistID.sql ，将
alter table bmsql_history add primary key (hist_id)；
修改为：
alter table bmsql_history add primary key (hist_id,h_c_w_id);

mv /home/kunlun/benchmarksql-5.0/run/sql.postgres/buildFinish.sql /home/kunlun/benchmarksql-5.0/run/sql.postgres/buildFinish.sql.bak 

2.1.4. Creating the Test Database 'bench' and Test User 'bench' in Klustron

psql -h 192.168.0.20 -p 47001 -U abc postgres
postgres=# create user bench with password 'bench';
postgres=# create database bench with owner bench ;

2.1.5. Adjusting Parameters in benchmarksql5.0/run/props.pg

db=postgres
driver=org.postgresql.Driver
conn=jdbc:postgresql://192.168.0.20:47001/bench
user=bench
password=bench

warehouses=50
loadWorkers=50

terminals=100
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=30
//Number of total transactions per minute
limitTxnsPerMin=0

//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=false

//The following five values must add up to 100
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1
osCollectorSSHAddr=kunlun@192.168.0.21
osCollectorDevices=net_enp4s0 blk_ nvme1n1

2.1.6. Generating Test Data

cd benchmarksql5.0/run/

./runDatabaseBuild.sh props.pg

2.1.7. Analyzing Test Data Tables and Columns, Collecting Statistics

Copy tableCreates.sql to the computing node and edit a script named ana.sh in the current directory as follows:

for i in `cat ./tableCreates.sql | grep 'create table' | grep -v partition | awk '{print $3}'`
do
        echo "select * from $i limit 1;" > a.sql
        clomun=`psql -h 192.168.0.20 -p 47001 -U bench bench  -f a.sql | sed -n 1p | sed 's/|/,/g'`
        analyze="analyze $i($clomun)"
        echo $analyze
        echo $analyze > tmp.sql
        psql -h 192.168.0.20 -p 47001 -U bench bench  -f tmp.sql
done

Run this script to complete statistical analysis of all test tables.

2.1.8. Transaction Cleanup on All Storage Nodes

Edit a script named xa.sh for transaction cleanup and include the following content:

echo 'xa recover'> tmp.sql
echo $1
for i in `mysql -h 192.168.0.$1 -ppgx_pwd -upgx -P$2 < tmp.sql | awk '{print $4}' | sed '1d'`
do
        printf "xa rollback \'$i\'" > tmp.sql
        echo "========$i========"
        mysql -h 192.168.0.$1 -ppgx_pwd -upgx -P$2 < tmp.sql
done

Copy it to the storage nodes, shard1(192.168.0.21) and shard2(192.168.0.22), and provide the corresponding parameters for script execution.

Example: sh xa.sh 21 57003

2.2. Running the Test

Execute the runBenchMark.sh props.pg command, and you will see output similar to the following:

After completing the stress test, a directory like "my_result_2023-04-16_074338" will be generated in the current directory.

2.3. Generating the Test Report and Reading the Results

Execute generateReport.sh <my_result_directory> to generate an HTML analysis report for the current test case configuration (warehouses=50, terminals=100, runMins=30). Key output information obtained in the report is as follows:

Overall tpmC:	9059.77
Overall tpmTotal:	20142.97

Monitoring of Klustron storage node (192.168.0.21) is shown below:

03 Summary of TPCC Performance Test Comparison Results

Comparison with 50 warehouses:

Results Analysis

From the performance data mentioned above, it can be observed that KunlunBase outperforms Greenplum significantly in terms of TPCC performance. This advantage is primarily reflected in the speed of continuous streaming data updates, where KunlunBase is faster than Greenplum. This has significant practical implications and value.

One typical scenario is when data updates continuously flow into databases from multiple business systems and are supplemented by data from sources such as app and web tracking. In such cases, if the inflow speed cannot keep up with the data generation speed, issues like data versions getting outdated will arise.

Another scenario involves periodically aggregating data from other sources. In this case, the data update speed directly determines the ingestion speed. If the data update ingestion speed is too slow and user business growth leads to a larger volume of data to ingest, the time it takes for data ingestion will increase.

Since Greenplum's data is stored in PostgreSQL, and PostgreSQL's storage engine has inherent weaknesses, it frequently needs to perform VACUUM operations to reclaim storage space. This VACUUM operation locks the table when processing a data table, which means the table cannot be updated for an extended period. For users, this is usually an intolerable flaw.

END