MySQL Innodb - Truncate Partition. Does it reclaims space?

Question

Once a month I need to remove all the old data from the table. Previously I was copying all the needed data to another table, then drop the original table and rename the new table as original. When I ran drop table the space is reclaimed.

But now I am deciding to use Partitioning in the table. So my question is when I will do:

ALTER TABLE mytable TRUNCATE PARTITION partition

Will it reclaims the space? If it does not reclaims the space then am I left with only choice to OPTIMIZE the table or is there anything else I am missing? Since doing OPTIMIZE PARTITION on innodb table will lock the whole table.

Answer 1

You have not said what value innodb_file_per_table was set to when you created the table. If OFF, you have no chance of shrinking any file on disk. Instead, you can only send it off to be 'free' and potentially reused by inserts. With ON...

A third option is ALTER TABLE REORGANIZE PARTITION ... where you essentially state the same partition as the 'from' and 'to'. This degenerate version effectively does what OPTIMIZE PARTITION should do (but fails to).

Fourth option, which would work in some partition types: DROP PARTITION and add it back.

Yes, OPTIMIZE PARTITION rebuilds the entire table. After 5.6.17 (5.7.4), you may may be able to do it non-blocking (ALGORITHM=INPLACE).

Even if TRUNCATE does not free the space, will you be adding data back in? Of so, then one could argue that it is "not a problem".

• Johnny Broz: Yes innodb_file_per_table is on that's why when I drop the table it gives me the space back. Does REORGANIZE partition blocks the table? or maybe it is so fast that it does not matter?
• Rick James: I suspects it blocks the table long enough to copy over the partition(s) involved. In your case (after truncation), that should be fast.
• Johnny Broz: Just for your knowledge. I just tested on AWS RDS with 100gb of table divided in 4 partitions. Other 8 were already empty. When I truncate each partition the space was immediately available without doing OPTIMIZE, REORGANIZE. Is this normal?
• Rick James: Maybe. TRUNCATE may be implemented as DROP + CREATE. The manual may say specifically.