Sysbench MySQL效能測試比較On SSD & SATA

標籤：style   blog   http   color   io   os   使用   ar   for   

機器硬體環境：

CPU: Intel(R) Xeon(R) CPU E3-1230 v3 @ 3.30GHz 4核

Mem: 32G

SSD: INTEL SSDSC2BP480G4

SATA HDD: 2*500G LVM

 

sysbench 0.5的安裝：

#bzr branch lp:~sysbench-developers/sysbench/0.5  sysbench#cd sysbench/#./configure --prefix=/usr/local/sysbench0.5#make && make install然後拷貝編譯好的源碼至 /usr/local/src目錄下#cp -dprf sysbench /usr/local/src/

 

這裡注意sysbench 0.5 --help選項中並未顯示針對OLTP的協助資訊，如下：

[[email protected] bin]# /usr/local/sysbench0.5/bin/sysbench --helpMissing required command argument.Usage:  sysbench [general-options]... --test=<test-name> [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]  --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 [off]  --rand-init=[on|off]        initialize random number generator [off]  --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, ignored when 0 [0]  --rand-pareto-h=N           parameter h for pareto distibution [0.2]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=<name> help‘ for a list of options for each test.

 

還得看sysben0.4的相關協助，如下：

[[email protected] bin]#/usr/local/sysbench0.4/bin/sysbench --helpUsage:  sysbench [general-options]... --test=<test-name> [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 [32K]  --init-rng=[on|off]        initialize random number generator [off]  --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 exitCompiled-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 testCommands: prepare run cleanup help versionSee ‘sysbench --test=<name> help‘ for a list of options for each test.[[email protected] bin]# ./sysbench --test=oltp helpsysbench 0.4.12:  multi-threaded system evaluation benchmarkoltp 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-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-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]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 drivermysql 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 []

 

先sysbench測試on SSD:

my.cnf 配置如下：

[client]port            = 3306socket          = /ssd/mysql5.5/mysql.sock[mysqld]port            = 3306socket          = /ssd/mysql5.5/mysql.sockdatadir = /ssd/mysql5.5/databack_log = 50character-set-filesystem=utf8character-set-server=utf8collation-server=utf8_general_cimax_connections = 100max_connect_errors = 10table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 1Mmax_heap_table_size = 64Mread_buffer_size = 2Mread_rnd_buffer_size = 16Msort_buffer_size = 8Mjoin_buffer_size = 8Mthread_cache_size = 8thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = INNODBthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mserver-id = 1key_buffer_size = 32Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 20Ginnodb_file_per_table = trueinnodb_data_file_path = ibdata1:10M:autoextendinnodb_data_ssd_dir = /ssd/mysql5.5/datainnodb_write_io_threads = 16innodb_read_io_threads = 16innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 256Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_log_group_ssd_dir = /ssd/mysql5.5/datainnodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 8192

View Code

 

接下來，使用SYSBENCH 產生初始化資料：

/usr/local/sysbench0.5/bin/sysbench --test=/usr/local/src/sysbench/sysbench/sysbench --test=/usr/local/src/sysbench/sysbench/tests/db/parallel_prepare.lua --mysql-socket=/ssd/mysql5.5/mysql.sock --mysql-user=root --mysql-table-engine=innodb --oltp-table-size=25000000 --oltp-tables-count=16 --rand-init=on --num-threads=16 --oltp-read-only=off --report-interval=10 --rand-type=special --rand-spec-pct=5 --max-requests=0 --percentile=99 --max-time=6000 run

 

接下來sysbench 測試OLTP應用：

/usr/local/sysbench0.5/bin/sysbench --test=/usr/local/src/sysbench/sysbench/sysbench --test=/usr/local/src/sysbench/sysbench/tests/db/oltp.lua --mysql-socket=/ssd/mysql5.5/mysql.sock --mysql-user=root --mysql-table-engine=innodb --oltp-table-size=25000000 --oltp-tables-count=16 --rand-init=on --num-threads=16 --oltp-read-only=off --report-interval=10 --rand-type=special --rand-spec-pct=5 --max-requests=0 --percentile=99 --max-time=6000 run

 

測試結果如下：

sysbench 0.5: multi-threaded system evaluation benchmark

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

Random number generator seed is 0 and will be ignored

Threads started!

[ 10s] threads: 16, tps: 431.35, reads/s: 6056.98, writes/s: 1727.62, response time: 77.86ms (99%)
[ 20s] threads: 16, tps: 671.30, reads/s: 9394.20, writes/s: 2684.20, response time: 53.66ms (99%)
[ 30s] threads: 16, tps: 889.20, reads/s: 12454.40, writes/s: 3557.70, response time: 42.33ms (99%)
[ 40s] threads: 16, tps: 970.80, reads/s: 13589.70, writes/s: 3883.40, response time: 39.56ms (99%)
[ 50s] threads: 16, tps: 1024.20, reads/s: 14342.20, writes/s: 4098.50, response time: 38.07ms (99%)
[ 60s] threads: 16, tps: 1034.90, reads/s: 14480.88, writes/s: 4136.40, response time: 37.70ms (99%)
[ 70s] threads: 16, tps: 947.10, reads/s: 13266.32, writes/s: 3793.20, response time: 122.98ms (99%)
[ 80s] threads: 16, tps: 1006.20, reads/s: 14081.49, writes/s: 4020.40, response time: 77.28ms (99%)
[ 90s] threads: 16, tps: 938.30, reads/s: 13133.50, writes/s: 3753.10, response time: 129.52ms (99%)
。。。。。。。省略

OLTP test statistics:
queries performed:
read: 80147774
write: 22899364
other: 11449682
total: 114496820
transactions: 5724841 (954.14 per sec.)    （代表TPS）
deadlocks: 0 (0.00 per sec.)
read/write requests: 103047138 (17174.45 per sec.) （代表QPS）
other operations: 11449682 (1908.27 per sec.)

General statistics:
total time: 6000.0260s
total number of events: 5724841
total time taken by event execution: 95991.9466s
response time:
min: 2.11ms
avg: 16.77ms
max: 75652.05ms
approx. 99 percentile: 74.13ms

Threads fairness:
events (avg/stddev): 357802.5625/970.53
execution time (avg/stddev): 5999.4967/0.01

 

同樣的方法測試mysql on SATA上的結果，待後續：

 

Sysbench MySQL效能測試比較On SSD & SATA