Weird bahaviour of mysql 5.7.32-35-log after running optimize on table

I started a new job recently and in the new company we have a few tables (innodb engine) that are used for calculating data and they are deleted frequently and inserted about the same amount of data every time.
I checked for fragmentation and it had frag ratio of 135 data was 58 mb and free was 7 giga
They are doing deletes because they had lock issues with truncate inn the past not sure why. will check that later.
I decided to run optimize on them and after that watched to see how fragmentation builds but for some reason instead of seeing free data getting bigger I see data_length getting bigger although the table data isn’t changing by much it is about the same but data_length is almost doubling every time a delete and insert is happening.
Anyone had this issue?

I ran optimize again on the tables at the beginning still saw data getting bigger but today it was back to normal free data was bigger and data length got smaller to the correct size. so i guess it takes it a while sometimes. although previous time also was a day after and still was wrong.

1 Like