0

I have a large MySql InnoDB table with about 200 million rows. One of the columns has some data in it that I really don't need any more, so I'm thinking about removing that column, or setting all the cells in that column to NULL.

This column contains a text value of about 300 characters, so by removing it I'll save about 300 bytes x 200 million rows = 60GB.

But, will this leave a lot of "holes" in my data? Can MySql effectively and efficiently re-use this space without some sort of de-fragmentation? Or does that just not matter since it's InnoDB and on an SSD drive?

Thanks!

Update: The table has about columns total, but most of them are INT values. This text field is by far the biggest. Each row only takes up about 4KB of space total.

Improve this question

3 Answers 3

Reset to default
1

InnoDB stores data (and indexes) in 16KB blocks.

It sounds like you are shrinking each row by an average of 7% (300/4K).

If the shrinkage had been more than 50%, then probably InnoDB would have noticed adjacent half-full blocks and coalesced them, thereby freeing up some of the predicted savings. (A block is 16KB.) I say only "some" because it is not aggressive at packing the blocks after UPDATEs or DELETEs.

If you had not done the OPTIMIZE, future inserts/deletes/updates, if they were scattered around the table, would have filled in the 'holes' and/or led to blocking coalescing. If, on the other hand, the main activity is to add new rows at the 'end' of the PRIMARY KEY values, then this subsequent cleanup would not happen, and the OPTIMIZE would be desirable.

How much did the OPTIMIZE benefit you? Performance probably changed very little. Sure, you freed up some space, but, if you are not running out of disk space, that does not matter much.

Another issue... Tablespaces. The old default was to put all tables into a single tablespace (the file named ibdata1). It would grow but never shrink. Any freed blocks would be available for future database work, but would not be given back to the OS.

The new default (innodb_file_per_table) has the same problem. However, an explicit OPTIMIZE rebuilds the table, and does give the freed space back to the OS. Caveat: During the process, you need 2x the disk space. So, if you were very tight on space, OPTIMIZE would fail. However...

OPTIMIZE TABLE (for InnoDB) is "Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables with FULLTEXT indexes. The operation uses the INPLACE algorithm, but ALGORITHM and LOCK syntax is not permitted. " -- https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

Improve this answer
4
  • You were correct that OPTIMIZE would need 2x the disk space. Unfortunately, I didn't know that when I tried it, and it ran for 4 hours and then crashed due to lack of disk space. I need to try it again but first I need to clone the server onto a bigger hard drive. I strongly suspect I will get the 60GB back, but performance will not be affected much, as you suggested.
    – Steve B
    Commented Oct 4, 2018 at 1:22
  • @SteveB - I may be wrong on the 2x. See paragraph I added.
    – Rick James
    Commented Oct 4, 2018 at 1:54
  • Interesting, thanks for the update. My mysql version is 5.6.40, but it definitely still seemed to require 2x the disk space. I'm not sure why.
    – Steve B
    Commented Oct 5, 2018 at 2:23
  • There are tricks to freeing up disk space when space it tight. Start a new Question and list the largest few tables (SHOW TABLE STATUS), the size of ibdata1, df (or disk stats if running Windows.
    – Rick James
    Commented Oct 5, 2018 at 2:54
0

It's depend. The row format for the table can be choosed on creation or alteration. Possible options are: DYNAMIC, COMPACT, REDUNDANT and COMPRESSED.

In your case the COMPACT has a good balance between disk footprint and proceeding overhead.

Very detailed explanation of the each format is provided here : https://dev.mysql.com/doc/refman/8.0/en/innodb-physical-record.html

Improve this answer
1
  • My row format is DYNAMIC. In the manual it says that columns are only stored off-page if the row is too long (larger than the page size). In my case, since each entire row only takes about 4KB, it would seem to me that this column (that I plan to set to NULL) would be stored in-line, and therefore it would lead to "holes" in the data. If that's correct, is this a problem?
    – Steve B
    Commented Sep 4, 2018 at 18:01
0

At least in this particular case, the answer was no. I NULLed out all the values in this column, which should have saved about 60GB. But, the size of the table on disk did not decrease at all.

It appears it needs an OPTIMIZE.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.