Geek asks and answers

MySql restores blob space with InnoDB

I need to create a table that will contain a large BLOB that will occupy 98% of the row size. However, this BLOB will be temporarily stored in the row and moved to another location, and then the BLOB column will be set to NULL. Can MySQL reuse this space or will it always be part of a string? If it cannot be automatically reused, is there another way (say, an optimization table) that I can use to manually restore this space? If not, I might need to find another solution to handle this particular data set.

+3
source share
1 answer

It looks like you would definitely be better off using Redis, or even just saving your blob to the file system. This is what I recommend; save the data in a file and put the file name in the table. After the asynchronous download in s3 is complete, set to null. If you use a file system optimized for processing large objects, it will always be faster than pasting into a database.

How exactly the drops are saved depends not only on the length of the data, but also on the version of mysql that you are actually using. Deleted rows can always be reused, and when subsequent inserts do not reuse the space, it can be restored using the optimization table. But optimizing a table will take a lock on that table. Thus, you slow down the system a second time.

+1
source

More articles:


All Articles