MySQL Bugs: #107308: ALTER TABLE...ALGORITHM=INSTANT stalls on AHI drop

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

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.

Corrected title (INSTANCE should be INSTANT)

Corrected title (INSTANCE should be INSTANT)

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.

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".

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.

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

Hi All,

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

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