OceanBase Sysbench Test

root@dbabackup:/data/tools/sysbench# cat /etc/issue
Ubuntu 20.04.4 LTS \n \l

（1）下载安装：

 #apt -y install make automake libtool pkg-config libaio-dev
 # For MySQL support
 apt -y install libmysqlclient-dev libssl-dev
 # For PostgreSQL support
 apt -y install libpq-dev

#curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash

#sudo apt -y install sysbench

（2）测试加载库是否正常

root@dbabackup:/data/tools# ldd /usr/bin/sysbench
linux-vdso.so.1 (0x00007fffe0564000)
libmysqlclient.so.21 => /lib/x86_64-linux-gnu/libmysqlclient.so.21 (0x00007f969653f000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f969651c000)
libpq.so.5 => /data/pgsqlbase/lib/libpq.so.5 (0x00007f96964d3000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f96964cd000)
libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f96964c8000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f9696377000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f969635c000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f969616a000)
libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f969614e000)
libssl.so.1.1 => /lib/x86_64-linux-gnu/libssl.so.1.1 (0x00007f96960bb000)
libcrypto.so.1.1 => /lib/x86_64-linux-gnu/libcrypto.so.1.1 (0x00007f9695de5000)
libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007f9695dc7000)
libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f9695be5000)
/lib64/ld-linux-x86-64.so.2 (0x00007f9696f2f000)

root@dbabackup:/data/tools# sysbench --help
Usage:
sysbench [options]... [testname] [command]

Commands implemented by most tests: prepare run cleanup help

General options:
--threads=N number of threads to use [1]
--events=N limit for total number of events [0]
--time=N limit for total execution time in seconds [10]
--forced-shutdown=STRING number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
--thread-stack-size=SIZE size of stack per thread [64K]
--rate=N average transactions rate. 0 for unlimited rate [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. []
--debug[=on|off] print more debugging info [off]
--validate[=on|off] perform validation checks where possible [off]
--help[=on|off] print help and exit [off]
--version[=on|off] print version and exit [off]
--config-file=FILENAME File containing command line options
--tx-rate=N deprecated alias for --rate [0]
--max-requests=N deprecated alias for --events [0]
--max-time=N deprecated alias for --time [0]
--num-threads=N deprecated alias for --threads [1]

Pseudo-Random Numbers Generator options:
--rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]
--rand-spec-iter=N number of iterations used for numbers generation [12]
--rand-spec-pct=N percentage of values to be treated as 'special' (for special distribution) [1]
--rand-spec-res=N percentage of 'special' values to use (for special distribution) [75]
--rand-seed=N seed for random number generator. When 0, the current time is used as a RNG seed. [0]
--rand-pareto-h=N parameter h for pareto distribution [0.2]

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

--percentile=N percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
--histogram[=on|off] print latency histogram in report [off]

General database options:

--db-driver=STRING specifies database driver to use ('help' to get list of available drivers) [mysql]
--db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
--db-debug[=on|off] print database-specific debug information [off]

Compiled-in database drivers:
mysql - MySQL driver
pgsql - PostgreSQL driver

mysql options:
--mysql-host=[LIST,...] MySQL server host [localhost]
--mysql-port=[LIST,...] MySQL server port [3306]
--mysql-socket=[LIST,...] MySQL socket
--mysql-user=STRING MySQL user [sbtest]
--mysql-password=STRING MySQL password []
--mysql-db=STRING MySQL database name [sbtest]
--mysql-ssl[=on|off] use SSL connections, if available in the client library [off]
--mysql-ssl-cipher=STRING use specific cipher for SSL connections []
--mysql-compression[=on|off] use compression, if available in the client library [off]
--mysql-debug[=on|off] trace all client library calls [off]
--mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205]
--mysql-dry-run[=on|off] Dry run, pretend that all MySQL client API calls are successful without executing them [off]

pgsql options:
--pgsql-host=STRING PostgreSQL server host [localhost]
--pgsql-port=N PostgreSQL server port [5432]
--pgsql-user=STRING PostgreSQL user [sbtest]
--pgsql-password=STRING PostgreSQL password []
--pgsql-db=STRING PostgreSQL database name [sbtest]

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

See 'sysbench <testname> help' for a list of options for each test.

root@dbabackup:/data/tools# sysbench --mysql-host=fdfasdfadfa.com --mysql-port=3306 --mysql-user=ec_mkl --mysql-db=test --table_size=1000000 --tables=30 --threads=32 --report-interval=10 --time=60 --db-ps-mode=disable --mysql-password=xxxxxxxxxx
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Reading the script from the standard input:

操作步聚（我的IOPS是1000）：

一、运行以下命令，初始化数据库：

root@dbabackup:/usr/share/sysbench# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host='tfffffffuncs.com' --mysql-port=3306 --mysql-user='ec_mkl' --mysql-db='test' --table_size=1000000 --tables=30 --threads=32 --report-interval=10 --time=60 --mysql-password='UxxxxxxxxhOVvUlffH' cleanup
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...
Dropping table 'sbtest11'...
Dropping table 'sbtest12'...
Dropping table 'sbtest13'...
Dropping table 'sbtest14'...
Dropping table 'sbtest15'...
Dropping table 'sbtest16'...
Dropping table 'sbtest17'...
Dropping table 'sbtest18'...
Dropping table 'sbtest19'...
Dropping table 'sbtest20'...
Dropping table 'sbtest21'...
Dropping table 'sbtest22'...
Dropping table 'sbtest23'...
Dropping table 'sbtest24'...
Dropping table 'sbtest25'...
Dropping table 'sbtest26'...
Dropping table 'sbtest27'...
Dropping table 'sbtest28'...
Dropping table 'sbtest29'...
Dropping table 'sbtest30'...

二、以下命令，新建表

root@dbabackup:/usr/share/sysbench# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host='ssssfwfw.com' --mysql-port=3306 --mysql-user='ec_mkl' --mysql-db='test' --table_size=1000000 --tables=30 --threads=32 --report-interval=10 --time=60 --mysql-password='vfdsfsdaf' prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest11'...
Creating table 'sbtest6'...
Creating table 'sbtest19'...
Creating table 'sbtest1'...
Creating table 'sbtest23'...
Creating table 'sbtest24'...
Creating table 'sbtest17'...
Creating table 'sbtest3'...
Creating table 'sbtest25'...
Creating table 'sbtest5'...
Creating table 'sbtest14'...
Creating table 'sbtest7'...
Creating table 'sbtest13'...
Creating table 'sbtest8'...
Creating table 'sbtest26'...
Creating table 'sbtest16'...
Creating table 'sbtest21'...
Creating table 'sbtest9'...
Creating table 'sbtest10'...
Creating table 'sbtest15'...
Creating table 'sbtest22'...
Creating table 'sbtest12'...
Creating table 'sbtest4'...
Creating table 'sbtest18'...
Creating table 'sbtest27'...
Creating table 'sbtest29'...
Creating table 'sbtest2'...
Creating table 'sbtest28'...
Creating table 'sbtest30'...
Creating table 'sbtest20'...
Inserting 1000000 records into 'sbtest11'
Inserting 1000000 records into 'sbtest19'
Inserting 1000000 records into 'sbtest3'
Inserting 1000000 records into 'sbtest1'
Inserting 1000000 records into 'sbtest25'
Inserting 1000000 records into 'sbtest6'
Inserting 1000000 records into 'sbtest24'
Inserting 1000000 records into 'sbtest17'
Inserting 1000000 records into 'sbtest23'
Inserting 1000000 records into 'sbtest5'
Inserting 1000000 records into 'sbtest4'
Inserting 1000000 records into 'sbtest13'
Inserting 1000000 records into 'sbtest10'
Inserting 1000000 records into 'sbtest8'
Inserting 1000000 records into 'sbtest16'
Inserting 1000000 records into 'sbtest14'
Inserting 1000000 records into 'sbtest15'
Inserting 1000000 records into 'sbtest21'
Inserting 1000000 records into 'sbtest26'
Inserting 1000000 records into 'sbtest7'
Inserting 1000000 records into 'sbtest9'
Inserting 1000000 records into 'sbtest12'
Inserting 1000000 records into 'sbtest18'
Inserting 1000000 records into 'sbtest29'
Inserting 1000000 records into 'sbtest22'
Inserting 1000000 records into 'sbtest27'
Inserting 1000000 records into 'sbtest2'
Inserting 1000000 records into 'sbtest30'
Inserting 1000000 records into 'sbtest28'
Inserting 1000000 records into 'sbtest20'

Creating a secondary index on 'sbtest28'...
Creating a secondary index on 'sbtest12'...
Creating a secondary index on 'sbtest27'...
Creating a secondary index on 'sbtest20'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest30'...
Creating a secondary index on 'sbtest26'...
Creating a secondary index on 'sbtest25'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest22'...
Creating a secondary index on 'sbtest29'...
Creating a secondary index on 'sbtest13'...
Creating a secondary index on 'sbtest21'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest18'...
Creating a secondary index on 'sbtest23'...
Creating a secondary index on 'sbtest16'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest24'...
Creating a secondary index on 'sbtest11'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest14'...
Creating a secondary index on 'sbtest17'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest15'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest19'...
Creating a secondary index on 'sbtest6'...

(3)运行以下命令，执行测试：

root@dbabackup:/usr/share/sysbench# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host='xxxxx' --mysql-port=3306 --mysql-user='ec_mkl' --mysql-db='test' --table_size=10000000 --tables=30 --threads=150 --report-interval=10 --time=60 --mysql-password='xfsfadf' --db-ps-mode=disable run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

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

Initializing worker threads...

Threads started!

[ 10s ] thds: 150 tps: 792.32 qps: 16000.59 (r/w/o: 11222.49/3179.07/1599.03) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 150 tps: 849.11 qps: 16979.14 (r/w/o: 11884.57/3396.35/1698.22) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 150 tps: 815.81 qps: 16303.61 (r/w/o: 11410.38/3262.02/1631.21) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 150 tps: 794.71 qps: 15911.52 (r/w/o: 11141.79/3179.62/1590.11) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 150 tps: 793.19 qps: 15850.38 (r/w/o: 11092.34/3172.16/1585.88) lat (ms,95%): 215.44 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 150 tps: 782.29 qps: 15651.71 (r/w/o: 10957.20/3129.94/1564.57) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 677992
write: 193712
other: 96856
total: 968560
transactions: 48428 (801.01 per sec.)
queries: 968560 (16020.27 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 60.4540s
total number of events: 48428

Latency (ms):
min: 126.72
avg: 186.22
max: 1046.07
95th percentile: 215.44
sum: 9018164.46

Threads fairness:
events (avg/stddev): 322.8533/24.77
execution time (avg/stddev): 60.1211/0.09

总结：

因我的是企业版v2.2.77版本，通过外网访问，后续生产中会打标最新的v3.2.30版本，给的IOPS才1000,只是完成了简单的测试过程，不代表业务真实环境。

以下是2022年08月09日 oceanbase3.2.3.1版本的压力测试 配置大小是16C70G1-1-1多区。

--table_size=1000000 --tables=30 --threads=32 --report-interval=10 --time=60

以下是2022年08月09日polardb-mysql生产库8C32G的

=================================================--table_size=10000000 --tables=30 --threads=100 --report-interval=10 --time=60 =========================================================================