MySQL如何使用sysbench做OLTP基準測試

141次閱讀

共計 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:

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)——–

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 基準測試”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

正文完