Bug #85487 num_tmp_files in filesort optimizer trace is nonsensical
Submitted: 16 Mar 2017 15:53 Modified: 21 Mar 2017 16:04
Reporter: Steinar Gunderson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[16 Mar 2017 15:53] Steinar Gunderson 
Description:
num_tmp_files in optimizer trace for filesort is a pretty meaningless number; it holds the number of chunks left after the final disk merge pass, so it's always between 1 and 15 (MERGEBUFF2). This isn't a very good indication of how much disk activity it is (and they're all really in one file, so “files” is wrong).

How to repeat:
N/A

Suggested fix:
Change to showing the number of chunks before any merge passes have happened.
[21 Mar 2017 16:04] Paul DuBois 
Posted by developer:
 
Noted in 8.0.2 changelog.

In optimizer trace output, num_tmp_files did not actually indicate
number of files. It has been renamed to
num_initial_chunks_spilled_to_disk and indicates the number of chunks
before any merging has occured.