[MDEV-14795] InnoDB system tablespace cannot be shrunk - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 11.2.1
Component/s: Documentation, Storage Engine - InnoDB
Labels:
- Preview_11.2

Description

hello
i used to have all tables on ibdata1 before, then i switched to one file per table, but the ibdata1 file keep its size.
I recently switched to mariadb 10.1, and hoped that the defragmenting function would help to shrink the file, but it didn't worked.
I optimised all tables, but the ibdata1 file did not changed at all, it is still 197gb

MariaDB [(none)]> show status like '%defragment%';

+----------------------------------------+--------+

| Variable_name                          | Value  |

+----------------------------------------+--------+

| Innodb_defragment_compression_failures | 0      |

| Innodb_defragment_failures             | 32872  |

| Innodb_defragment_count                | 113744 |

+----------------------------------------+--------+

3 rows in set (0.01 sec)

MariaDB [(none)]> show variables like '%defragment%';

+--------------------------------------+----------+

| Variable_name                        | Value    |

+--------------------------------------+----------+

| innodb_defragment                    | ON       |

| innodb_defragment_fill_factor        | 0.900000 |

| innodb_defragment_fill_factor_n_recs | 20       |

| innodb_defragment_frequency          | 40       |

| innodb_defragment_n_pages            | 7        |

| innodb_defragment_stats_accuracy     | 0        |

+--------------------------------------+----------+

Attachments

Issue Links

blocks

MDEV-28699 Shrink temporary tablespaces without restart

Closed

MDEV-32452 InnoDB system tablespace is not shrunk on slow shutdown

Closed

causes

MDEV-34216 Possible corruption when shrinking the system tablespace on innodb_fast_shutdown=0

Closed

is blocked by

MDEV-31373 innodb_undo_log_truncate=ON recovery results in a corrupted undo log

Closed

is duplicated by

MDEV-31462 ibdata1 shrinking

Closed

relates to

MDEV-19229 Allow innodb_undo_tablespaces to be changed after database creation

Closed

MDEV-28699 Shrink temporary tablespaces without restart

Closed

MDEV-29983 Deprecate innodb_file_per_table

Closed

MDEV-29999 innodb_undo_log_truncate=ON is not crash safe

Closed

MDEV-33112 innodb_undo_log_truncate=ON is blocking page writes

Closed

MDEV-35398 Improve shrinking of system tablespace

Closed

MDEV-35689 InnoDB system tables cannot be optimized or defragmented

Stalled

MDEV-21952 ibdata1 file size growing in MariaDB

Closed

MDEV-29986 Set innodb_undo_tablespaces=3 by default

Closed

MDEV-32622 [mariadb-galera] The undo log is very large, how can there be fewer undo log logs

Closed

MDEV-34529 Shrink the system tablespace when system tablespace contains MDEV-30671 leaked undo pages

Closed

(11 relates to)

Activity

Ascending order - Click to sort in descending order

martina342 created issue - 2017-12-29 00:13

Elena Stepanova made changes - 2017-12-29 00:31

Field	Original Value	New Value
Description	hello i used to have all tables on ibdata1 before, then i switched to one file per table, but the ibdata1 file keep its size. I recently switched to mariadb 10.1, and hoped that the defragmenting function would help to shrink the file, but it didn't worked. I optimised all tables, but the ibdata1 file did not changed at all, it is still 197gb MariaDB [(none)]> show status like '%defragment%'; +----------------------------------------+--------+ \| Variable_name \| Value \| +----------------------------------------+--------+ \| Innodb_defragment_compression_failures \| 0 \| \| Innodb_defragment_failures \| 32872 \| \| Innodb_defragment_count \| 113744 \| +----------------------------------------+--------+ 3 rows in set (0.01 sec) MariaDB [(none)]> show variables like '%defragment%'; +--------------------------------------+----------+ \| Variable_name \| Value \| +--------------------------------------+----------+ \| innodb_defragment \| ON \| \| innodb_defragment_fill_factor \| 0.900000 \| \| innodb_defragment_fill_factor_n_recs \| 20 \| \| innodb_defragment_frequency \| 40 \| \| innodb_defragment_n_pages \| 7 \| \| innodb_defragment_stats_accuracy \| 0 \| +--------------------------------------+----------+	hello i used to have all tables on ibdata1 before, then i switched to one file per table, but the ibdata1 file keep its size. I recently switched to mariadb 10.1, and hoped that the defragmenting function would help to shrink the file, but it didn't worked. I optimised all tables, but the ibdata1 file did not changed at all, it is still 197gb {noformat} MariaDB [(none)]> show status like '%defragment%'; +----------------------------------------+--------+ \| Variable_name \| Value \| +----------------------------------------+--------+ \| Innodb_defragment_compression_failures \| 0 \| \| Innodb_defragment_failures \| 32872 \| \| Innodb_defragment_count \| 113744 \| +----------------------------------------+--------+ 3 rows in set (0.01 sec) {noformat} {noformat} MariaDB [(none)]> show variables like '%defragment%'; +--------------------------------------+----------+ \| Variable_name \| Value \| +--------------------------------------+----------+ \| innodb_defragment \| ON \| \| innodb_defragment_fill_factor \| 0.900000 \| \| innodb_defragment_fill_factor_n_recs \| 20 \| \| innodb_defragment_frequency \| 40 \| \| innodb_defragment_n_pages \| 7 \| \| innodb_defragment_stats_accuracy \| 0 \| +--------------------------------------+----------+ {noformat}

Elena Stepanova made changes - 2017-12-29 00:35

Component/s		Documentation [ 10903 ]
Component/s		Storage Engine - InnoDB [ 10129 ]
Fix Version/s		10.1 [ 16100 ]
Assignee		Jan Lindström [ jplindst ]

Jan Lindström (Inactive) made changes - 2017-12-29 08:17

Assignee

Jan Lindström [ jplindst ]

Ian Gilfillan [ greenman ]

Ian Gilfillan made changes - 2018-01-25 07:45

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Rock Mutchler (Inactive) made changes - 2021-08-19 19:30

Link

This issue causes MENT-1304 [ MENT-1304 ]

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

Workflow

MariaDB v3 [ 84632 ]

MariaDB v4 [ 153459 ]

Marko Mäkelä made changes - 2022-05-31 05:44

Link

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

Marko Mäkelä made changes - 2022-11-09 09:10

Assignee	Ian Gilfillan [ greenman ]	Thirunarayanan Balathandayuthapani [ thiru ]
Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Marko Mäkelä made changes - 2022-11-09 09:11

Link

This issue blocks MENT-1304 [ MENT-1304 ]

Marko Mäkelä made changes - 2022-11-09 09:12

Fix Version/s		10.12 [ 28320 ]
Fix Version/s	N/A [ 14700 ]
Summary	Defragmenting does not shrink ibdata1	InnoDB system tablespace cannot be shrunk

Marko Mäkelä made changes - 2022-11-09 09:12

Link

This issue causes MENT-1304 [ MENT-1304 ]

Marko Mäkelä made changes - 2022-11-09 09:33

Link

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

Marko Mäkelä made changes - 2022-11-09 09:44

Link

This issue blocks ~~MDEV-29986~~ [ ~~MDEV-29986~~ ]

Marko Mäkelä made changes - 2022-11-09 10:16

Link

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

Julien Fritsch made changes - 2022-11-09 13:12

Priority

Major [ 3 ]

Critical [ 2 ]

Marko Mäkelä made changes - 2022-11-11 13:32

Link

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

Sergei Golubchik made changes - 2022-11-22 13:53

Link

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

Sergei Golubchik made changes - 2022-11-22 13:53

Link

This issue blocks ~~MDEV-29986~~ [ ~~MDEV-29986~~ ]

Thirunarayanan Balathandayuthapani made changes - 2022-12-13 10:39

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Marko Mäkelä made changes - 2023-01-10 11:44

Link

This issue blocks ~~MDEV-28699~~ [ ~~MDEV-28699~~ ]

Julien Fritsch made changes - 2023-05-02 12:31

Priority

Critical [ 2 ]

Blocker [ 1 ]

Sergei Golubchik made changes - 2023-05-02 12:34

Priority

Blocker [ 1 ]

Critical [ 2 ]

Julien Fritsch made changes - 2023-05-03 13:28

Priority

Critical [ 2 ]

Blocker [ 1 ]

Sergei Golubchik made changes - 2023-05-03 14:01

Priority

Blocker [ 1 ]

Critical [ 2 ]

Julien Fritsch made changes - 2023-05-03 14:16

Environment	debian 9
Issue Type	Bug [ 1 ]	Task [ 3 ]

Thirunarayanan Balathandayuthapani made changes - 2023-05-09 09:56

Fix Version/s

11.0 [ 28320 ]

Thirunarayanan Balathandayuthapani made changes - 2023-05-09 11:13

Fix Version/s

11.2 [ 28603 ]

Thirunarayanan Balathandayuthapani made changes - 2023-05-23 07:15

Assignee	Thirunarayanan Balathandayuthapani [ thiru ]	Marko Mäkelä [ marko ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Marko Mäkelä made changes - 2023-05-23 10:58

Assignee	Marko Mäkelä [ marko ]	Thirunarayanan Balathandayuthapani [ thiru ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Marko Mäkelä made changes - 2023-05-31 15:04

Link

This issue is blocked by ~~MDEV-31373~~ [ ~~MDEV-31373~~ ]

Thirunarayanan Balathandayuthapani made changes - 2023-06-08 06:17

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Thirunarayanan Balathandayuthapani made changes - 2023-06-08 06:31

Assignee

Thirunarayanan Balathandayuthapani [ thiru ]

Matthias Leich [ mleich ]

Matthias Leich made changes - 2023-06-15 10:01

Assignee	Matthias Leich [ mleich ]	Thirunarayanan Balathandayuthapani [ thiru ]
Status	In Testing [ 10301 ]	Stalled [ 10000 ]

Marko Mäkelä made changes - 2023-06-26 08:28

Link

This issue is duplicated by ~~MDEV-31462~~ [ ~~MDEV-31462~~ ]

Ralf Gebhardt made changes - 2023-06-26 10:57

Labels

Preview_11.2

Thirunarayanan Balathandayuthapani made changes - 2023-07-11 12:11

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Thirunarayanan Balathandayuthapani made changes - 2023-07-17 05:17

Assignee	Thirunarayanan Balathandayuthapani [ thiru ]	Marko Mäkelä [ marko ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Matthias Leich made changes - 2023-07-18 12:03

Comment

[ {noformat}
origin/bb-11.2-~~MDEV-14795~~_1 1779cb1af979cad1abe9d5c3ed4d37b6503175aa 2023-07-11T20:25:49+05:30
behaved relative well in RQG testing. I observed many bad effects but they occured/occur in preview-11.2,
11.2 b8c039fad16adf9cbc6dac2e34cdeb772b9065a0 2023-07-07T09:05:33+10:00
and sometimes other trees too.

The main testing campaign consisted of ~ 9900 tests which all used the server
startup option "--mysqld=--innodb_data_file_path=ibdata1:1M:autoextend:autoshrink".
Statistics from the remaining test results:
smallest system tablespace ever shrinked 1024 pages
biggest system tablespace ever shrinked 41152 pages
biggest shrink operation ~ 16000 pages truncated
smallest shrink operation ~ 250 pages truncated

{noformat}
]

Marko Mäkelä made changes - 2023-07-26 07:19

Assignee	Marko Mäkelä [ marko ]	Thirunarayanan Balathandayuthapani [ thiru ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Thirunarayanan Balathandayuthapani made changes - 2023-08-01 17:27

Fix Version/s		11.2.1 [ 29034 ]
Fix Version/s	11.2 [ 28603 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Marko Mäkelä made changes - 2023-08-02 12:44

Link

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

Marko Mäkelä made changes - 2023-10-12 06:11

Link

This issue blocks ~~MDEV-32452~~ [ ~~MDEV-32452~~ ]

Julien Fritsch made changes - 2023-10-26 08:35

Link

This issue is duplicated by MENT-1304 [ MENT-1304 ]

Marko Mäkelä made changes - 2023-10-30 11:01

Link

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

Ralf Gebhardt made changes - 2023-12-14 10:37

Link

This issue blocks MENT-1304 [ MENT-1304 ]

Marko Mäkelä made changes - 2023-12-22 08:38

Link

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

Rob Schwyzer (Inactive) made changes - 2024-04-04 20:02

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36724 ]

Rob Schwyzer (Inactive) made changes - 2024-04-05 18:34

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36724 ]

Marko Mäkelä made changes - 2024-05-22 13:50

Link

This issue causes ~~MDEV-34216~~ [ ~~MDEV-34216~~ ]

Jira Automation (IT) made changes - 2024-07-04 07:54

Zendesk Related Tickets

113383

Ralf Gebhardt made changes - 2024-07-15 09:59

Link

This issue blocks MENT-1304 [ MENT-1304 ]

Marko Mäkelä made changes - 2024-08-12 11:10

Link

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

Marko Mäkelä made changes - 2024-10-15 09:47

Link

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

Thirunarayanan Balathandayuthapani made changes - 2024-11-12 12:05

Link

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

Marko Mäkelä made changes - 2024-12-19 06:48

Link

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

People

Assignee:: Thirunarayanan Balathandayuthapani

Reporter:: martina342

Votes:: 2 Vote for this issue

Watchers:: 15 Start watching this issue

Dates

Created:: 2017-12-29 00:13

Updated:: 2 days ago 15:34

Resolved:: 2023-08-01 17:27

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration