This feature stores the basic stats of the base tables that are used in
a query, to the optimizer trace. This feature is also controlled by
optimizer_record_context, and is not enabled by default. The stats
such as num_of_records present in the table, indexes if present
then their names, along with the average number of records_per_key
with in each index are dumped to the trace. Additionally, stats from
range analysis of the queries are also dumped into the trace
The approach taken here is to extend the existing function
store_tables_context_in_trace() and add new dump_table_stats_to_trace()
to opt_trace_ddl_info.cc. For storing the range analysis stats into the
trace, they are first recorded in the hash defined in
Optimizer_Stats_Context_Recorder, and is used later while dumping the
stats into the trace from dump_table_stats_to_trace().
Several new tests are also added to opt_trace_store_stats.test
The attached_condition field of the produced json text had
"String with illegal unicode symbol" instead of the proper unicode
value.
The reason is that, the item field when being written to the json
writer in write_item(), used a String with charset my_charset_bin,
and that prevented the string from being escaped.
Solution is to change: -
1. charset to my_charset_utf8mb4_bin in sql_explain.cc.
2. Item::print_value(), handle string type specially if
the charset is my_charset_bin by appending hex value.
Expose InnoDB's Adaptive Hash Index (AHI) statistics through ANALYZE
FORMAT=JSON output and global status variables to provide query-level
and system-level visibility into AHI usage and effectiveness.
This allows DBAs and developers to monitor how well
the adaptive hash index is serving their workloads on a per-query basis.
The r_ahi_stats object (nested inside r_engine_stats) now reports four
key metrics: ahi_searches (successful AHI lookups), ahi_searches_btree
(AHI misses requiring B-tree fallback), ahi_rows_added (rows inserted
into AHI), and ahi_pages_added (pages indexed by AHI). These same
metrics are exposed globally via innodb_status_variables.
Transaction-local tracking eliminates contention by accumulating
statistics in trx_t fields (n_sea, n_non_sea, n_ahi_rows_added,
n_ahi_pages_added) and aggregating them into global atomic counters
during trx_t::commit_cleanup() and trx_t::free(),
following the pattern established for buf_pool.stat.n_page_gets.
AHI counters are now organized as data members of the btr_sea structure
where they logically belong, using anonymous unions to enable both
atomic writes and direct non-atomic reads.
- btr_ahi_inc_searches(): Increment trx->n_sea when AHI lookup succeeds.
- btr_ahi_inc_searches_btree(): Increment trx->n_non_sea when AHI lookup
fails and falls back to B-tree search.
- btr_ahi_inc_rows_added(): Increment trx->n_ahi_rows_added when rows
are added to the adaptive hash index structure.
- btr_ahi_inc_pages_added(): Increment trx->n_ahi_pages_added when new
pages are indexed by AHI.
- btr_cur_t::search_leaf(): Call btr_ahi_inc_searches() on successful
AHI hit and btr_ahi_inc_searches_btree() on AHI miss.
- btr_sea: Add four counter members with anonymous unions to allow atomic
writes and lockless reads: hit_count/hit_count_nonatomic (successful AHI
lookups), miss_count/miss_count_nonatomic (B-tree searches after AHI miss),
rows_added/rows_added_nonatomic (rows added to AHI), and
pages_added/pages_added_nonatomic (pages added to AHI). Also includes
hit_count_old and miss_count_old variant for computing deltas in monitor
output.
- btr0cur.h/btr0cur.cc: Remove global declarations and definitions of
btr_cur_n_sea, btr_cur_n_non_sea, btr_cur_n_sea_old, btr_cur_n_non_sea_old,
btr_ahi_n_rows_added, and btr_ahi_n_pages_added as they are now data
members of btr_sea.
- export_var_t: Remove innodb_ahi_hit, innodb_ahi_miss, innodb_ahi_rows_added,
and innodb_ahi_pages_added members. MySQL status variables now point
directly to btr_search counters.
- innodb_status_variables[]: Change AHI status variable pointers from
&export_vars.innodb_ahi_* to &btr_search.*_nonatomic, enabling zero-copy
reads: adaptive_hash_hash_searches points to &btr_search.hit_count_nonatomic,
adaptive_hash_non_hash_searches to &btr_search.miss_count_nonatomic,
adaptive_hash_rows_added to &btr_search.rows_added_nonatomic, and
adaptive_hash_pages_added to &btr_search.pages_added_nonatomic.
- MONITOR_OVLD_ADAPTIVE_HASH_ROW_ADDED, MONITOR_OVLD_ADAPTIVE_HASH_PAGE_ADDED:
Convert monitor counters to "overload" type that read from atomic counters
btr_search.rows_added and btr_search.pages_added respectively instead of
maintaining separate statistics.
- trx_t::commit_cleanup() and trx_t::free(): Update transaction cleanup to
increment btr_search.hit_count, btr_search.miss_count, btr_search.rows_added,
and btr_search.pages_added instead of the old global counters.
- trace_engine_stats(): Output r_ahi_stats object with all four AHI
counters in JSON format when any AHI activity is detected during query
execution.
- ha_handler_stats: Added ahi_searches, ahi_searches_btree, ahi_rows_added,
and ahi_pages_added fields to track per-query AHI statistics.
- Add mtr parameter to btr_search_move_or_delete_hash_entries(),
btr_cur_t::search_info_update(), btr_search_update_hash_on_insert(),
btr_search_update_hash_ref(), btr_search_info_update_hash(), and
btr_search_build_page_hash_index() to allow updating AHI statistics.
- ahi_stats.test: Comprehensive verification of AHI statistics reporting
across different scenarios: insufficient accesses (no AHI build),
threshold triggering (AHI construction), heavy warmup (full AHI
utilization), and disabled AHI (verify zero statistics).
- check_ahi_status.inc: Reusable include file for executing queries with
configurable warmup repetitions and extracting AHI statistics from
ANALYZE FORMAT=JSON output using JSON path expressions.
valgrind reported use of an un-initialized value, when trying to write
analyzed plan to json. Instead of fetching c_str() from the string
object and passing it to the writer object's add_string() method, one could
use an overridden add_string() that accepts a different string object.
This would avoid the valgrind warnings.
There are two types of partition index scans. Ordered and unordered.
Ordered scan is used in common cases, including when the partitioned
table is the first in the join order and when ORDER BY ... DESC.
Whereas unordered scan is used when the partitioned table is not the
first in the join order, or in mrr when mrr_mode does not have
HA_MRR_SORTED. It can be unclear whether ordered or unordered scan has
been used. Therefore in this patch we add a field
"partitions_index_scan_method" to ANALYZE FORMAT=JSON to reveal with
method has been used. Possible values include
"merge_ordered_scans" (for ordered scan)
"iterate_over_partitions" (for unordered scan) and "both" (when both
have been used).
This commits adds the "materialization" block to the output of
EXPLAIN/ANALYZE FORMAT=JSON when materialized subqueries are involved
into processing. In the case of ANALYZE additional runtime information
is displayed, such as:
- chosen strategy of materialization
- number of partial match/index lookup loops
- sizes of partial match buffers
The symptoms were: take a server with no activity and a table that's
not in the buffer pool. Run a query that reads the whole table and
observe that r_engine_stats.pages_read_count shows about 2% of the table
was read. Who reads the rest?
The cause was that page prefetching done inside InnoDB was not counted.
This counts page prefetch requests made in buf_read_ahead_random() and
buf_read_ahead_linear() and makes them visible in:
- ANALYZE: r_engine_stats.pages_prefetch_read_count
- Slow Query Log: Pages_prefetched:
This patch intentionally doesn't attempt to count the time to read the
prefetched pages:
* there's no obvious place where one can do it
* prefetch reads may be done in parallel (right?), it is not clear how
to count the time in this case.
- Change the comments in class ha_handler_stats to say the members
are in ticks, not milliseconds.
- In sql_explain.cc, adjust the scale to print milliseconds.
Part#2, variant 2: Make the printed r_ values in JSON output consistent.
After this patch, ANALYZE output has:
- r_index_rows (NEW) - Observed number of rows before ICP or Rowid Filtering
checks. This is a per-scan average. like r_rows and "rows" are.
- r_rows (AS BEFORE) - Observed number of rows after ICP and Rowid Filtering.
- r_icp_filtered (NEW) - Observed selectivity of ICP condition.
- (AS BEFORE) observed selectivity of Rowid Filter is in
$.rowid_filter.r_selectivity_pct
- r_total_filtered - Observed combined selectivity: fraction of rows left
after applying ICP condition, Rowid Filter, and attached_condition.
This is now comparable with "filtered" and is printed right after it.
- r_filtered (AS BEFORE) - Observed selectivity of "attached_condition".
Tabular ANALYZE output is not changed. Note that JSON's r_filtered and
r_rows have the same meanings as before and have the same meaning as in
tabular output.
(Based on the original patch by Jason Cu)
Part #1:
- Add ha_handler_stats::{icp_attempts,icp_match}, make
handler_index_cond_check() increment them.
- ANALYZE FORMAT=JSON now prints r_icp_filtered based on these counters.
Before this patch, the code in Item_field::print() used
this convention (described in sql_explain.h:ExplainDataStructureLifetime):
- By default, the table that Item_field refers to is accessible.
- ANALYZE and SHOW {EXPLAIN|ANALYZE} may print Items after some
temporary tables have been dropped. They use
QT_DONT_ACCESS_TMP_TABLES flag. When it is ON, Item_field::print
will not access the table it refers to, if it is a temp.table
The bug was that EXPLAIN statement also may compute subqueries (depending
on subquery context and @@expensive_subquery_limit setting). After the
computation, the subquery calls JOIN::cleanup(true) which drops some of
its temporary tables. Calling Item_field::print() that refer to such table
will cause an access to free'd memory.
In this patch, we take into account that query optimization can compute
a subquery and discard its temporary tables. Item_field::print() now
assumes that any temporary table might have already been dropped.
This means QT_DONT_ACCESS_TMP_TABLES flag is not needed - we imply it is
always present.
But we also make one exception: derived tables are not freed in
JOIN::cleanup() call. They are freed later in close_thread_tables(),
at the same time when regular tables are closed.
Because of that, Item_field::print may assume that temp.tables
representing derived tables are available.
Initial patch by: Rex Jonston
Reviewed by: Monty <monty@mariadb.org>
