Bug #102289 buf_flush_or_remove_pages causes large tps fluctuation with write-heavy workload
Submitted: 19 Jan 6:06 Modified: 20 Jan 6:41
Reporter: Zhou Xinjing (OCA) Email Updates:
Status: Verified Impact on me:
None 
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 6:06] Zhou Xinjing
Description:
We are observing significant performance fluctuation when creating index along with write-heavy workloads. 

Some key steps to reproduce the phenomenon.
1. innodb_buffer_pool_size = 128G.
2. prepare a database with 16 sysbench tables each with 50M rows(~182G on disk).
3. prepare another database with a small sysbench table(let's say sbtest2.sbtest1) with 2.5M rows(965M on disk). 
4. run oltp_update_non_index with 64 threads
5. 30s into the oltp_update_non_index workload, open up another mysql client and run alter table sbtest1 add index ck_1(c, k) on database sbtest2.

We get the following results during the alter operation:
[ 36s ] thds: 64 tps: 41620.44 qps: 41620.44 (r/w/o: 0.00/41620.44/0.00) lat (ms,95%): 2.26 err/s: 0.00 reconn/s: 0.00
[ 37s ] thds: 64 tps: 16432.87 qps: 16432.87 (r/w/o: 0.00/16432.87/0.00) lat (ms,95%): 2.07 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 64 tps: 56104.55 qps: 56104.55 (r/w/o: 0.00/56104.55/0.00) lat (ms,95%): 1.64 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 64 tps: 60182.19 qps: 60182.19 (r/w/o: 0.00/60182.19/0.00) lat (ms,95%): 1.64 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 64 tps: 18716.52 qps: 18716.52 (r/w/o: 0.00/18716.52/0.00) lat (ms,95%): 2.18 err/s: 0.00 reconn/s: 0.00
[ 41s ] thds: 64 tps: 33491.40 qps: 33491.40 (r/w/o: 0.00/33491.40/0.00) lat (ms,95%): 1.67 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 64 tps: 57321.09 qps: 57321.09 (r/w/o: 0.00/57321.09/0.00) lat (ms,95%): 1.73 err/s: 0.00 reconn/s: 0.00
[ 43s ] thds: 64 tps: 51727.59 qps: 51727.59 (r/w/o: 0.00/51727.59/0.00) lat (ms,95%): 2.14 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 64 tps: 7072.98 qps: 7072.98 (r/w/o: 0.00/7072.98/0.00) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 64 tps: 1029.02 qps: 1029.02 (r/w/o: 0.00/1029.02/0.00) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 64 tps: 473.00 qps: 473.00 (r/w/o: 0.00/473.00/0.00) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00
[ 47s ] thds: 64 tps: 1061.02 qps: 1061.02 (r/w/o: 0.00/1061.02/0.00) lat (ms,95%): 134.90 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 64 tps: 2172.02 qps: 2172.02 (r/w/o: 0.00/2172.02/0.00) lat (ms,95%): 57.87 err/s: 0.00 reconn/s: 0.00
[ 49s ] thds: 64 tps: 263.00 qps: 263.00 (r/w/o: 0.00/263.00/0.00) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 64 tps: 7976.91 qps: 7976.91 (r/w/o: 0.00/7976.91/0.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 64 tps: 25746.90 qps: 25746.90 (r/w/o: 0.00/25746.90/0.00) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 64 tps: 4777.07 qps: 4777.07 (r/w/o: 0.00/4777.07/0.00) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00
[ 53s ] thds: 64 tps: 26537.01 qps: 26537.01 (r/w/o: 0.00/26537.01/0.00) lat (ms,95%): 6.43 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 64 tps: 13343.91 qps: 13343.91 (r/w/o: 0.00/13343.91/0.00) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 64 tps: 19472.06 qps: 19472.06 (r/w/o: 0.00/19472.06/0.00) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 64 tps: 14592.00 qps: 14592.00 (r/w/o: 0.00/14592.00/0.00) lat (ms,95%): 9.91 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 64 tps: 10282.61 qps: 10282.61 (r/w/o: 0.00/10282.61/0.00) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 64 tps: 21206.72 qps: 21206.72 (r/w/o: 0.00/21206.72/0.00) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 64 tps: 5684.08 qps: 5684.08 (r/w/o: 0.00/5684.08/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 64 tps: 13527.99 qps: 13527.99 (r/w/o: 0.00/13527.99/0.00) lat (ms,95%): 23.10 err/s: 0.00 reconn/s: 0.00
[ 61s ] thds: 64 tps: 1839.99 qps: 1839.99 (r/w/o: 0.00/1839.99/0.00) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 62s ] thds: 64 tps: 721.00 qps: 721.00 (r/w/o: 0.00/721.00/0.00) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 63s ] thds: 64 tps: 1336.00 qps: 1336.00 (r/w/o: 0.00/1336.00/0.00) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00
[ 64s ] thds: 64 tps: 2807.00 qps: 2807.00 (r/w/o: 0.00/2807.00/0.00) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 64 tps: 10292.92 qps: 10292.92 (r/w/o: 0.00/10292.92/0.00) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
[ 66s ] thds: 64 tps: 1361.98 qps: 1361.98 (r/w/o: 0.00/1361.98/0.00) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00
[ 67s ] thds: 64 tps: 762.02 qps: 762.02 (r/w/o: 0.00/762.02/0.00) lat (ms,95%): 244.38 err/s: 0.00 reconn/s: 0.00
[ 68s ] thds: 64 tps: 993.98 qps: 993.98 (r/w/o: 0.00/993.98/0.00) lat (ms,95%): 134.90 err/s: 0.00 reconn/s: 0.00
[ 69s ] thds: 64 tps: 1141.05 qps: 1141.05 (r/w/o: 0.00/1141.05/0.00) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 64 tps: 731.99 qps: 731.99 (r/w/o: 0.00/731.99/0.00) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00

mysql> alter table sbtest1 add index ck_1(c, k);
Query OK, 0 rows affected (2 min 6.83 sec)  <<<--------
Records: 0  Duplicates: 0  Warnings: 0

Observe that tps significantly drops from tens of thousands to only a few hundred and fluctuates greatly.
And this fluctuation continues for about a few minutes which is the time it took for the alter table operation.

Currently, ddl operations such as CREATE INDEX require synchronously flushing dirty pages in the flush_list.
All of these operations go through buf_flush_or_remove_pages function which is very inefficient when these ddl operations run along with write-heavy workload.
buf_flush_or_remove_pages requires rescanning the flush list from the tail every time a dirty page is successfully flushed or removed. 
When the flush list is full of dirty pages from other tables, buf_flush_or_remove_pages need to scan the list repeatedly while holding flush_list mutex and buf_pool mutex. Although the mutexes are released every BUF_LRU_DROP_SEARCH_SIZE iterations, 
the repeated scans still result in large perf fluctuations on concurrent transactions as they need to touch the flush list. 

The root cause is similar to what was described in another bug report https://bugs.mysql.com/bug.php?id=95582.
However, what has not been pointed out in that bug report is that the repeated scans of flush_list in buf_flush_dirty_pages could have a great performance impact on other transactions for a very long time.
We also took the perf-record of mysqld and the result is similar to what was described in https://bugs.mysql.com/bug.php?id=95582

How to repeat:
As above

Suggested fix:
The reason for the repeated scan is because, during the traversal of the flush list, prev link can't be trusted after releasing flush list mutex and buf pool mutex.

We propose a solution to speed up the scan safely:

We denote the repeated scan as pessimistic scan since on every successful page flush or removal the scan needs to restart from the tail of the flush list.
We introduce a new optimistic scan of the flush list that only traverses the flush list once before performing the pessimistic scan. 

During the optimistic scan, before going to perform the flush or removal of a target page, we first pin the page pointed by the prev pointer using buf_page_set_sticky.
This ensures that the page pointed by prev pointer stays in flush list even if we release buf_pool mutex and the block->mutex.
After the target page flush or removal is complete and flush_list_mutex and buf_pool mutex are regained, 
we can be sure that the prev pointer is a safe point to continue the scan.
In such a way, we are able to flush most of the target dirty pages in just one scan.

There are some cases where pinning prev page is not allowed since the prev page could be undergoing i/o operations or pinned by other threads.
We have to skip these prev pages and therefore miss some pages. So the optimistic scan does the flush in a best-effort way. 
However, we could rely on the pessimistic scans to catch all the missed pages. 
Since the optimistic scan handles most of the target dirty pages, the work left to the pessimistic scan is usually very small.
This idea is a bit similar to the two-stage adaptive hash index cleanup where the first stage does the cleanup in a "best-effort" way as well.

With this approach, we do not need to re-enable redo logging for the bulk loading or concurrent hazard pointers as proposed in https://bugs.mysql.com/bug.php?id=95582.
[19 Jan 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 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 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 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 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 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 6:41] Zhou Xinjing
Correction: 8.0.23
[20 Jan 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 13:32] MySQL Verification Team
Thank you Mr. Xinjing !!!!!!

So we have patches for  both, 5.7 & 8.0 ....