2021-04-20

sysbench mysql 基准测试

一直想知道mysql单机最大能支持多少tps和qps，网上说的都是要看具体情况。很少给出具体数字。所以用sysbench测试一下mysql，这个数应该是理论是的最高值

测试机器

1. 阿里云mysql rds 8cpu16G内存；
2. 测试服务器 阿里云服务器 4cpu 8g内存；

测试数据

1. 测试语句
   sysbench /include/oltp_legacy/oltp.lua --mysql-host=ip --mysql-port=3306 --mysql-user=root --mysql-password=pd
   –oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000
   –threads=100 --time=120 --report-interval=10
2. 测试方法
   threads 分别取 10 25 40 50 100 200 测试
3. 测试结果

---

Number of threads: 10
transactions: 54848 (457.01 per sec.)
queries: 1096960 (9140.17 per sec.)

---

Number of threads: 25
transactions: 165643 (1380.02 per sec.)
queries: 3312860 (27600.31 per sec.)

---

Number of threads: 40
transactions: 218315 (1818.70 per sec.)
queries: 4366317 (36374.22 per sec.)

---

Number of threads: 100
transactions: 292187 (2433.81 per sec.)
queries: 5843756 (48676.43 per sec.)

---

Number of threads: 200
transactions: 284251 (2367.36 per sec.)
queries: 5685068 (47347.65 per sec.)

---

4. 测试数据库cpu负载

4. 测试结果

阿里云一个8cpu16g内存数据库
tps能到2367 qps能到47347
在单机mysql的情况情况下，系统实现1000tqp应该没问题