MySQL 8.0.20性能到底有多牛
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: MySQL 8.0.20性能到底有多牛
MySQL 8.0.20发布已经有几周了，最近太累，实在不想动了。借二宝/三宝不在家之际，昨晚用公司研发环境测试了一下，感觉性能确实有很大的提升；不废话，直接上数据。为了便于对比，为分别测试了MySQL 8.0.19 MGR 三节点和MySQL 8.0.20 MGR三节点的情况。
8.0.19的数据如下：
|
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 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
[root@td2 lua]# /opt/sysbench-master/src/sysbench oltp_read_write.lua --mysql-host=192.168.101.41 --mysql-port=3306 --mysql-user=root --mysql-db=enmotech --mysql-password=enmotech --table_size=100000 --tables=10 --threads=16 --report-interval=10 --time=300 run sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3) Running the test with following options: Number of threads: 16 Report intermediate results every 10 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 10s ] thds: 16 tps: 1164.17 qps: 23298.56 (r/w/o: 16311.12/4657.49/2329.95) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 16 tps: 1164.51 qps: 23295.03 (r/w/o: 16306.66/4659.35/2329.02) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 16 tps: 1154.28 qps: 23087.51 (r/w/o: 16161.92/4617.02/2308.56) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 16 tps: 1124.87 qps: 22495.91 (r/w/o: 15746.41/4499.96/2249.53) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 16 tps: 1154.65 qps: 23090.30 (r/w/o: 16164.27/4616.62/2309.41) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 16 tps: 1154.79 qps: 23090.44 (r/w/o: 16161.62/4619.25/2309.57) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 16 tps: 1127.40 qps: 22557.33 (r/w/o: 15791.12/4511.31/2254.90) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 16 tps: 1105.80 qps: 22112.76 (r/w/o: 15478.24/4422.91/2211.61) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 16 tps: 1116.41 qps: 22332.98 (r/w/o: 15634.82/4465.34/2232.82) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 16 tps: 1089.80 qps: 21791.20 (r/w/o: 15253.00/4358.60/2179.60) lat (ms,95%): 21.50 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 16 tps: 1080.28 qps: 21599.27 (r/w/o: 15117.87/4320.83/2160.57) lat (ms,95%): 21.50 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 16 tps: 1072.62 qps: 21461.79 (r/w/o: 15024.24/4292.30/2145.25) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00 [ 130s ] thds: 16 tps: 1136.58 qps: 22726.32 (r/w/o: 15907.77/4545.40/2273.15) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00 [ 140s ] thds: 16 tps: 1112.42 qps: 22251.80 (r/w/o: 15576.58/4450.68/2224.54) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00 [ 150s ] thds: 16 tps: 1122.95 qps: 22461.20 (r/w/o: 15723.17/4491.82/2246.21) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00 [ 160s ] thds: 16 tps: 1123.24 qps: 22463.43 (r/w/o: 15724.88/4492.07/2246.48) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00 [ 170s ] thds: 16 tps: 1145.91 qps: 22919.33 (r/w/o: 16043.46/4584.05/2291.82) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00 [ 180s ] thds: 16 tps: 1141.69 qps: 22830.28 (r/w/o: 15981.12/4565.88/2283.29) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00 [ 190s ] thds: 16 tps: 1142.89 qps: 22855.17 (r/w/o: 15998.51/4570.77/2285.89) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00 [ 200s ] thds: 16 tps: 1146.21 qps: 22925.11 (r/w/o: 16047.18/4585.62/2292.31) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00 [ 210s ] thds: 16 tps: 1136.59 qps: 22730.48 (r/w/o: 15910.31/4546.88/2273.29) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00 [ 220s ] thds: 16 tps: 1116.60 qps: 22341.49 (r/w/o: 15641.69/4466.60/2233.20) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00 [ 230s ] thds: 16 tps: 1115.00 qps: 22298.16 (r/w/o: 15607.34/4460.81/2230.01) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00 [ 240s ] thds: 16 tps: 1136.78 qps: 22731.93 (r/w/o: 15912.27/4546.41/2273.25) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00 [ 250s ] thds: 16 tps: 1146.62 qps: 22933.84 (r/w/o: 16053.91/4586.39/2293.54) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00 [ 260s ] thds: 16 tps: 1123.11 qps: 22458.17 (r/w/o: 15720.92/4491.03/2246.22) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00 [ 270s ] thds: 16 tps: 1092.91 qps: 21857.81 (r/w/o: 15300.05/4372.04/2185.72) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00 [ 280s ] thds: 16 tps: 1103.70 qps: 22075.45 (r/w/o: 15453.54/4414.61/2207.31) lat (ms,95%): 21.50 err/s: 0.00 reconn/s: 0.00 [ 290s ] thds: 16 tps: 1120.86 qps: 22424.40 (r/w/o: 15696.84/4485.64/2241.92) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00 [ 300s ] thds: 16 tps: 1129.66 qps: 22591.38 (r/w/o: 15814.59/4517.56/2259.23) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 4732728 write: 1352208 other: 676104 total: 6761040 transactions: 338052 (1126.74 per sec.) queries: 6761040 (22534.72 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 1126.7361 time elapsed: 300.0277s total number of events: 338052 Latency (ms): min: 5.17 avg: 14.20 max: 202.20 95th percentile: 20.00 sum: 4798744.41 Threads fairness: events (avg/stddev): 21128.2500/755.31 execution time (avg/stddev): 299.9215/0.01 |
行这里为其实测试了多次，性能都差不多；总的来讲8.0.19 的tps在1126左右，qps在22000左右。注意我这里其实并没有把资源压满（因为研发有测试环境在run)；如果资源压满估计应该在QPS在25000左右。另外我们也能看到抖动还是不低;延迟最低5.17ms，平均14.2ms，最大202ms。
下面我们来看看8.0.20的表现；为了对比，2次测试的数据库参数保持一致；其中8.0.20的测试我就增加了如下2个参数：
|
1 2 3 4 |
[root@td1 ~]# cat /etc/my.cnf|grep 'double' innodb_doublewrite_dir = /mysql_dwlog innodb_doublewrite_files =4 [root@td1 ~]# |
行MySQL 8.0.20 提供了一个针对double write的新机制，通过将double write文件进行独立存放；可以提供更高的性能。在之前版本中默认情况下就在innodb data home下面；即在系统表空间中。
|
1 2 3 4 5 6 |
[root@td1 data]# pwd /opt/mysql_8.0.19/data [root@td1 data]# ls -ltr *dblw* -rw-r----- 1 mysql mysql 9568256 May 24 21:43 #ib_16384_1.dblwr -rw-r----- 1 mysql mysql 1179648 May 24 22:11 #ib_16384_0.dblwr [root@td1 data]# |
直接上测试数据：
|
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 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
[root@td2 lua]# /opt/sysbench-master/src/sysbench oltp_read_write.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-db=test --mysql-password=enmotech --table_size=100000 --tables=10 --threads=16 --report-interval=10 --time=300 run sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3) Running the test with following options: Number of threads: 16 Report intermediate results every 10 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 10s ] thds: 16 tps: 1549.00 qps: 31000.56 (r/w/o: 21702.24/5492.79/3805.53) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 16 tps: 1576.83 qps: 31539.42 (r/w/o: 22078.36/5605.79/3855.26) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 16 tps: 1559.88 qps: 31195.77 (r/w/o: 21836.90/5547.82/3811.05) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 16 tps: 1582.76 qps: 31650.94 (r/w/o: 22154.90/5651.20/3844.84) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 16 tps: 1472.78 qps: 29465.41 (r/w/o: 20625.96/5258.11/3581.34) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 16 tps: 1544.19 qps: 30884.16 (r/w/o: 21618.90/5523.17/3742.08) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 16 tps: 1544.10 qps: 30877.82 (r/w/o: 21614.25/5530.89/3732.69) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 16 tps: 1583.28 qps: 31664.51 (r/w/o: 22166.53/5686.73/3811.25) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 16 tps: 1584.55 qps: 31687.19 (r/w/o: 22180.06/5685.00/3822.13) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 16 tps: 1551.10 qps: 31025.81 (r/w/o: 21717.93/5558.72/3749.15) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 16 tps: 1480.17 qps: 29608.06 (r/w/o: 20726.12/5336.60/3545.34) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 16 tps: 1499.03 qps: 29975.90 (r/w/o: 20982.42/5398.87/3594.61) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00 [ 130s ] thds: 16 tps: 1484.46 qps: 29690.77 (r/w/o: 20783.62/5356.05/3551.10) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00 [ 140s ] thds: 16 tps: 1507.21 qps: 30146.36 (r/w/o: 21103.61/5451.83/3590.92) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00 [ 150s ] thds: 16 tps: 1536.63 qps: 30728.08 (r/w/o: 21510.51/5556.91/3660.67) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00 [ 160s ] thds: 16 tps: 1523.88 qps: 30483.70 (r/w/o: 21337.12/5516.73/3629.85) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00 [ 170s ] thds: 16 tps: 1414.10 qps: 28278.62 (r/w/o: 19795.24/5132.22/3351.15) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00 [ 180s ] thds: 16 tps: 1499.10 qps: 29981.71 (r/w/o: 20986.91/5438.06/3556.74) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00 [ 190s ] thds: 16 tps: 1442.23 qps: 28844.35 (r/w/o: 20190.39/5245.00/3408.97) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00 [ 200s ] thds: 16 tps: 1468.25 qps: 29371.57 (r/w/o: 20559.65/5341.53/3470.39) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00 [ 210s ] thds: 16 tps: 1526.81 qps: 30534.74 (r/w/o: 21375.07/5563.14/3596.53) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00 [ 220s ] thds: 16 tps: 1547.11 qps: 30939.35 (r/w/o: 21659.08/5636.45/3643.83) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00 [ 230s ] thds: 16 tps: 1493.30 qps: 29863.43 (r/w/o: 20904.92/5450.81/3507.70) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00 [ 240s ] thds: 16 tps: 1553.41 qps: 31065.13 (r/w/o: 21743.26/5679.08/3642.79) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00 [ 250s ] thds: 16 tps: 1537.47 qps: 30755.83 (r/w/o: 21529.63/5622.84/3603.36) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00 [ 260s ] thds: 16 tps: 1531.96 qps: 30642.71 (r/w/o: 21448.95/5598.41/3595.36) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00 [ 270s ] thds: 16 tps: 1554.62 qps: 31084.77 (r/w/o: 21761.86/5697.63/3625.28) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00 [ 280s ] thds: 16 tps: 1491.84 qps: 29841.02 (r/w/o: 20886.27/5487.75/3466.99) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00 [ 290s ] thds: 16 tps: 1522.66 qps: 30456.63 (r/w/o: 21319.49/5585.21/3551.93) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00 [ 300s ] thds: 16 tps: 1536.78 qps: 30726.19 (r/w/o: 21508.61/5639.16/3578.42) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 6398196 write: 1652782 other: 1089302 total: 9140280 transactions: 457014 (1523.31 per sec.) queries: 9140280 (30466.29 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 1523.3146 time elapsed: 300.0129s total number of events: 457014 Latency (ms): min: 2.69 avg: 10.50 max: 241.46 95th percentile: 18.28 sum: 4798305.49 Threads fairness: events (avg/stddev): 28563.3750/165.99 execution time (avg/stddev): 299.8941/0.00 |
我们可以看到，tps达到了1500，QPS突破了30000；相比8.0.19版本而言，大概提升了进40%的性能；如果将资源跑满；同时将sysbench放到其他主机远程调用的话，我相信性能还有一定提升。我们不得不说这一改变，带来的结果确实是性能吊炸天的提升了。
不过，我们也不难发现抖动还是不低，延迟最低2.69ms，最大达到了240ms。平均达到了10.5ms；相比8.0.19版本也提升了不少。
对于8.0版本，在mgr流控方面有了一些新的机制变化：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> show variables like '%control%'; +-----------------------------------------------------+--------+ | Variable_name | Value | +-----------------------------------------------------+--------+ | group_replication_flow_control_applier_threshold | 250000 | | group_replication_flow_control_certifier_threshold | 250000 | | group_replication_flow_control_hold_percent | 10 | | group_replication_flow_control_max_quota | 0 | | group_replication_flow_control_member_quota_percent | 0 | | group_replication_flow_control_min_quota | 0 | | group_replication_flow_control_min_recovery_quota | 0 | | group_replication_flow_control_mode | QUOTA | | group_replication_flow_control_period | 1 | | group_replication_flow_control_release_percent | 50 | +-----------------------------------------------------+--------+ 10 rows in set (0.00 sec) |
新从参数上来看，8.0在流控方面比之前版本精细的多了。但是不得不说还是有一些问题。最近在某微信群看一前网易MySQL大佬说group_replication_flow_control_period参数如果调整为0.5甚至到0.2s的话，mgr的延迟要降低很多，同时性能可以提升很多。由于该参数显示设置最低为1s；因此无法在参数层面控制；只能修改代码重编译；改天研究一下。
无论如何，我们可以看到MySQL 8.0越来越稳定，相信不久的将来，将迎来大面积上线。
补充：
补充一点,8.0.18版本居然引入了一个比较坑爹的bug；导致后续版本init初始化时很容易报错；因此参数文件中增加了一些额外的插件，比如半同步等。我这里也遇到了，初始化了不下5次，才成功。
|
1 2 3 4 5 6 7 8 9 |
2020-05-24T15:13:36.259827Z 0 [System] [MY-013169] [Server] /opt/mysql_8.0.20/bin/mysqld (mysqld 8.0.20) initializing of server in progress as process 5485 2020-05-24T15:13:36.294926Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2020-05-24T15:13:43.319219Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 13 in a file operation. 2020-05-24T15:13:43.319429Z 1 [ERROR] [MY-012595] [InnoDB] The error means mysqld does not have the access rights to the directory. 2020-05-24T15:13:43.319630Z 1 [ERROR] [MY-012929] [InnoDB] InnoDB Database creation was aborted with error Generic error. You may need to delete the ibdata1 file before trying to start up again. 2020-05-24T15:13:43.686125Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2020-05-24T15:13:43.686427Z 0 [ERROR] [MY-013236] [Server] The designated data directory /opt/mysql_8.0.20/data/ is unusable. You can remove all files that the server added to it. 2020-05-24T15:13:43.686886Z 0 [ERROR] [MY-010119] [Server] Aborting 2020-05-24T15:13:43.688166Z 0 [System] [MY-010910] [Server] /opt/mysql_8.0.20/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL. |
按照这个思路，我这里通过屏蔽double write参数后成功初始化，然后再还原参数即可。
Leave a Reply
You must be logged in to post a comment.