What are the possible downsides or consequences of using ALGORITHM=INPLACE, LOCK=NONE when running alters on a table?

[simrah]

I've been trying to find the answer to this online for a while with no luck.

What are possible downsides or consequences to running an alter (such as add index, add column, drop index, drop column) with ALGORITHM=INPLACE, LOCK=NONE in mysql 8.0 innodb?

I figure there have to be some concerns or situations where you would not want to use LOCK=NONE at least.

If new rows are being added to the table as you run the alter to add an index, is it possible that some indexes wouldn't be created? I'm just wondering if I should be concerned about using these for all alters I run.

Thanks

[Alberto]

Some drawbacks of INPLACE algorithm are:

1. Long-running online DDL operations can cause replication lag in slaves. Online DDL operation must finish running on the master before it is run on the slave. Also, DML that was processed concurrently on the master is only processed on the slave after the DDL operation on the slave is completed.
2. larger https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_online_alter_log_max_size size extends the period of time at the end of the DDL operation when the table is locked to apply the data from the log.
3. At time can cause high IO usage for a larger table at high concurrency servers ( Aggressive in terms of resource consumption)

Check the list of https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html

Before running a DDL https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html on a large table, check whether the operation is fast or slow as follows:

1. Clone the table structure.
2. Populate the cloned table with a small amount of data.
3. Run the DDL operation on the cloned table.
4. Check whether the “rows affected” value is zero or not. A nonzero value means the operation copies table data, which might require special planning. For example, you might do the DDL operation during a period of scheduled downtime, or on each replica server one at a time.