why does InnoDB keep on growing without for every update?

I have a table which consists of heavy blobs, and I wanted to conduct some tests on it.

I know deleted space is not reclaimed by innodb, so I decided to reuse existing records by updating its own values instead of createing new records.

But I noticed, whether I delete and insert a new entry, or I do UPDATE on existing ROW, InnoDB keeps on growing.

Assuming I have 100 Rows, each Storing 500KB of information, My InnoDB size is 10MB, now when I call UPDATE on all rows (no insert/ no delete), the innodb grows by ~8MB for every run I do. All I am doing is I am storing exactly 500KB of data in each row, with little modification, and size of blob is fixed.

What can I do to prevent this?

I know about optimize table, but I cant do it because on regular usage, the table is going to be 60-100GB big, and runn开发者_运维问答ing optimize will just stall entire server.

But I noticed, whether I delete and insert a new entry, or I do UPDATE on existing ROW, InnoDB keeps on growing.

InnoDB is a multiversion system.

This means that each time you update or delete a row, the previous version of the row gets copied to a special place called a rollback segment. The original record is rewritten with the new data (in case of an UPDATE) or marked as deleted (in case of a DELETE).

If you do a transaction rollback or execute a concurrent SELECT statement (which has to see the previous version since the new one is not yet committed), the data is retrieved from the rollback segment.

The problem with this is that InnoDB tablespace never shrinks. If the tablespace is full, new space is allocated. The space occupied by the rollback information from the commited transactions may be reused too, but if the tablespace is full it will be extended and this is permanent.

There is no supported way to shrink the ibdata* files except backing up the whole database, dropping and recreating the InnoDB files and restoring from the backup.

Even if you use innodb_file_per_table option, the rollback tablespace is still stored in the shared ibdata* files.

Note, though, that space occupied by the tables and space occupied by the tablespace are the different things. Tables can be shrunk by running OPTIMIZE and this will make them more compact (within the tablespace), but the tablespace itself cannot be shrunk.

You can reduce table size by running Optimize query

OPTIMIZE TABLE table_name;

more information can be found here