If the --innodb-optimize-keys mysqldump option was available with MariaDB I would use it when backing up and moving tables using mysqldump. It can also be used to shrink InnoDB table files on mysqld instances where "ALTER TABLE table_name ROW_FORMAT=Compact" does not result in fast index creation being used and where expand_fast_index_creation is not available so "OPTIMIZE TABLE table_name" and "ALTER TABLE table_name ENGINE=INNODB" do not use fast index creation.
Having support for expand_fast_index_creation would also be great, but I think there is value from just adding the pragmatic mysqldump option.
Applying the latest changes with fixes for the mysqldump option to MariaDB 10 was relatively easy. The original work and subsequent patches with tests having been created by Alexey Kopytov.
The default mysqldump is with --opt, implying --extended-insert, so one INSERT per table. It can be configured otherwise. (up until the max-packet-size - default 24M)
For LOAD DATA we'd need the mariadb client (or server - mysqldump isn't strictly for the consumption of mysql/mariadb client) to recognize a an inline version of LOAD DATA. Otherwise some form of multiple files like https://github.com/maxbube/mydumper/commits/master (that seems to be getting activity again).
Daniel Black
added a comment - - edited The default mysqldump is with --opt, implying --extended-insert, so one INSERT per table. It can be configured otherwise. (up until the max-packet-size - default 24M)
For LOAD DATA we'd need the mariadb client (or server - mysqldump isn't strictly for the consumption of mysql/mariadb client) to recognize a an inline version of LOAD DATA . Otherwise some form of multiple files like https://github.com/maxbube/mydumper/commits/master (that seems to be getting activity again).
In MDEV-24818 we hacked the code so that a multi-statement INSERT transaction from mysqldump will be accelerated using the MDEV-515 mechanism. The data loading can be accelerated seriously further by MDEV-24621.
Marko Mäkelä
added a comment - In MDEV-24818 we hacked the code so that a multi-statement INSERT transaction from mysqldump will be accelerated using the MDEV-515 mechanism. The data loading can be accelerated seriously further by MDEV-24621 .
git show mariadb-10.0.20:storage/innobase/include/univ.i|grep VERSION|head -3
#define INNODB_VERSION_MAJOR 5
#define INNODB_VERSION_MINOR 6
#define INNODB_VERSION_BUGFIX 25
MariaDB 10.0.20 claims to be based on the InnoDB from MySQL 5.6.25. That version includes Alter_inplace_info::RECREATE_TABLE, which had been added in MySQL 5.6.13 to support OPTIMIZE TABLE using the WL#6255 InnoDB online table rebuild algorithm whose original version was released as part of MySQL 5.6.8.
I think that the last missing piece to speed up data loading is MDEV-16281, to implement the multi-threaded creation of index trees.
Marko Mäkelä
added a comment - The Description feels a bit outdated.
git show mariadb-10.0.20:storage/innobase/include/univ.i|grep VERSION|head -3
#define INNODB_VERSION_MAJOR 5
#define INNODB_VERSION_MINOR 6
#define INNODB_VERSION_BUGFIX 25
MariaDB 10.0.20 claims to be based on the InnoDB from MySQL 5.6.25. That version includes Alter_inplace_info::RECREATE_TABLE , which had been added in MySQL 5.6.13 to support OPTIMIZE TABLE using the WL#6255 InnoDB online table rebuild algorithm whose original version was released as part of MySQL 5.6.8.
I think that the last missing piece to speed up data loading is MDEV-16281 , to implement the multi-threaded creation of index trees.
Note: To benefit from MDEV-24621, a special option --no-autocommit needs to be specified to mariadb-dump until MDEV-32250 makes it the default.
Marko Mäkelä
added a comment - Note: To benefit from MDEV-24621 , a special option --no-autocommit needs to be specified to mariadb-dump until MDEV-32250 makes it the default.
Task
Major
MDEV-11415 Remove excessive undo logging during ALTER TABLE…ALGORITHM=COPY
MDEV-34739 Implement DISABLE KEYS/ENABLE KEYS in Innodb
The default mysqldump is with --opt, implying --extended-insert, so one INSERT per table. It can be configured otherwise. (up until the max-packet-size - default 24M)
For LOAD DATA we'd need the mariadb client (or server - mysqldump isn't strictly for the consumption of mysql/mariadb client) to recognize a an inline version of LOAD DATA. Otherwise some form of multiple files like https://github.com/maxbube/mydumper/commits/master (that seems to be getting activity again).