Hi Rhys, On Fri, Mar 17, 2023 at 1:04 PM <Rhys.Campbell@swisscom.com> 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