共计 12739 个字符，预计需要花费 32 分钟才能阅读完成。
这篇文章给大家分享的是有关 MySQL 如何使用 sysbench 做 OLTP 基准测试的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。
一、安装
① 下载源码包：https://dev.mysql.com/downloads/benchmarks.html
② 安装依赖 yum -y install automake autoconf libtool
③tar xzvf sysbench-0.4.12.10.tar.gz; cd sysbench-0.4.12.10/
④./autogen.sh; ./configure
⑤ make make install
然后我们可以在 sysbench 目录看到可以执行文件 sysbench 了
二、命令
首先看下命令基本用法
root@10.30.5.2:sysbench# ./sysbench –help
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.
以上可以看到 sysbench 可以测试的有 CPU、磁盘 IO、内存、线程、MUTEX 以及 OLTP，
常用参数：
–num-threads=N 并发线程数
–max-requests=N 限制压测请求总数
–max-time=N 限制压测时间
这里看下 OLTP 测试方法
./sysbench –test=oltp help
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
oltp options:
–oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]
–oltp-reconnect-mode=STRING reconnect mode {session,transaction,query,random} [session]
–oltp-sp-name=STRING name of store procedure to call in SP test mode []
–oltp-read-only=[on|off] generate only read queries (do not modify database) [off]
–oltp-avoid-deadlocks=[on|off] generate update keys in increasing order to avoid deadlocks [off]
–oltp-skip-trx=[on|off] skip BEGIN/COMMIT statements [off]
–oltp-range-size=N range size for range queries [100]
–oltp-point-selects=N number of point selects [10]
–oltp-use-in-statement=N Use IN-statement with 10 PK lookups per query [0]
–oltp-simple-ranges=N number of simple ranges [1]
–oltp-sum-ranges=N number of sum ranges [1]
–oltp-order-ranges=N number of ordered ranges [1]
–oltp-distinct-ranges=N number of distinct ranges [1]
–oltp-index-updates=N number of index update [1]
–oltp-non-index-updates=N number of non-index updates [1]
–oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
–oltp-auto-inc=[on|off] whether AUTO_INCREMENT (or equivalent) should be used on id column [on]
–oltp-connect-delay=N time in microseconds to sleep after connection to database [10000]
–oltp-user-delay-min=N minimum time in microseconds to sleep after each request [0]
–oltp-user-delay-max=N maximum time in microseconds to sleep after each request [0]
–oltp-table-name=STRING name of test table [sbtest]
–oltp-table-size=N number of records in test table [10000]
–oltp-dist-type=STRING random numbers distribution {uniform,gaussian,special} [special]
–oltp-dist-iter=N number of iterations used for numbers generation [12]
–oltp-dist-pct=N percentage of values to be treated as special (for special distribution) [1]
–oltp-dist-res=N percentage of special values to use (for special distribution) [75]
–oltp-point-select-mysql-handler=[on|off]Use MySQL HANDLER for point select [off]
–oltp-point-select-all-cols=[on|off] select all columns for the point-select query [off]
–oltp-secondary=[on|off] Use a secondary index in place of the PRIMARY index [off]
–oltp-num-partitions=N Number of partitions used for test table [0]
–oltp-num-tables=N Number of test tables [1]
General database options:
–db-driver=STRING specifies database driver to use (help to get list of available drivers)
–db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
Compiled-in database drivers:
mysql – MySQL driver
mysql options:
–mysql-host=[LIST,…] MySQL server host [localhost]
–mysql-port=N MySQL server port [3306]
–mysql-socket=STRING MySQL socket
–mysql-user=STRING MySQL user [sbtest]
–mysql-password=STRING MySQL password []
–mysql-db=STRING MySQL database name [sbtest]
–mysql-table-engine=STRING storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]
–mysql-engine-trx=STRING whether storage engine used is transactional or not {yes,no,auto} [auto]
–mysql-ssl=[on|off] use SSL connections, if available in the client library [off]
–myisam-max-rows=N max-rows parameter for MyISAM tables [1000000]
–mysql-create-options=STRING additional options passed to CREATE TABLE []
说明：
常用参数
① 基本参数
–db-driver=mysql 对 mysql 进行 OLTP 基准测试
–mysql-host、–mysql-port、–mysql-socket、–mysql-user、–mysql-password 这些是基本的参数我就不解释了
–mysql-db=xxx 压测的 database，这里得指定一下
② oltp 常用参数
–oltp-test-mode=complex/simple/nontrx 测试模式
–oltp-num-tables=10 oltp 测试的表数量 0.4.10 版本最大表数量 16
–oltp-table-size=xxx 测试表的记录数
三、测试
① 测试准备：
root@10.30.5.2:sysbench#./sysbench –num-threads=64 –max-requests=200000 –test=oltp –db-driver=mysql –mysql-user=root –mysql-host=10.30.22.2 –mysql-password=x –oltp-test-mode=complex –mysql-db=tab –oltp-table-size=5000000 –oltp-num-tables=16 prepare
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Creating table sbtest14 …
Creating table sbtest7 …
Creating table sbtest …
Creating table sbtest11 …
Creating table sbtest8 …
Creating table sbtest6 …
Creating table sbtest9 …
Creating table sbtest12 …
Creating table sbtest3 …
Creating table sbtest15 …
Creating table sbtest2 …
Creating table sbtest4 …
Creating table sbtest1 …
Creating table sbtest5 …
Creating table sbtest13 …
Creating table sbtest10 …
Creating 5000000 records in table sbtest11 …
Creating 5000000 records in table sbtest1 …
Creating 5000000 records in table sbtest14 …
Creating 5000000 records in table sbtest …
Creating 5000000 records in table sbtest6 …
Creating 5000000 records in table sbtest2 …
Creating 5000000 records in table sbtest13 …
Creating 5000000 records in table sbtest15 …
Creating 5000000 records in table sbtest12 …
Creating 5000000 records in table sbtest4 …
Creating 5000000 records in table sbtest3 …
Creating 5000000 records in table sbtest9 …
Creating 5000000 records in table sbtest8 …
Creating 5000000 records in table sbtest10 …
Creating 5000000 records in table sbtest5 …
Creating 5000000 records in table sbtest7 …
② 测试结果
点击 (此处) 折叠或打开
root@10.30.5.2:sysbench# ./sysbench –num-threads=64 –max-requests=200000 –test=oltp –db-driver=mysql –mysql-user=root –mysql-host=10.30.22.xxx –mysql-password=xxx –oltp-test-mode=complex –mysql-db=tab –oltp-table-size=5000000 –oltp-num-tables=16 run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 64
Random number generator seed is 0 and will be ignored
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using BEGIN for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 200000
Using 16 test tables
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 2800224
write: 1000080
other: 400032
total: 4200336
transactions: 200016 (2000.64 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 3800304 (38012.16 per sec.)
other operations: 400032 (4001.28 per sec.)
General statistics:
total time: 99.9760s
total number of events: 200016
total time taken by event execution: 6394.8091
response time:
min: 11.00ms
avg: 31.97ms
max: 293.00ms
approx. 95 percentile: 52.10ms
Threads fairness:
events (avg/stddev): 3125.2500/50.55
execution time (avg/stddev): 99.9189/0.01
③ 测试清除
点击 (此处) 折叠或打开
root@10.30.5.2:sysbench# ./sysbench –num-threads=64 –max-requests=200000 –test=oltp –db-driver=mysql –mysql-user=root –mysql-host=10.30.22.xxx –mysql-password=xxx –oltp-test-mode=complex –mysql-db=tab –oltp-table-size=5000000 –oltp-num-tables=16 cleanup
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Dropping table sbtest …
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 …
Done.
总结：
1 PREPARE 阶段
在 PREPARE 阶段我们就需要想好，此时 mysql 的配置，如 innodb_flush_log_at_trx_commit、sync_binlog 以及 BP 的大小等。
然后结合 BP 的大小我们需要创建表的记录数，表的个数，并发线程等，综合考虑
① 若数据量 BP 所有数据都会缓存到内存，此时增加 并发线程数 来测整个此时的 CPU 核数是否能抗住测试压力
② 若数据量 BP 则主要测试整个系统的稳定性，我们可以结合监控看缓存命中率(orzdba)，以及对应的 磁盘 IO(iostat / orzdba) 等，来获取整个数据库系统的薄弱点
2 RUN 阶段
在 RUN 阶段的同时，我们可以通过 orzdba/iostat 等工具查看当前的数据库状态
① 上面的测试结果我们可以看到，
transactions: 200016 (2000.64 per sec.) TPS 大概为 2000
read/write requests: 3800304 (38012.16 per sec.) QPS 达到 38000
approx. 95 percentile: 52.10ms 95% 的请求相应时间在 52.10ms 左右
可以说性能是相当不错了(这里我测试的是腾讯云 CDB , 配置为 1000MB 的 BP)
② 通过 orzdba 结合 running 过程查看数据库状态
点击 (此处) 折叠或打开
root@10.30.5.2:orzdba_home# ./orzdba -mysql -innodb -rt
.=================================================.
| Welcome to use the orzdba tool ! |
| Yep…Chinese English~ |
=============== Date : 2017-04-18 ===============
HOST: 10.30.22.2 IP: 10.30.5.2
DB : performance_schema|tab
Var : binlog_format[MIXED] max_binlog_cache_size[17179869184G] max_binlog_size[1G]
max_connect_errors[999999999] max_connections[800] max_user_connections[0]
open_files_limit[102400] sync_binlog[0] table_definition_cache[768]
table_open_cache[512] thread_cache_size[512]
innodb_adaptive_flushing[ON] innodb_adaptive_hash_index[ON] innodb_buffer_pool_size[893M]
innodb_file_per_table[ON] innodb_flush_log_at_trx_commit[2] innodb_flush_method[O_DIRECT]
innodb_io_capacity[20000] innodb_lock_wait_timeout[7200] innodb_log_buffer_size[64M]
innodb_log_file_size[500M] innodb_log_files_in_group[2] innodb_max_dirty_pages_pct[75]
innodb_open_files[1024] innodb_read_io_threads[4] innodb_thread_concurrency[0]
innodb_write_io_threads[4]
——– -QPS- -TPS- -Hit%- —innodb bp pages status– —–innodb data status—- –innodb log– his –log(byte)– read —query— ——threads—— —–bytes—- ——–tcprstat(us)——–
time | ins upd del sel iud| lor hit| data free dirty flush| reads writes read written|fsyncs written| list uflush uckpt view inside que| run con cre cac| recv send| count avg 95-avg 99-avg|
17:24:53| 0 0 0 0 0| 0 100.00| 0 0 0 0| 0 0 0 0| 0 0| 0 0 0 0 0 0| 0 0 0 0| 0 0| 0 0 0 0|
17:24:54| 2153 6454 2153 30115 10760| 478891 97.72| 55869 0 19432 2372| 11355 6686 177.4m 79.2m| 2 5.1m| 137 1.7m 180.6m 57 0 0| 20 68 0 1| 1.1m 10.7m| 23711 267 183 236|
17:24:55| 1960 5891 1962 27470 9813| 437599 97.71| 55872 0 19793 2132| 10416 6063 162.8m 71.4m| 1 4.8m| 142 2.4m 183.5m 62 0 0| 14 68 0 1| 1005k 9.7m| 23004 356 215 283|
17:24:56| 2027 6091 2033 28422 10151| 451846 97.74| 55870 0 20024 2249| 10609 6320 165.8m 75.2m| 3 4.9m| 133 927k 186.5m 59 0 0| 8 68 0 1| 1.0m 10.2m| 22684 299 186 244|
17:24:57| 2291 6865 2286 32067 11442| 511514 97.69| 55870 0 20248 2611| 12314 7194 192.4m 87.0m| 2 5.4m| 128 19k 189.6m 54 0 0| 4 68 0 1| 1.1m 11.5m| 25197 267 188 234|
17:24:58| 2210 6632 2207 30947 11049| 493747 97.77| 55865 0 20361 2371| 11478 6810 179.3m 79.5m| 2 5.4m| 130 267k 192.9m 64 0 0| 48 68 0 1| 1.1m 11.3m| 24586 270 182 232|
17:24:59| 2225 6680 2226 31102 11131| 496716 97.70| 55866 0 20305 2602| 11891 7059 185.8m 86.6m| 1 5.3m| 149 473k 196.0m 53 0 0| 11 68 0 1| 1.1m 11.3m| 20655 371 219 304|
17:25:00| 2126 6377 2130 29819 10633| 472984 97.70| 55868 0 20195 2489| 11332 6749 177.1m 82.8m| 2 5.0m| 125 370k 199.0m 62 0 0| 13 68 0 1| 1.1m 10.8m| 8707 958 664 856|
17:25:01| 2169 6507 2165 30307 10841| 484346 97.71| 55766 99 20214 2485| 11550 6849 180.5m 82.7m| 1 5.1m| 133 808k 202.0m 57 0 0| 15 68 0 1| 1.1m 11.2m| 8578 996 636 844|
可以发现 在 32 个 thread 并发进行 complex 操作的时候，每秒的 insert 量 update 量 delete 量 select 量可以看得非常清楚，还有 innodb_log 的 fsync 量，以及数据库的 response time。
感谢各位的阅读！关于“MySQL 如何使用 sysbench 做 OLTP 基准测试”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
