一、环境:京东云Centos6.8 ,cpu16核,内存32G,SAS 3G,转速未知 ,SSD云盘300G
mysql 版本是:5.7.20 ,默认rpm安装,单实例。
压测工具是:sysbench1.0.9
mysql压力测试在京东云ssd云盘fio测试4K随机读写iops是4144.
首先加载数据,23G,10个表,每个表的数据是1000万。
二、测试1,首先做一个基准测试,慢慢往上加并发连接。10个表,1000万数据,20个线程,60秒。tps是774
三、测试2
四、测试3,线程达到256个,tps结果为1740,相比测试2的1577tps,上升不明显。并发线程翻倍。再往上升是不是就是瓶颈了呢?
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
- [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
- [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 并发线程变为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 贴出测试结果
- [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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30393770/viewspace-2150070/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30393770/viewspace-2150070/