Dump all data reduced size, but fragmentation got worse

by yaki_nuka   Last Updated May 04, 2018 13:00 PM

I have a question about tables and fragmentation in MySQL

My starting point is this question:

How find and fix fragmented MySQL tables

I have a table with 1 million of rows and 128MB of space. This table is partitioned.

I dump all the rows to a file with mysqldump, create the same table with '__BAK' suffix, and then insert all the data from the file to the '__BAK' table.

With this operation, the table space reduce to 112MB. Ok, I expected it.

But from the link above, I check fragmentation index (or ratio) and get these results:

"ENGINE" "TABLE_NAME" "data_length" "index_length" "data_free" "frag_ratio"

"InnoDB" "mytable" "100" "28" "26" 0,2031

"InnoDB" "mytable__BAK" "85" "27" "43" 0,3849

The frag ratio got worse in the new table, with supposedly all the rows together.

So, my questions are:

  • Is this because of the partitioning? Should I check frag ratio in partitioned tables?
  • Maybe it's not necessary to defrag a table with this low frag ratio, isn't it?
  • The steps I have described above, are right to reduce fragmentation and/or reduce size?
  • If I would have to choose an option, which one should I? First table (less frag ratio, more space) or second one (more ratio, less space)?

Any contribution or idea is very appreciated. Thanks a lot.

Related Questions

Spreading a table's partitions across drives

Updated August 15, 2015 18:00 PM

Failed to mount external drive while booting

Updated December 16, 2017 15:00 PM

Orange pi resize filesystem instructions not working

Updated February 25, 2018 07:00 AM