mysql 8 数据库基准测试

  • 目标： 对mysql8 执行读写分离测试场景进行基准测试，写入的表未带索引
  • 被服务器配置： 2台 4c4g mysql 服务器
  • 测试机器配置： 2c2g
  • 测试工具：sysbench

压测前数据库内存情况

1
2
3
4
[opsuser@hdvs-infra-dccloudmysql-01 ~]$ free -m
              total        used        free      shared  buff/cache   available
Mem:           3770         972         670          51        2127        2498
Swap:             0           0           0

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
[opsuser@hdvs-infra-zzktest-01 ~]$ sysbench /home/opsuser/sysbench/src/lua/oltp_insert.lua \
>  --db-driver=mysql \
>  --mysql-host=10.126.109.149 \
>  --mysql-port=3306 \
>  --mysql-user=root \
>  --mysql-password=******** \
>  --mysql-db=sysbenchdb \
>  --tables=10 \
>  --table-size=100000 \
>  prepare
sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 100000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 100000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 100000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 100000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 100000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 100000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 100000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 100000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 100000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'...

执行基准测试

仅插入测试

执行压测

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
[ 5s ] thds: 100 tps: 10909.22 qps: 10909.22 (r/w/o: 0.00/10909.22/0.00) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 100 tps: 11646.24 qps: 11646.24 (r/w/o: 0.00/11646.24/0.00) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 100 tps: 11584.97 qps: 11584.97 (r/w/o: 0.00/11584.97/0.00) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 12266.55 qps: 12266.55 (r/w/o: 0.00/12266.55/0.00) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 100 tps: 11514.56 qps: 11514.56 (r/w/o: 0.00/11514.56/0.00) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 11078.29 qps: 11078.29 (r/w/o: 0.00/11078.29/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 100 tps: 10610.29 qps: 10610.29 (r/w/o: 0.00/10610.29/0.00) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 10511.07 qps: 10511.07 (r/w/o: 0.00/10511.07/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 100 tps: 11210.59 qps: 11210.59 (r/w/o: 0.00/11210.59/0.00) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 10472.56 qps: 10472.56 (r/w/o: 0.00/10472.56/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 100 tps: 11108.16 qps: 11108.16 (r/w/o: 0.00/11108.16/0.00) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 10485.01 qps: 10485.01 (r/w/o: 0.00/10485.01/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           667114
        other:                           0
        total:                           667114
    transactions:                        667114 (11114.01 per sec.)
    queries:                             667114 (11114.01 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      11114.0082
    time elapsed:                        60.0246s
    total number of events:              667114

Latency (ms):
         min:                                    2.30
         avg:                                    8.99
         max:                                  130.46
         95th percentile:                       16.41
         sum:                              5999496.75

Threads fairness:
    events (avg/stddev):           6671.1400/27.37
    execution time (avg/stddev):   59.9950/0.00

数据库内存使用

1
2
3
4
[opsuser@hdvs-infra-dccloudmysql-01 ~]$ free -m
              total        used        free      shared  buff/cache   available
Mem:           3770         971         172          51        2626        2494
Swap:             0           0           0

写入报告

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
+---------------------------------+-------------------+-----------------------------------------------------------+
|             查询执行情况          |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 读操作查询数 (read)             | 0                 | 表示执行的读取操作查询的次数                                |
| 写操作查询数 (write)            | 667114            | 表示执行的写入操作查询的次数                                |
| 其他操作查询数 (other)          | 0                 | 表示除读取和写入之外的其他操作查询的次数                   |
| 总查询数 (total)                | 667114            | 表示所有查询（包括读取、写入和其他操作）的总次数          |
+---------------------------------+-------------------+-----------------------------------------------------------+

+---------------------------------+-------------------+-----------------------------------------------------------+
|             事务情况             |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 事务数量 (transactions)         | 667114            | 表示执行的事务数量                                         |
| 平均每秒事务数 (transactions/s) | 11114.01          | 表示平均每秒执行的事务数量                                |
| 平均每秒查询数 (queries/s)      | 11114.01          | 表示平均每秒执行的查询数量                                 |
+---------------------------------+-------------------+-----------------------------------------------------------+

+---------------------------------+-------------------+-----------------------------------------------------------+
|             错误和重连           |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 忽略的错误数 (ignored errors)   | 0                 | 表示被忽略的错误数量                                       |
| 重连次数 (reconnects)          | 0                 | 表示发生的重新连接次数                                     |
+---------------------------------+-------------------+-----------------------------------------------------------+

+---------------------------------+-------------------+-----------------------------------------------------------+
|             吞吐量               |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 每秒事件数（EPS） (events/s)   | 11114.0082        | 表示每秒执行的事件数量                                     |
| 经过时间 (time elapsed)        | 60.0246秒         | 表示经过的总时间                                           |
| 总事件数 (total number of events) | 667114          | 表示总共发生的事件数量                                     |
+---------------------------------+-------------------+-----------------------------------------------------------+

+---------------------------------+-------------------+-----------------------------------------------------------+
|             延迟情况             |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 最小延迟 (min)                  | 2.30毫秒          | 表示最小延迟时间                                           |
| 平均延迟 (avg)                  | 8.99毫秒          | 表示平均延迟时间                                           |
| 最大延迟 (max)                  | 130.46毫秒        | 表示最大延迟时间                                           |
| 95th百分位延迟 (95th percentile) | 16.41毫秒       | 表示95th百分位的延迟时间                                  |
| 总延迟时间 (sum)               | 5999496.75毫秒   | 表示总延迟时间                                             |
+---------------------------------+-------------------+-----------------------------------------------------------+

+---------------------------------+-------------------+-----------------------------------------------------------+
|             线程公平性           |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 事件数（平均/标准差）          | 6671.1400/27.37   | 表示事件数量的平均值和标准差                               |
| 执行时间（平均/标准差）        | 59.9950/0.00      | 表示执行时间的平均值和标准差                               |
+---------------------------------+-------------------+-----------------------------------------------------------+

只读测试

执行前内存

1
2
3
4
[opsuser@hdvs-infra-dccloudmysql-01 ~]$ free -m
              total        used        free      shared  buff/cache   available
Mem:           3770         972         171          51        2626        2492
Swap:             0           0           0

执行测试

1
2
3
4
5
6
7
8
9
10
11
12
13
sysbench /home/opsuser/sysbench/src/lua/oltp_read_only.lua \
 --db-driver=mysql \
 --mysql-host=10.126.109.150 \
 --mysql-port=3306 \
 --mysql-user=root \
 --mysql-password=********* \
 --mysql-db=sysbenchdb \
 --tables=10 \
 --table-size=100000 \
 --report-interval=5 \
 --threads=100 \
 --time=60 \
 run

执行压测

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
[ 5s ] thds: 100 tps: 2494.35 qps: 40090.45 (r/w/o: 35082.16/0.00/5008.28) lat (ms,95%): 130.13 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 100 tps: 2685.57 qps: 42984.97 (r/w/o: 37614.22/0.00/5370.75) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 100 tps: 2722.84 qps: 43501.81 (r/w/o: 38055.73/0.00/5446.08) lat (ms,95%): 137.35 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 2825.18 qps: 45241.60 (r/w/o: 39591.25/0.00/5650.35) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 100 tps: 2876.59 qps: 46035.39 (r/w/o: 40282.82/0.00/5752.57) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 2831.84 qps: 45332.50 (r/w/o: 39668.01/0.00/5664.49) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 100 tps: 2792.80 qps: 44632.62 (r/w/o: 39048.22/0.00/5584.40) lat (ms,95%): 118.92 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 2773.76 qps: 44394.33 (r/w/o: 38845.81/0.00/5548.52) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 100 tps: 2761.56 qps: 44199.95 (r/w/o: 38677.03/0.00/5522.92) lat (ms,95%): 130.13 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 2759.79 qps: 44156.90 (r/w/o: 38637.12/0.00/5519.79) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 100 tps: 2733.83 qps: 43751.55 (r/w/o: 38283.68/0.00/5467.87) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 2820.18 qps: 45080.89 (r/w/o: 39441.73/0.00/5639.16) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            2316958
        write:                           0
        other:                           330994
        total:                           2647952
    transactions:                        165497 (2754.21 per sec.)
    queries:                             2647952 (44067.36 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      2754.2097
    time elapsed:                        60.0887s
    total number of events:              165497

Latency (ms):
         min:                                    3.00
         avg:                                   36.27
         max:                                 1138.98
         95th percentile:                      125.52
         sum:                              6002318.93

Threads fairness:
    events (avg/stddev):           1654.9700/273.52
    execution time (avg/stddev):   60.0232/0.02

结论

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
+---------------------------------+-------------------+-----------------------------------------------------------+
|             查询执行情况          |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 读操作查询数 (read)             | 2316958           | 表示执行的读取操作查询的次数                                |
| 写操作查询数 (write)            | 0                 | 表示执行的写入操作查询的次数                                |
| 其他操作查询数 (other)          | 330994            | 表示除读取和写入之外的其他操作查询的次数                   |
| 总查询数 (total)                | 2647952           | 表示所有查询（包括读取、写入和其他操作）的总次数          |
+---------------------------------+-------------------+-----------------------------------------------------------+

+---------------------------------+-------------------+-----------------------------------------------------------+
|             事务情况             |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 事务数量 (transactions)         | 165497            | 表示执行的事务数量                                         |
| 平均每秒事务数 (transactions/s) | 2754.21           | 表示平均每秒执行的事务数量                                |
| 平均每秒查询数 (queries/s)      | 44067.36          | 表示平均每秒执行的查询数量                                 |
+---------------------------------+-------------------+-----------------------------------------------------------+

+---------------------------------+-------------------+-----------------------------------------------------------+
|             错误和重连           |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 忽略的错误数 (ignored errors)   | 0                 | 表示被忽略的错误数量                                       |
| 重连次数 (reconnects)          | 0                 | 表示发生的重新连接次数                                     |
+---------------------------------+-------------------+-----------------------------------------------------------+

+---------------------------------+-------------------+-----------------------------------------------------------+
|             吞吐量               |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 每秒事件数（EPS） (events/s)   | 2754.2097         | 表示每秒执行的事件数量                                     |
| 经过时间 (time elapsed)        | 60.0887秒         | 表示经过的总时间                                           |
| 总事件数 (total number of events) | 165497          | 表示总共发生的事件数量                                     |
+---------------------------------+-------------------+-----------------------------------------------------------+

+---------------------------------+-------------------+-----------------------------------------------------------+
|             延迟情况             |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 最小延迟 (min)                  | 3.00毫秒          | 表示最小延迟时间                                           |
| 平均延迟 (avg)                  | 36.27毫秒         | 表示平均延迟时间                                           |
| 最大延迟 (max)                  | 1138.98毫秒       | 表示最大延迟时间                                           |
| 95th百分位延迟 (95th percentile) | 125.52毫秒      | 表示95th百分位的延迟时间                                  |
| 总延迟时间 (sum)               | 6002318.93毫秒    | 表示总延迟时间                                             |
+---------------------------------+-------------------+-----------------------------------------------------------+

+---------------------------------+-------------------+-----------------------------------------------------------+
|             线程公平性           |                   |                                                           |
+---------------------------------+-------------------+-----------------------------------------------------------+
| 事件数（平均/标准差）          | 1654.9700/273.52 | 表示事件数量的平均值和标准差                               |
| 执行时间（平均/标准差）        | 60.0232/0.02      | 表示执行时间的平均值和标准差                               |
+---------------------------------+-------------------+-----------------------------------------------------------+

清理压测数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[opsuser@hdvs-infra-zzktest-01 ~]$ sysbench /home/opsuser/sysbench/src/lua/oltp_insert.lua \
>  --db-driver=mysql \
>  --mysql-host=10.126.109.149 \
>  --mysql-port=3306 \
>  --mysql-user=root \
>  --mysql-password=McdPasHvi*7Bj+ \
>  --mysql-db=sysbenchdb \
>  --tables=10 \
>  cleanup
sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...