机器硬件环境：
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.5sysbench
#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-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= 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-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=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= 3306socket= /ssd/mysql5.5/mysql.sock[mysqld]port= 3306socket= /ssd/mysql5.5/mysql.sock
datadir= /ssd/mysql5.5/data
back_log= 50character-set-filesystem=utf8
character-set-server=utf8
collation-server=utf8_general_ci
max_connections= 100max_connect_errors= 10table_open_cache= 2048max_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= 8thread_concurrency= 8query_cache_size=64M
query_cache_limit=2M
ft_min_word_len= 4default-storage-engine=INNODB
thread_stack=192K
transaction_isolation=REPEATABLE-READ
tmp_table_size=64M
server-id= 1key_buffer_size=32M
bulk_insert_buffer_size=64M
myisam_sort_buffer_size=128M
myisam_max_sort_file_size=10G
myisam_repair_threads= 1myisam_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= 16innodb_read_io_threads= 16innodb_thread_concurrency= 16innodb_flush_log_at_trx_commit= 1innodb_log_buffer_size=256M
innodb_log_file_size=512M
innodb_log_files_in_group= 3innodb_log_group_ssd_dir= /ssd/mysql5.5/data
innodb_max_dirty_pages_pct= 90innodb_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
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.)
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上的结果, 结果如下：
nohup: ignoring input
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: 5.21, reads/s: 91.04, writes/s: 21.73, response time: 3504.01ms (99%)
[ 20s] threads: 16, tps: 7.10, reads/s: 96.10, writes/s: 29.30, response time: 4795.20ms (99%)
[ 30s] threads: 16, tps: 8.10, reads/s: 105.10, writes/s: 30.70, response time: 3616.98ms (99%)
[ 40s] threads: 16, tps: 7.40, reads/s: 110.30, writes/s: 29.60, response time: 2875.84ms (99%)
[ 50s] threads: 16, tps: 7.30, reads/s: 105.90, writes/s: 29.50, response time: 3043.23ms (99%)
[ 60s] threads: 16, tps: 8.40, reads/s: 101.40, writes/s: 33.20, response time: 5186.41ms (99%)
[ 70s] threads: 16, tps: 6.50, reads/s: 111.20, writes/s: 29.20, response time: 2856.11ms (99%)
[ 80s] threads: 16, tps: 8.50, reads/s: 111.50, writes/s: 31.60, response time: 3209.78ms (99%)
[ 90s] threads: 16, tps: 8.20, reads/s: 112.50, writes/s: 32.20, response time: 2959.68ms (99%)
[ 100s] threads: 16, tps: 7.80, reads/s: 115.50, writes/s: 31.40, response time: 3325.18ms (99%)
[ 110s] threads: 16, tps: 9.00, reads/s: 112.40, writes/s: 35.60, response time: 3183.94ms (99%)
[ 120s] threads: 16, tps: 7.80, reads/s: 124.80, writes/s: 35.60, response time: 4818.22ms (99%)
[ 130s] threads: 16, tps: 8.00, reads/s: 114.10, writes/s: 28.50, response time: 2826.34ms (99%)
[ 140s] threads: 16, tps: 8.30, reads/s: 114.00, writes/s: 36.30, response time: 3235.83ms (99%)
[ 150s] threads: 16, tps: 8.30, reads/s: 114.80, writes/s: 30.60, response time: 4418.92ms (99%)
[ 160s] threads: 16, tps: 8.20, reads/s: 118.20, writes/s: 32.60, response time: 2917.46ms (99%)
。。。。。。。
OLTP test statistics:
queries performed:
read: 1932490
write: 552140
other: 276070
total: 2760700
transactions: 138035 (23.00 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 2484630 (414.06 per sec.)
other operations: 276070 (46.01 per sec.)
General statistics:
total time: 6000.6796s
total number of events: 138035
total time taken by event execution: 96004.4845s
response time:
min: 123.86ms
avg: 695.51ms
max: 7019.34ms
approx. 99 percentile: 2235.13ms
Threads fairness:
events (avg/stddev): 8627.1875/24.01
execution time (avg/stddev): 6000.2803/0.14
对比TPS以及响应时间：
SSD：
TPS:954.14 response time: < 100 ms
SATA:
TPS: 23.00response time: > 3000 ms
可见SSD TPS 基本上是SATA的40倍，响应时间30倍左右。
额外说下针对此mysql server的应用测试后，在关闭mysql时，耗时将近10分钟，log如下：
141007 11:59:20 [Note] /usr/sbin/mysqld: Normal shutdown
141007 11:59:20 [Note] Event Scheduler: Purging the queue. 0 events
141007 11:59:20 InnoDB: Starting shutdown...
141007 11:59:21 InnoDB: Waiting for 200 pages to be flushed
141007 12:00:20 InnoDB: Waiting for master thread to be suspended
141007 12:00:22 InnoDB: Waiting for 200 pages to be flushed
141007 12:01:21 InnoDB: Waiting for master thread to be suspended
141007 12:01:23 InnoDB: Waiting for 200 pages to be flushed
141007 12:02:21 InnoDB: Waiting for master thread to be suspended
141007 12:02:24 InnoDB: Waiting for 201 pages to be flushed
141007 12:03:21 InnoDB: Waiting for master thread to be suspended
141007 12:03:25 InnoDB: Waiting for 201 pages to be flushed
141007 12:04:21 InnoDB: Waiting for master thread to be suspended
141007 12:04:26 InnoDB: Waiting for 200 pages to be flushed
141007 12:05:21 InnoDB: Waiting for master thread to be suspended
141007 12:05:27 InnoDB: Waiting for 200 pages to be flushed
141007 12:06:22 InnoDB: Waiting for master thread to be suspended
141007 12:06:28 InnoDB: Waiting for 200 pages to be flushed
141007 12:07:22 InnoDB: Waiting for master thread to be suspended
141007 12:07:29 InnoDB: Waiting for 200 pages to be flushed
141007 12:08:22 InnoDB: Waiting for master thread to be suspended
141007 12:08:30 InnoDB: Waiting for 200 pages to be flushed
141007 12:09:22 InnoDB: Waiting for master thread to be suspended
141007 12:09:31 InnoDB: Waiting for 200 pages to be flushed
141007 12:10:22 InnoDB: Waiting for master thread to be suspended
141007 12:10:33 InnoDB: Waiting for 200 pages to be flushed
141007 12:11:23 InnoDB: Waiting for master thread to be suspended
141007 12:11:35 InnoDB: Waiting for 200 pages to be flushed
141007 12:12:23 InnoDB: Waiting for master thread to be suspended
141007 12:12:37 InnoDB: Waiting for 201 pages to be flushed
141007 12:13:25 InnoDB: Shutdown completed; log sequence number 113522107648
141007 12:13:25 [Note] /usr/sbin/mysqld: Shutdown complete
141007 12:13:25 mysqld_safe mysqld from pid file /home/mysql5.5/data/test.netsoft.me.pid ended
可见mysql 在关闭时，仍有大量的脏页需要刷新到磁盘中。
307
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
