Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
N/A
-
None
Description
REPAIR for InnoDB tables was introduced in MDEV-33449. As of the time of filing this report, the task description says that now REPAIR is one of three ways to optimize the row space usage. Generally, it does, but somehow differently from OPTIMIZE (==recreate+analyze) or ALTER .. FORCE. So, in some cases, REPAIR on an already optimized table makes it bigger.
In the example below, both ALTER and OPTIMIZE shrink the tablespace from 40M to 18M. But REPAIR ends up with 22M. So, if it's executed after delete, on the 40M tablespace, it indeed optimizes it (although not as efficiently as OPTIMIZE or ALTER); but if it's executed on the 18M tablespace, it increases it to 22M.
I didn't check whether the issue scales.
--source include/have_innodb.inc
|
--source include/have_sequence.inc
|
|
|
create table t (a int primary key, b varchar(256)) engine=InnoDB; |
insert into t select seq, repeat('a',256) from seq_1_to_100000; |
delete from t where a%2 = 0; |
--let $datadir=`select @@datadir`
|
--replace_result $datadir <datadir>
|
--exec ls -s --block-size=K $datadir/test/t.ibd
|
optimize table t; |
--replace_result $datadir <datadir>
|
--exec ls -s --block-size=K $datadir/test/t.ibd
|
repair table t; |
--replace_result $datadir <datadir>
|
--exec ls -s --block-size=K $datadir/test/t.ibd
|
alter table t force; |
--replace_result $datadir <datadir>
|
--exec ls -s --block-size=K $datadir/test/t.ibd
|
|
|
# Cleanup
|
drop table t; |
|
bb-11.5-MDEV-32188-timestamps 65b4a991a89fd72dfd409e7addd17c8476da3e0f |
delete from t where a%2 = 0;
|
40960K <datadir>/test/t.ibd
|
optimize table t;
|
Table Op Msg_type Msg_text
|
test.t optimize note Table does not support optimize, doing recreate + analyze instead
|
test.t optimize status OK
|
18432K <datadir>/test/t.ibd
|
repair table t;
|
Table Op Msg_type Msg_text
|
test.t repair status OK
|
22528K <datadir>/test/t.ibd
|
alter table t force;
|
18432K <datadir>/test/t.ibd
|
drop table t;
|
Attachments
Issue Links
- is caused by
-
MDEV-33449 improving repair of tables
-
- Closed
-