Description:
Hi,
In the util.loadDump utility mysql-shell can be configured to delay index creation on tables until the data load has completed(deferTableIndexes). Once all tables are loaded, indexes will then be added to each table on the database.
While doing some testing I notice that on the same configuration for tpcc 6000 wh, myloader with `--innodb-optimize-keys` enabled will complete faster. Based on my investigation this seems to be due to --innodb-optimize-keys defaulting to `AFTER_IMPORT_PER_TABLE`, which means once a table is loaded, its associated indexes will be added; it will not wait for all tables to be imported before adding indexes.
> .. option:: --innodb-optimize-keys
> Creates the table without the indexes and it adds them at the end. Options: AFTER_IMPORT_PER_TABLE and AFTER_IMPORT_ALL_TABLES. Default: AFTER_IMPORT_PER_TABLE
https://github.com/mydumper/mydumper/blob/master/docs/myloader_usage.rst#execution-options
Using AFTER_IMPORT_PER_TABLE allows DDLs for tables which have completed loading start before the full dump is complete. For scenarios where some tables in the load are a lot larger than others, this will allow the load to complete faster.
How to repeat:
mysqlsh --host=<HOST> -u user -p --execute util.loadDump("/logical/mysqlsh", {dryRun: false, schema: "tpcc_8000", threads: 30, progressFile: "~/6000wh_progress_custom_chunk_new.json", showProgress: true, deferTableIndexes: "all", maxBytesPerTransaction: "100M", loadUsers: false})
myloader --user=user --password=passwrd --host=<HOST> --port=3306 --database=tpcc_8000 --directory=/logical/mydumper --threads=30 --verbose=2 --innodb-optimize-keys --max-threads-for-index-creation=30 --defaults-file=/xtrabackup/logical/mydumper.cnf -o
Suggested fix:
Add an new flag or option to control whether indexes can be added to completed tables while other tables are still loading, similar to myloader. Default can remain the same, but would be nice to have it configurable.
Thanks!
Marc
