I don't understand. Why cannot ALGORITHM=INPLACE prevent instant changes?

MDEV-13134 introduced two proper subsets of ALGORITHM=INPLACE: ALGORITHM=NOCOPY and ALGORITHM=INSTANT.

As noted in our documentation, the ALGORITHM clause (other than ALGORITHM=COPY) specifies the least efficient algorithm that the user is willing to accept.

There currently is no mechanism for specifying the most efficient algorithm.

Two cases of ALGORITHM=INSTANT affect the data file format of InnoDB: MDEV-11369 (instant ADD COLUMN…LAST) and MDEV-15562 (instant DROP COLUMN or changing the order of columns). A user might want to be able to export data files to older MariaDB Server versions or to MySQL, or a user might want to keep the data file in the ‘canonical’ format in order to work around bugs (such as MDEV-19783) or to ensure that there will be no performance overhead for DML operations. (Instant DROP COLUMN of fixed-size NOT NULL columns will incur storage overhead, compared to rebuilding the table.)

Related to this, I think that it could be useful to have something that identifies the actual format of the InnoDB data files (‘normal’, ‘instant add to the end’, ‘instant add/drop/reorder’) of tables and partitions. It is not currently being reflected anywhere.

Maybe refining the ALGORITHM clause is wrong for this, and we’d want something like ALTER COMPATIBLE TABLE, or we would want to introduce a settable parameter which would cause the refusal of certain cases of ALTER TABLE…ALGORITHM=NOCOPY or ALTER TABLE…ALGORITHM=INSTANT.

Suggestions for better names are welcome, but I would consider reintroducing the once-removed innodb_file_format parameter in MariaDB Server 10.3 onwards, with the following options:

• innodb_file_format=canonical (0): do not allow instant add/drop/reorder, to maintain format compatibility with MariaDB 10.x and MySQL 5.x
• innodb_file_format=add (default in 10.3): allow instant add, but not drop/reorder, to maintain compatibility with MariaDB 10.3
• innodb_file_format=drop (available and default in 10.4+ only, default): allow instant add/drop/reorder
• innodb_file_format=strict_canonical: Like canonical, but whenever ALTER TABLE is executed, and a table (or partition) is not in the canonical format, force the table to be rebuilt.
• innodb_file_format=strict_add: Like add, but whenever ALTER TABLE is executed, and a table (or partition) is not in the 10.3-compatible format, force the table to be rebuilt.
• innodb_file_format=strict_drop: identical to drop (could be added in 10.4+ for symmetry, and to prepare for MDEV-16291 in a future server version).

I feel that "ALGORITHM clause specifies the least efficient algorithm that the user is willing to accept" is a rather unexpected semantics. In particular, because ALGORITHM=COPY means just that, COPY.

May be we'd better change it to say that ALGORITHM=XXX means exactly that, XXX. And to have "least efficient" one can specify ALGORITHM >= INPLACE ? (or <=, whatever ordering of algorithms we can come up with). Same semantics for the LOCK clause.

Alternatively, I was thinking of ALGORITHM=INPLACE,INSTANT,NOCOPY but it's more verbose and, I feel, redundantly specific, a user generally only needs "at least as efficient as XXX", not a list of specific algorithms.

to summarize, if "ALGORITHM" is an ENUM, than one can use <=, if it's a SET one has to list all options. But both approaches are well known to MariaDB users, nothing new to learn here.

serg, could you please give a concrete proposal that would be compatible with replication from older versions?

Yes. One of

• redefine ALGORITHM=XXX to mean "use exactly algorithm XXX, nothing else"
• allow to specify multiple alternative algorithms, ALGORITHM=INPLACE,COPY,NOCOPY

or

• redefine ALGORITHM=XXX to mean "use exactly algorithm XXX, nothing else"
• define ordering for algorithm values, e.g. COPY > INPLACE > NOCOPY > INSTANT
• allow to specify ALGORITHM>=XXX and ALGORITHM<=XXX

the second syntax is shorter to type, but it implies we'll always be able to order algorithms, even in the future. Sounds like a rather risky assumption.

Both proposals extend the current syntax, so replication from older versions will work normally.

Let us take an example: replication of the following statement to a 10.3 server:

ALTER TABLE t ADD COLUMN c INT;

GeoffMontee provided the following motivation in the Description:

We may want to add a way for users to avoid putting tables into the non-canonical INSTANT format, if they would like.

serg, you did not cover SET alter_algorithm at all. It was introduced in 10.3 by MDEV-13134. You only mentioned changes to the processing of an explicit ALGORITHM clause, which would override the session variable alter_algorithm.

The user could want to use the INSTANT format only on a specific set of nodes that are being replicated from or to, by setting a configuration parameter, which could be alter_algorithm or something else (such as the repurposed innodb_file_format that I suggested).

Also, I have trouble understanding the following part of the proposal:

"use exactly algorithm XXX, nothing else"

That seems to contradict with the following in MDEV-13134:

With respect to the allowed operations, ALGORITHM=INSTANT is a subset of ALGORITHM=NOCOPY which is a subset of ALGORITHM=INPLACE which is a subset of ALGORITHM=COPY.

ALGORITHM=INPLACE was introduced in MySQL 5.6 and MariaDB 10.0 already. It can indeed cover 3 fundamentally different classes of operations:

• instantaneous (like ALGORITHM=INSTANT): renaming columns, adding/removing FOREIGN KEY constraint (if foreign_key_checks=0), starting with MySQL 5.7 or MariaDB 10.2.2: extending some VARCHAR
• true in-place operation (like ALGORITHM=NOCOPY): DROP INDEX, ADD INDEX of secondary indexes
• table rebuild (copying it; contradicting the INPLACE keyword): add/drop/reorder columns, add/drop PRIMARY KEY

For ALGORITHM=INSTANT or ALGORITHM=COPY, the proposal “use exactly algorithm XXX” is clear, and does not change anything.

But, what does “use exactly algorithm NOCOPY” mean? That the operation must consume some time that is proportional to the size of the table? Should we reject the following (if the column is only being renamed)?

ALTER TABLE t CHANGE c d INT, ALGORITHM=NOCOPY;

If yes, that would break replication from older versions.

Note: I do not think that we can redefine ALGORITHM=NOCOPY to only cover those operations that do not require changes to the file format (MDEV-11369, MDEV-15562). The following is currently being accepted by 10.3 and 10.4 GA versions, and if we started to reject that, we would break replication from those versions:

ALTER TABLE t ADD e INT, ALGORITHM=NOCOPY;

Also, what does “use exactly algorithm INPLACE” mean? That it should imply the FORCE clause, which was introduced in MariaDB 5.5.42, to ensure that the table will be rebuilt? But, that would again break the replication of the following from older versions:

ALTER TABLE t CHANGE c d INT, ALGORITHM=INPLACE;

ALTER TABLE t CHANGE d c INT, FORCE, ALGORITHM=INPLACE;

Assuming that the type of the column does not change, the first operation is instantaneous. The second operation will cause the table to be copied. And InnoDB could refuse table-rebuilding ALGORITHM=INPLACE in some cases, most notably, if multiple FULLTEXT INDEX exist in the table. Furthermore, not all cases of table-rebuilding ALGORITHM=INPLACE allow LOCK=NONE.

In summary, the proposal would seem to not only make replication (and binlog roll-forward) considerably slower (by replacing schema-only changes with table rebuild operations), but also cause it to abort because previously valid operations could be rejected.

I would appreciate feedback on my proposal of introducing a configuration parameter that would restrict the storage engine’s choice of file formats.

I did not mean metadata only changes. They're basically always done without any data changes by updating the metadata only.
It isn't particularly logical behavior, but changing that is not part of this MDEV.

But, what does “use exactly algorithm NOCOPY” mean? That the operation must consume some time that is proportional to the size of the table?

Not really. It means data-changing ALTER that doesn't rebuild the primary key and is not deferred (does not use non-canonical INSTANT format).

COPY means upper-layer copy, copy_data_between_tables().

INPLACE is a superset of NOCOPY, data-changing non-deferred ALTER, even if it copies the table internally (so, yes, ALGORITHM=INPLACE,NOCOPY is redundant, unfortunately).

INSTANT means deferred ALTER operations.

session variable alter_algorithm — I'd rather made it a SET too, not an ENUM.

serg, what you think might be logical and what the MariaDB Server 10.3 and 10.4 GA releases implement are two different things.
In particular:

[algorithm NOCOPY] means data-changing ALTER that doesn't rebuild the primary key and is not deferred (does not use non-canonical INSTANT format).

Currently, the following is accepted by MariaDB Server 10.3 and 10.4:

--source include/have_innodb.inc

CREATE TABLE t1 (a SERIAL) ENGINE=InnoDB;

ALTER TABLE t1 ADD b INT, ALGORITHM=NOCOPY;

DROP TABLE t1;

You seem to be suggesting that such operations should be rejected. I think that we should ensure that replication from older versions to newer ones always works. If we changed the interpretation of ALGORITHM=NOCOPY in your suggested way in a later release (major release or point release), replication would be broken.

The MDEV-13134 idea is that ALGORITHM (other than COPY) specifies an upper bound for the algorithmic complexity. If a more time-consuming algorithm is needed, the operation will be refused. In my opinion, the ‘allowed file formats’ is a separate dimension and cannot be captured by ALGORITHM.

While I have a fairly good reason to believe that the corruption bug in MariaDB 10.3 was fixed in MDEV-19916, users could want to force their data files to remain in the canonical format for two major reasons:

• ability to export data files to MariaDB 10.2, MySQL 5.7 or older
• avoiding any overhead for DML operations

If we make a file format parameter part of the syntax, we could both control the choice of formats and detect the current format of the table:

ALTER TABLE t FORMAT='10.2'; -- rebuilds the table if it was not in a 10.2-compatible format

SET alter_format='10.3';

ALTER TABLE t DROP COLUMN c, ALGORITHM=NOCOPY; -- error: 10.3 does not support instant DROP COLUMN

ALTER TABLE t FORMAT='10.2', ALGORITHM=NOCOPY; -- issues an error only if the table is in the 'instant' format

ALTER TABLE t FORMAT='10.3', ALGORITHM=NOCOPY; -- issues an error only if the table is not compatible with MariaDB Server 10.3

SET alter_format=DEFAULT;

ALTER TABLE t DROP COLUMN c, ALGORITHM=INSTANT; -- succeeds in 10.4 or later

The alter_format=DEFAULT or FORMAT=DEFAULT would be to adjust the file format if needed, to avoid table rebuild.
Setting the format to '10.2' would disable both MDEV-11369 (instant ADD COLUMN) and MDEV-15562 (instant DROP COLUMN or reordering columns) for InnoDB, requiring table rebuild for these operations. Setting the format to '10.3' would allow instant ADD COLUMN, but not MDEV-15562.

I primarily want us to have a logical, consistent, storage engine independent ALTER TABLE syntax. Adding even more InnoDB specific keywords is hardly a step in the right direction.

Currently

• ALGORITHM=COPY means "create a new table copy the data over, rename"
• ALGORITHM=INPLACE means "not COPY"
• ALGORITHM=NOCOPY does not mean "not COPY" at all, it means "INPLACE, but don't rebuild a primary key", it's InnoDB-specific thing and a subset of INPLACE, not mutually exclusive.
• ALGORITHM=INSTANT means metadata-only changes and new table format with a deferred row changes, a subset of NOCOPY.

I'd prefer them to be either strictly ordered and inclusive (every value includes all following values, COPY includes INPLACE) or exclusive (INPLACE does not include NOCOPY, NOCOPY does not include INSTANT). With clearly defined engine-independent semantics (NOCOPY is weird, INSTANT is fine).

If replication is your only concern, it's easy to make the replication thread to ignore the ALGORITHM clause completely. It won't fix mysqlbinlog|mysql case, but replication will work no matter what we change here.

serg, I feel that it would be counter-intuitive to redefine ALGORITHM=INSTANT to refer only to those operations that change the data format of the table.

As far as I understand, renaming a column, changing the collation of a non-indexed column, or extending the maximum length of a VARCHAR column are examples of operations that ALGORITHM=INSTANT could allow for any storage engine. As a matter of fact, some of these operations are currently supported by multiple storage engines.

For many users, the file format does not matter, but the algorithmic complexity or the expected duration of the operation does. The current definition of ALGORITHM=INSTANT should be intuitive to them.

At the same time, some users might want to control the file format as well. That could be provided by introducing a storage engine specific parameter, if extending the ALTER TABLE syntax is not desirable. However, in my opinion innodb_file_per_table and innodb_encrypt_tables were mistakes, and there should be no engine-specific ‘side channels’ that modify the operation of ALTER TABLE.

One more use case for this feature could be replication:

• We might want to use instant ADD COLUMN or DROP COLUMN on a master and most replicas.
• But we might also want to set up some replicas that have the tables in the canonical format.

Especially after instant DROP COLUMN, the performance of subsequent UPDATE and INSERT can suffer compared to the canonical format, because the clustered index records would keep storing dummy garbage values for the dropped columns. So, as a maintenance task, we might want to replace instances where the ‘dirty’ instant ALTER TABLE was invoked with ‘clean’ replicas.

serg, for this use case, I think that a non-replicated InnoDB parameter (such as repurposing innodb_file_format like I suggested above) would work best. Do you agree?

I think that our decision on this must be consistent with other potential changes in this area, such as the MDEV-18845 proposal to introduce SET alter_lock, extending the MDEV-13134 introduction of alter_algorithm.

Based on a discussion with serg, I intend to proceed with my plan to repurpose the deprecated parameter innodb_file_format. The alter_algorithm parameter could be clarified or revised as part of MDEV-18845 later.

I am thinking to revive the deprecated innodb_file_format as an ENUM, with the following names, starting with MariaDB 10.3:

• innodb_file_format=barracuda (0): do not allow instant add/drop/reorder, to maintain format compatibility with MariaDB 10.x and MySQL 5.x
• innodb_file_format=strict_barracuda: Like barracuda, but whenever ALTER TABLE is executed, and a table (or partition) is not in the canonical format, force the table to be rebuilt.
• innodb_file_format=add (default in 10.3): allow instant add (MDEV-11369), but not drop/reorder, to maintain compatibility with MariaDB 10.3
• innodb_file_format=strict_add: Like add, but whenever ALTER TABLE is executed, and a table (or partition) is not in the 10.3-compatible format, force the table to be rebuilt.
• innodb_file_format=drop (available and default starting with 10.4): allow instant add/drop/reorder (MDEV-15562)
• innodb_file_format=strict_drop: identical to drop (to prepare for MDEV-16291 in a future server version).

In old configuration files, users can have innodb_file_format=barracuda, which was necessary to unlock the creation of ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED tables until that option became the default in MariaDB 10.2 (and MySQL 5.7). The value innodb_file_format=antelope was accepted in the past to limit InnoDB to the MySQL 5.0.3 ROW_FORMAT=REDUNDANT and ROW_FORMAT=COMPACT, but it is unlikely that anyone would explicitly configure that.

Because ENUM values cannot have multiple names, the name innodb_file_format=barracuda that was originally introduced in the Oracle-distributed InnoDB Plugin for MySQL 5.1 would be used. It is a rather accurate and canonical name of the data file format, after all.

Unfortunately, the ENUM values add and drop would have to quoted:

SET GLOBAL innodb_file_format='add'; -- works

SET GLOBAL innodb_file_format=add; -- syntax error!

Due to this, we’d better use different format names.

I hope you would consider different names even regardless the syntax error. In my opinion, it should either be a real name , something abstract (like barracuda), or something descriptive and accurate. "Add" or "drop" are not descriptive or accurate names for the meaning they are supposed to carry, they are just confusing. File format is a lot more than the way one particular variant of one particular SQL command is executed, it can't be named based on that. File format "add" – if anything, I would guess that it has something to do with how the underlying file gets written to, like always adding contents to the file, never replacing or removing, something like that; but certainly not that it's about how instant ALTER TABLE ... ADD is allowed to be.

I am considering to use append and mapped instead of ‘add’ or ‘drop’. Here is the 10.3 version. The commit message includes some reasoning for these names.

After some more discussions, I am suggesting the following names:

--echo #do not allow MDEV-11369 instant ADD / MDEV-15562 instant DROP/reorder

set global innodb_file_format=barracuda;

--echo # ditto, and if instant ADD/DROP had been used in the past, ALTER TABLE would rebuild the table

set global innodb_file_format=force_barracuda_compatible;

--echo # do not allow MDEV-15562 instant DROP/redorder

set global innodb_file_format=10_3; 

--echo # ditto, and any subsequent ALTER TABLE would rebuild to the requested format if needed

set global innodb_file_format=force_10_3_compatible;

--echo # allow instant ADD/DROP/reorder, but not possible future format changes

set global innodb_file_format=10_4; 

--echo # same as 10_4, at least for now

set global innodb_file_format=force_10_4_compatible;

I would use _ instead of . so that quotes around the parameter values can be avoided.

We might want to omit the force_…_compatible variants and always enforce the requested format in ALTER TABLE or OPTIMIZE TABLE. That could cause a negative surprise for users who might have a leftover innodb_file_format=barracuda in their configuration files and could have executed instant ADD/DROP in an earlier 10.3 or 10.4 version. However, they should have noticed the deprecation warning for that parameter.

For future file format changes after MariaDB Server 10.5, we might introduce another parameter to control whether instant ADD/DROP is allowed, or we could introduce further values for innodb_file_format. In my vision, we could extend the 10.4 metadata record format with further things (persistent COUNT in MDEV-18188, or secondary index metadata for simpler IMPORT in MDEV-11658), and users could want to use that format while preventing instant ADD/DROP/reorder column.

From the user point of view, the file format might be an uninteresting implementation detail. What the user wants is a guarantee that there will be no overhead for future DML operations. Instant ADD COLUMN or DROP COLUMN always incurs some of that. So, reviving the deprecated parameter innodb_file_format might be a bad idea, especially given the future plans to improve the .ibd file format in ways that have nothing to do with instant ALTER TABLE operations.

I revise my suggestion to introduce a new parameter innodb_instant_alter_column_allowed:

--echo #Do not allow MDEV-11369 instant ADD / MDEV-15562 instant DROP/reorder

--echo #If a table already was in conflicting format, force a rebuild on any subsequent ALTER TABLE.

--echo #Coincidentally, IMPORT TABLESPACE should work to any MariaDB 10.x version.

SET GLOBAL innodb_instant_alter_column_allowed=no;

--echo #Do not allow MDEV-15562 instant DROP/reorder but do allow MDEV-11369

--echo #If a table already was in conflicting format, force a rebuild on any subsequent ALTER TABLE.

SET GLOBAL innodb_instant_alter_column_allowed=add_last;

--echo #Allow any instant ALTER TABLE.

SET GLOBAL innodb_instant_alter_column_allowed=add_drop_reorder;

The maximum and default values would be as follows:

• 10.3: innodb_instant_alter_column_allowed=add_last (other allowed values: no)
• 10.4 and 10.5: innodb_instant_alter_column_allowed=add_drop_reorder (other allowed values: no, add_last)

I pushed this to 10.3 and merged up to 10.5. Starting with 10.3.24, we introduce the parameter with the default value add_last, and the non-default value never would introduce a constraint. Starting with MariaDB 10.4.14, 10.5.3, and later, we introduce the parameter with the default value will be add_drop_reorder.

--echo #Do not allow MDEV-11369 instant ADD / MDEV-15562 instant DROP/reorder

--echo #If a table already was in conflicting format, force a rebuild on any subsequent ALTER TABLE.

--echo #Coincidentally, IMPORT TABLESPACE should work to any MariaDB 10.x version.

SET GLOBAL innodb_instant_alter_column_allowed=never;

--echo #Do not allow MDEV-15562 instant DROP/reorder but do allow MDEV-11369

--echo #If a table already was in conflicting format, force a rebuild on any subsequent ALTER TABLE.

SET GLOBAL innodb_instant_alter_column_allowed=add_last;

--echo #Allow any instant ALTER TABLE.

SET GLOBAL innodb_instant_alter_column_allowed=add_drop_reorder;