| 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: | |
| 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 | ||
[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 ?
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); ......