Why is my MariaDb not adding a column to a large table using the INSTANT algorithm

I have a huge table in a MariaDb (10.4.10-MariaDB-1:10.4.10+maria~bionic) and I am adding a new column using

alter table Appointment add column responsible_organization varchar(256);

The existing table is this:

CREATE TABLE `Appointment` (
    `id` VARCHAR(256) NOT NULL,
    `version` VARCHAR(24) NOT NULL,
    `repetition_ref` VARCHAR(256) NULL DEFAULT NULL,
    `type` VARCHAR(256) NULL DEFAULT NULL,
    `comment` VARCHAR(2048) NULL DEFAULT NULL,
    `description` VARCHAR(2048) NULL DEFAULT NULL,
    `end` DATETIME NULL DEFAULT NULL,
    `start` DATETIME NULL DEFAULT NULL,
    `status` VARCHAR(256) NULL DEFAULT NULL,
    `statuschangedate` DATETIME NULL DEFAULT NULL,
    `deliverystatus` VARCHAR(256) NULL DEFAULT NULL,
    `reasoncancelled` VARCHAR(256) NULL DEFAULT NULL,
    `visit_type` VARCHAR(256) NULL DEFAULT NULL,
    `modified_db_time` TIMESTAMP(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3),
    `markedasdeleted` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`, `version`),
    INDEX `FKc4f6e4y3ftaya162pwf7v4uj4` (`deliverystatus`),
    INDEX `FKeuhxsh83rlweegn404penommb` (`reasoncancelled`),
    INDEX `FK5xmurewn61wf4n3of5yx2nsmg` (`visit_type`),
    INDEX `modified_db_time` (`modified_db_time`),
    CONSTRAINT `FK5xmurewn61wf4n3of5yx2nsmg` FOREIGN KEY (`visittype`) REFERENCES `Coding` (`id`),
    CONSTRAINT `FKc4f6e4y3ftaya162pwf7v4uj4` FOREIGN KEY (`deliverystatus`) REFERENCES `CodeableConcept` (`id`),
    CONSTRAINT `FKeuhxsh83rlweegn404penommb` FOREIGN KEY (`reasoncancelled`) REFERENCES `CodingDt` (`id`)
)
;

As far as I read the MariaDb documentation it should choose the most efficient algorithm if I don't specify any. I would expect it to use INPLACE at the minimum. But when I run it i can see in the process list that it is running with the state "Copy to tmp table". So this is the COPY algorithm right?

I then tried to force it to use INSTANT as sugested by @o-jones. That gave me this output:

MariaDB [mydb]> alter table Appointment add column responsibleorganisation varchar(256), ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY

Weird, since I am adding a column.

I am wondering if it has something to do with the table being created on an older version of MariaDb and not having been rebuilt recently. I have found references to this being an issue for tables with old style temporal columns.

The variables old_alter_table and alter_algorithm both have the value DEFAULT

My table has 110+ million rows, so I would have liked to find a way to optimize this.

Any ideas?

Solution 1:

MariaDB Server 10.4.10 was released over 2 years ago, in November 2019. Is this repeatable with a more recent version? There have been some fixes to ALTER TABLE since then.

If the problem is repeatable with the latest version in the 10.4 series, I would suggest that you file a bug report at https://jira.mariadb.org with a minimal reproducible test case (CREATE TABLE and ALTER TABLE statements).

With a more recent version, the answer could have been that MDEV-20590 introduced a way to disable instant operations that involve changing the data file format. SET GLOBAL innodb_instant_alter_column_allowed=never; would make ADD COLUMN always rebuild the table, like it did before MariaDB Server 10.3.

Why is my MariaDb not adding a column to a large table using the INSTANT algorithm

Solution 1:

Related

Recent Posts