Bug #102289 | buf_flush_or_remove_pages causes large tps fluctuation with write-heavy workload | ||
---|---|---|---|
Submitted: | 19 Jan 2021 6:06 | Modified: | 20 Jan 2021 6:41 |
Reporter: | Zhou Xinjing (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
Version: | 5.7, 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | flush_list, innodb, perf |
[19 Jan 2021 6:06]
Zhou Xinjing
[19 Jan 2021 6:15]
Zhou Xinjing
The patch implements the above-mentioned approach. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: flush-list-scan-opt.diff (application/octet-stream, text), 5.89 KiB.
[19 Jan 2021 6:16]
Zhou Xinjing
After applying the patch, we get the following smooth result during alter table. [ 48s ] thds: 64 tps: 79209.26 qps: 79209.26 (r/w/o: 0.00/79209.26/0.00) lat (ms,95%): 1.12 err/s: 0.00 reconn/s: 0.00 [ 49s ] thds: 64 tps: 35608.79 qps: 35608.79 (r/w/o: 0.00/35608.79/0.00) lat (ms,95%): 1.32 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 64 tps: 78205.49 qps: 78205.49 (r/w/o: 0.00/78205.49/0.00) lat (ms,95%): 1.16 err/s: 0.00 reconn/s: 0.00 [ 51s ] thds: 64 tps: 35985.92 qps: 35985.92 (r/w/o: 0.00/35985.92/0.00) lat (ms,95%): 1.12 err/s: 0.00 reconn/s: 0.00 [ 52s ] thds: 64 tps: 71622.00 qps: 71622.00 (r/w/o: 0.00/71622.00/0.00) lat (ms,95%): 1.16 err/s: 0.00 reconn/s: 0.00 [ 53s ] thds: 64 tps: 70456.03 qps: 70456.03 (r/w/o: 0.00/70456.03/0.00) lat (ms,95%): 1.12 err/s: 0.00 reconn/s: 0.00 [ 54s ] thds: 64 tps: 30348.36 qps: 30348.36 (r/w/o: 0.00/30348.36/0.00) lat (ms,95%): 1.23 err/s: 0.00 reconn/s: 0.00 [ 55s ] thds: 64 tps: 75583.77 qps: 75583.77 (r/w/o: 0.00/75583.77/0.00) lat (ms,95%): 1.16 err/s: 0.00 reconn/s: 0.00 [ 56s ] thds: 64 tps: 49296.78 qps: 49296.78 (r/w/o: 0.00/49296.78/0.00) lat (ms,95%): 1.16 err/s: 0.00 reconn/s: 0.00 [ 57s ] thds: 64 tps: 47692.11 qps: 47692.11 (r/w/o: 0.00/47692.11/0.00) lat (ms,95%): 1.23 err/s: 0.00 reconn/s: 0.00 [ 58s ] thds: 64 tps: 76058.75 qps: 76058.75 (r/w/o: 0.00/76058.75/0.00) lat (ms,95%): 1.21 err/s: 0.00 reconn/s: 0.00 [ 59s ] thds: 64 tps: 43987.92 qps: 43987.92 (r/w/o: 0.00/43987.92/0.00) lat (ms,95%): 1.34 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 64 tps: 73026.81 qps: 73026.81 (r/w/o: 0.00/73026.81/0.00) lat (ms,95%): 1.27 err/s: 0.00 reconn/s: 0.00 [ 61s ] thds: 64 tps: 67107.05 qps: 67107.05 (r/w/o: 0.00/67107.05/0.00) lat (ms,95%): 1.23 err/s: 0.00 reconn/s: 0.00 [ 62s ] thds: 64 tps: 47484.06 qps: 47484.06 (r/w/o: 0.00/47484.06/0.00) lat (ms,95%): 1.37 err/s: 0.00 reconn/s: 0.00 [ 63s ] thds: 64 tps: 73602.99 qps: 73602.99 (r/w/o: 0.00/73602.99/0.00) lat (ms,95%): 1.27 err/s: 0.00 reconn/s: 0.00 [ 64s ] thds: 64 tps: 48762.52 qps: 48762.52 (r/w/o: 0.00/48762.52/0.00) lat (ms,95%): 1.30 err/s: 0.00 reconn/s: 0.00 [ 65s ] thds: 64 tps: 58581.27 qps: 58581.27 (r/w/o: 0.00/58581.27/0.00) lat (ms,95%): 1.23 err/s: 0.00 reconn/s: 0.00 [ 66s ] thds: 64 tps: 68831.04 qps: 68831.04 (r/w/o: 0.00/68831.04/0.00) lat (ms,95%): 1.18 err/s: 0.00 reconn/s: 0.00 [ 67s ] thds: 64 tps: 32060.76 qps: 32060.76 (r/w/o: 0.00/32060.76/0.00) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00 [ 68s ] thds: 64 tps: 15865.79 qps: 15865.79 (r/w/o: 0.00/15865.79/0.00) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00 <----- [ 69s ] thds: 64 tps: 78256.95 qps: 78256.95 (r/w/o: 0.00/78256.95/0.00) lat (ms,95%): 1.14 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 64 tps: 41666.71 qps: 41666.71 (r/w/o: 0.00/41666.71/0.00) lat (ms,95%): 1.14 err/s: 0.00 reconn/s: 0.00 [ 71s ] thds: 64 tps: 76128.74 qps: 76128.74 (r/w/o: 0.00/76128.74/0.00) lat (ms,95%): 1.18 err/s: 0.00 reconn/s: 0.00 [ 72s ] thds: 64 tps: 55925.18 qps: 55925.18 (r/w/o: 0.00/55925.18/0.00) lat (ms,95%): 1.12 err/s: 0.00 reconn/s: 0.00 [ 73s ] thds: 64 tps: 62939.82 qps: 62939.82 (r/w/o: 0.00/62939.82/0.00) lat (ms,95%): 1.14 err/s: 0.00 reconn/s: 0.00 [ 74s ] thds: 64 tps: 79017.85 qps: 79017.85 (r/w/o: 0.00/79017.85/0.00) lat (ms,95%): 1.12 err/s: 0.00 reconn/s: 0.00 [ 75s ] thds: 64 tps: 37517.16 qps: 37517.16 (r/w/o: 0.00/37517.16/0.00) lat (ms,95%): 1.21 err/s: 0.00 reconn/s: 0.00 [ 76s ] thds: 64 tps: 78200.31 qps: 78200.31 (r/w/o: 0.00/78200.31/0.00) lat (ms,95%): 1.12 err/s: 0.00 reconn/s: 0.00 mysql> alter table sbtest1 add index ck_1(c, k); Query OK, 0 rows affected (14.38 sec) Records: 0 Duplicates: 0 Warnings: 0 And create index finishes in 14s which is 9 times faster than the pessimistic scan.
[19 Jan 2021 14:45]
MySQL Verification Team
Hi Mr. Xinjing, Thank you for your performance improvement report. I have analysed your data and your patch and I agree with you totally. Verified as reported for 5.7. Please, inform us whether similar patch is applicable for 8.0. Thanks in advance.
[20 Jan 2021 6:09]
Zhou Xinjing
The problem exists in 8.0.33 as well. By running oltp_write_only workload with 256 threads and concurrently with alter table add index operation, we get the following results: [ 26s ] thds: 256 tps: 30722.58 qps: 184442.51 (r/w/o: 0.00/122996.34/61446.17) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00 [ 27s ] thds: 256 tps: 31803.04 qps: 190620.26 (r/w/o: 0.00/127014.18/63606.09) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00 [ 28s ] thds: 256 tps: 31139.59 qps: 186935.54 (r/w/o: 0.00/124656.36/62279.18) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00 [ 29s ] thds: 256 tps: 30259.81 qps: 181429.83 (r/w/o: 0.00/120918.22/60511.61) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 256 tps: 30639.50 qps: 183712.00 (r/w/o: 0.00/122438.00/61274.00) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00 [ 31s ] thds: 256 tps: 30394.92 qps: 182724.54 (r/w/o: 0.00/121921.70/60802.84) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00 [ 32s ] thds: 256 tps: 17473.99 qps: 104790.94 (r/w/o: 0.00/69842.96/34947.98) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00 [ 33s ] thds: 256 tps: 2264.91 qps: 13311.46 (r/w/o: 0.00/8791.64/4519.82) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00 [ 34s ] thds: 256 tps: 2782.89 qps: 16808.35 (r/w/o: 0.00/11233.57/5574.79) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00 [ 35s ] thds: 256 tps: 29024.10 qps: 174324.63 (r/w/o: 0.00/116275.43/58049.21) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00 [ 36s ] thds: 256 tps: 1137.00 qps: 6817.00 (r/w/o: 0.00/4543.00/2274.00) lat (ms,95%): 404.61 err/s: 0.00 reconn/s: 0.00 [ 37s ] thds: 256 tps: 2036.00 qps: 12257.99 (r/w/o: 0.00/8185.99/4072.00) lat (ms,95%): 559.50 err/s: 0.00 reconn/s: 0.00 [ 38s ] thds: 256 tps: 28794.14 qps: 172488.85 (r/w/o: 0.00/114904.58/57584.28) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00 [ 39s ] thds: 256 tps: 3780.38 qps: 22948.34 (r/w/o: 0.00/15383.57/7564.77) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 256 tps: 1387.92 qps: 8249.52 (r/w/o: 0.00/5474.68/2774.84) lat (ms,95%): 634.66 err/s: 0.00 reconn/s: 0.00 [ 41s ] thds: 256 tps: 16597.88 qps: 99407.29 (r/w/o: 0.00/66225.53/33181.76) lat (ms,95%): 57.87 err/s: 0.00 reconn/s: 0.00 [ 42s ] thds: 256 tps: 16786.08 qps: 100925.48 (r/w/o: 0.00/67338.33/33587.15) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00 [ 43s ] thds: 256 tps: 15633.98 qps: 92911.89 (r/w/o: 0.00/61643.93/31267.96) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00 [ 44s ] thds: 256 tps: 90.99 qps: 1274.87 (r/w/o: 0.00/1095.88/178.98) lat (ms,95%): 1013.60 err/s: 0.00 reconn/s: 0.00 [ 45s ] thds: 256 tps: 16826.81 qps: 101182.89 (r/w/o: 0.00/67526.27/33656.62) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00 [ 46s ] thds: 256 tps: 1994.00 qps: 11936.02 (r/w/o: 0.00/7948.01/3988.01) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00 [ 47s ] thds: 256 tps: 8038.51 qps: 47967.07 (r/w/o: 0.00/31891.05/16076.02) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00 [ 48s ] thds: 256 tps: 18819.04 qps: 113214.25 (r/w/o: 0.00/75575.17/37639.08) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00 [ 49s ] thds: 256 tps: 5952.99 qps: 35701.93 (r/w/o: 0.00/23795.95/11905.98) lat (ms,95%): 520.62 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 256 tps: 3540.97 qps: 21178.85 (r/w/o: 0.00/14096.90/7081.95) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00 [ 51s ] thds: 256 tps: 19746.82 qps: 118493.91 (r/w/o: 0.00/79000.27/39493.64) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00 [ 52s ] thds: 256 tps: 11171.90 qps: 67089.40 (r/w/o: 0.00/44745.60/22343.80) lat (ms,95%): 29.19 err/s: 0.00 reconn/s: 0.00 [ 53s ] thds: 256 tps: 2394.95 qps: 14070.69 (r/w/o: 0.00/9288.80/4781.89) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00 [ 54s ] thds: 256 tps: 11545.67 qps: 69518.05 (r/w/o: 0.00/46418.71/23099.35) lat (ms,95%): 114.72 err/s: 0.00 reconn/s: 0.00 [ 55s ] thds: 256 tps: 15191.89 qps: 90325.36 (r/w/o: 0.00/59941.57/30383.78) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00 [ 56s ] thds: 256 tps: 6871.00 qps: 42111.02 (r/w/o: 0.00/28369.02/13742.01) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00 [ 57s ] thds: 256 tps: 5408.64 qps: 32099.89 (r/w/o: 0.00/21290.60/10809.29) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00 mysql> alter table sbtest1 add index ck_1(c, k); Query OK, 0 rows affected (1 min 58.93 sec) <---- Records: 0 Duplicates: 0 Warnings: 0 The fluctuation is still pretty significant
[20 Jan 2021 6:13]
Zhou Xinjing
By applying the patch with a little bit of modification, we get a much better result: [ 58s ] thds: 256 tps: 28684.30 qps: 171900.80 (r/w/o: 0.00/114541.21/57359.60) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00 [ 59s ] thds: 256 tps: 29237.58 qps: 175538.47 (r/w/o: 0.00/117065.31/58473.16) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 256 tps: 28256.99 qps: 169453.93 (r/w/o: 0.00/112936.95/56516.98) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00 [ 61s ] thds: 256 tps: 29525.60 qps: 177224.58 (r/w/o: 0.00/118167.39/59057.19) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00 [ 62s ] thds: 256 tps: 30168.39 qps: 181066.35 (r/w/o: 0.00/120727.57/60338.79) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00 [ 63s ] thds: 256 tps: 29473.37 qps: 176940.24 (r/w/o: 0.00/117993.50/58946.74) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00 [ 64s ] thds: 256 tps: 30194.98 qps: 181181.88 (r/w/o: 0.00/120791.92/60389.96) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00 [ 65s ] thds: 256 tps: 23017.99 qps: 137365.94 (r/w/o: 0.00/91329.95/46035.99) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00 [ 66s ] thds: 256 tps: 15000.65 qps: 90431.84 (r/w/o: 0.00/60435.55/29996.29) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00. <<--- [ 67s ] thds: 256 tps: 21250.11 qps: 126942.58 (r/w/o: 0.00/84437.37/42505.21) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00. <<--- scanning the flush list [ 68s ] thds: 256 tps: 5024.96 qps: 30132.74 (r/w/o: 0.00/20082.83/10049.91) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00. <<--- [ 69s ] thds: 256 tps: 28615.19 qps: 172470.01 (r/w/o: 0.00/115239.62/57230.39) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 256 tps: 29035.10 qps: 173394.41 (r/w/o: 0.00/115324.22/58070.19) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00 [ 71s ] thds: 256 tps: 27903.31 qps: 168338.84 (r/w/o: 0.00/112532.22/55806.62) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00 [ 72s ] thds: 256 tps: 28763.52 qps: 172485.13 (r/w/o: 0.00/114964.09/57521.04) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00 [ 73s ] thds: 256 tps: 28840.83 qps: 172888.00 (r/w/o: 0.00/115202.33/57685.67) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00 [ 74s ] thds: 256 tps: 29384.64 qps: 176340.85 (r/w/o: 0.00/117576.56/58764.28) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00 [ 75s ] thds: 256 tps: 29357.88 qps: 176244.30 (r/w/o: 0.00/117522.54/58721.77) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00 mysql> alter table sbtest1 add index ck_1(c, k); Query OK, 0 rows affected (15.99 sec) <<---- Records: 0 Duplicates: 0 Warnings: 0 The time for the alter operation is reduced by around 8X.
[20 Jan 2021 6:16]
Zhou Xinjing
Patch for 8.0.33 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: flush-list-scan-opt-8.0.diff (application/octet-stream, text), 5.94 KiB.
[20 Jan 2021 6:41]
Zhou Xinjing
Correction: 8.0.23
[20 Jan 2021 12:15]
Zhou Xinjing
Previous patch does not handle concurrent io_fix accesses correctly. Here is an updated patch. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: flush-list-scan-opt-8.0.diff (application/octet-stream, text), 6.85 KiB.
[20 Jan 2021 13:32]
MySQL Verification Team
Thank you Mr. Xinjing !!!!!! So we have patches for both, 5.7 & 8.0 ....