percona-lab / sysbench-tpcc

Sysbench scripts to generate a tpcc-like workload for MySQL and PostgreSQL

sysbench-tpcc

TPCC-like workload for sysbench 1.0.x. Make sure you are using sysbench 1.0.14 or better!

prepare data and tables

./tpcc.lua --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=sbt --time=300 --threads=64 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql prepare

prepare for RocksDB

./tpcc.lua --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=sbr --time=3000 --threads=64 --report-interval=1 --tables=10 --scale=100 --use_fk=0 --mysql_storage_engine=rocksdb --mysql_table_options='COLLATE latin1_bin' --trx_level=RC --db-driver=mysql prepare

Run benchmark

./tpcc.lua --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=sbt --time=300 --threads=64 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql run

Cleanup

./tpcc.lua --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=sbt --time=300 --threads=64 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql cleanup

sysbench-tpcc's Issues

Execution crashes for PostgreSQL with SQL syntax error

Syntactically wrong SQL is fired against PostgreSQL after few hours of execution.
here is the extract from the log

...
[ 20060s ] thds: 4 tps: 11.00 qps: 360.60 (r/w/o: 165.80/163.90/30.90) lat (ms,99%): 90.78 err/s 0.00 reconn/s: 0.00
[ 20070s ] thds: 4 tps: 8.50 qps: 282.90 (r/w/o: 128.80/128.10/26.00) lat (ms,99%): 48.34 err/s 0.00 reconn/s: 0.00
[ 20080s ] thds: 4 tps: 10.80 qps: 316.10 (r/w/o: 146.20/135.70/34.20) lat (ms,99%): 42.61 err/s 0.00 reconn/s: 0.00
[ 20090s ] thds: 4 tps: 11.60 qps: 342.50 (r/w/o: 157.20/149.50/35.80) lat (ms,99%): 87.56 err/s 0.00 reconn/s: 0.00
[ 20100s ] thds: 4 tps: 9.10 qps: 220.40 (r/w/o: 100.80/96.00/23.60) lat (ms,99%): 34.95 err/s 0.00 reconn/s: 0.00
[ 20110s ] thds: 4 tps: 9.30 qps: 260.40 (r/w/o: 118.90/117.50/24.00) lat (ms,99%): 39.65 err/s 0.10 reconn/s: 0.00
FATAL: PQexec() failed: 7 syntax error at or near "LIMIT"
FATAL: failed query was: DELETE FROM history1 where h_w_id=2 AND h_d_id=2 LIMIT 10
FATAL: `thread_run' function failed: /usr/share/sysbench/tpcc_run.lua:785: SQL error, errno = 0, state = '42601': syntax error at or near "LIMIT"
...

A DELETE statement should no have LIMIT clause.

Maximum Cpu Usage...

Hi again,
I have some questions about sysbench-tpcc:

1. When I load 50 warehouses with 10 tables it takes about 4 hours! Is this reasonable or I've done some mistakes?
2. I have a machine with 8 cores and 8GB of memory. What's the best configuration to put this system under stress? I mean the CPU works with maximum power. I tried this:
   "
   ./tpcc.lua --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=sys --time=1200 --threads=100 --report-interval=1 --tables=10 --scale=50 --db-driver=mysql run
   "
   But the CPU usage is about 20 percent! I also tried more threads like 256, and I got this:
   "
   Error in my_thread_global_end(): 256 threads didn't exit
   "
   Could you please help me with this?

segmentation fault (core dumped) ubuntu

Hi,
I am running sysbench tpc-c 1.0.20 on PostgreSQL 14.

During the "prepare" I get the following error:
segmentation fault (core dumped) ubuntu

On "Ubuntu 20.04.3 LTS" and "Ubuntu 18.04.6 LTS" I have the same issue.

In the syslog have:
labxxxx kernel: [12728.541831] sysbench[32695]: segfault at 0 ip 00007f762213f461 sp 00007f761c0eb918 error 4 in libc-2.27.so[7f7621fb1000+1e7000]

The workaround was to limit PostgreSQL buffer pool memory from 300GB to 20GB, but this is not a realistic setting. Since I have 512GB of RAM.

tpcc.lua script is not terminating when it hit "Unknown storage engine" error

tpcc.lua script is not terminating when it hit Unknown storage engine error. It also open blank sessions in mysql server

[vagrant@pgsql ~]$ /usr/share/sysbench/tpcc.lua --mysql-user=root --mysql-db=test --time=30 --threads=64 --report-interval=1 --tables=2 --scale=10 --use_fk=0 --mysql_storage_engine=rocksdb --db-driver=mysql prepare
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating tables: 2

Creating tables: 1

FATAL: mysql_drv_query() returned error 1286 (Unknown storage engine 'rocksdb') for query '     CREATE TABLE IF NOT EXISTS warehouse1 (
        w_id smallint not null,
        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),
        w_tax decimal(4,2),
        w_ytd decimal(12,2),
        primary key (w_id)
        ) /*! ENGINE = rocksdb */ '
FATAL: mysql_drv_query() returned error 1286 (Unknown storage engine 'rocksdb') for query '     CREATE TABLE IF NOT EXISTS warehouse2 (
        w_id smallint not null,
        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),
        w_tax decimal(4,2),
        w_ytd decimal(12,2),
        primary key (w_id)
        ) /*! ENGINE = rocksdb */ '
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/tpcc_common.lua:140: SQL error, errno = 1286, state = '42000': Unknown storage engine 'rocksdb'



^C^C^C^C

^C

MySQL processlist.

mysql> show full processlist;
+-----+------+-----------+------+---------+------+----------+-----------------------+-----------+---------------+
| Id  | User | Host      | db   | Command | Time | State    | Info                  | Rows_sent | Rows_examined |
+-----+------+-----------+------+---------+------+----------+-----------------------+-----------+---------------+
| 204 | root | localhost | test | Sleep   |    1 |          | NULL                  |         0 |             0 |
| 206 | root | localhost | test | Sleep   |    1 |          | NULL                  |         0 |             0 |
[..]
| 267 | root | localhost | test | Sleep   |    1 |          | NULL                  |         0 |             0 |
| 268 | root | localhost | NULL | Query   |    0 | starting | show full processlist |         0 |             0 |
+-----+------+-----------+------+---------+------+----------+-----------------------+-----------+---------------+
63 rows in set (0.00 sec)

mysql>

See 'sysbench --test=<name> help' for a list of options for each test.

Hi to all,
I'm trying to run the benchmark as you said but after I run the preparation command the result is this:

Unknown command: ./tpcc.lua.
Usage:
sysbench [general-options]... --test= [test-options]... command

General options:
--num-threads=N number of threads to use [1]
--max-requests=N limit for total number of requests [10000]
--max-time=N limit for total execution time in seconds [0]
--forced-shutdown=STRING amount of time to wait after --max-time before forcing shutdown [off]
--thread-stack-size=SIZE size of stack per thread [32K]
--init-rng=[on|off] initialize random number generator [off]
--seed-rng=N seed for random number generator, ignored when 0 [0]
--tx-rate=N target transaction rate (tps) [0]
--tx-jitter=N target transaction variation, in microseconds [0]
--report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
--report-checkpoints=[LIST,...]dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
--test=STRING test to run
--debug=[on|off] print more debugging info [off]
--validate=[on|off] perform validation checks where possible [off]
--help=[on|off] print help and exit
--version=[on|off] print version and exit

Log options:
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [4]

--percentile=N percentile rank of query response times to count [95]

Compiled-in tests:
fileio - File I/O test
cpu - CPU performance test
memory - Memory functions speed test
threads - Threads subsystem performance test
mutex - Mutex performance test
oltp - OLTP test

Commands: prepare run cleanup help version

See 'sysbench --test= help' for a list of options for each test.

It's completely confusing since my command is this:

./tpcc.lua --mysql-socket=/tmp/mysql-node1.sock --mysql-user=root --mysql-db=sbt --time=300 --threads=64 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql prepare

Please help me and let me know what is wrong!

Best,
Vahid

binary log disabled during prepare

Commit d3ebc95 somehow added SET SESSION sql_log_bin = 0, which means prepare would only insert data into the target node, but not the other galera cluster nodes or async replication nodes. This was really confusing until I noticed the commit. Looks like a mistake?

How to set the percent number of read and write when testing tpcc

In the scenario of mixed read and write , how can I control the percents of read or write (For example 30% write and 70% read) in tpcc testing ?

function failed: ./tpcc_common.lua:526: db_bulk_insert_next() failed

ubuntu bionic percona server 5.6 latest

16:06:38 [root@db-143[DP]:~/sysbench-tpcc-master] $ ./tpcc.lua --mysql-socket=/var/run/mysqld/mysqld.sock --mysql-user=root --mysql-password=xxxxx --mysql-port=3306 --mysql-db=sbt --time=300 -
-threads=64 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql prepare
sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)

results after same time with:
FATAL: `sysbench.cmdline.call_command' function failed: ./tpcc_common.lua:526: db_bulk_insert_next() failed

However running it with the following doesnt end with an error:
16:12:43 [root@db-143[DP]:~/sysbench-tpcc-master] $ ./tpcc.lua --mysql-socket=/var/run/mysqld/mysqld.sock --mysql-user=root --mysql-password=xxxxxx --mysql-port=3306 --mysql-db=sbt --time=300 -
-threads=64 --report-interval=1 --tables=1 --scale=1 --db-driver=mysql prepare

... but the run cmd crashes instantly:
16:13:41 [root@db-143[DP]:~/sysbench-tpcc-master] $ ./tpcc.lua --mysql-socket=/var/run/mysqld/mysqld.sock --mysql-user=root --mysql-password=xxxxx --mysql-port=3306 --mysql-db=sbt --time=300 -
-threads=64 --report-interval=1 --tables=1 --scale=1 --db-driver=mysql run
sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 64
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

FATAL: thread_run' function failed: ./tpcc_run.lua:115: attempt to perform arithmetic on local 'd_next_o_id' (a nil value) ALERT: attempt to fetch row from an empty result set FATAL: thread_run' function failed: ./tpcc_run.lua:466: attempt to index global 'row' (a nil value)
FATAL: thread_run' function failed: ./tpcc_run.lua:115: attempt to perform arithmetic on local 'd_next_o_id' (a nil value) (last message repeated 2 times) FATAL: thread_run' function failed: ./tpcc_run.lua:353: attempt to perform arithmetic on a nil value
FATAL: `thread_run' function failed: ./tpcc_run.lua:115: attempt to perform arithmetic on local 'd_next_o_id' (a nil value)

FATAL: `thread_run' function failed: ./tpcc_common.lua:649: bad argument #3 to 'bor' (number expected, got function)

./tpcc.lua --pgsql-host=10.10.10.10 --pgsql-port=5432 --pgsql-user=sysbench --pgsql-password=sysbench --pgsql-db=sysbench --threads=64 --tables=1 --scale=100 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql prepare

./tpcc.lua --pgsql-host=10.10.10.10 --pgsql-port=5432 --pgsql-user=sysbench --pgsql-db=sysbench --pgsql-password=sysbench --threads=64 --tables=1 --scale=100 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --time=360 --report-interval=5 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 64
Report intermediate results every 5 second(s)
Initializing random number generator from current time

Initializing worker threads...

DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
DB SCHEMA public
Threads started!

FATAL: `thread_run' function failed: ./tpcc_common.lua:649: bad argument #3 to 'bor' (number expected, got function)

Asking for Clarification About the Delivery Transaction Implementation

In the delivery transaction, percona sysbench uses "SELECT FOR UPDATE" in

        rs = con:query(([[SELECT no_o_id
                                     FROM new_orders%d 
                                    WHERE no_d_id = %d 
                                      AND no_w_id = %d 
                                      ORDER BY no_o_id ASC LIMIT 1 FOR UPDATE]])
                                   :format(table_num, d_id, w_id))

But from the TPC-C spec I don't find out any place that requires this statement to perform locking read on the record. Could you please give an explanation about it ? Since the performance will be better if we use non locking read

Data truncated for column 'h_data' at row

I find that the length of h_data field in the history table is 24.

mysql> desc history1;

+----------+--------------+------+------+---------+-------+
| Field    | Type         | Null | Key  | Default | Extra |
+----------+--------------+------+------+---------+-------+
| h_c_id   | int(11)      | YES  |      | NULL    |       |
| h_c_d_id | tinyint(4)   | YES  |      | NULL    |       |
| h_c_w_id | smallint(6)  | YES  | MUL  | NULL    |       |
| h_d_id   | tinyint(4)   | YES  |      | NULL    |       |
| h_w_id   | smallint(6)  | YES  | MUL  | NULL    |       |
| h_date   | datetime     | YES  |      | NULL    |       |
| h_amount | decimal(6,2) | YES  |      | NULL    |       |
| h_data   | varchar(24)  | YES  |      | NULL    |       |
+----------+--------------+------+------+---------+-------+
8 rows in set (0.00 sec)

but https://github.com/Percona-Lab/sysbench-tpcc/blob/master/tpcc_run.lua#L406 inserts a data which length is 25, so here we will meet a warning, I reproduce it in MySQL 5.7.

mysql> INSERT INTO history1 (h_c_d_id, h_c_w_id, h_c_id, h_d_id,  h_w_id, h_date, h_amount, h_data) VALUES (6,1,642,6,1,NOW(),5,'name-ussgn name-zamed    ');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1265 | Data truncated for column 'h_data' at row 1 |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)

Do we need to adjust to insert 24 chars or alert the history field length?

Custom lua script for postgres DML statements

Hi Team,

Please can you provide sample LUA script for DML operation on postgres like simple queries as below :-

select count(*) from table;
insert into table_name values(100,200,..);
update table_name set col1=something where coln=something_else;

I checked this link https://www.percona.com/blog/2019/04/25/creating-custom-sysbench-scripts/ but it gives details only about MySql like driver initialization as drv = sysbench.sql.driver() and opening connection as con = drv:connect(), etc.

I dont know what will be the equivalent functions/keywords/syntax for Postgres.

Thanks

postgresql feature appears to be broken

Not sure why an Invalid statement like "SET autocommit=1" issued against PG.
Details below

$ /home/postgres/sysbench-tpcc/tpcc.lua --db-driver=pgsql --pgsql-host=localhost --pgsql-db=postgres --pgsql-schema=sb --pgsql-user=postgres --pgsql-password=vagrant --pgsql-port=5432 prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

DB SCHEMA sb
Creating tables: 1

Adding indexes 1 ...

Adding FK 1 ...

Waiting on tables 30 sec

FATAL: PQexec() failed: 7 unrecognized configuration parameter "autocommit"
FATAL: failed query was: SET autocommit=1
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/tpcc_common.lua:410: SQL error, errno = 0, state = '42704': unrecognized configuration parameter "autocommit"

FATAL: `thread_run' function failed: ./tpcc_run.lua:721: bad argument #1 to 'unpack' (table expected, got nil)

./tpcc.lua --mysql-host=192.168.198.239 --mysql-port=3346 --mysql-user=

admin --mysql-password=123456 --mysql-db=sysbench_tpcc --time=300 --threads=64 --report-interval=1 --tables=10 --scale=100 --db-driver=mysql runsysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 64
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

FATAL: `thread_run' function failed: ./tpcc_run.lua:721: bad argument #1 to 'unpack' (table expected, got
nil)[ 1s ] thds: 17 tps: 65.80 qps: 1535.32 (r/w/o: 670.95/602.16/262.20) lat (ms,95%): 943.16 err/s 0.00 reco
nn/s: 0.00[root@dbbackup sysbench-tpcc-2.2]# sysbench --version
sysbench 1.0.20
[root@dbbackup sysbench-tpcc-2.2]# lua -v
Lua 5.4.6 Copyright (C) 1994-2023 Lua.org, PUC-Rio

load does not terminate on error

FATAL: mysql_drv_query() returned error 1799 (Creating index 'fkey_order_line_21' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.) for query 'CREATE INDEX fkey_order_line_21 ON order_line1 (ol_supply_w_id,ol_i_id)'
FATAL: `sysbench.cmdline.call_command' function failed: ./tpcc_common.lua:333: SQL error, errno = 1799, state = 'HY000': Creating index 'fkey_order_line_21' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

For some reason it did not abort the load script as I think it should in this casel

length of h_data

h_data length is 25 chars in following code

:format(table_num, c_d_id, c_w_id, c_id, d_id, w_id, h_amount, string.format("%10s %10s ",w_name,d_name)))

and it is 24 in tpcc_common.lua.

query = string.format([[
create table IF NOT EXISTS history%d (
%s
h_c_id int,
h_c_d_id ]] .. tinyint_type .. [[,
h_c_w_id smallint,
h_d_id ]] .. tinyint_type .. [[,
h_w_id smallint,
h_date ]] .. datetime_type .. [[,
h_amount decimal(6,2),
h_data varchar(24) %s -----
) %s %s]],

for postgresql (performance improve) , should use prepared statement

for postgresql (performance improve), should use prepared statement, exp:

prepare p1(int) as select * from tbl where id=$1;

execute p1(:id);
...

Payment choose warehouse incorrectly

According to the TPCC spec, for payment transaction, the warehouse choosing rule is as follows:

So for any worker, the home warehouse should be fixed across a full run.

However in the code https://github.com/Percona-Lab/sysbench-tpcc/blob/master/tpcc_run.lua#L226, the home warehouse is selected randomly for each time we start a payment transaction.

I guess this might be a different implementation to TPC-C spec

FATAL:'sysbench.cmdline.call_command' function failed: ./tpcc_common.lua:477:db_bulk_insert_next()

os:centos7.4
mysql:5.7.24
sysbench:1.0.15
sysbench-tpcc:2.2

FATAL: mysql_drv_query() returned error 1105 (Percona-XtraDB-Cluster prohibits use of DML command on a table

os: centos 7.8
mysql: Percona-XtraDB-Cluster 5.7.30
sysbench: 1.0.20
sysbench-tpcc: 1.0.22

Understanding the output of this script

can someone help me understand the output of this script. e.g. I see:

[ 93s ] thds: 56 tps: 43.01 qps: 1397.23 (r/w/o: 645.10/662.11/90.01) lat (ms,95%): 2680.11 err/s 1.00 reconn/s: 0.00
[ 94s ] thds: 56 tps: 42.99 qps: 1384.68 (r/w/o: 636.85/663.85/83.98) lat (ms,95%): 2680.11 err/s 0.00 reconn/s: 0.00
[ 95s ] thds: 56 tps: 51.00 qps: 1370.99 (r/w/o: 622.00/646.00/103.00) lat (ms,95%): 2539.17 err/s 1.00 reconn/s: 0.00
[ 96s ] thds: 56 tps: 53.01 qps: 1369.34 (r/w/o: 640.16/622.16/107.03) lat (ms,95%): 3095.38 err/s 0.00 reconn/s: 0.00
[ 97s ] thds: 56 tps: 39.00 qps: 1367.00 (r/w/o: 626.00/665.00/76.00) lat (ms,95%): 2539.17 err/s 0.00 reconn/s: 0.00
[ 98s ] thds: 56 tps: 43.99 qps: 1346.74 (r/w/o: 609.88/647.87/88.98) lat (ms,95%): 2728.81 err/s 1.00 reconn/s: 0.00
[ 99s ] thds: 56 tps: 45.01 qps: 1323.31 (r/w/o: 607.14/625.15/91.02) lat (ms,95%): 2932.60 err/s 0.00 reconn/s: 0.00
[ 100s ] thds: 56 tps: 44.99 qps: 1345.57 (r/w/o: 607.80/647.79/89.97) lat (ms,95%): 3151.62 err/s 0.00 reconn/s: 0.00
[ 101s ] thds: 56 tps: 58.01 qps: 1354.30 (r/w/o: 611.13/631.14/112.02) lat (ms,95%): 2362.72 err/s 0.00 reconn/s: 0.00
[ 102s ] thds: 56 tps: 45.00 qps: 1368.93 (r/w/o: 622.97/653.96/92.00) lat (ms,95%): 2880.27 err/s 0.00 reconn/s: 0.00

what does it mean? how do i get the overall transactions per second?

bad argument #3 to 'format'

The full error is

FATAL: `thread_run' function failed: ...cal/Cellar/sysbench/1.0.17_1/share/sysbench/tpcc_run.lua:648: bad argument #3 to 'format' (number expected, got nil)

It seems to happen randomly, some long runs complete and some shorter ones encounter this right at the start. The error points to this command:

UPDATE customer%d
                        SET c_balance = c_balance + %f,
                            c_delivery_cnt = c_delivery_cnt + 1
                      WHERE c_id = %d
                        AND c_d_id = %d
                        AND c_w_id = %d]])
                      :format(table_num, sm_ol_amount, o_c_id, d_id, w_id)

When I run the query that generates o_c_id, I get a value though. It's quite puzzling; I thought it had to do with data corruption from exiting the command unexpectedly but it happens even after a fresh prepare and not tampering with anything. Hopefully you can shed some light on it. Thanks.

`innodb_flush_log_at_trx_commit` is not reset correctly at the end of `prepare`

Commit d3ebc95 tries to get the current value of innodb_flush_log_at_trx_commit, set it globally to 0 to make data loading faster, and then reset it back to the original value.

However, when there are multiple threads doing the same thing, some threads will end up getting SET @trx = 0, as the value was already modified globally by the earlier threads. Subsequently, once the prepare phase is done, the server will now be running with innodb_flush_log_at_trx_commit = 0. This then typically makes the benchmark numbers look much better than actual.

For more context, I discovered this flaw because I just couldn't get my numbers to match with Percona's or Dimitri's. In my test steps, I would usually do the prepare first, then stop the server to take a ZFS snapshot, start back the server, and then only execute the run. This means the numbers are not impacted by this issue. Then, yesterday, I decided to just do the prepare and run without restarting the server in between, and boom, the numbers are magically 8x better.

I suspect some of the posted benchmark numbers by Percona or Dimitri could be impacted by this (but can't tell based on the posting date / commit date).

Implementation doesn't take into account 5.2.5.4 of the TPC-C spec - think time

Perhaps I missed something in the code but it looks like this benchmark doesn't implement the "think time" for each transaction as outlined in section 5.2.5.4 of the TPC-C spec here: http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c_v5.11.0.pdf

trx_level issue

When I run either 'prepare' or 'run' with the trx_level setting, I am getting the following message 'Unrecognized command line argument:  --trx_level=RC'. I am running it on Postgres.

For example:
./tpcc.lua --pgsql-user=postgres --pgsql-db=sbtest --time=36000 --threads=56 --report-interval=1 --tables=10 --scale=100 --use_fk=0  --trx_level=RC --pgsql-password=oracle --db-driver=pgsql run

The script would run only if --trx_level is removed.

FATAL: `thread_run' function failed: ./tpcc_run.lua:721: bad argument #1 to 'unpack' (table expected, got nil)

sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 64
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

FATAL: `thread_run' function failed: ./tpcc_run.lua:721: bad argument #1 to 'unpack' (table expected, got
nil)[ 1s ] thds: 50 tps: 60.87 qps: 1337.17 (r/w/o: 580.77/503.93/252.47) lat (ms,95%): 926.33 err/s 0.00 reco
nn/s: 0.00[ 2s ] thds: 50 tps: 1.00 qps: 99.04 (r/w/o: 42.02/56.02/1.00) lat (ms,95%): 1771.29 err/s 0.00 reconn/s:
0.00Error in my_thread_global_end(): 1 threads didn't exit

Question about other_ware function

Not really understand other_ware in tpcc_run.lua.

https://github.com/Percona-Lab/sysbench-tpcc/blob/585e2b8af327d35866aed7e56837c2134d8835a9/tpcc_run.lua#L24C1-L36C4

I think this code will finally generate a warehouse_id exactly as same as input, which is opposite to the comment over the code.

tpcc_run.lua:325: attempt to index global 'row' (a nil value)

In following code,when namecnt is 1, (namecnt / 2 ) + 1 =2,which result error.

    local namecnt = con:query_row(([[SELECT count(c_id) namecnt
                                       FROM customer%d
                                      WHERE c_w_id = %d
                                        AND c_d_id= %d
                                        AND c_last='%s']]):format(table_num, c_w_id, c_d_id, c_last ))

    if namecnt % 2 == 1 then
            namecnt = namecnt + 1
    end
	..........
	..........
	
 for i = 1,  (namecnt / 2 ) + 1 do
		.............

fix: line 313/461
if namecnt % 2 == 1 then
to
if namecnt >1 and namecnt % 2 == 1 then

No .deb release for Ubuntu 22.04 / Jammy?

I noticed that this package was dropped from here: https://repo.percona.com/apt/dists/jammy/main/binary-amd64/

Any chance that you guys will continue building it?

Sysbench Grafana JSON file and datasource for TPC-C for "order-demo"

Need to access Sysbench Grafana files for TPC-C for "order-demo" that Peter showed in the GrafanaCon: Business Dashboards with Grafana and MySQL.

What does it need to prepare RocksDB and choose use db sbr instead of sbt

Low throughput...

Hello,
I have 5 nodes, all have 8 cores and SSD storage.
3 first nodes are a Galera cluster,
4th node is a single node Galera cluster,
and the last one has native MySQL.
I loaded 100 warehouses on all three systems with tpcc_load.
Then I tried to benchmark with tpcc_start, and this is the results in the best case:
3-Node Galera cluster = 3796.400 TpmC
1-Node Galera cluster = 5894.800 TpmC
1-Node Native MySQL = 17991.400 TpmC

As you can see there is a big difference between 3-Node Galera cluster and 1-Node Native MySQL!
I can't understand what's the bottleneck in my system!

This is the configuration for Galera nodes:
[mysqld]
datadir=/tmp/galera/node1
port=3306
socket=/tmp/galera/node1/mysql-node1.sock
pid-file=/tmp/galera/node1/mysql-node1.pid
log-error=/tmp/galera/node1/mysql-node1.err
binlog_format=ROW
innodb_autoinc_lock_mode=2
default_storage_engine=innodb
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2
wsrep_on=ON
wsrep_provider=/home/galera/libgalera_smm.so
wsrep_cluster_name = singlebox
wsrep_cluster_address="gcomm://192.168.3.1,192.168.3.2,192.168.3.3"
wsrep_node_name='node1'
wsrep_node_address='192.168.3.1'
wsrep_provider_options="gcs.fc_limit=1024"

And, ./tpcc_start -h192.168.3.1 -dtpcc -uroot -w100 -c128 -r10 -i1 -l300

Do you have an idea about this?

Thanks in advance.
Vahid

Payment transaction uses wrong warehouse id

I think that the payment transaction uses w_id instead of c_w_id in several places. For instance:

-- SELECT count(c_id) 
--	FROM customer
--	WHERE c_w_id = :c_w_id
--	AND c_d_id = :c_d_id
--	AND c_last = :c_last;*/
  
	local namecnt = con:query_row(([[SELECT count(c_id) namecnt
			                   FROM customer%d
			                  WHERE c_w_id = %d 
			                    AND c_d_id= %d
                                            AND c_last='%s']]):format(table_num, w_id, c_d_id, c_last ))

According to specification this should pass in c_w_id. Updating the warehouse, for example looks correct instead:

-- UPDATE district SET d_ytd = d_ytd + :h_amount
--		WHERE d_w_id = :w_id 
--		AND d_id = :d_id;*/

  con:query(([[UPDATE district%d 
                 SET d_ytd = d_ytd + %d 
               WHERE d_w_id = %d 
                 AND d_id= %d]]):format(table_num, h_amount, w_id, d_id))

I'm happy to open a PR but first want to clarify whether this is intentional or not.

namecnt not made even (?)

Not sure about this one, but in tpcc_run.lua lines 461 to 463 there is:

if namecnt % 2 == 0 then
    namecnt = namecnt + 1
end

In the standard however, the example transaction states as

if (namecnt%2) namecnt++; / / Locate midpoint customer

And therefore, I believe, it must be if namecnt % 2 == 1 then to increment if namecnt is uneven.

If my observation is correct, then I am happy to open a PR.