Bug #118136 8.0.27 DDL performance degradation - Flush Observer

• Submitted: 8 May 10:10
• Modified: 8 May 10:23
• Reporter: Xizhe Zhang (OCA)
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S5 (Performance)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: Contribution

[8 May 10:10] Xizhe Zhang

Description:
In Bug #116738, we can see that 8.0.26 only calls the 'flush_observer' function once, which takes 3.16 seconds; the 'ddl::Builder::finish' function in 8.0.27 calls the 'Flush_observer::flush' function once for each index. These two calls take about 6 seconds, which is exactly twice as long as 8.0.26.

So I monitored the calls to the 'Flush_observer::flush' function in 8.0.27 and found that there are actually 3 calls, 2 from the 'ddl::Builder::finish' function and 1 from the final 'ddl::Context::cleanup':

ddl::Builder::finish
ddl::Builder::finish
ddl::Context::cleanup
                             mean               std                min                max       count                sum
flush_observer  1978570647.000000 1713641950.664917        5469.000000  2990844808.000000    3.000000  5935711941.000000

The ‘Flush_observer::flush’ function is to wait for dirty pages to be flushed to disk, which actually only needs to be completed for the last time. Modify the 8.0.27 code to only keep the call in 'ddl::Context::cleanup', which reduce the number of calls to 1, and reduce the time consumption to 2.97s:
                            mean  std               min               max    count               sum
flush_observer 2968506794.000000  nan 2968506794.000000 2968506794.000000 1.000000 2968506794.000000

How to repeat:
See Bug #116738

[8 May 10:15] Xizhe Zhang

Hello, Verification Team, this is my patch based on 8.0.42

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: ddl_flush_observer.diff (application/octet-stream, text), 530 bytes.

[8 May 10:23] MySQL Verification Team

Hello Xizhe Zhang ,

Thank you for the report and contribution.

regards,
Umesh