MySQL Bugs: #106357: Alter table add column with algorithm=instant halts all other queries

Bug #106357	Alter table add column with algorithm=instant halts all other queries
Submitted:	2 Feb 2022 12:35	Modified:	2 Feb 2022 13:52
Reporter:	Sunny Nagra	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.27	OS:	Red Hat (CentOS 7)
Assigned to:		CPU Architecture:	x86 (64)

Description:
I have a customer who has MySQL 8.0.27 installed. 

Whenever, we run an ALTER TABLE ADD COLUMN, ALGORITHM=INSTANT, it starts to add the column(s), but towards the end of the alter, something is issued and all other irrelevant queries that aren't even touching the DB in question will end up in 'Opening tables' state and will stay there until the operation is complete. 

This severely impacts live production operation and brings the customers site down. 

I'm not sure if this is a known bug, but I couldn't find anything online. 

How to repeat:
Run ALTER TABLE <table_name> ADD COLUMN <any number of columns>, ALGORITHM=INSTANT;

Ensure other DML queries are running at the same time and frequently, then keep an eye on the process list to see these queries in the 'Opening tables' state. 

Suggested fix:
I would suggest to see if this assumption of FLUSH TABLES is required on all tables or just for the table that is being modified?

Hi Mr. Nagra,

Thank you for your bug report.

However, we have not been able to repeat the behaviour that you reported.

We have run an instant DDL which added a new column and tried to run a number of queries on the number of unrelated tables and did that without any single problem. 

What you are reporting is not a bug, but evidently a problem of the insufficient tuning of the MySQL server. You do not have sufficient caches for the table or sizes of various relevant buffers.

There are many other configuration variables that are not tuned well, but this is a metterr of how you set a configuration and it is not our bug.

Can't repeat.

What caches are you referring to? 

table_open_cache, table_definition_cache? 

Could you explain the flow behind how the instant DDL works behind the scenes? Does it issue any FLUSH TABLES etc?

Hello Mr. Nagra,

Yes, both of those caches, but not only those ..... Many other general settings, with many  InnoDB settings as well ...... This is all explained in our Reference Manual, which you can find on dev.mysql.com site.

No, tables are not flushed with instant DDLs.

Please, do note that this is a forum for true MySQL bugs and not a forum for getting answers to the  questions. Forum where you can ask questions is:

http://forums.mysql.com