MySQL Bugs: #98511: OPTIMIZE TABLE on myisam can increase table size (~2x) and reduce performance

Bug #98511	OPTIMIZE TABLE on myisam can increase table size (~2x) and reduce performance
Submitted:	7 Feb 2020 10:11	Modified:	30 Jun 2020 17:34
Reporter:	Pete Dishman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S5 (Performance)
Version:	8.0.19, 8.0.20	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Since upgrading to mysql 8, we've seen that OPTIMIZE TABLE statements on some myisam tables can result in the table nearly doubling in size on disk and query performance being significantly reduced.

Once this has happened to a table, the only way to restore size/performance is to run `ALTER TABLE <tablename> FORCE` or dump & reinsert table contents.

On some tables in mysql 8, `ALTER TABLE <tablename> FORCE` is the only way to achieve what `OPTIMIZE TABLE <tablename>` did in previous versions.

Running `myisamchk -ei <tablename>` on the 'optimized' table shows that the number of Record blocks and linkdata is significantly increased by the optimize statement.

I've reproduced this on v8.0.11, 8.0.18 and 8.0.19.

v5.7.24 shows the expected behaviour

In the worst cases we've seen an 11Gb table increase in size to 27Gb
and other tables query performance drop from ~50ms to >1000ms after running OPTIMIZE TABLE

How to repeat:
Problem is reproducible by creating table with int and varchar fields, inserting some data, then increasing the size of data in one of the text fields before running optimize.
Results can be seen by checking size of .myd file on disk, and output of `myisamchk -ei`

drop database if exists test;
create database test;
use test;
create table bug ( id int, name varchar(255), description varchar(255), count int, primary key(id)) engine=myisam;
insert into bug values (1, "test1", "description1", 1), (2, "test2", "description2", 2), (3, "test3", "description3", 3);
flush tables;

now check size of bug.myd and run myisamchk -ei bug

update bug set name="testing test2" where id=2;
flush tables;

now check size of bug.myd and run myisamchk -ei bug

optimize table bug;

now check size of bug.myd and run myisamchk -ei bug

alter table bug force;

now check size of bug.myd and run myisamchk -ei bug

Before running optimize, bug.myd was 120 bytes and myisamchk gives this output:
- check records and index references
Records:                 3    M.recordlength:       31   Packed:            92%
Recordspace used:       98%   Empty space:           1%  Blocks/Record:   1.33
Record blocks:           4    Delete blocks:         0
Record data:            95    Deleted data:          0
Lost space:              2    Linkdata:             23

After running optimize, bug.myd was 200 bytes and myisamchk gives this:
- check records and index references
Records:                 3    M.recordlength:       31   Packed:            88%
Recordspace used:       90%   Empty space:           5%  Blocks/Record:   3.33
Record blocks:          10    Delete blocks:         0
Record data:            95    Deleted data:          0
Lost space:             10    Linkdata:             95

Running an 'ALTER TABLE bug FORCE' then reduces size to 104 bytes, and myisamchk output of:
Records:                 3    M.recordlength:       31   Packed:            93%
Recordspace used:      100%   Empty space:           0%  Blocks/Record:   1.00
Record blocks:           3    Delete blocks:         0
Record data:            95    Deleted data:          0
Lost space:              0    Linkdata:              9

Thanks,  verified as described on 8.0.19.

output from my test.

Attachment: bug98511_8.0.19.txt (text/plain), 8.73 KiB.

https://bugs.mysql.com/bug.php?id=99797

Bug #99797 marked as duplicate of this one

As programmer I can imagine it may be not such trivial as it hasn't been fixed since 4 months.

On the other hand this bug has such a huge impact that it should have a higher priority.

Bug #100017 marked as duplicate of this one

Posted by developer:
 
Fixed in 8.0.22.

OPTIMIZE TABLE for MyISAM tables could cause table size to increase
and query performance to decrease.