测试环境
2核4G 请求发起机器
2核4G 自建mysql
2核4G 阿里云mysql
过程
yum install sysbench
create database sbtest;
create user 'sbtest'@'%' identified by 'Yhu678YGF!';
grant all privileges on sbtest.* to sbtest;
flush privileges;
sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=100000 --mysql-table-engine=innodb --oltp-tables-count=100 --mysql-user=sbtest --mysql-password=Yhu678YGF! --mysql-port=3306 --mysql-host=10.1.62.42 --max-requests=0 --time=60 --report-interval=1 --threads=32 --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp_sum_ranges=0 --oltp_order_ranges=0 --oltp_distinct_ranges=0 --oltp-read-only=on --db-driver=mysql prepare
sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=100000 --mysql-table-engine=innodb --oltp-tables-count=100 --mysql-user=cchen --mysql-password=RDSmima0 --mysql-port=3306 --mysql-host=10.1.62.40 --max-requests=0 --time=60 --report-interval=1 --threads=32 --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp_sum_ranges=0 --oltp_order_ranges=0 --oltp_distinct_ranges=0 --oltp-read-only=on --db-driver=mysql prepare
sysbench --num-threads=32 --max-time=60 --max-requests=999999999 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=25000 --oltp-tables-count=100 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.1.62.42 --mysql-port=3306 --mysql-user=sbtest --mysql-password=Yhu678YGF! prepare
sysbench --num-threads=32 --max-time=60 --max-requests=999999999 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=25000 --oltp-tables-count=100 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.1.62.40 --mysql-port=3306 --mysql-user=cchen --mysql-password=RDSmima0 prepare
sysbench --num-threads=32 --max-time=60 --max-requests=999999999 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=25000 --oltp-tables-count=100 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=192.168.1.234 --mysql-port=3306 --mysql-user=sbtest --mysql-password=Yhu678YGF prepare
drop database sbtest;
用例 test 5.7 阿里云5.6
sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=100000 --mysql-table-engine=innodb --oltp-tables-count=100 --mysql-user=cchen --mysql-password=RDSmima0 --mysql-port=3306 --mysql-host=10.1.62.40 --max-requests=0 --time=60 --report-interval=1 --threads=32 --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp_sum_ranges=0 --oltp_order_ranges=0 --oltp_distinct_ranges=0 --oltp-read-only=on --db-driver=mysql SQL statistics: SQL statistics: SQL statistics:
queries performed: queries performed: queries performed:
read: 1140503 read: 1010268 read: 649935
write: 0 write: 0 write: 0
other: 2281006 other: 2020536 other: 1299870
total: 3421509 total: 3030804 total: 1949805
transactions: 1140503 (19004.41 per sec.) transactions: 1010268 (16834.98 per sec.) transactions: 649935 (10827.58 per sec.)
queries: 3421509 (57013.22 per sec.) queries: 3030804 (50504.93 per sec.) queries: 1949805 (32482.74 per sec.)
ignored errors: 0 (0.00 per sec.) ignored errors: 0 (0.00 per sec.) ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: General statistics: General statistics:
total time: 60.0111s total time: 60.0080s total time: 60.0238s
total number of events: 1140503 total number of events: 1010268 total number of events: 649935
Latency (ms): Latency (ms): Latency (ms):
min: 0.28 min: 0.29 min: 1.12
avg: 1.68 avg: 1.90 avg: 2.95
max: 825.43 max: 248.96 max: 43.85
95th percentile: 2.61 95th percentile: 2.66 95th percentile: 22.28
sum: 1917994.59 sum: 1918593.51 sum: 1919495.86
Threads fairness: Threads fairness: Threads fairness:
events (avg/stddev): 35640.7188/305.11 events (avg/stddev): 31570.8750/784.75 events (avg/stddev): 20310.4688/850.73
execution time (avg/stddev): 59.9373/0.02 execution time (avg/stddev): 59.9560/0.00 execution time (avg/stddev): 59.9842/0.00
sysbench --num-threads=32 --max-time=60 --max-requests=999999999 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=100000 --oltp-tables-count=64 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.1.62.42 --mysql-port=3306 --mysql-user=sbtest --mysql-password=Yhu678YGF!
SQL statistics: SQL statistics:
queries performed: queries performed:
read: 146314 read: 333620
write: 41804 write: 95320
other: 20902 other: 47660
total: 209020 total: 476600
transactions: 10451 (172.33 per sec.) transactions: 23830 (396.81 per sec.)
queries: 209020 (3446.69 per sec.) queries: 476600 (7936.22 per sec.)
ignored errors: 0 (0.00 per sec.) ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: General statistics:
total time: 60.6416s total time: 60.0515s
total number of events: 10451 total number of events: 23830
Latency (ms): Latency (ms):
min: 5.21 min: 11.46
avg: 184.67 avg: 80.63
max: 1133.32 max: 312.74
95th percentile: 559.50 95th percentile: 101.13
sum: 1929935.18 sum: 1921355.72
Threads fairness: Threads fairness:
events (avg/stddev): 326.5938/10.38 events (avg/stddev): 744.6875/31.44
execution time (avg/stddev): 60.3105/0.15 execution time (avg/stddev): 60.0424/0.00
sysbench --num-threads=32 --max-time=60 --max-requests=999999999 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=10000 --oltp-tables-count=64 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.1.62.42 --mysql-port=3306 --mysql-user=sbtest --mysql-password=Yhu678YGF!
SQL statistics: SQL statistics:
queries performed: queries performed:
read: 706216 read: 354438
write: 201676 write: 101268
other: 100853 other: 50634
total: 1008745 total: 506340
transactions: 50409 (839.75 per sec.) transactions: 25317 (421.54 per sec.)
queries: 1008745 (16804.40 per sec.) queries: 506340 (8430.89 per sec.)
ignored errors: 35 (0.58 per sec.) ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: General statistics:
total time: 60.0264s total time: 60.0554s
total number of events: 50409 total number of events: 25317
Latency (ms): Latency (ms):
min: 4.92 min: 12.09
avg: 38.10 avg: 75.86
max: 286.64 max: 198.96
95th percentile: 89.16 95th percentile: 99.33
sum: 1920498.60 sum: 1920553.94
Threads fairness: Threads fairness:
events (avg/stddev): 1575.2812/21.68 events (avg/stddev): 791.1562/25.33
execution time (avg/stddev): 60.0156/0.00 execution time (avg/stddev): 60.0173/0.02
sysbench --num-threads=1000 --max-time=60 --max-requests=999999999 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=10000 --oltp-tables-count=64 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.1.62.42 --mysql-port=3306 --mysql-user=sbtest --mysql-password=Yhu678YGF! prepare
SQL statistics: SQL statistics:
queries performed: queries performed:
read: 695548 read: 252070
write: 195898 write: 71990
other: 98415 other: 35998
total: 989861 total: 360058
transactions: 48733 (802.66 per sec.) transactions: 17993 (286.75 per sec.)
queries: 989861 (16303.54 per sec.) queries: 360058 (5738.08 per sec.)
ignored errors: 949 (15.63 per sec.) ignored errors: 12 (0.19 per sec.)
reconnects: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: General statistics:
total time: 60.7121s total time: 62.7464s
total number of events: 48733 total number of events: 17993
Latency (ms): Latency (ms):
min: 5.92 min: 46.85
avg: 1240.61 avg: 3409.72
max: 14095.18 max: 24131.98
95th percentile: 1803.47 95th percentile: 9977.52
sum: 60458783.50 sum: 61351137.21
Threads fairness: Threads fairness:
events (avg/stddev): 48.7330/3.45 events (avg/stddev): 17.9930/2.74
execution time (avg/stddev): 60.4588/0.20 execution time (avg/stddev): 61.3511/0.77
sysbench --num-threads=32 --max-time=60 --max-requests=999999999 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=50000 --oltp-tables-count=100 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.1.62.42 --mysql-port=3306 --mysql-user=sbtest --mysql-password=Yhu678YGF!
SQL statistics: SQL statistics:
queries performed: queries performed:
read: 135380 read: 337148
write: 38680 write: 96328
other: 19340 other: 48164
total: 193400 total: 481640
transactions: 9670 (160.82 per sec.) transactions: 24082 (400.71 per sec.)
queries: 193400 (3216.33 per sec.) queries: 481640 (8014.20 per sec.)
ignored errors: 0 (0.00 per sec.) ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: General statistics:
total time: 60.1285s total time: 60.0961s
total number of events: 9670 total number of events: 24082
Latency (ms): Latency (ms):
min: 4.98 min: 11.90
avg: 198.70 avg: 79.79
max: 1812.13 max: 246.64
95th percentile: 646.19 95th percentile: 101.13
sum: 1921423.34 sum: 1921546.54
Threads fairness: Threads fairness:
events (avg/stddev): 302.1875/9.65 events (avg/stddev): 752.5625/25.20
execution time (avg/stddev): 60.0445/0.02 execution time (avg/stddev): 60.0483/0.02
sysbench --num-threads=32 --max-time=60 --max-requests=999999999 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=25000 --oltp-tables-count=100 --db-driver=mysql --mysql-table-engine=innodb --mysql-host=10.1.62.42 --mysql-port=3306 --mysql-user=sbtest --mysql-password=Yhu678YGF!
SQL statistics: SQL statistics:
queries performed: queries performed:
read: 282996 read: 337918
write: 80847 write: 96548
other: 40425 other: 48274
total: 404268 total: 482740
transactions: 20211 (336.03 per sec.) transactions: 24137 (401.88 per sec.)
queries: 404268 (6721.33 per sec.) queries: 482740 (8037.53 per sec.)
ignored errors: 3 (0.05 per sec.) ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: General statistics:
total time: 60.1449s total time: 60.0585s
total number of events: 20211 total number of events: 24137
Latency (ms): Latency (ms):
min: 5.26 min: 12.91
avg: 95.07 avg: 79.58
max: 852.45 max: 194.49
95th percentile: 253.35 95th percentile: 101.13
sum: 1921401.40 sum: 1920740.91
Threads fairness: Threads fairness:
events (avg/stddev): 631.5938/14.00 events (avg/stddev): 754.2812/28.80
execution time (avg/stddev): 60.0438/0.05 execution time (avg/stddev): 60.0232/0.02
结论
2万5以下数据量自建有性能优势,阿里云表现平稳,随数据量变化波动较小
2万5以上考虑用阿里云数据库
