I support an application with a table with a LONGTEXT column. (We actually support multiple DBMSes, but for the purposes of this question, I am asking about MySQL.)

As it turns out, the data in that column comprises a very large chunk of the total usage in the entire database (about 40%!), and that data is simply debugging information anyways.

Now, we want to keep the last month of debug data around, so we can't drop the column entirely, so I assume we can free up space by updating older rows to NULL.

My questions:

  1. When I set the values of that column to NULL, does it actually delete the data? I know that LONGTEXT data is stored separately from the rest of the table. Are the now-orphaned entries "garbage-collected" or otherwise cleaned up at some point?
  2. If this doesn't happen automatically, what do I need to do to trigger a cleanup?
  3. Once the data is actually deleted, will that space be reclaimed by the OS/filesystem, or will it remain claimed by the database? If we wanted to release the space back to the filesystem (so we can pay for a smaller hosted instance), how would we go about releasing that space?

Yes and no.

In InnoDB, "small" columns are stored with the record in a BTree. "Large" columns are stored elsewhere. The cutoff between small and large is complex. So let me ask how big your LONGTEXT values actually are.

When you shrink something that is stored with the record, it is unlikely to free up any space other than in the one 16KB block in question. This can be reused if some row is inserted or a column is updated to be bigger.

Or, if two adjacent BTree blocks are only half full, the engine may coalesce them, thereby freeing up a block. More on free blocks later.

If you are NULLing a large value (let's say more than 3KB), then the space in the off-record storage is freed. This will free up some number of blocks, depending on the actual size.

What about the freed up block(s)? Well, they are parts of "extents". Etc, etc.

Bottom lines:

  • Nulling a column may free up block(s)
  • Those blocks may be available for future activity.
  • The blocks will not be released back to the filesystem of the OS. That is ibdata1 nor .ibd will shrink.

OK, I told you more than you needed. Let me re-address the question of "Can I pay for a smaller VM by doing such NULLing?"

Again, Yes and no.

No, the disk allocation will not automatically shrink.

Yes, in that you could take action to give the space back to the FS. Now, to discuss this...

  • OPTIMIZE TABLE blocks usage of one table, but shrinks its disk footprint if it had been created with innodb_file_per_table = ON. Else, no benefit.
  • Dumping the entire database and starting over by reloading it on a fresh MySQL instance, will release space, regardless of the above setting. But this is a big task.
  • Check with the VM provider. They may have a way of doing that dump+reload.

To get an estimate of the disk space needed, see the SELECT in http://mysql.rjweb.org/doc.php/memory#another_algorithm . But double what it gives you -- you do need space for various other things.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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