Bug #107308 ALTER TABLE...ALGORITHM=INSTANT stalls on AHI drop
Submitted: 16 May 2022 21:50 Modified: 10 Aug 2022 12:25
Reporter: Marcos Albe (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[16 May 2022 21:50] Marcos Albe
Description:
When doing ALTER TABLE x ADD COLUMN y INT NOT NULL DEFAULT 'whatever' ALGORITHM=INSTANT, depending on contents of AHI it will be really instantaneous or not.  When it's not we can see the ALTER thread stuck in btr_drop_ahi_for_table:
      1 ::??,btr_drop_ahi_for_table,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,::??,mysql_alter_table,Sql_cmd_alter_table::execute,mysql_execute_command,dispatch_sql_command,dispatch_command,do_command,::??,::??,start_thread,clone

First run takes minutes:
mysql> ALTER TABLE `documents`  ADD COLUMN `col1` DATETIME DEFAULT NULL AFTER `col2`, ADD COLUMN `col3` VARCHAR(36) DEFAULT NULL AFTER `col1`, ADD COLUMN `col21` VARCHAR(32) NOT NULL DEFAULT '' AFTER `col3`,  ALGORITHM=INSTANT;
Query OK, 0 rows affected (13 min 3.82 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
Later run takes 50ms:
mysql> ALTER TABLE `documents`  ADD COLUMN `col4` DATETIME DEFAULT NULL AFTER `col21`, ADD COLUMN `col5` VARCHAR(36) DEFAULT NULL AFTER `col4`, ADD COLUMN `col22` VARCHAR(32) NOT NULL DEFAULT '' AFTER `col5`,  ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

This is because there's nothing in the AHI for the table the second time.

How to repeat:
Don't have a script to reproduce, but you can see the code and with a large AHI it will happen:
Guess AHI contents should be versioned so queries can access only fresh entries in the hash table, and the drop of the old versions could happen in the background.-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 11027, free list len 452002, seg size 463030, 292150 merges
merged operations:
 insert 326646, delete mark 381719, delete 80763
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 50468303, node heap has 296 buffer(s)
Hash table size 50468303, node heap has 1083 buffer(s)
Hash table size 50468303, node heap has 91 buffer(s)
Hash table size 50468303, node heap has 2 buffer(s)
Hash table size 50468303, node heap has 1 buffer(s)
Hash table size 50468303, node heap has 215 buffer(s)
Hash table size 50468303, node heap has 661 buffer(s)
Hash table size 50468303, node heap has 258089 buffer(s)
1213000.00 hash searches/s, 476000.00 non-hash searches/s

Suggested fix:
Guess AHI contents should be versioned so queries can access only fresh entries in the hash table, and the drop of the old versions could happen in the background.
[16 May 2022 21:57] Marcos Albe
Corrected title (INSTANCE should be INSTANT)
[16 May 2022 21:57] Marcos Albe
Corrected title (INSTANCE should be INSTANT)
[17 May 2022 13:11] MySQL Verification Team
Hi Mr. Albe,

Thank you very much for your bug report.

However, what you describe is exactly how Adaptive Hash Indexing is supposed to work , since it was introduced. What you are suggesting are changing in the design of AHI.

We can accept that, but only as a feature request or performance improvement. If you agree with this conclusion that we can verify your report as a feature request.

Thanks in advance.
[18 Jun 2022 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[9 Aug 2022 21:52] Sveta Smirnova
Yes, this will be a nice feature. I suggest at least add a warning that this DDL causes the process of removing data from the AHI.
[9 Aug 2022 23:40] Marcos Albe
Hello Sinisa,

Thanks, as mentioned by Sveta, this will indeed make for a good FR; A warning would be nice to have, and if re-architecting is feasible (now that CI development is rolling) it would be great to do this in a lazy way (in the background).  

Thanks for the attention to the issue, and apologies for slow response!
Best,
Marcos
[10 Aug 2022 12:25] MySQL Verification Team
Hi All,

This is, indeed, a very small, but useful feature request.

Verified as aa feature request .......