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
