Fragmentation in InnoDB tables: dump all data together reduced size, but fragmentation got worse [closed]

Question

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 (last column) 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.

Maybe I'm playing around fragmentation.

In summary, do you know any good practice or recommendations about MySQL fragmentation (in partitioned tables or not)?

Answer 1

PARTITION is awful when it comes to incurring extra space.

Most DBAs don't really have need for Partitioning, even though they think they do. Please provide your justification for your situation. I may be able to convince you to get rid of partitioning and make some indexes, and come out ahead. Please provide SHOW CREATE TABLE, how many partitions, what queries benefit from partitioning, etc.

One rule of thumb: "Don't use PARTITION unless you will have >1M rows". A big reason is the non-trivial "free" space for each partition. More RoTs: http://mysql.rjweb.org/doc.php/partitionmaint

Notice how each partition claims to have 4MB, 5MB, 6MB or 7MB of "free" space. (Actually, there is a lot of fragmentation that is not included in that number.)

See if this will give you the partition details (after filling in the database name and table name):

       SELECT *
            FROM information_schema.PARTITIONS
            WHERE TABLE_SCHEMA = ?
              AND TABLE_NAME = ?
            ORDER BY PARTITION_ORDINAL_POSITION,
                  SUBPARTITION_ORDINAL_POSITION

For non-partitioned tables in InnoDB, there is rarely anything that you can or should do to get rid of fragmentation. Show me the schema for some big table; I may have other advice, such as smaller datatypes or fewer indexes -- This will save disk space, which I assume is the 'real' problem.

One more thing -- Was the table created with innodb_file_per_table = ON? If not, the you can't release the space to the OS. It will go back into "available for other inserts into other tables", but not the OS. Do not change that setting without further research and discussion. (You don't want a big, but empty, ibdata1 with all your data in individual tablespaces.)