1

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

Improve this question
2
  • 1
    If the requested Algorithm cannot be performed, it will refuse to even start the Alter. For example, Altering the Primary Key must be done with COPY. Lock=NONE has some caveats (see the manual), but they may be acceptable to you.
    – Rick James
    May 25, 2022 at 3:51
  • 1
    @RickJames It depends on server SQL mode. Server may either fail the query or execute it with the warning similar to "ALGORITHM=INPLACE is not applicable, COPY is used".
    – Akina
    May 25, 2022 at 5:02

1 Answer 1

Reset to default
2

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 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 Online DDL Limitations

Before running a DDL operation 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.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.