| Bug #99993 | Add optimizer trace for in-memory estimate | ||
|---|---|---|---|
| Submitted: | 26 Jun 2020 7:57 | Modified: | 9 Jun 2021 16:53 |
| Reporter: | Øystein Grøvlen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 8.0.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Jun 2020 8:40]
MySQL Verification Team
Hello Øystein, Thank you for the feature request! Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). More details are described in "Contributions" tab, please ensure to re-send the patch via that tab. Otherwise we would not be able to accept it. regards, Umesh
[30 Jun 2020 4:14]
Øystein Grøvlen
Add in-memory estimate to optimizer trace (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: memtrace.diff (application/octet-stream, text), 642 bytes.
[9 Jun 2021 16:53]
Jon Stephens
Documented fix as follows in the MySQL 8.0.27 changelog:
Added an in-memory estimate to the optimizer trace to indicate
how much of a given table is buffered in the buffer pool.
Our thanks to Øystein Grøvlen for the contribution.
Closed.
Description: How much of the table is buffered in the buffer pool, is taken into account in the optimizer cost model. However, this number is not given in the optimizer trace. This makes it difficult to know what effect this has had on the choice of query plan. How to repeat: N/A Suggested fix: diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 51265f046da..94f46678b3d 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -6029,7 +6029,8 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree, trace_idx.add("rowid_ordered", param->is_ror_scan) .add("using_mrr", !(mrr_flags & HA_MRR_USE_DEFAULT_IMPL)) - .add("index_only", read_index_only); + .add("index_only", read_index_only) + .add("in_memory", param->table->key_info[keynr].in_memory_estimate()); if (param->skip_records_in_range) { trace_idx.add_alnum("rows", "not applicable")