Questions on sysbench

Hello Everyone,

When report-interval is specified, it shows r/w/o. r-read, w-write, what is o?

[ 35s ] thds: 100 tps: 32.02 qps: 719.49 (r/w/o: 551.37/97.07/71.05) lat (ms,95%): 2828.87 err/s: 0.00 reconn/s: 0.00

Also the report shows as below. Why are transactions lower than number of writes?

SQL statistics:
queries performed:
read: 30828
write: 8808
other: 4404
total: 44040
transactions: 2202 (35.01 per sec.)

The sysbench command used for this is:
sysbench /usr/local/Cellar/sysbench/1.0.20_3/share/sysbench/oltp_read_write.lua --time=60 --histogram --threads=100 --db-driver=mysql --mysql-db=sysbench --mysql-user=admin --mysql-password=somepass --tables=4 --table-size=500000 --report-interval=1 --mysql-host=$hostname run

Thank you!

Hi @Venkat ,

Thanks for reaching out.

Transactions cannot be counted as normal queries. When I say normal queries, I mean executing insert,update straight away with auto commit as default. But when you define various queries in a set, it becomes a transaction.

For example :

begin;
insert into t1 (c1) values(1);
select c1 from t1;
commit;

This becomes a transaction. So what you see in output is different number of writes than the number of transactions. Hope this provide the clarification.

2 Likes

Hi @Ankit_Kapoor1,
Thanks for your response. Yes, I understand that part. But while reading your response, I also realized, what I observe is natural as the transaction will bundle multiple writes and hence lower number of transactions than the writes, which was my question. Also, would you know what does “o” represent in r/w/o?

Thanks again!
Venkat

1 Like

Hi @Venkat
By default sysbench will run several queries per transaction, as you can see from the lua code in oltp_common.lua:

The default is to run more reads than writes, but this can be modified via command line switches:

michael@xps:~$ sysbench /usr/share/sysbench/oltp_read_write.lua help
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

oltp_read_write.lua options:
  --auto_inc[=on|off]           Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
  --create_secondary[=on|off]   Create a secondary index in addition to the PRIMARY KEY [on]
  --delete_inserts=N            Number of DELETE/INSERT combinations per transaction [1]
  --distinct_ranges=N           Number of SELECT DISTINCT queries per transaction [1]
  --index_updates=N             Number of UPDATE index queries per transaction [1]
  --mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
  --non_index_updates=N         Number of UPDATE non-index queries per transaction [1]
  --order_ranges=N              Number of SELECT ORDER BY queries per transaction [1]
  --pgsql_variant=STRING        Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
  --point_selects=N             Number of point SELECT queries per transaction [10]
  --range_selects[=on|off]      Enable/disable all range SELECT queries [on]
  --range_size=N                Range size for range SELECT queries [100]
  --secondary[=on|off]          Use a secondary index in place of the PRIMARY KEY [off]
  --simple_ranges=N             Number of simple range SELECT queries per transaction [1]
  --skip_trx[=on|off]           Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
  --sum_ranges=N                Number of SELECT SUM() queries per transaction [1]
  --table_size=N                Number of rows per table [10000]
  --tables=N                    Number of tables [1]

The o refers to other and these are the non-DML queries, for example BEGIN, COMMIT, PREPARE, etc

Thank you, @Michael Coburn !

1 Like