mysql version: mysql5.6.35
os: CentOS Linux release 7.2.1511 (Core)
IP:10.159.45.100
CPU: 32 CPU
Memory: 128GB
Sysbench:Version: 1.1.0 (参数比起老版本,有些变化,需要留意)
sysbench基准测试
下载地址:
https://github.com/akopytov/sysbench
#依赖包安装:
yum -y install make automake libtool pkgconfig libaio-devel vim-common
sysbench /usr/xxxx/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.159.45.100 --mysql-port=4000 --mysql-db=test --mysql-user=test --mysql-password=testtest --tables=10 --table-size=5000000 --threads=10 prepare
#25线程
sysbench /usr/xxxx/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.159.45.100 --mysql-port=4000 --mysql-db=test --mysql-user=test --mysql-password=testtest --tables=10 --table-size=5000000 --threads=20 --report-interval=5 --time=500 run
#50线程
sysbench /usr/xxxx/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.159.45.100 --mysql-port=4000 --mysql-db=test --mysql-user=test --mysql-password=testtest --tables=10 --table-size=5000000 --threads=50 --report-interval=5 --time=500 run
#100线程
sysbench /usr/xxxx/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.159.45.100 --mysql-port=4000 --mysql-db=test --mysql-user=test --mysql-password=testtest --tables=10 --table-size=5000000 --threads=100 --report-interval=5 --time=500 run
#150线程
sysbench /usr/xxxx/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.159.45.100 --mysql-port=4000 --mysql-db=test --mysql-user=test --mysql-password=testtest --tables=10 --table-size=5000000 --threads=150 --report-interval=5 --time=500 run
#200线程
sysbench /usr/xxxx/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.159.45.100 --mysql-port=4000 --mysql-db=test --mysql-user=test --mysql-password=testtest --tables=10 --table-size=5000000 --threads=150 --report-interval=5 --time=500 run
#清除数据
sysbench /usr/xxxx/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.159.45.100 --mysql-port=4000 --mysql-db=test --mysql-user=test --mysql-password=testtest --tables=10 --table-size=5000000 --threads=10 cleanup
#mysql参数
[mysqld]
port=4000
max_connections=3000
max_allowed_packet=64M
max_heap_table_size=256M
net_buffer_length=8K
read_buffer_size=2M
read_rnd_buffer_size=512K
sort_buffer_size=2M
join_buffer_size=4M
thread_cache_size=512
tmp_table_size=128M
binlog-format=ROW
sync_binlog=0
key_buffer_size=32M
bulk_insert_buffer_size=16M
innodb_file_per_table
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=40G
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_fast_shutdown=1
innodb_max_dirty_pages_pct=75
innodb_lock_wait_timeout=120
innodb_rollback_on_timeout=1
innodb_flush_method=O_DIRECT
innodb_support_xa=OFF
transaction_isolation=READ-COMMITTED
测试结果,测试结果如下,感觉不尽如人意,我的期望是应该能达到6000左右的tps,结果没达到。
测试表 | 并发线程数 | qps | tps | 平均响应时间rt(毫秒) |
10表 | 20 | 34705 | 1735 | 12 |
50 | 59778 | 2999 | 17 | |
100 | 75377 | 3768 | 27 | |
125 | 66138 | 3306 | 38 | |
150 | 68122 | 3200 | 44 |