使用 sysbench 測試 MySQL 的效能(二)
上篇說到在這個伺服器上安裝mysql 5.5.16老是啟動就crashed，設定引數innodb_native_aio = off也沒有用。主要是因為核心引數沒有設定好，須增加與非同步相關的設定：
fs.aio-max-nr = 3145728
同時innodb的引數還是一樣：
innodb_log_file_size = 200M
innodb_log_files_in_group = 3
innodb_log_files_in_group = 3
innodb_buffer_pool_size = 2048M
innodb_log_buffer_size = 16M
innodb_log_buffer_size = 16M
innodb_additional_mem_pool_size = 60M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 0
query_cache_size = 0
init_connect='SET autocommit=0'
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 0
query_cache_size = 0
init_connect='SET autocommit=0'
從下面看出TPS確實比5.1有很大的提高，同時對多cpu的利用也更加充分
[root@testdb2 bin]# ./sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=sbtest --mysql-password=123456 --mysql-host=localhost --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=20 --max-requests=100000 --oltp-test-mode=complex run
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 20
Running the test with following options:
Number of threads: 20
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 100000
Threads started!
Done.
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 100000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 1400140
write: 500050
other: 200020
total: 2100210
transactions: 100010 (2031.71 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900190 (38602.54 per sec.)
other operations: 200020 (4063.43 per sec.)
queries performed:
read: 1400140
write: 500050
other: 200020
total: 2100210
transactions: 100010 (2031.71 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900190 (38602.54 per sec.)
other operations: 200020 (4063.43 per sec.)
Test execution summary:
total time: 49.2245s
total number of events: 100010
total time taken by event execution: 982.9139
per-request statistics:
min: 2.48ms
avg: 9.83ms
max: 1001.61ms
approx. 95 percentile: 19.33ms
total time: 49.2245s
total number of events: 100010
total time taken by event execution: 982.9139
per-request statistics:
min: 2.48ms
avg: 9.83ms
max: 1001.61ms
approx. 95 percentile: 19.33ms
Threads fairness:
events (avg/stddev): 5000.5000/172.07
execution time (avg/stddev): 49.1457/0.02
events (avg/stddev): 5000.5000/172.07
execution time (avg/stddev): 49.1457/0.02
[root@testdb2 bin]# ./sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=sbtest --mysql-password=123456 --mysql-host=localhost --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=15 --max-requests=100000 --oltp-test-mode=complex run
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 15
Running the test with following options:
Number of threads: 15
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 100000
Threads started!
Done.
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 100000
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 1400084
write: 500030
other: 200012
total: 2100126
transactions: 100006 (2075.49 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900114 (39434.40 per sec.)
other operations: 200012 (4150.99 per sec.)
queries performed:
read: 1400084
write: 500030
other: 200012
total: 2100126
transactions: 100006 (2075.49 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1900114 (39434.40 per sec.)
other operations: 200012 (4150.99 per sec.)
Test execution summary:
total time: 48.1842s
total number of events: 100006
total time taken by event execution: 721.1929
per-request statistics:
min: 2.70ms
avg: 7.21ms
max: 997.79ms
approx. 95 percentile: 8.92ms
total time: 48.1842s
total number of events: 100006
total time taken by event execution: 721.1929
per-request statistics:
min: 2.70ms
avg: 7.21ms
max: 997.79ms
approx. 95 percentile: 8.92ms
Threads fairness:
events (avg/stddev): 6667.0667/136.28
execution time (avg/stddev): 48.0795/0.03
events (avg/stddev): 6667.0667/136.28
execution time (avg/stddev): 48.0795/0.03
下面是cpu的使用情況，比較充分的利用了多個cpu的能力：
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
24 0 1575868 37612 115372 27004044 0 0 0 3004 8835 169704 46 34 20 0 0
18 0 1575868 36744 115380 27004204 0 0 4 2720 7426 171943 44 35 20 0 0
23 0 1575868 36620 115396 27004212 0 0 4 2960 7271 162067 45 36 19 0 0
21 0 1575868 36620 115396 27004212 0 0 0 4 6649 173904 47 42 11 0 0
26 0 1575868 36992 115412 27004216 0 0 0 3928 7743 177347 46 41 14 0 0
9 0 1575868 36496 115428 27004204 0 0 12 2468 8154 175983 43 40 17 0 0
26 0 1575868 36496 115444 27004228 0 0 4 2416 9160 172780 46 38 16 0 0
24 0 1575868 36248 115460 27004212 0 0 0 2716 7968 174385 49 37 15 0 0
26 0 1575868 35876 115476 27004236 0 0 0 2504 7406 181327 49 42 9 0 0
27 0 1575868 35876 115492 27004220 0 0 0 2616 6903 182099 48 39 13 0 0
21 0 1575868 36000 115508 27004236 0 0 4 2824 8915 174273 45 40 15 0 0
23 0 1575868 36620 115524 27004220 0 0 0 2508 7299 175947 45 41 14 0 0
25 0 1575868 36620 115540 27004240 0 0 0 2480 8489 178815 44 43 12 0 0
27 0 1575868 36736 115556 27004224 0 0 0 2852 7201 172072 43 43 14 0 0
22 0 1575868 36000 115584 27004228 0 0 0 3020 7271 177431 43 40 17 0 0
27 0 1575868 36248 115584 27004232 0 0 0 2584 8935 176867 49 30 21 0 0
16 0 1575868 36000 115592 27004264 0 0 0 2628 7144 164690 52 29 20 0 0
17 0 1575868 35480 115616 27004240 0 0 0 3028 7813 170283 49 37 14 0 0
28 0 1575868 29664 115640 27004692 0 0 204 3348 8833 163507 44 44 11 0 0
27 0 1575868 30068 115648 26998316 0 0 88 3384 7921 157621 44 42 13 1 0
22 0 1575864 40552 115656 26992500 4 0 92 2860 6497 128295 36 53 10 0 0
21 0 1575864 33732 115672 26993368 0 0 28 3320 6950 161859 46 43 11 0 0
25 0 1575792 30244 115692 26993440 20 0 32 2632 7703 147772 39 51 10 0 0
22 0 1575792 46520 115700 26993604 0 0 4 2048 8538 150350 38 49 13 0 0
27 0 1575792 46892 115704 26993628 0 0 0 2728 10178 180623 47 39 14 0 0
r b swpd free buff cache si so bi bo in cs us sy id wa st
24 0 1575868 37612 115372 27004044 0 0 0 3004 8835 169704 46 34 20 0 0
18 0 1575868 36744 115380 27004204 0 0 4 2720 7426 171943 44 35 20 0 0
23 0 1575868 36620 115396 27004212 0 0 4 2960 7271 162067 45 36 19 0 0
21 0 1575868 36620 115396 27004212 0 0 0 4 6649 173904 47 42 11 0 0
26 0 1575868 36992 115412 27004216 0 0 0 3928 7743 177347 46 41 14 0 0
9 0 1575868 36496 115428 27004204 0 0 12 2468 8154 175983 43 40 17 0 0
26 0 1575868 36496 115444 27004228 0 0 4 2416 9160 172780 46 38 16 0 0
24 0 1575868 36248 115460 27004212 0 0 0 2716 7968 174385 49 37 15 0 0
26 0 1575868 35876 115476 27004236 0 0 0 2504 7406 181327 49 42 9 0 0
27 0 1575868 35876 115492 27004220 0 0 0 2616 6903 182099 48 39 13 0 0
21 0 1575868 36000 115508 27004236 0 0 4 2824 8915 174273 45 40 15 0 0
23 0 1575868 36620 115524 27004220 0 0 0 2508 7299 175947 45 41 14 0 0
25 0 1575868 36620 115540 27004240 0 0 0 2480 8489 178815 44 43 12 0 0
27 0 1575868 36736 115556 27004224 0 0 0 2852 7201 172072 43 43 14 0 0
22 0 1575868 36000 115584 27004228 0 0 0 3020 7271 177431 43 40 17 0 0
27 0 1575868 36248 115584 27004232 0 0 0 2584 8935 176867 49 30 21 0 0
16 0 1575868 36000 115592 27004264 0 0 0 2628 7144 164690 52 29 20 0 0
17 0 1575868 35480 115616 27004240 0 0 0 3028 7813 170283 49 37 14 0 0
28 0 1575868 29664 115640 27004692 0 0 204 3348 8833 163507 44 44 11 0 0
27 0 1575868 30068 115648 26998316 0 0 88 3384 7921 157621 44 42 13 1 0
22 0 1575864 40552 115656 26992500 4 0 92 2860 6497 128295 36 53 10 0 0
21 0 1575864 33732 115672 26993368 0 0 28 3320 6950 161859 46 43 11 0 0
25 0 1575792 30244 115692 26993440 20 0 32 2632 7703 147772 39 51 10 0 0
22 0 1575792 46520 115700 26993604 0 0 4 2048 8538 150350 38 49 13 0 0
27 0 1575792 46892 115704 26993628 0 0 0 2728 10178 180623 47 39 14 0 0
top - 10:00:02 up 267 days, 22:37, 4 users, load average: 3.67, 0.90, 0.35
Tasks: 765 total, 2 running, 763 sleeping, 0 stopped, 0 zombie
Cpu0 : 40.7%us, 43.3%sy, 0.0%ni, 15.4%id, 0.3%wa, 0.0%hi, 0.0%si, 0.3%st
Cpu1 : 40.8%us, 44.1%sy, 0.0%ni, 14.4%id, 0.0%wa, 0.0%hi, 0.3%si, 0.3%st
Cpu2 : 39.7%us, 46.6%sy, 0.0%ni, 13.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 45.9%us, 41.6%sy, 0.0%ni, 11.8%id, 0.0%wa, 0.0%hi, 0.3%si, 0.3%st
Cpu4 : 39.5%us, 43.1%sy, 0.0%ni, 17.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 25.6%us, 28.9%sy, 0.0%ni, 45.2%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 43.8%us, 42.8%sy, 0.0%ni, 12.7%id, 0.0%wa, 0.0%hi, 0.7%si, 0.0%st
Cpu7 : 38.2%us, 45.4%sy, 0.0%ni, 15.4%id, 0.0%wa, 0.0%hi, 0.7%si, 0.3%st
Cpu8 : 44.7%us, 44.7%sy, 0.0%ni, 10.2%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu9 : 36.3%us, 48.4%sy, 0.0%ni, 15.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu10 : 37.3%us, 42.6%sy, 0.0%ni, 19.8%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu11 : 43.0%us, 43.0%sy, 0.0%ni, 13.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.3%st
Cpu12 : 45.4%us, 42.2%sy, 0.0%ni, 12.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.3%st
Cpu13 : 40.2%us, 41.2%sy, 0.0%ni, 18.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.3%st
Cpu14 : 39.4%us, 42.3%sy, 0.0%ni, 17.6%id, 0.0%wa, 0.0%hi, 0.7%si, 0.0%st
Cpu15 : 46.4%us, 41.1%sy, 0.0%ni, 12.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32628736k total, 32597064k used, 31672k free, 115176k buffers
Swap: 104856244k total, 1575868k used, 103280376k free, 27011068k cached
Tasks: 765 total, 2 running, 763 sleeping, 0 stopped, 0 zombie
Cpu0 : 40.7%us, 43.3%sy, 0.0%ni, 15.4%id, 0.3%wa, 0.0%hi, 0.0%si, 0.3%st
Cpu1 : 40.8%us, 44.1%sy, 0.0%ni, 14.4%id, 0.0%wa, 0.0%hi, 0.3%si, 0.3%st
Cpu2 : 39.7%us, 46.6%sy, 0.0%ni, 13.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 45.9%us, 41.6%sy, 0.0%ni, 11.8%id, 0.0%wa, 0.0%hi, 0.3%si, 0.3%st
Cpu4 : 39.5%us, 43.1%sy, 0.0%ni, 17.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 25.6%us, 28.9%sy, 0.0%ni, 45.2%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 43.8%us, 42.8%sy, 0.0%ni, 12.7%id, 0.0%wa, 0.0%hi, 0.7%si, 0.0%st
Cpu7 : 38.2%us, 45.4%sy, 0.0%ni, 15.4%id, 0.0%wa, 0.0%hi, 0.7%si, 0.3%st
Cpu8 : 44.7%us, 44.7%sy, 0.0%ni, 10.2%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu9 : 36.3%us, 48.4%sy, 0.0%ni, 15.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu10 : 37.3%us, 42.6%sy, 0.0%ni, 19.8%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu11 : 43.0%us, 43.0%sy, 0.0%ni, 13.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.3%st
Cpu12 : 45.4%us, 42.2%sy, 0.0%ni, 12.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.3%st
Cpu13 : 40.2%us, 41.2%sy, 0.0%ni, 18.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.3%st
Cpu14 : 39.4%us, 42.3%sy, 0.0%ni, 17.6%id, 0.0%wa, 0.0%hi, 0.7%si, 0.0%st
Cpu15 : 46.4%us, 41.1%sy, 0.0%ni, 12.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32628736k total, 32597064k used, 31672k free, 115176k buffers
Swap: 104856244k total, 1575868k used, 103280376k free, 27011068k cached
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/758322/viewspace-708617/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用 sysbench 測試 MySQL 的效能MySql
- 使用sysbench測試Mysql效能MySql
- mysql效能測試工具之sysbench薦MySql
- sysbench 測試MySQLMySql
- 使用 Sysbench 進行 Linux 效能測試Linux
- sysbench測試mysql oltpMySql
- mysql之 sysbench0.4.12資料庫效能測試MySql資料庫
- 使用sysbench測試mysql及postgresql(完整版)MySql
- sysbench的安裝和做效能測試
- 通過sysbench工具實現MySQL資料庫的效能測試MySql資料庫
- MySQL基準測試工具sysbenchMySql
- Mysql 壓力測試工具sysbenchMySql
- 【Mysql】sysbench基準測試工具MySql
- MySQL 效能壓測工具-sysbench，從入門到自定義測試項MySql
- 使用Sysbench對滴滴雲MySQL進行基準測試MySql
- 【MySQL】利用sysbench進行基準測試MySql
- 資料庫效能測試：sysbench用法詳解資料庫
- mysql ,tidb sysbench 測試結果記錄MySqlTiDB
- 詳解 MySQL 基準測試和 sysbench 工具MySql
- 利用sysbench進行MySQL OLTP基準測試MySql
- KunlunDB 0.9.1版本Sysbench效能測試報告測試報告
- 開源多執行緒效能測試工具-sysbench執行緒
- MySQL壓力測試中定製sysbench的Lua模板MySql
- sysbench測試軟體
- mysql壓力測試在青雲PCIE盤sysbench版本MySql
- MySQL 加密的效能測試MySql加密
- 效能測試工具Locust使用指南(二)
- 使用sysbench壓力測試MySQL（一）(r11筆記第3天)MySql筆記
- MySQL DB Server 上面安裝 sysbench 作壓力測試MySqlServer
- MYSQL 寫效能測試MySql
- MySQL索引效能測試MySql索引
- 【效能測試】使用ab做Http效能測試HTTP
- 使用tpcc-mysql對mysql進行TPCC效能測試MySql
- 面向開發的測試技術（二）：效能測試
- mysql叢集壓力測試在京東雲盤：sysbench版本MySql
- mysql壓力測試在京東雲ssd雲盤sysbench版本MySql
- 效能測試總結(二)---測試流程篇
- mysql簡單效能測試MySql