Bug #109417 Bad performance by call btr_drop_ahi_for_table within dict sys_mutex protection

• Submitted: 16 Dec 2022 23:30
• Modified: 17 Dec 2022 15:30
• Reporter: Fangxin Flou (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S5 (Performance)
• Version: 8.0.31
• OS: Any
• CPU Architecture: Any
• Tags: adaptive hash index, dict_sys

[16 Dec 2022 23:30] Fangxin Flou

Description:
If we enable AHI, btr_drop_ahi_for_table may take long time because it need a full buffer pool scan. And we can see some btr_drop_ahi_for_table call with dict_sys_mutex protection, it may cause major performance issue.

see the following code from ha_innodb.cc

/** Invalidate an entry or entries for partitoined table from the dict cache.
@param[in]      schema_name     Schema name
@param[in]      table_name      Table name */
static void innobase_dict_cache_reset(const char *schema_name,
                                      const char *table_name) {
  char name[FN_REFLEN];
  snprintf(name, sizeof name, "%s/%s", schema_name, table_name);

  dict_sys_mutex_enter();

  dict_table_t *table = dict_table_check_if_in_cache_low(name);

  if (table != nullptr) {
    btr_drop_ahi_for_table(table);
    dict_table_remove_from_cache(table);
  } else if (strcmp(schema_name, "mysql") != 0) {
    dict_partitioned_table_remove_from_cache(name);
  }

  dict_sys_mutex_exit();
}

How to repeat:
N/A

Suggested fix:
We can disable AHI at table level temporary, and run btr_drop_ahi_for_table before enter dict_sys->mutex protection to avoid heavy lock contention.

[17 Dec 2022 15:30] MySQL Verification Team

Hello Fangxin Flou,

Thank you for the report and performance improvement feedback.

regards,
Umesh