sysbench 0.5 基準測試

sysbench 介紹

SysBench是一個模組化的、跨平台、多線程基準測試載入器，主要用於評估測試各種不同系統參數下的資料庫負載情況。它主要包括以下幾種方式的測試： cpu效能 磁碟io效能 發送器效能 記憶體配置及傳輸速度 POSIX線程效能 資料庫效能(OLTP基準測試)

目前sysbench主要支援 MySQL,pgsql,Oracle 這3種資料庫。 sysbench 安裝

預設支援MySQL，如果需要測試Oracle/PostgreSQL，則在configure時需要加上–with-oracle或者–with-pgsql參數，
sysbench預設安裝在： /usr/local/bin/sysbench

shell> tar -zxvf sysbench-0.5.tar.gz -C /db/tool/shell> cd /db/tool/sysbench-0.5shell> chmod +x autogen.shshell> ./autogen.sh automake 1.10.x (aclocal) wasn't found, exitingshell> yum install automakeshell> ./autogen.sh libtoolize 1.4+ wasn't found, exitingshell> yum install libtoolshell> ./autogen.sh ./autogen.sh ./autogen.sh: running `aclocal -I m4' ./autogen.sh: running `libtoolize --copy --force' libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'.libtoolize: copying file `config/ltmain.sh'libtoolize: putting macros in AC_CONFIG_MACRO_DIR, `m4'.libtoolize: copying file `m4/libtool.m4'libtoolize: copying file `m4/ltoptions.m4'libtoolize: copying file `m4/ltsugar.m4'libtoolize: copying file `m4/ltversion.m4'libtoolize: copying file `m4/lt~obsolete.m4'./autogen.sh: running `autoheader' ./autogen.sh: running `automake -c --foreign --add-missing' configure.ac:23: installing `config/compile'configure.ac:11: installing `config/config.guess'configure.ac:11: installing `config/config.sub'configure.ac:16: installing `config/install-sh'configure.ac:16: installing `config/missing'sysbench/Makefile.am: installing `config/depcomp'./autogen.sh: running `autoconf' Libtoolized with: libtoolize (GNU libtool) 2.2.6bAutomade with: automake (GNU automake) 1.11.1Configured with: autoconf (GNU Autoconf) 2.63shell> ./configure --prefix=/db/sysbench --with-mysql-includes=/db/mysql/include --with-mysql-libs=/db/mysql/libshell> make && make install測試安裝shell> cp /db/sysbench/bin/sysbench /usr/local/bin/shell> sysbench --helpsysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory問題原因：sysbench無法找到mysql的庫檔案，可能是環境變數LD_LIBRARY_PATH沒有設定，設定後即可解決該問題： 添加export LD_LIBRARY_PATH=/db/mysql/lib即可

sysbench 參數

shell> sysbench --helpMissing required command argument.Usage:  sysbench [general-options]... --test= [test-options]... commandGeneral 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 [64K]  --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]  --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 [off]  --rand-init=[on|off]       initialize random number generator [off]  --rand-type=STRING         random numbers distribution {uniform,gaussian,special} [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, ignored when 0 [0]Log options:  --verbosity=N      verbosity level {5 - debug, 0 - only critical messages} [3]  --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 testCommands: prepare run cleanup help versionSee 'sysbench --test= help' for a list of options for each test.

CPU測試

sysbench CPU測試使用64位整數，測試計算素數直到某個最大值所需要的時間

shell> sysbench --test=cpu --cpu-max-prime=2000 runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Random number generator seed is 0 and will be ignoredPrimer numbers limit: 2000Threads started!Test execution summary:    total time:                          2.2452s    total number of events:              10000    total time taken by event execution: 2.2347s    per-request statistics:         min:                                  0.20ms         avg:                                  0.22ms         max:                                  3.35ms         approx.  95 percentile:               0.27msThreads fairness:    events (avg/stddev):           10000.0000/0.00    execution time (avg/stddev):   2.2347/0.00補充：查看CPU資訊方法查看物理cpu個數 grep "physical id" /proc/cpuinfo | sort -u | wc -l查看核心數量 grep "core id" /proc/cpuinfo | sort -u | wc -l查看線程數量 grep "processor" /proc/cpuinfo | sort -u | wc -l在sysbench的測試中，--num-threads取值為"線程數量"即可

線程(thread)測試

測試線程調度器的效能。對於高負載情況下測試線程調度器的行為非常有用

shell> sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 64Random number generator seed is 0 and will be ignoredThreads started!Test execution summary:    total time:                          1.9581s    total number of events:              10000    total time taken by event execution: 124.8938s    per-request statistics:         min:                                  0.05ms         avg:                                 12.49ms         max:                                151.15ms         approx.  95 percentile:              50.83msThreads fairness:    events (avg/stddev):           156.2500/14.48    execution time (avg/stddev):   1.9515/0.00

檔案IO效能測試 產生需要的測試檔案，檔案總大小5G，16個並發線程。執行完後會在目前的目錄下產生一堆小檔案。

shell> sysbench --test=fileio --num-threads=16 --file-total-size=5G prepare

執行測試，指定隨機讀寫入模式

指定讀寫入模式： seqwr 順序寫入 seqrewr 順序重寫 seqrd 順序讀取 rndrd 隨機讀取 rndwr 隨機寫入 rndrw 混合隨機讀/寫

shell> sysbench --test=fileio --num-threads=16 --init-rng=on --file-total-size=5G --file-test-mode=rndrw runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 16Random number generator seed is 0 and will be ignoredThreads started!Operations performed:  5999 reads, 4001 writes, 12800 Other = 22800 TotalRead 93.734Mb  Written 62.516Mb  Total transferred 156.25Mb  (9.2561Mb/sec)   ##輸送量  592.39 Requests/sec executedTest execution summary:    total time:                          16.8808s    total number of events:              10000    total time taken by event execution: 176.1816s    per-request statistics:         min:                                  0.01ms         avg:                                 17.62ms         max:                                416.73ms         approx.  95 percentile:             104.82msThreads fairness:    events (avg/stddev):           625.0000/62.39    execution time (avg/stddev):   11.0114/0.67

清除測試檔案

shell> sysbench --test=fileio --num-threads=16 --file-total-size=5G cleanup

互斥鎖(Mutex)測試

測試互斥鎖的效能，方式是類比所有線程在同一時刻並發運行，並都短暫請求互斥鎖。

shell> sysbench --test=mutex --num-threads=16 --mutex-num=1024 --mutex-locks=10000 --mutex-loops=5000 runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 16Random number generator seed is 0 and will be ignoredThreads started!Test execution summary:    total time:                          0.0135s    total number of events:              16    total time taken by event execution: 0.0411s    per-request statistics:         min:                                  0.70ms         avg:                                  2.57ms         max:                                  9.19ms         approx.  95 percentile:               9.16msThreads fairness:    events (avg/stddev):           1.0000/0.00    execution time (avg/stddev):   0.0026/0.00

記憶體測試

記憶體測試測試了記憶體的連續讀寫效能。

shell> sysbench --test=memory --num-threads=16 --memory-block-size=8192 --memory-total-size=1G runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 16Random number generator seed is 0 and will be ignoredThreads started!Operations performed: 131072 (381158.38 ops/sec)1024.00 MB transferred (2977.80 MB/sec)       ##輸送量Test execution summary:    total time:                          0.3439s    total number of events:              131072    total time taken by event execution: 3.9915s    per-request statistics:         min:                                  0.00ms         avg:                                  0.03ms         max:                                 51.02ms         approx.  95 percentile:               0.00ms  ##大約95%的時間分布Threads fairness:    events (avg/stddev):           8192.0000/1166.77    execution time (avg/stddev):   0.2495/0.02

MySQL資料庫測試

sysbench 0.5通過一系列LUA指令碼來替換之前的oltp，來類比更接近真實的基準測試環境。這些測試指令碼包含：insert.lua、oltp.lua、parallel_prepare.lua、select_random_points.lua、update_index.lua、delete.lua oltp_simple.lua、select.lua、select_random_ranges.lua、update_non_index.lua，指令碼使用方式基本類似。

sysbench 0.5預設使用sbtest庫，但是需要自己手工先建立好，也可以使用--mysql-db指定，其他非預設項指定選項： -mysql-host -mysql-port -mysql-socket -mysql-user -mysql-password -mysql-db -mysql-ssl prepare
產生表並插入資料，可使用parallel_prepare.lua指令碼來並行準備資料。 –db-driver 伺服器類型 mysql | drizzle,預設為mysql –mysql-table-engine 表存數引擎 –myisam-max-rows MyISAM表MAX_ROWS選項(用於大表) –oltp-table-count 產生表數量[sbtest1、sbtest2...] –oltp-table-size 產生表的行數 –oltp-secondary ID列產生二級索引而不是主鍵 –oltp-auto-inc 設定ID列是否自增 on | off，預設為on

shell> cd /db/tool/sysbench-0.5/sysbenchshell> sysbench --test=./tests/db/oltp.lua --mysql-table-engine=myisam --oltp-table-size=100000 --mysql-db=test --mysql-user=root --oltp-tables-count=10 --mysql-password=oracle --mysql-socket=/tmp/mysql.sock preparesysbench 0.5:  multi-threaded system evaluation benchmarkCreating table 'sbtest1'...Inserting 100000 records into 'sbtest1'Creating table 'sbtest2'...Inserting 100000 records into 'sbtest2'Creating table 'sbtest3'...Inserting 100000 records into 'sbtest3'Creating table 'sbtest4'...Inserting 100000 records into 'sbtest4'Creating table 'sbtest5'...Inserting 100000 records into 'sbtest5'Creating table 'sbtest6'...Inserting 100000 records into 'sbtest6'Creating table 'sbtest7'...Inserting 100000 records into 'sbtest7'Creating table 'sbtest8'...Inserting 100000 records into 'sbtest8'Creating table 'sbtest9'...Inserting 100000 records into 'sbtest9'Creating table 'sbtest10'...Inserting 100000 records into 'sbtest10'也可使用parallel_prepare.lua指令碼並行準備測試資料，線程數應該為運行表的倍數shell> sysbench --test=./tests/db/parallel_prepare.lua  --mysql-table-engine=myisam --oltp-table-size=100000  --num-threads=10 --mysql-db=test  --mysql-user=root --oltp-tables-count=10 --mysql-password=oracle  --mysql-socket=/tmp/mysql.sock runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 10Random number generator seed is 0 and will be ignoredThreads started!thread prepare8Creating table 'sbtest9'...thread prepare1Creating table 'sbtest2'...thread prepare0Creating table 'sbtest1'...thread prepare5Creating table 'sbtest6'...thread prepare3Creating table 'sbtest4'...thread prepare6Creating table 'sbtest7'...thread prepare9Creating table 'sbtest10'...thread prepare4Creating table 'sbtest5'...thread prepare2Creating table 'sbtest3'...thread prepare7Creating table 'sbtest8'...Inserting 100000 records into 'sbtest1'Inserting 100000 records into 'sbtest9'Inserting 100000 records into 'sbtest8'Inserting 100000 records into 'sbtest6'Inserting 100000 records into 'sbtest7'Inserting 100000 records into 'sbtest2'Inserting 100000 records into 'sbtest10'Inserting 100000 records into 'sbtest4'Inserting 100000 records into 'sbtest5'Inserting 100000 records into 'sbtest3'OLTP test statistics:    queries performed:        read:                            0        write:                           380        other:                           20        total:                           400    transactions:                        0      (0.00 per sec.)    deadlocks:                           0      (0.00 per sec.)    read/write requests:                 380    (32.82 per sec.)    other operations:                    20     (1.73 per sec.)Test execution summary:    total time:                          11.5785s    total number of events:              10000    total time taken by event execution: 0.1422s    per-request statistics:         min:                                  0.00ms         avg:                                  0.01ms         max:                                 19.55ms         approx.  95 percentile:               0.00mshttp://ww3.sinaimg.cn/bmiddle/671c4d8egw1enjimkujv2g20b40b4b29.gifThreads fairness:    events (avg/stddev):           1000.0000/2912.74    execution time (avg/stddev):   0.0142/0.04

run –oltp-tables-count –oltp-read-only 僅執行SELECT測試 on | off,預設為off –oltp-dist-type 隨機數分布狀態。uniform(均勻分布)、gauss(高斯分布)、special(特殊分布) –oltp-dist-pct 特殊分布的百分比值 –oltp-dist-res 特殊分布的百分比 –oltp-point-selects 單個事務中指定的selec查詢個數 –oltp-range-size 範圍查詢的範圍大小，該值應比oltp-table-size小 –oltp-simple-ranges 單個事務中指定的簡單範圍查詢個數 –oltp-sum-ranges 單個事務中指定的SUM範圍查詢個數 –oltp-order-ranges 單個事務中指定的ORDER範圍查詢個數 –oltp-distinct-ranges 單個事務中指定的DISTINCT範圍查詢個數 –oltp-index-updates 單個事務中指定的使用索引更新的個數 –oltp-non-index-updates 單個事務中指定的不使用索引更新的個數

shell> sysbench --test=./tests/db/oltp.lua --num_threads=10  --oltp-table-size=100000 --mysql-db=test --mysql-user=root --oltp-tables-count=10 --mysql-password=oracle  runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 10Random number generator seed is 0 and will be ignoredThreads started!OLTP test statistics:    queries performed:        read:                            140028  -- 讀總數        write:                           40008   -- 寫總數        other:                           20004   -- 其他動作總數(SELECT、INSERT、UPDATE、DELETE之外的操作，例如COMMIT等)        total:                           200040  -- 全部總數    transactions:                        10002  (234.44 per sec.)  -- 總事務數(每秒事務數)    deadlocks:                           0      (0.00 per sec.)    -- 發生死結總數    read/write requests:                 180036 (4219.99 per sec.) -- 讀寫總數(每秒讀寫次數)    other operations:                    20004  (468.89 per sec.)  -- 其他動作總數(每秒其他動作次數)Test execution summary:    total time:                          42.6626s  -- 總耗時    total number of events:              10002     -- 共發生多少事務數    total time taken by event execution: 426.3020s -- 所有事務耗時相加(不考慮並行因素)    per-request statistics:         min:                                  5.36ms   -- 最小耗時         avg:                                 42.62ms   -- 平均耗時         max:                                183.44ms   -- 最長耗時         approx.  95 percentile:              59.81ms   -- 超過99%平均耗時Threads fairness:    events (avg/stddev):           1000.2000/23.87    execution time (avg/stddev):   42.6302/0.01

clearnup

shell> sysbench --test=./tests/db/oltp.lua --oltp-table-size=100000 --mysql-db=test --mysql-user=root --oltp-tables-count=10 --mysql-password=oracle  cleanupsysbench 0.5:  multi-threaded system evaluation benchmarkDropping 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'...

可以對資料庫進行調優後，再使用sysbench對OLTP進行測試，看看TPS是不是會有所提高。
注意：sysbench的測試只是基準測試，並不能代表實際企業環境下的效能指標。

參見： http://www.percona.com/docs/wiki/benchmark:sysbench:olpt.lua

整理自網路 Svoid 2014-12-24
--本篇文章轉自：http://blog.itpub.net/29733787/viewspace-1383197/