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