1.主机配置
| 主机名 | 机器物理配置 | 作用 |
|---|---|---|
| ops-test.corp.cootek.com | 4c16g | 远端sysbench执行机器 |
| mirror2204.corp.cootek.com | 2c8g | Proxy机器 |
| hs-mysql-elk01.corp.cootek.com | 4c32g | mysql主库 |
| hs-mysql-elk03.corp.cootek.com | 4c32g | mysql从库 |
数据库初始化按照2c8g进行配置，并行复制（4个同步线程），
数据库参数innodb_flush_log_at_trx_commit=2，sync_binlog=1000
2.混合读写
2.1连接Proxy压测
压测命令，混合读写测试
sysbench oltp_read_write --mysql-host=172.30.0.2 --mysql-port=6033 --mysql-user=sysbench --mysql-password=sysbenchpwd --mysql-db=sysbench --tables=30 --table-size=1000000 --threads=16 --time=600 --report-interval=10 run
压测结果
[ 540s ] thds: 16 tps: 898.71 qps: 17976.88 (r/w/o: 12584.33/3594.84/1797.72) lat (ms,95%): 25.74 err/s: 0.00 reconn/s: 0.00
[ 550s ] thds: 16 tps: 895.70 qps: 17916.41 (r/w/o: 12542.21/3582.90/1791.30) lat (ms,95%): 25.74 err/s: 0.00 reconn/s: 0.00
[ 560s ] thds: 16 tps: 889.79 qps: 17790.89 (r/w/o: 12451.73/3559.58/1779.59) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 570s ] thds: 16 tps: 892.39 qps: 17855.47 (r/w/o: 12500.34/3570.35/1784.78) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 580s ] thds: 16 tps: 884.80 qps: 17690.39 (r/w/o: 12382.49/3538.40/1769.50) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 590s ] thds: 16 tps: 903.10 qps: 18057.36 (r/w/o: 12639.67/3611.59/1806.10) lat (ms,95%): 25.28 err/s: 0.00 reconn/s: 0.00
[ 600s ] thds: 16 tps: 909.31 qps: 18188.01 (r/w/o: 12731.78/3637.32/1818.91) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 7488054
write: 2139444
other: 1069722
total: 10697220
transactions: 534861 (891.37 per sec.)
queries: 10697220 (17827.37 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0431s
total number of events: 534861
Latency (ms):
min: 7.48
avg: 17.95
max: 94.42
95th percentile: 26.20
sum: 9599491.76
Threads fairness:
events (avg/stddev): 33428.8125/720.31
execution time (avg/stddev): 599.9682/0.01
从压测结束上看压测600秒，TPS：891，QPS：17827，95th：26.20
监控截图
2.2直连mysql压测
压测命令，混合读写测试
sysbench oltp_read_write --mysql-host=hs-mysql-elk01.corp.cootek.com --mysql-port=3318 --mysql-user=sysbench --mysql-password=sysbenchpwd --mysql-db=sysbench --tables=30 --table-size=1000000 --threads=16 --time=300 --report-interval=10 run
压测结果
[ 160s ] thds: 16 tps: 906.40 qps: 18130.37 (r/w/o: 12692.18/3625.39/1812.80) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 16 tps: 907.11 qps: 18140.27 (r/w/o: 12698.02/3628.03/1814.22) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 16 tps: 901.30 qps: 18025.85 (r/w/o: 12618.46/3604.79/1802.59) lat (ms,95%): 25.28 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 16 tps: 906.50 qps: 18132.87 (r/w/o: 12693.48/3626.39/1813.00) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 16 tps: 899.60 qps: 17988.88 (r/w/o: 12591.18/3598.50/1799.20) lat (ms,95%): 25.28 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 16 tps: 887.00 qps: 17736.33 (r/w/o: 12414.92/3547.51/1773.90) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 16 tps: 899.90 qps: 18001.53 (r/w/o: 12601.72/3599.91/1799.90) lat (ms,95%): 25.28 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 16 tps: 910.80 qps: 18217.71 (r/w/o: 12752.13/3643.98/1821.59) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 16 tps: 900.50 qps: 18011.53 (r/w/o: 12608.62/3601.91/1801.00) lat (ms,95%): 25.28 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 16 tps: 909.39 qps: 18184.73 (r/w/o: 12728.88/3637.27/1818.58) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 16 tps: 908.61 qps: 18174.45 (r/w/o: 12722.21/3634.83/1817.42) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 16 tps: 897.29 qps: 17943.38 (r/w/o: 12560.21/3588.58/1794.59) lat (ms,95%): 25.74 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 16 tps: 906.50 qps: 18125.24 (r/w/o: 12686.46/3625.99/1812.79) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 16 tps: 912.41 qps: 18254.92 (r/w/o: 12779.68/3650.22/1825.01) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 16 tps: 907.60 qps: 18152.74 (r/w/o: 12707.46/3630.09/1815.19) lat (ms,95%): 25.28 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 3796702
write: 1084772
other: 542386
total: 5423860
transactions: 271193 (903.91 per sec.)
queries: 5423860 (18078.26 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0193s
total number of events: 271193
Latency (ms):
min: 6.06
avg: 17.70
max: 84.39
95th percentile: 25.28
sum: 4799686.86
Threads fairness:
events (avg/stddev): 16949.5625/1536.18
execution time (avg/stddev): 299.9804/0.01
从压测结束上看压测300秒，TPS：903，QPS：18078，95th：25.28
2.3小结
| TPS | QPS | 95TH | |
|---|---|---|---|
| 连接Proxy | 891 | 17827 | 26.20 |
| 直连mysql | 903 | 18078 | 25.28 |
| 损耗 | 1.3289% | 1.3884 | 3.63924% |
从监控上看连接Proxy的情况17827的QPS，会将Proxy的机器（2c8g）cpu和内存打满
3.只读
3.1连接Proxy压测
压测命令，只读测试
sysbench oltp_read_only --mysql-host=172.30.0.2 --mysql-port=6033 --mysql-user=sysbench --mysql-password=sysbenchpwd --mysql-db=sysbench --tables=30 --table-size=1000000 --threads=16 --time=600 --report-interval=10 run
压测结果
[ 460s ] thds: 16 tps: 995.41 qps: 15927.51 (r/w/o: 13936.69/0.00/1990.81) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 470s ] thds: 16 tps: 980.59 qps: 15691.66 (r/w/o: 13730.48/0.00/1961.18) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00
[ 480s ] thds: 16 tps: 985.51 qps: 15767.24 (r/w/o: 13796.23/0.00/1971.02) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 490s ] thds: 16 tps: 992.81 qps: 15886.99 (r/w/o: 13901.38/0.00/1985.61) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 500s ] thds: 16 tps: 985.79 qps: 15768.44 (r/w/o: 13796.96/0.00/1971.48) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00
[ 510s ] thds: 16 tps: 994.90 qps: 15918.89 (r/w/o: 13929.19/0.00/1989.70) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 520s ] thds: 16 tps: 994.90 qps: 15920.43 (r/w/o: 13930.42/0.00/1990.00) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 530s ] thds: 16 tps: 981.50 qps: 15701.63 (r/w/o: 13738.73/0.00/1962.90) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 540s ] thds: 16 tps: 977.50 qps: 15640.93 (r/w/o: 13685.93/0.00/1954.99) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 550s ] thds: 16 tps: 989.70 qps: 15834.65 (r/w/o: 13855.25/0.00/1979.41) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 560s ] thds: 16 tps: 978.80 qps: 15664.23 (r/w/o: 13706.52/0.00/1957.70) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00
[ 570s ] thds: 16 tps: 988.20 qps: 15810.42 (r/w/o: 13834.12/0.00/1976.30) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 580s ] thds: 16 tps: 1001.29 qps: 16017.12 (r/w/o: 14014.63/0.00/2002.49) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 590s ] thds: 16 tps: 970.30 qps: 15527.64 (r/w/o: 13586.93/0.00/1940.70) lat (ms,95%): 29.19 err/s: 0.00 reconn/s: 0.00
[ 600s ] thds: 16 tps: 964.10 qps: 15429.07 (r/w/o: 13500.86/0.00/1928.21) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 8311198
write: 0
other: 1187314
total: 9498512
transactions: 593657 (989.40 per sec.)
queries: 9498512 (15830.35 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0173s
total number of events: 593657
Latency (ms):
min: 6.71
avg: 16.17
max: 121.46
95th percentile: 27.66
sum: 9599235.59
Threads fairness:
events (avg/stddev): 37103.5625/2427.22
execution time (avg/stddev): 599.9522/0.01
从压测结束上看压测600秒，TPS：989，QPS：15830，95th：27.66
3.2直连mysql压测
压测命令，只读测试
sysbench oltp_read_only --mysql-host=hs-mysql-elk01.corp.cootek.com --mysql-port=3318 --mysql-user=sysbench --mysql-password=sysbenchpwd --mysql-db=sysbench --tables=30 --table-size=1000000 --threads=16 --time=300 --report-interval=10 run
压测结果
[ 190s ] thds: 16 tps: 1128.69 qps: 18058.59 (r/w/o: 15801.21/0.00/2257.39) lat (ms,95%): 21.50 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 16 tps: 1139.41 qps: 18228.53 (r/w/o: 15949.71/0.00/2278.82) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 16 tps: 1150.10 qps: 18404.71 (r/w/o: 16104.61/0.00/2300.10) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 16 tps: 1141.00 qps: 18254.93 (r/w/o: 15972.92/0.00/2282.00) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 16 tps: 1149.89 qps: 18398.87 (r/w/o: 16098.89/0.00/2299.98) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 16 tps: 1148.90 qps: 18381.69 (r/w/o: 16083.99/0.00/2297.70) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 16 tps: 1126.61 qps: 18026.87 (r/w/o: 15773.65/0.00/2253.22) lat (ms,95%): 21.89 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 16 tps: 1144.40 qps: 18310.97 (r/w/o: 16022.18/0.00/2288.80) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 16 tps: 1148.49 qps: 18376.31 (r/w/o: 16079.22/0.00/2297.09) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 16 tps: 1143.89 qps: 18299.01 (r/w/o: 16011.32/0.00/2287.69) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 16 tps: 1130.41 qps: 18085.92 (r/w/o: 15825.10/0.00/2260.81) lat (ms,95%): 21.50 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 16 tps: 1145.89 qps: 18330.88 (r/w/o: 16039.40/0.00/2291.49) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 4788140
write: 0
other: 684020
total: 5472160
transactions: 342010 (1139.95 per sec.)
queries: 5472160 (18239.22 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0198s
total number of events: 342010
Latency (ms):
min: 3.86
avg: 14.03
max: 88.24
95th percentile: 21.11
sum: 4799618.77
Threads fairness:
events (avg/stddev): 21375.6250/2855.23
execution time (avg/stddev): 299.9762/0.01
从压测结束上看压测300秒，TPS：1139，QPS：18239，95th：21.11
3.3小结
| TPS | QPS | 95TH | |
|---|---|---|---|
| 连接Proxy | 989 | 15830 | 27.66 |
| 直连mysql | 1139 | 18239 | 21.11 |
| 损耗 | 13.16% | 13.20% | 23.68% |
只读场景损耗过高，考虑在提供一个只读端口
4.批量写
暂时未测
5.只写
暂时未测
6.条件主键更新非主键
暂时未测
7.索引范围查询
暂时未测
8.索引随机查询
暂时未测
