maria-discuss team mailing list archive

[Marko Mäkelä <marko.makela@xxxxxxxxxxx>]

Hi Rhys,

On Fri, Mar 17, 2023 at 1:04 PM <Rhys.Campbell@xxxxxxxxxxxx> wrote:
> I'm back working with MariaDB again after a break of some years. While InnoDB Online-DDL had appeared when I was working with it, it was still sometimes a challenge to manage schema changes for very large tables.

One pet hate already back in the MySQL 5.6.8 times when I had
implemented WL#5526 (online ADD INDEX) and WL#6255 (online table
rebuild) was something that was finally fixed in MariaDB 10.8 as
https://jira.mariadb.org/browse/MDEV-11675 (Lag free ALTER). I think
that this was the main reason to have tools like
pt-online-schema-table, to reduce the replication lag. With
MDEV-11675, the replicas will start ALTER operation simultaneously
with the primary server, and eventually apply a "commit" or "rollback"
event based on the final outcome.

Another old problem was "fake duplicates", fixed in
https://jira.mariadb.org/browse/MDEV-15250 (MariaDB 10.6.8), by
replicating concurrent DML during InnoDB transaction commit and not
earlier. The implementation was great work by my colleague Thiru.

You should also be aware of the instant ADD column (MDEV-11369,
MariaDB 10.3) and instant drop/reorder column (MDEV-15562, MariaDB
10.4). Something similar to this was implemented in MySQL 8.0 a few
years later. I would not necessarily make extensive use of these
(especially instant DROP), because of additional storage and access
overhead. In a perfect world, there would be resources to develop an
InnoDB-like storage engine with a saner file format that would allow
the data file to be eventually converted into a canonical format with
no "instantness overhead".

Another motivation for online schema change tools might be that many
operations were not supported natively. In MariaDB 10.4, much of this
was fixed (there is a table in
https://jira.mariadb.org/browse/MDEV-11424). For some changes, such as
changing column data types in a way that requires some data
conversion, the non-native ALGORITHM=COPY is still needed. We do have
an online variant of that under development; see
https://jira.mariadb.org/browse/MDEV-16329.

> Is it comparable to MSSQL (which I found to be pretty good for tables in the many tens of GB)?

I hope that others can say something about that.

Best regards,
Marko
-- 
Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation