BLOB data management in MySQL during update to null?

Question

I have several transactional tables that has a logically-placed text and a blob column in MySQL. After a period of time these data columns are not needed on rows older than X days. I need to be as storage-aware as possible. This is on multiple nodes in a cluster that have limited local and NFS storage, so keeping them as files not viable.

Two obvious solutions, which is the better (if any) way of ensuring freed blob table storage:

On some schedule, update all the rows where date < X days ago set the text and blob columns to null. Does this actually free the blob data storage in the "hidden" table?
Keep these blobs in a separate table and then delete from the table where date < X days ago.

Keeping in good schema form, I don't want to have a blob-table per transaction table as to keep the transaction keys independent, and I don't want to have associative/smart/compound keys in a single blob table.

What mechanism works best for temporal blob storage in MySQL?

Ali · Accepted Answer · 2012-03-06 08:12:34Z

1

I assume that you have InnoDB or NDB storage engine in your database. In this case, by default, you will never give back the disk space after update/delete rows or tables (including BLOB columns).

There is only way to solve the problem. Set

innodb_file_per_table=1

in my.cnf, and you will have separate files for each table. Then, the second way (drop table when it's no needed) will restore storage space.

answered Mar 6, 2012 at 8:12

Ali

2061 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jé Queue Over a year ago

Good info and forgot that on innodb. The isam tables do re-use storage on delete, yes? can I recover space using an isam sub-table with delete (are blobs inlined in those tables?). Low write volume so the table locking should not be an issue.

Ali Over a year ago

For re-use MyISAM storage, run OPTIMIZE TABLE table1, table2... Of course, these tables will be locked all the time.

Jé Queue Over a year ago

But on MyISAM (which I'm considering for this separate blob table) regardless of the table locking, new inserts will eventually fill old deleted records?

Collectives™ on Stack Overflow

BLOB data management in MySQL during update to null?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related