I think that similar to what was done already in MDEV-11415 and MDEV-515, it would be best to disable row-level undo logging for LOAD into an empty table. MDEV-515 allows to replace the row-level undo logging with table-level logging and locking. We definitely would not want to have an extremely slow row-by-row rollback of the bulk insert in case the server was killed or the operation failed. So, table-level logging seems to be the way to go.

As noted in MDEV-24813, the interaction of LOCK TABLE and transactions is rather broken.

If I understood it correctly, for MDEV-33625 we’d want a way to enable MDEV-515 but disable MDEV-24621 for data loads. If I got it correctly, the current idea would be to create the clustered index row by row, and then create the indexes afterwards (MDEV-5171).

the table is loaded in the same order as SELECT * INTO OUTFILE, i.e in primary key order, or whatever Innodb thinks is primary key (unique not null?). So it is a good order already . If DISABLE/ENABLE KEYS worked in Innodb out-of-the-box, this would be the best thing ever, then nothing would need to be done. Otherwise, we might parse SHOW CREATE TABLE output and delay creation of all secondary indexes, and foreign key constraints until after LOAD DATA finishes. This seems good at least "on paper", percona has that option, so why can't we.

MDEV-24621 just did not work. temp is not infinite, sort buffer size is not infinite, but tables to be loaded in LOAD DATA can be hundreds of GB, that's normal for production quality databases.

wlad, is there any LOAD DATA syntax that asserts that the data is sorted by the primary key? As far as I can tell, heap organized tables (such as MyISAM or Aria) do not care about the order, but InnoDB certainly would care and could skip a merge sort step for the clustered index, which would allow it to build the index page by page, with fewer buffer pool operations and less log records written.

There is no such syntax. In vast majority of cases mariadb-import cares about, the output for Innodb is naturally sorted by primary key already, because it comes from "SELECT * from Innodb_table into outfile" order, for "SELECT *" it would be silly not to take clustered index to for this operation (although it is possible, and I have seen Innodb sorting by another secondary key in SELECT *, I'd think it is very rare in practice)

There is an option in mariadb-dump to force output by primary key order --order-by-primary , and perhaps it makes sense to always do that for Innodb.

MyISAM and Aria are not really interesting here, they store as they want to, the objective here is to improve Innodb load times.

About this page-by-page optimization, I filed MDEV-34703 previously. I can't take this optimization, it does not work for me, on Linux, on Windows, anywhere. It is much slower as no-optimization at all, also it causes OOM on Linux, so, a no-go

I see that some regression tests are failing. That would have to be fixed before I can review this.

MDEV-34703 already addresses this issue and avoids the temporary file usage for clustered
index and avoids writing undo log for the load data statement. It reduces the I/O, temporary file usage and reduces the time significantly
for the load statement during bulk insert mode.

Technically, it is not exactly a duplicate of MDEV-34703, it would be better if everyone could benefit from reduced purge and bufferpool activity, without setting 2 session variables.