Details
-
Type:
Task
-
Status: Stalled
-
Priority:
Major
-
Resolution: Unresolved
-
Fix Version/s: 10.1
-
Component/s: None
-
Labels:None
-
Sprint:10.0.20, 10.0.21
Description
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Alexey pointed me to a recent bug with mysqldump --innodb-optimize-keys in Percona Server, where incorrect CREATE TABLE statement for partitioned tables are produced, it has not been fixed yet:
https://bugs.launchpad.net/percona-server/+bug/1233841
This is a diff against 10.0, it contains just the changes needed for mysqldump from the Percona 5.5 code and two other tiny changes, an update to the man page and the addition of the warning for the duplicate index in the test result. Attribution may also be needed.
added:
mysql-test/r/percona_mysqldump_innodb_optimize_keys.result
mysql-test/t/percona_mysqldump_innodb_optimize_keys.test
modified:
client/client_priv.h
client/mysqldump.c
man/mysqldump.1
This would still have this bug - https://bugs.launchpad.net/percona-server/+bug/1233841
The above bug is fixed.
http://lists.askmonty.org/pipermail/commits/2015-July/008193.html
Could you review this again, as I had to rewrite internal parser to support unquoted identifiers
and multi-column keys.
I suppose since it's just an option in a client program, it can be added even after 10.0.5, setting to 10.0.6 for now.