Bug #107308 ALTER TABLE...ALGORITHM=INSTANT stalls on AHI drop
Submitted: 16 May 21:50 Modified: 20 Jun 12:05
Reporter: Marcos Albe Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[16 May 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 21:57] Marcos Albe
Corrected title (INSTANCE should be INSTANT)
[16 May 21:57] Marcos Albe
Corrected title (INSTANCE should be INSTANT)
[17 May 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 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".