Bug #109416 inplace alter table stuck table access on btr_drop_ahi_for_table phrase
Submitted: 16 Dec 2022 23:17 Modified: 20 Dec 2022 5:52
Reporter: Fangxin Flou (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: adaptive hash index, ALTER TABLE

[16 Dec 2022 23:17] Fangxin Flou
Description:
During inplace alter table operation, we see the following steps:

1, lock table with MDL_EXCLUSIVE
2, prepare inplace_alter_table
3, down grade the mdl lock
4, run inplace_alter_table_impl
5, upgrade mdl_lock to MDL_EXCLUSIVE
6, commit inplace_alter_table (which call btr_drop_ahi_for_table for old table)
7, release the mdl lock.

As we know that the btr_drop_ahi_for_table may take long time, suggest to add extra btr_drop_ahi_for_table calls right after inplace_alter_table_impl without holding the exclusive mdl lock. 

How to repeat:
enable AHI.
session 1: run sysbench with oltp_point_select.
session 2: run "alter table sbtest1 engine innodb".

Suggested fix:
add extra btr_drop_ahi_for_table calls in inplace_alter_table_impl

template <typename Table>
bool ha_innobase::inplace_alter_table_impl(TABLE *altered_table,
                                           Alter_inplace_info *ha_alter_info) {
......
  btr_drop_ahi_for_table(m_prebuilt->table);

  ddl::Context ddl(trx, m_prebuilt->table, ctx->new_table, ctx->online,
                   ctx->add_index, ctx->add_key_numbers, ctx->num_to_add_index,
                   altered_table, ctx->add_cols, ctx->col_map, ctx->add_autoinc,
                   ctx->sequence, ctx->skip_pk_sort, ctx->m_stage, add_v,
                   eval_table, thd_ddl_buffer_size(m_prebuilt->trx->mysql_thd),
                   thd_ddl_threads(m_prebuilt->trx->mysql_thd));

  const auto err = clean_up(ddl.build());

  btr_drop_ahi_for_table(m_prebuilt->table);
......
[18 Dec 2022 1:20] Fangxin Flou
It's easy to reproduce

-- session 1:
mysql> alter table sbtest1 engine innodb;
Query OK, 0 rows affected (25 min 57.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- run sysbench
[ 1546s ] thds: 256 tps: 361866.67 qps: 361866.67 (r/w/o: 361866.67/0.00/0.00) lat (ms,95%): 1.55
[ 1547s ] thds: 256 tps: 173134.68 qps: 173134.68 (r/w/o: 173134.68/0.00/0.00) lat (ms,95%): 1.55
[ 1548s ] thds: 256 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00
[ 1549s ] thds: 256 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00
[ 1550s ] thds: 256 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00
......
[ 1577s ] thds: 256 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00
[ 1578s ] thds: 256 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00
[ 1579s ] thds: 256 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00
[ 1580s ] thds: 256 tps: 504768.91 qps: 504768.91 (r/w/o: 504768.91/0.00/0.00) lat (ms,95%): 0.75
[ 1581s ] thds: 256 tps: 820775.44 qps: 820775.44 (r/w/o: 820775.44/0.00/0.00) lat (ms,95%): 0.54
[ 1582s ] thds: 256 tps: 858924.56 qps: 858924.56 (r/w/o: 858924.56/0.00/0.00) lat (ms,95%): 0.46
[20 Dec 2022 5:52] MySQL Verification Team
Hello Fangxin Flou,

Thank you for the report and feedback.

regards,
Umesh
[13 Jun 2023 14:22] Marcos Albe
Possible dupe of https://bugs.mysql.com/bug.php?id=107308 ?