Sysbench MySQL性能测试比较On SSD & SATA
机器硬件环境：
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的帮助信息，如下：
[root@test bin]# /usr/local/sysbench0.5/bin/sysbench --help Missing required command argument. Usage: sysbench [general-options]... --test=<test-name> [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 [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 test Commands: prepare run cleanup help version See ‘sysbench --test=<name> help‘ for a list of options for each test.
还得看sysben0.4的相关帮助，如下：
[root@test bin]#/usr/local/sysbench0.4/bin/sysbench --help Usage: sysbench [general-options]... --test=<test-name> [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] --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 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=<name> help‘ for a list of options for each test. [root@test bin]# ./sysbench --test=oltp help sysbench 0.4.12: 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-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 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 []
先sysbench测试on SSD:
my.cnf 配置如下：
[client] port = 3306 socket = /ssd/mysql5.5/mysql.sock [mysqld] port = 3306 socket = /ssd/mysql5.5/mysql.sock datadir = /ssd/mysql5.5/data back_log = 50 character-set-filesystem=utf8 character-set-server=utf8 collation-server=utf8_general_ci max_connections = 100 max_connect_errors = 10 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M read_buffer_size = 2M read_rnd_buffer_size = 16M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = INNODB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M server-id = 1 key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 20G innodb_file_per_table = true innodb_data_file_path = ibdata1:10M:autoextend innodb_data_ssd_dir = /ssd/mysql5.5/data innodb_write_io_threads = 16 innodb_read_io_threads = 16 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 256M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_log_group_ssd_dir = /ssd/mysql5.5/data innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192
接下来，使用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上的结果，待后续：
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
