阿里云自建mysql与阿里云mysql服务性能测试

测试环境

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以上考虑用阿里云数据库

你可能感兴趣的