一、環境：京東雲Centos6.8 ，cpu16核,記憶體32G，SAS 3G,轉速未知，SSD雲盤300G
mysql 版本是：5.7.20 ，預設rpm安裝,單例項。
壓測工具是：sysbench1.0.9
mysql壓力測試在京東雲ssd雲盤fio測試4K隨機讀寫iops是4144.

mysql參數列基本配置：
innodb_buffer_pool_size = 22938M
innodb_buffer_pool_instances = 8
skip-name-resolve
transaction_isolation=READ-COMMITTED
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_log_files_in_group=5
innodb_temp_data_file_path=ibtmp1:512M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite=on
sync_binlog=0
max_prepared_stmt_count=1048576
max_connections = 600
max_connect_errors = 1000000
max_allowed_packet = 32M
open_files_limit = 65535
skip_name_resolve = 1

首先載入資料，23G,10個表，每個表的資料是1000萬。

[root@mysql3 data]# ls -lhS mysql1/sbtest/
total 23G
-rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest10.ibd
-rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest1.ibd
-rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest2.ibd
-rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest3.ibd
-rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest4.ibd
-rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest5.ibd
-rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest6.ibd
-rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest7.ibd
-rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest8.ibd
-rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest9.ibd
-rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest10.frm
-rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest1.frm
-rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest2.frm
-rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest3.frm
-rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest4.frm
-rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest5.frm
-rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest6.frm
-rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest7.frm
-rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest8.frm
-rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest9.frm
-rw-r----- 1 mysql mysql 61 Jan 11 19:32 db.opt

二、測試1，首先做一個基準測試，慢慢往上加併發連線。10個表，1000萬資料，20個執行緒，60秒。tps是774

測試1
[root@mysql3 tmp]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=10000000 --tables=10 --threads=20 --time=60 --report-interval=30 run
sysbench 1.0.9 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 20
Report intermediate results every 30 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads
[ 30s ] thds: 20 tps: 667.42 qps: 13357.70 (r/w/o: 9351.99/2670.21/1335.50) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 20 tps: 880.89 qps: 17618.79 (r/w/o: 12332.59/3524.41/1761.79) lat (ms,95%): 40.37 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 650580
write: 185880
other: 92940
total: 929400
transactions: 46470 (774.21 per sec.)
queries: 929400 (15484.29 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0203s
total number of events: 46470
Latency (ms):
min: 7.81
avg: 25.82
max: 368.26
95th percentile: 52.89
sum: 1199957.04
Threads fairness:
events (avg/stddev): 2323.5000/12.11
execution time (avg/stddev): 59.9979/0.00

三、測試2

測試2 併發執行緒變為100個，tps值增加了到1577.
[root@mysql3 tmp]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=10000000 --tables=10 --threads=100 --time=120 --report-interval=30 run
sysbench 1.0.9 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 100
Report intermediate results every 30 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads
[ 30s ] thds: 100 tps: 1579.48 qps: 31645.01 (r/w/o: 22156.90/6325.84/3162.27) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 1509.51 qps: 30185.61 (r/w/o: 21131.66/6034.96/3018.98) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 1593.80 qps: 31871.44 (r/w/o: 22306.36/6377.47/3187.60) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 1625.77 qps: 32518.43 (r/w/o: 22764.28/6502.93/3251.22) lat (ms,95%): 114.72 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 2651026
write: 757436
other: 378718
total: 3787180
transactions: 189359 (1577.34 per sec.)
queries: 3787180 (31546.82 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0477s
total number of events: 189359
Latency (ms):
min: 7.24
avg: 63.38
max: 403.67
95th percentile: 118.92
sum: 12001058.69
Threads fairness:
events (avg/stddev): 1893.5900/17.58
execution time (avg/stddev): 120.0106/0.01
iostat的一次統計
avg-cpu: %user %nice %system %iowait %steal %idle
77.75 0.00 11.25 1.11 0.00 9.90
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 0.00 0.00 0.12 0.00 1.00 8.00 0.00 7.00 0.00 7.00 7.00 0.09
vdb 0.00 7379.75 505.75 938.50 31545.00 66056.00 67.58 1.93 1.34 0.84 1.60 0.42 60.99

四、測試3，執行緒達到256個，tps結果為1740，相比測試2的1577tps，上升不明顯。併發執行緒翻倍。再往上升是不是就是瓶頸了呢？

測試3 貼出測試結果
[root@mysql3 tmp]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=10000000 --tables=10 --threads=256 --time=120 --report-interval=30 run
SQL statistics:
queries performed:
read: 2926574
write: 836164
other: 418082
total: 4180820
transactions: 209041 (1740.35 per sec.)
queries: 4180820 (34807.06 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.1109s
total number of events: 209041

mysql壓力測試在京東雲ssd雲盤sysbench版本

相關文章