MySQL如何使用sysbench做OLTP基準(zhǔn)測試

發(fā)布時間：2021-11-06 11:07:35 來源：億速云閱讀：221 作者：小新欄目：MySQL數(shù)據(jù)庫

這篇文章給大家分享的是有關(guān)MySQL如何使用sysbench做OLTP基準(zhǔn)測試的內(nèi)容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

一、安裝
① 下載源碼包：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 目錄看到可以執(zhí)行文件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、內(nèi)存、線程、MUTEX 以及OLTP ，
常用參數(shù)：
--num-threads=N 并發(fā)線程數(shù)
--max-requests=N 限制壓測請求總數(shù)
--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 []

說明：
常用參數(shù)
① 基本參數(shù)
--db-driver=mysql 對mysql進行 OLTP 基準(zhǔn)測試
--mysql-host 、--mysql-port、--mysql-socket、--mysql-user、--mysql-password 這些是基本的參數(shù)我就不解釋了
--mysql-db=xxx 壓測的database，這里得指定一下
② oltp常用參數(shù)
--oltp-test-mode=complex/simple/nontrx 測試模式
--oltp-num-tables=10 oltp測試的表數(shù)量 0.4.10版本最大表數(shù)量16
--oltp-table-size=xxx 測試表的記錄數(shù)

三、測試
① 測試準(zhǔn)備：

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'...

② 測試結(jié)果

點擊(此處)折疊或打開

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.

總結(jié)：
1> PREPARE階段
在 PREPARE 階段我們就需要想好，此時mysql 的配置，如 innodb_flush_log_at_trx_commit 、 sync_binlog 以及BP 的大小等。
然后結(jié)合 BP 的大小我們需要創(chuàng)建表的記錄數(shù)，表的個數(shù)，并發(fā)線程等，綜合考慮
① 若數(shù)據(jù)量 < BP 所有數(shù)據(jù)都會緩存到內(nèi)存，此時增加并發(fā)線程數(shù) 來測整個此時的CPU核數(shù)是否能抗住測試壓力
② 若數(shù)據(jù)量 >> BP 則主要測試整個系統(tǒng)的穩(wěn)定性，我們可以結(jié)合監(jiān)控看緩存命中率( orzdba )，以及對應(yīng)的磁盤IO( iostat / orzdba ) 等，來獲取整個數(shù)據(jù)庫系統(tǒng)的薄弱點

2> RUN 階段
在 RUN 階段的同時，我們可以通過 orzdba/iostat 等工具查看當(dāng)前的數(shù)據(jù)庫狀態(tài)
① 上面的測試結(jié)果我們可以看到，
transactions: 200016 (2000.64 per sec.) TPS 大概為 2000
read/write requests: 3800304 (38012.16 per sec.) QPS 達到 38000
approx. 95 percentile: 52.10ms 95%的請求相應(yīng)時間在52.10ms左右
可以說性能是相當(dāng)不錯了(這里我測試的是騰訊云 CDB , 配置為 1000MB 的BP )
② 通過orzdba 結(jié)合running過程查看數(shù)據(jù)庫狀態(tài)

點擊(此處)折疊或打開

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|

可以發(fā)現(xiàn) 在 32個 thread并發(fā)進行complex操作的時候，每秒的insert量 update量 delete量 select量可以看得非常清楚，還有innodb_log 的fsync量，以及數(shù)據(jù)庫的response time。

感謝各位的閱讀！關(guān)于“MySQL如何使用sysbench做OLTP基準(zhǔn)測試”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

向AI問一下細節(jié)

MySQL如何使用sysbench做OLTP基準(zhǔn)測試

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽