Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
None
-
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
MDEV-11415 Remove excessive undo logging during ALTER TABLE…ALGORITHM=COPY
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-34739 Implement DISABLE KEYS/ENABLE KEYS in Innodb
-
- Open
-
Activity
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.
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.
MDEV-34739 has been filed for the request to support ALTER TABLE…DISABLE KEYS and ALTER TABLE…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).