[MDEV-5171] Add support for --innodb-optimize-keys to mysqldump. - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Fix Version/s: N/A
Component/s: Scripts & Clients
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.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

mdev5171.diff.txt
44 kB
2013-10-30 14:45

Issue Links

relates to

MDEV-515 innodb bulk insert

Closed

MDEV-11415 Remove excessive undo logging during ALTER TABLE…ALGORITHM=COPY

Closed

MDEV-16281 Implement parallel CREATE INDEX, ALTER TABLE, or bulk load

Open

MDEV-24621 In bulk insert, pre-sort and build indexes one page at a time

Closed

MDEV-32250 To benefit from MDEV-515 , please make option --no-autocommit default in mysqldump from 10.6 onwards

Closed

MDEV-34719 Disable purge for LOAD DATA INFILE into empty table

Closed

MDEV-34739 Implement DISABLE KEYS/ENABLE KEYS in Innodb

Open

(2 relates to)

Activity

Ascending order - Click to sort in descending order

Peter (Stig) Edwards created issue - 2013-10-22 20:42

Elena Stepanova made changes - 2013-10-22 22:03

Field	Original Value	New Value
Affects Version/s	10.0.4 [ 13101 ]
Issue Type	Bug [ 1 ]	Task [ 3 ]

Elena Stepanova made changes - 2013-10-22 22:05

Fix Version/s

10.0.6 [ 13202 ]

Peter (Stig) Edwards made changes - 2013-10-30 14:45

Attachment

mdev5171.diff.txt [ 24102 ]

Sergei Golubchik made changes - 2013-11-18 21:52

Fix Version/s		10.0.7 [ 14100 ]
Fix Version/s	10.0.6 [ 13202 ]

Sergei Golubchik made changes - 2013-12-28 17:50

Fix Version/s		10.0.8 [ 14200 ]
Fix Version/s	10.0.7 [ 14100 ]

Sergei Golubchik made changes - 2014-02-10 16:13

Fix Version/s		10.0.9 [ 14400 ]
Fix Version/s	10.0.8 [ 14200 ]

Sergei Golubchik made changes - 2014-03-09 22:02

Fix Version/s		10.0.10 [ 14500 ]
Fix Version/s	10.0.9 [ 14400 ]

Sergei Golubchik made changes - 2014-03-31 14:23

Fix Version/s		10.0.11 [ 15200 ]
Fix Version/s	10.0.10 [ 14500 ]

Sergei Golubchik made changes - 2014-05-10 23:13

Fix Version/s		10.0.12 [ 15201 ]
Fix Version/s	10.0.11 [ 15200 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 29408 ]

MariaDB v2 [ 42927 ]

Sergei Golubchik made changes - 2014-06-16 13:27

Fix Version/s		10.0.13 [ 16000 ]
Fix Version/s	10.0.12 [ 15201 ]

Elena Stepanova made changes - 2015-04-20 14:50

Summary

Please consider adding support for --innodb-optimize-keys to mysqldump.

[PATCH] Please consider adding support for --innodb-optimize-keys to mysqldump.

Sergei Golubchik made changes - 2015-04-20 15:18

Priority

Minor [ 4 ]

Major [ 3 ]

Jan Lindström (Inactive) made changes - 2015-04-28 16:49

Assignee

Jan Lindström [ jplindst ]

Jan Lindström (Inactive) made changes - 2015-05-04 09:24

Status

Open [ 1 ]

In Progress [ 3 ]

Jan Lindström (Inactive) made changes - 2015-05-04 10:27

Summary

[PATCH] Please consider adding support for --innodb-optimize-keys to mysqldump.

Add support for --innodb-optimize-keys to mysqldump.

Jan Lindström (Inactive) made changes - 2015-05-12 12:33

Assignee	Jan Lindström [ jplindst ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:50

Workflow

MariaDB v2 [ 42927 ]

MariaDB v3 [ 61935 ]

Sergei Golubchik made changes - 2015-06-09 16:59

Sprint

10.0.20 [ 5 ]

Sergei Golubchik made changes - 2015-06-09 16:59

Rank

Ranked higher

Sergei Golubchik made changes - 2015-06-11 21:07

Assignee	Sergei Golubchik [ serg ]	Jan Lindström [ jplindst ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2015-06-17 01:05

Sprint

10.0.20 [ 5 ]

10.0.20, 10.1.6-1 [ 5, 6 ]

Sergei Golubchik made changes - 2015-06-17 01:05

Rank

Ranked higher

Sergei Golubchik made changes - 2015-06-17 01:24

Sprint

10.0.20, 10.1.6-1 [ 5, 6 ]

10.0.20 [ 5 ]

Jan Lindström (Inactive) made changes - 2015-07-21 18:38

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Rasmus Johansson (Inactive) made changes - 2015-07-22 12:57

Sprint

10.0.20 [ 5 ]

10.0.20, 10.0.21 [ 5, 8 ]

Jan Lindström (Inactive) made changes - 2015-07-27 13:06

Assignee	Jan Lindström [ jplindst ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Rasmus Johansson (Inactive) made changes - 2015-07-28 16:35

Sprint

10.0.20, 10.0.21 [ 5, 8 ]

10.0.20, 10.0.21, 5.5.45 [ 5, 8, 9 ]

Rasmus Johansson (Inactive) made changes - 2015-07-28 16:35

Rank

Ranked higher

Sergei Golubchik made changes - 2015-07-28 16:37

Sprint

10.0.20, 10.0.21, 5.5.45 [ 5, 8, 9 ]

10.0.20, 10.0.21 [ 5, 8 ]

Sergei Golubchik made changes - 2015-09-11 20:29

Fix Version/s		10.1 [ 16100 ]
Fix Version/s	10.0 [ 16000 ]

Sergei Golubchik made changes - 2015-09-12 12:50

Assignee	Sergei Golubchik [ serg ]	Jan Lindström [ jplindst ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Rasmus Johansson (Inactive) made changes - 2016-11-28 11:11

Fix Version/s		10.3 [ 22126 ]
Fix Version/s	10.1 [ 16100 ]

Marko Mäkelä made changes - 2017-10-23 10:35

Link

This issue relates to ~~MDEV-515~~ [ ~~MDEV-515~~ ]

Marko Mäkelä made changes - 2017-10-23 10:35

Link

This issue relates to ~~MDEV-11415~~ [ ~~MDEV-11415~~ ]

Jan Lindström (Inactive) made changes - 2017-12-13 11:15

Sprint

10.0.20, 10.0.21 [ 5, 8 ]

10.0.20, 10.0.21, 10.1.30 [ 5, 8, 215 ]

Jan Lindström (Inactive) made changes - 2017-12-13 11:15

Rank

Ranked lower

Jan Lindström (Inactive) made changes - 2017-12-13 11:15

Sprint

10.0.20, 10.0.21, 10.1.30 [ 5, 8, 215 ]

10.0.20, 10.0.21 [ 5, 8 ]

Jan Lindström (Inactive) made changes - 2017-12-13 11:15

Rank

Ranked higher

Sergei Golubchik made changes - 2018-02-14 15:21

Fix Version/s		10.4 [ 22408 ]
Fix Version/s	10.3 [ 22126 ]

Jan Lindström (Inactive) made changes - 2019-03-04 07:25

Fix Version/s

10.4 [ 22408 ]

Marko Mäkelä made changes - 2021-02-09 08:29

Link

This issue relates to ~~MDEV-24621~~ [ ~~MDEV-24621~~ ]

Sergei Golubchik made changes - 2021-12-06 21:22

Workflow

MariaDB v3 [ 61935 ]

MariaDB v4 [ 131626 ]

Jan Lindström made changes - 2023-04-11 05:38

Assignee

Jan Lindström [ jplindst ]

Marko Mäkelä [ marko ]

Marko Mäkelä made changes - 2023-04-11 09:27

Link

This issue relates to MDEV-16281 [ MDEV-16281 ]

Marko Mäkelä made changes - 2023-04-11 09:29

issue.field.resolutiondate

2023-04-11 09:29:27.0

2023-04-11 09:29:27.9

Marko Mäkelä made changes - 2023-04-11 09:29

Component/s		Scripts & Clients [ 11002 ]
Fix Version/s		N/A [ 14700 ]
Resolution		Won't Fix [ 2 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Marko Mäkelä made changes - 2023-10-24 10:02

Link

This issue relates to ~~MDEV-32250~~ [ ~~MDEV-32250~~ ]

Marko Mäkelä made changes - 2024-08-07 12:08

Link

This issue relates to ~~MDEV-34719~~ [ ~~MDEV-34719~~ ]

Marko Mäkelä made changes - 2024-08-29 06:51

Link

This issue relates to MDEV-34739 [ MDEV-34739 ]

People

Assignee:: Marko Mäkelä

Reporter:: Peter (Stig) Edwards

Votes:: 5 Vote for this issue

Watchers:: 14 Start watching this issue

Dates

Created:: 2013-10-22 20:42

Updated:: 2024-08-29 06:53

Resolved:: 2023-04-11 09:29

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration