Hello and thank you for mariadb,
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.
Here are some links to the background:
http://bugs.mysql.com/bug.php?id=57583
http://bugs.mysql.com/bug.php?id=49120
http://www.percona.com/doc/percona-server/5.5/management/innodb_expanded_fast_index_creation.html#expand_fast_index_creation
http://www.mysqlperformanceblog.com/2012/06/19/building-indexes-by-sorting-in-innodb-aka-fast-index-creation/
http://www.mysqlperformanceblog.com/2011/11/06/improved-innodb-fast-index-creation/
http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/
https://bugs.launchpad.net/percona-server/+bug/989253
https://bugs.launchpad.net/percona-server/+bug/858945
https://bugs.launchpad.net/percona-server/+bug/744103
https://github.com/facebook/mysql-5.6/commit/7862a74ddb48eceaf2a48531d20550752c868a46
I tested using the example from Mark Callaghan in http://bugs.mysql.com/bug.php?id=57583
create table rt (i int primary key auto_increment, j float) engine=innodb;
|
insert into rt values (null, 1);
|
create index x2 on rt(j);
|
insert into rt select null, rand(0) from rt; (21 times for 2,097,152 rows)
|
|
120M test/rt.ibd
|
|
Data_length: 62472192
|
Index_length: 50937856
|
Data_free: 7340032
|
|
mariadb-10.0.4-linux-x86_64/bin/mysqldump --order-by-primary test rt | mysql -D test2
|
|
120M test2/rt.ibd
|
|
Data_length: 62472192
|
Index_length: 50937856
|
Data_free: 7340032
|
|
Percona-Server-5.6.13-rel61.0-461.Linux.x86_64/bin/mysqldump --order-by-primary --innodb-optimize-keys test rt | mysql -D test3
|
|
92M test3/rt.ibd
|
|
Data_length: 62472192
|
Index_length: 30998528
|
Data_free: 0
|
Thanks again.
Task
Major
MDEV-11415 Remove excessive undo logging during ALTER TABLE…ALGORITHM=COPY
MDEV-34739 Implement DISABLE KEYS/ENABLE KEYS in Innodb
