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.
Fixed by checking handler_stats if it's active instead of
thd->variables.log_slow_verbosity & LOG_SLOW_VERBOSITY_ENGINE.
Reviewed-by: Sergei Petrunia <sergey@mariadb.com>
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.
(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.
The new statistics is enabled by adding the "engine", "innodb" or "full"
option to --log-slow-verbosity
Example output:
# Pages_accessed: 184 Pages_read: 95 Pages_updated: 0 Old_rows_read: 1
# Pages_read_time: 17.0204 Engine_time: 248.1297
Page_read_time is time doing physical reads inside a storage engine.
(Writes cannot be tracked as these are usually done in the background).
Engine_time is the time spent inside the storage engine for the full
duration of the read/write/update calls. It uses the same code as
'analyze statement' for calculating the time spent.
The engine statistics is done with a generic interface that should be
easy for any engine to use. It can also easily be extended to provide
even more statistics.
Currently only InnoDB has counters for Pages_% and Undo_% status.
Engine_time works for all engines.
Implementation details:
class ha_handler_stats holds all engine stats. This class is included
in handler and THD classes.
While a query is running, all statistics is updated in the handler. In
close_thread_tables() the statistics is added to the THD.
handler::handler_stats is a pointer to where statistics should be
collected. This is set to point to handler::active_handler_stats if
stats are requested. If not, it is set to 0.
handler_stats has also an element, 'active' that is 1 if stats are
requested. This is to allow engines to avoid doing any 'if's while
updating the statistics.
Cloned or partition tables have the pointer set to the base table if
status are requested.
There is a small performance impact when using --log-slow-verbosity=engine:
- All engine calls in 'select' will be timed.
- IO calls for InnoDB reads will be timed.
- Incrementation of counters are done on local variables and accesses
are inline, so these should have very little impact.
- Statistics has to be reset for each statement for the THD and each
used handler. This is only 40 bytes, which should be neglectable.
- For partition tables we have to loop over all partitions to update
the handler_status as part of table_init(). Can be optimized in the
future to only do this is log-slow-verbosity changes. For this to work
we have to update handler_status for all opened partitions and
also for all partitions opened in the future.
Other things:
- Added options 'engine' and 'full' to log-slow-verbosity.
- Some of the new files in the test suite comes from Percona server, which
has similar status information.
- buf_page_optimistic_get(): Do not increment any counter, since we are
only validating a pointer, not performing any buf_pool.page_hash lookup.
- Added THD argument to save_explain_data_intern().
- Switched arguments for save_explain_.*_data() to have
always THD first (generates better code as other functions also have THD
first).
