Mysql – How to reclaim space after nulling out LONGTEXT records on MySQL

MySQL

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:

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?
If this doesn't happen automatically, what do I need to do to trigger a cleanup?
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?

Best Answer

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.

InnoDB Architecture

This first thing that comes to mind is the InnoDB Buffer Pool. It holds three things

Data Pages from Accessed .ibd files
Index Pages from Accessed .ibd files
Insert Buffer for Changes to Secondary Indexes

Any query accessing infrequent data will basically remove the working dataset from the InnoDB Buffer Pool just to load the adhoc data. To get those data back, you have two options:

OPTION 1 : Reread the data and index pages from disk. You are certainly going to experience a cache storm that will impact Server Load with Disk I/O, Memory Utilization, and possible swapping.
OPTION 2 : Unload Buffer Pool Before Accessing Old Data and Reload Buffer Pool Afterwards. This is a new feature for MySQL 5.6. Apparently, it was made for occasions like this. Here are the options for loading the InnoDB Buffer Pool at Startup, Shutdown and On-Demand :

EPILOGUE

OPTION 1 is something you reluctantly live with until all subsequent queries bring back your most recent working dataset
OPTION 2 requires scripting adhoc reports to do the following
- Backing up the InnoDB Buffer Pool
- Run the adhoc queries on the infrequent data
- Restoring the InnoDB Buffer Pool
OPTION 2 requires upgrading to MySQL 5.6
There are other ways MySQL and InnoDB are impacted. I discussed these in my recent post How to optimize MySQL for large BLOB updates

How Much Space is Needed to Reclaim Space from a Table in InnoDB

DISKSPACE FOR EVERYTHING INNODB

SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace
FROM information_schema.tables
WHERE engine='InnoDB';

DISKSPACE FOR DATABASE mydb BY TABLE

SELECT
    IFNULL(tbl,'Total') table_name,
    FORMAT(SUM(table_bytes)/POWER(1024,3),2) table_size
FROM
(
    SELECT table_name tbl,SUM(data_length+index_length)table_bytes
    FROM information_schema.tables WHERE table_schema='mydb
    GROUP BY table_name WITH ROLLUP
) A ORDER BY ISNULL(tbl) DESC,table_bytes;

DISKSPACE FOR INDIVIDUAL TABLE (mydb.mytable)

SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) Table_Diskspace
FROM information_schema.tables
WHERE table_schema='mydb;
AND table_name='mytable';

These queries will tell you how much space you need for any table, database, and engine.

SUGGESTION #1

If you would like to simulate OPTIMIZE TABLE just to see diskspace usage, you run the following steps on mydb.mytable

use mydb
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new SELECT * FROM mytable;
ANALYZE TABLE mytable_new;
SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) Table_Diskspace
FROM information_schema.tables
WHERE table_schema='mydb;
AND table_name='mytable_new';
#
# Stop Here. If you are happy with the size, do the last two commands
# If you prefer, drop table mytable_new and do do the online schema change
#
RENAME TABLE mytable TO mytable_old,mytable_newTO mytable;
DROP TABLE mytable_old;

Doing this can give an idea who big the .ibd file will be after running OPTIMIZE TABLE.

SUGGESTION #2

Please do not convert the table to MyISAM. It will be building two files (.MYD and .MYI). Then, you try to converting to InnoDB again. It is essentially the same as doing #SUGGESTION #1. MyISAM is also a little more prone to corruption with no recovery processes in place.

EPILOGUE

By all means, you can proceed with pt-online-schema-change if your data is live. My answer simply gives you queries to estimate sizes of the tables and approximate temp table sizes for OPTIMIZE TABLE.

If you are doing this during off hours, try my post Recover the disk space after deleting rows from table

Best Answer

Related Solutions

Mysql – Specific disadvantages to storing infrequently accessed large files in a large database

InnoDB Architecture

EPILOGUE

How Much Space is Needed to Reclaim Space from a Table in InnoDB

DISKSPACE FOR EVERYTHING INNODB

DISKSPACE FOR DATABASE mydb BY TABLE

DISKSPACE FOR INDIVIDUAL TABLE (mydb.mytable)

SUGGESTION #1

SUGGESTION #2

EPILOGUE

GIVE IT A TRY !!!

Related Question